Rename LOB Segments, partitions, sub partitions in Oracle 23ai

From Oracle 23ai, you can rename LOB (Large Object) segments, partitions and sub partitions using “ALTER TABLE RENAME LOG” statement. In previous versions of the database to rename LOB segment you need to move it using “ALTER TABLE MOVE” statement.

When renaming a segment make sure the segment is unique within the database, in case of any conflicts you will receive ORA-64233 or ORA-63223 error messages. To make sure segment available in the database you can query ALL_LOBS, ALL_PARTITIONS, ALL_LOG_SUBPARTITIONS.

You can use below syntax/example to rename LOB segment

ALTER TABLE <TABLE_NAME> RENAME LOB (<column_name>) <segment_name> to <new_segment_name>;

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'LOB_TAB1’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- ----------------------- -------------------------
LOB_TAB1        LOB_COL1            LOB_SEG1

SQL> ALTER TABLE lob_tab1 RENAME LOB (lob_col1) log_seg1 TO log_seg1_new;

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'LOB_TAB1’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- ----------------------- --------------------
LOB_TAB1        LOB_COL1            LOB_SEG1_NEW


You can also rename partition, sub partition segments using below examples

For Partition,
ALTER TABLE lob_tab2 RENAME LOB(lob_col2) PARTITION lob_seg2 TO lob_seg2_new;

For Sub partition,
ALTER TABLE lob_tab3 RENAME LOB(LOB_COL3) SUBPARTITION lob_seg3 TO lob_seg3_new;

The new capabilities simplify the management of LOBs, partitions by reducing the overhead in the Oracle Database.

Thanks & Regards,

https://oracleracexpert.com

Convert Partitioned Table to Non-partitioned Table and vice versa

You might come across the situation that you need to convert Partitioned Table to Non-partitioned Table and vice versa.

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

$ 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’;

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