In this article we'll focus on the ORACLE_LOADER and ORACLE_DATAPUMP examples.
1. Setup
Connect to a privileged user and create a new test user called TESTUSER1.
CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA
--DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO testuser1;
Create two new directory objects, making sure TESTUSER1 has READ, WRITE and EXECUTE permissions on them.
More Info: 1Z0-497: Oracle Database 12c Essentials
CREATE OR REPLACE DIRECTORY TMP_DIR1 AS '/tmp/';
GRANT READ, WRITE, EXECUTE ON DIRECTORY TMP_DIR1 TO testuser1;
CREATE OR REPLACE DIRECTORY TMP_DIR2 AS '/tmp/';
GRANT READ, WRITE, EXECUTE ON DIRECTORY TMP_DIR2 TO testuser1;
All other actions will be performed from TESTUSER1.
CONN testuser1/testuser1@//localhost:1521/pdb1
2. Create Hybrid Partitioned Table (ORACLE_LOADER)
Generate some flat files with dummy data for use with external table partitions.
SET MARKUP CSV ON QUOTE ON
SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK OFF PAGESIZE 0
SPOOL /tmp/gbr1.txt
SELECT 'GBR',
object_id,
owner,
object_name
FROM all_objects
WHERE object_id <= 3999
AND rownum <= 1000;
SPOOL OFF
SPOOL /tmp/gbr2.txt
SELECT 'GBR',
object_id,
owner,
object_name
FROM all_objects
WHERE object_id BETWEEN 4000 AND 5999
AND rownum <= 1000;
SPOOL OFF
SPOOL /tmp/ire1.txt
SELECT 'IRE',
object_id,
owner,
object_name
FROM all_objects
WHERE object_id <= 3999
AND rownum <= 1000;
SPOOL OFF
SPOOL /tmp/ire2.txt
SELECT 'IRE',
object_id,
owner,
object_name
FROM all_objects
WHERE object_id BETWEEN 4000 AND 5999
AND rownum <= 1000;
SPOOL OFF
SET MARKUP CSV OFF
SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK ON PAGESIZE 14
The following example creates a hybrid partitioned table. It uses list partitioning, with a single internal partition for USA data, and two external partitions for GBR and IRE data respectively.
--DROP TABLE test_ol_hybrid_part_tab PURGE;
CREATE TABLE test_ol_hybrid_part_tab (
country_code VARCHAR2(3) NOT NULL,
object_id NUMBER NOT NULL,
owner VARCHAR2(128) NOT NULL,
object_name VARCHAR2(128) NOT NULL
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp_dir1
ACCESS PARAMETERS (
FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(country_code, object_id, owner, object_name)
)
REJECT LIMIT UNLIMITED
)
PARTITION BY LIST (country_code) (
PARTITION usa VALUES ('USA'),
PARTITION gbr VALUES ('GBR') EXTERNAL
LOCATION ('gbr1.txt', 'gbr2.txt'),
PARTITION ire VALUES ('IRE') EXTERNAL
DEFAULT DIRECTORY tmp_dir2 LOCATION ('ire1.txt', 'ire2.txt')
);
The EXTERNAL PARTITION ATTRIBUTES clause defines the table-level external table parameters. For external partitions, the EXTERNAL clause is used to define partition-level attributes, such as file locations and non-default directory objects.
The HYBRID column in the CDB|DBA|ALL|USER}_TAABLES views show this is a hybrid table.
COLUMN table_name FORMAT A30
COLUMN hybrid FORMAT A6
SELECT table_name, hybrid FROM user_tables;
TABLE_NAME HYBRID
------------------------------ ------
TEST_OL_HYBRID_PART_TAB YES
SQL>
We only have data in the external partitions at the moment.
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_ol_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
GBR 2000
IRE 2000
SQL>
We can perform DML on the internal partition as normal.
INSERT INTO test_ol_hybrid_part_tab
SELECT 'USA',
object_id,
owner,
object_name
FROM all_objects
WHERE rownum <= 2000;
COMMIT;
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_ol_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
GBR 2000
IRE 2000
USA 2000
SQL>
As you might expect, trying to insert into the external partitions results in an error.
INSERT INTO test_ol_hybrid_part_tab VALUES ('GBR', 9999, 'X', 'X');
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
SQL>
The full list of supported operations can be found in the Hybrid Partitioned Tables document.
3. Create Hybrid Partitioned Table (ORACLE_DATAPUMP)
We create a dump file called "bgr_xt.dmp" containing BGR data. Creating the external table generates the Data Pump dump file. When we drop the table the dump file remains on the database file system, so we can use it for our test.
CREATE TABLE bgr_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY tmp_dir1
LOCATION ('bgr_xt.dmp')
) AS
SELECT 'BGR' AS country_code,
object_id,
owner,
object_name
FROM all_objects
WHERE rownum <= 2000;
DROP TABLE bgr_xt;
The following example creates a hybrid partitioned table. It uses list partitioning, with a single internal partition for USA data, and a data pump external partitions for BGR data.
--DROP TABLE test_dp_hybrid_part_tab PURGE;
CREATE TABLE test_dp_hybrid_part_tab (
country_code VARCHAR2(3) NOT NULL,
object_id NUMBER NOT NULL,
owner VARCHAR2(128) NOT NULL,
object_name VARCHAR2(128) NOT NULL
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY tmp_dir1
)
PARTITION BY LIST (country_code) (
PARTITION usa VALUES ('USA'),
PARTITION bgr VALUES ('BGR') EXTERNAL
LOCATION ('bgr_xt.dmp')
);
The EXTERNAL PARTITION ATTRIBUTES clause defines the table-level Data Pump external table parameters. For external partitions, the EXTERNAL clause is used to define partition-level attributes, such as file locations and non-default directory objects.
The HYBRID column in the CDB|DBA|ALL|USER}_TAABLES views show this is a hybrid table.
COLUMN table_name FORMAT A30
COLUMN hybrid FORMAT A6
SELECT table_name, hybrid FROM user_tables;
TEST_OL_HYBRID_PART_TAB YES
TEST_DP_HYBRID_PART_TAB YES
SQL>
We only have data in the external partitions at the moment.
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_dp_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
BGR 2000
SQL>
We can perform DML on the internal partition as normal.
INSERT INTO test_dp_hybrid_part_tab
SELECT 'USA',
object_id,
owner,
object_name
FROM all_objects
WHERE rownum <= 2000;
COMMIT;
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_dp_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
BGR 2000
USA 2000
SQL>
As you might expect, trying to insert into the external partitions results in an error.
INSERT INTO test_dp_hybrid_part_tab VALUES ('GBR', 9999, 'X', 'X');
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
SQL>
The full list of supported operations can be found in the Hybrid Partitioned Tables document.
4. Add/Drop Partitions
We drop the partition for the IRE data from the TEST_OL_HYBRID_PART_TAB table.
ALTER TABLE test_ol_hybrid_part_tab DROP PARTITION ire;
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_ol_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
GBR 2000
USA 2000
SQL>
We add back the partition for the IRE data.
ALTER TABLE test_ol_hybrid_part_tab ADD PARTITION ire VALUES ('IRE') EXTERNAL
DEFAULT DIRECTORY tmp_dir2 LOCATION ('ire1.txt', 'ire2.txt');
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_ol_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
GBR 2000
IRE 2000
USA 2000
SQL>
We drop the partition for the BGR data from the TEST_DP_HYBRID_PART_TAB table.
ALTER TABLE test_dp_hybrid_part_tab DROP PARTITION bgr;
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_dp_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
USA 2000
SQL>
We add back the partition for the BGR data.
ALTER TABLE test_dp_hybrid_part_tab ADD PARTITION bgr VALUES ('BGR') EXTERNAL
DEFAULT DIRECTORY tmp_dir2 LOCATION ('bgr_xt.dmp');
COLUMN country_code FORMAT A12
SELECT country_code, COUNT(*) as amount
FROM test_dp_hybrid_part_tab
GROUP BY country_code
ORDER BY country_code;
COUNTRY_CODE AMOUNT
------------ ----------
BGR 2000
USA 2000
SQL>
The operations on the internal partitions are as you would expect to regular partitioned tables. The full list of supported operations can be found in the Hybrid Partitioned Tables document.
5. Restrictions
The standout restrictions are as follows.
◉ You can only use RANGE or LIST partitioning.
◉ There is no support for REFERENCE and SYSTEM partitioning.
Most of the other restrictions are what you would expect for the various types of partitions.
The full list of restrictions can be found in the Hybrid Partitioned Tables document.
Source: oracle-base.com
Posts
0 comments:
Post a Comment