115 - [Oracle 19C] External table with PARTITIONS

 

 

External table with PARTITIONS

show user
show con_name
/*
USER is "HR"
CON_NAME 
------------------------------
ORCLPDB
*/

 

 

 

CREATE TABLE sales_by_country
      (trans_id number, 
       trans_amount number,
       country varchar2(3))
   PARTITION BY LIST (country)
      (PARTITION c1 VALUES ('USA'),
       PARTITION c2 VALUES ('JOR')
      );
/*
Table SALES_BY_COUNTRY created.
*/



SELECT table_name, partition_name, high_value, partition_position
FROM   user_tab_partitions
WHERE TABLE_NAME='SALES_BY_COUNTRY'
ORDER BY table_name, partition_name;
/*
TABLE_NAME           PARTITION_NAME       HIGH_VALUE   PARTITION_POSITION
-------------------- -------------------- ------------ ------------------
SALES_BY_COUNTRY     C1                   'USA'                         1
SALES_BY_COUNTRY     C2                   'JOR'                         2
*/



insert into sales_by_country values (1,500,'USA');
insert into sales_by_country values (2,600,'USA');
insert into sales_by_country values (3,400,'USA');
insert into sales_by_country values (4,500,'JOR');
insert into sales_by_country values (5,600,'JOR');
insert into sales_by_country values (6,400,'JOR');
COMMIT;
/*
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
Commit complete.
*/



select * from sales_by_country;
/*
  TRANS_ID TRANS_AMOUNT COU
---------- ------------ ---
         1          500 USA
         2          600 USA
         3          400 USA
         4          500 JOR
         5          600 JOR
         6          400 JOR

6 rows selected. 
*/

 

 

 

# NOW DO THIS

insert into sales_by_country values (7,3500,'UAE');
/*
Error starting at line : 71 in command -
insert into sales_by_country values (7,3500,'UAE')
Error at Command Line : 71 Column : 13
Error report -
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 -  "inserted partition key does not map to any partition"
*Cause:    An attempt was made to insert a record into, a Range or Composite
           Range object, with a concatenated partition key that is beyond
           the concatenated partition bound list of the last partition -OR-
           An attempt was made to insert a record into a List object with
           a partition key that did not match the literal values specified
           for any of the partitions.
*Action:   Do not insert the key. Or, add a partition capable of accepting
           the key, Or add values matching the key to a partition specification
*/



SELECT country, COUNT(1) FROM sales_by_country GROUP BY country;
/*
COUNTRY      COUNT(1)
---------- ----------
USA                 3
JOR                 3
*/



# YOU CAN DO THIS
SELECT COUNT(1) FROM sales_by_country PARTITION  (c1);
SELECT COUNT(1) FROM sales_by_country PARTITION  (c2);
/*
  COUNT(1)
----------
         3


  COUNT(1)
----------
         3
*/



# LET US ADD NEW PARTITION

ALTER TABLE sales_by_country
ADD PARTITION C3 VALUES('UAE');
/*
Table SALES_BY_COUNTRY altered.
*/



insert into sales_by_country values (7,3500,'UAE');
COMMIT;
/*
1 row inserted.
Commit complete.
*/



SELECT COUNT(1) FROM sales_by_country PARTITION  (c3);
/*
  COUNT(1)
----------
         1
*/

 

 

 

# 파티션 확인

SELECT table_name, partition_name, high_value, partition_position
FROM   user_tab_partitions
WHERE TABLE_NAME='SALES_BY_COUNTRY'
ORDER BY table_name, partition_name;
/*
TABLE_NAME           PARTITION_NAME       HIGH_VALUE   PARTITION_POSITION
-------------------- -------------------- ------------ ------------------
SALES_BY_COUNTRY     C1                   'USA'                         1
SALES_BY_COUNTRY     C2                   'JOR'                         2
SALES_BY_COUNTRY     C3                   'UAE'                         3
*/



SELECT * FROM sales_by_country;
/*
  TRANS_ID TRANS_AMOUNT COUNTRY   
---------- ------------ ----------
         1          500 USA       
         2          600 USA       
         3          400 USA       
         4          500 JOR       
         5          600 JOR       
         6          400 JOR       
         7         3500 UAE 
*/

 

 

 

# NOW LET US DO 3 FILES .DAT EACH FILE CONTAIN COUNTRY
# PUT THEM IN /u01/app/oracle

/* FILE 1: USA.dat
1,500,USA
2,600,USA
3,400,USA
*/

/* FILE 2: JOR.dat
4,500,JOR
5,600,JOR
6,400,JOR
*/

/* FILE 3: UAE.dat
7,3500,UAE
*/



[oracle@test pump]$ pwd
/u01/app/oracle/pump

[oracle@test pump]$ ll
합계 56
....
-rw-r--r-- 1 oracle oinstall    30 12월 25 20:01 JOR.dat
-rw-r--r-- 1 oracle oinstall    11 12월 25 20:02 UAE.dat
-rw-r--r-- 1 oracle oinstall    30 12월 25 20:01 USA.dat
-rwxr-xr-x 1 oracle oinstall    65 12월 20 22:43 old_emp_data.csv
....
*/


# first thing make sure that  DIRECTORY EMP_DIR exists

SELECT * FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME='EMP_DIR'; 
/*
OWNER      DIRECTORY_NAME  DIRECTORY_PATH            ORIGIN_CON_ID
---------- --------------- ------------------------- -------------
SYS        EMP_DIR         /u01/app/oracle/pump                  3
*/

 

 

 

CREATE TABLE  sales_by_country_ext
     (trans_id number, 
       trans_amount number,
       country varchar2(3)
      )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY EMP_DIR
      ACCESS PARAMETERS
        (RECORDS DELIMITED BY NEWLINE
         FIELDS TERMINATED BY ','
        )
     )
     reject limit unlimited
   PARTITION BY LIST (country)
   (
   PARTITION c1 VALUES ('USA') LOCATION ('USA.dat'),
   PARTITION c2 VALUES ('JOR') LOCATION ('JOR.dat'),
   PARTITION c3 VALUES ('UAE') LOCATION ('UAE.dat')
   )
/*
Table SALES_BY_COUNTRY_EXT created.
*/



SELECT * FROM sales_by_country_ext;
/*
  TRANS_ID TRANS_AMOUNT COUNTRY   
---------- ------------ ----------
         1          500 USA       
         2          600 USA       
         3          400 USA       
         4          500 JOR       
         5          600 JOR       
         6          400 JOR       
         7         3500 UAE       

7 rows selected. 
*/



SELECT table_name, partition_name, location, directory_name
FROM all_XTERNAL_LOC_PARTITIONS
where table_name='SALES_BY_COUNTRY_EXT';
/*
TABLE_NAME           PARTITION_NAME       LOCATION   DIRECTORY_NAME 
-------------------- -------------------- ---------- ---------------
SALES_BY_COUNTRY_EXT C1                   USA.dat                   
SALES_BY_COUNTRY_EXT C2                   JOR.dat                   
SALES_BY_COUNTRY_EXT C3                   UAE.dat
*/