Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, October 9, 2012

ALTER TABLE MOVE

You can use dbms_redef for reorg:-

Redefining Tables Online

In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:
  • Improve the performance of queries or DML
  • Accommodate application changes
  • Manage storage
Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.
When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.
Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

 but for people looking for quick fix or short cuts here it is..

Re-org or move objects around dirty way:-

To move TABLES:

SELECT'ALTER TABLE '||OWNER||'.'||TABLE_NAME ||' MOVE TABLESPACE APPS_TS_TX_DATA;'
 FROM DBA_TABLES WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'
 and owner='JTF'

To move indexes:
SELECT'ALTER INDEX '||OWNER||'.'||INDEX_NAME ||' REBUILD TABLESPACE APPS_TS_TX_IDX;'
 FROM DBA_INDEXES WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'


To move LOB SEGMENT:
SELECT 'ALTER TABLE '||OWNER ||'.'|| TABLE_NAME || ' MOVE LOB('|| COLUMN_NAME ||') STORE AS (TABLESPACE APPS_TS_TX_DATA);'
 FROM DBA_LOBS WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'


To move TABLE SUBPARTITION:
 select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION '||SUBPARTITION_NAME ||' TABLESPACE APPS_TS_TX_DATA;'
    FROM DBA_TAB_SUBPARTITIONS WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'



To move LOB SUB PARTITION:

SCRIPT:-
SELECT 'ALTER TABLE '||TABLE_OWNER ||'."'|| TABLE_NAME || '" MOVE SUBPARTITION '|| SUBPARTITION_NAME ||'  TABLESPACE APPS_TS_TX_DATA LOB ('||
COLUMN_NAME||') STORE AS (TABLESPACE APPS_TS_TX_DATA);'
 FROM DBA_LOB_SUBPARTITIONS WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'

EX:-
   ALTER TABLE FPA."AW$FPAPJP" MOVE SUBPARTITION SYS_IL_SUBP127 TABLESPACE APPS_TS_TX_DATA  LOB(AWLOB) STORE AS (TABLESPACE APPS_TS_TX_DATA);


If there are many objects to move:

Datapump:

expdp \"/ as sysdba\" directory=TABPSLACE_exp dumpfile=exp_tablespace.dmp logfile=mydb_exp.log tablespaces='APPS_TS_TX_DATA_OLD'

Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=tablespace_exp dumpfile=mydb_tablespace.dmp logfile=mydb_exp.log tablespaces=APPS_TS_TX_DATA_OLD
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 179.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/RLS_POLICY
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS



Import it with REMAP_TABLESPACE or as desired...

No comments: