You can RUN below command to identify weather the Table is partitioned or not
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;
TABLE_NAME PAR
--------------------- ---
EMP YES
I suggest using data pump to convert Partitioned Table to Non-partitioned Table. Below are the steps
Take a backup of the table that you want to convert to NON-Partitioned table
$ expdp SCOTT/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=emp_expdp.log tables=EMP
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . exported "SCOTT"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . exported "SCOTT"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Import the table into TEST schema using PARTITIONS_OPTIONS parameter
TABLE_NAME PAR
---------------------- ---
EMP NO
DROP PARTATION
If you need to drop the partition use below command
Method 1: Drop the specific partition
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;
Method 2: DELETE all rows part of partition and remove partition
SQL> DELETE FROM EMP PARTITION (YEAR_2001;);
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;
Method 3:- Drop partition and update indexes
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001 UPDATE INDEXES;
ADD/MODFY partition
You can use ALTER TABLE ADD PARTITION, MODIFY PARTITION statement on a table
For ex:-
SQL>ALTER TABLE EMP MODIFY PARTITION BY RANGE (JOIN_DATE)
(PARTITION YEAR_2001 VALUES LESS THAN (to_date('01-JAN-2002','dd-mon-yyyy')),
PARTITION YEAR_2002 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')));
$ impdp TEST/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=imp_emp.log remap_schema=SCOTT:TEST partition_options=merge
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . imported "TEST"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . imported "TEST"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sun July 5 08:44:21 2020 elapsed 0 00:00:10
This command will merge all partitions while importing as single table.
RUN below command to verify and you should see the out as
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . imported "TEST"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . imported "TEST"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sun July 5 08:44:21 2020 elapsed 0 00:00:10
This command will merge all partitions while importing as single table.
RUN below command to verify and you should see the out as
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;
TABLE_NAME PAR
---------------------- ---
EMP NO
DROP PARTATION
If you need to drop the partition use below command
Method 1: Drop the specific partition
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;
Method 2: DELETE all rows part of partition and remove partition
SQL> DELETE FROM EMP PARTITION (YEAR_2001;);
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;
Method 3:- Drop partition and update indexes
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001 UPDATE INDEXES;
ADD/MODFY partition
You can use ALTER TABLE ADD PARTITION, MODIFY PARTITION statement on a table
For ex:-
SQL>ALTER TABLE EMP MODIFY PARTITION BY RANGE (JOIN_DATE)
(PARTITION YEAR_2001 VALUES LESS THAN (to_date('01-JAN-2002','dd-mon-yyyy')),
PARTITION YEAR_2002 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')));
SQL> ALTER TABLE EMP ADD PARTITION YEAR_2003 VALUES LESS THAN ( '01-JAN-2004' ) ;
Please refer oracle documentation for detailed PARTITIONS options and methods..etc
Thanks
http://oracleracexpert.com, Oracle ACE
I think this is often associated with informative and knowledgeable posts. However, I prefer to thank you for the efforts you have created in penning this blog. Visit here: SEO Company.
ReplyDeleteYou have a great website. Shared on Delicious and sent to a couple of buddies. Thank you!Ableton Live Crack
ReplyDeleteThis comment has been removed by the author.
ReplyDelete