select SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_segments where segment_name='TABLE_NAME';
SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME';
SUM(BYTES)/1024/1024/1024
-------------------------
228.612793
SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME';
SUM(BYTES)/1024/1024/1024
-------------------------
197.090515
select segment_name,segment_type from dba_segments where owner='ownername' and segment_name='TABLE_NAME';
export and import
expdp \'/ as sysdba\' tables=OMSTG.KVI_SUMMARY_HIST directory=EXPDP_DIR dumpfile=META.dmp logfile=META.log content=METADATA_ONLY
expdp \'/ as sysdba\' tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump.dmp logfile=ods_dump.log
expdp / logfile=metadata dumpfile=metadata parallel=4 compression=all content=METADATA_ONLY
impdp \'/ as sysdba\' tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump.dmp REMAP_TABLESPACE =OSADMIN_DATA01:USER_TEMP;
expdp \'/ as sysdba\' tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump1.dmp logfile=ods_dump1.log
impdp \'/ as sysdba\' tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump1.dmp REMAP_TABLESPACE =USER_TEMP:OSADMIN_DATA01 table_exists_action=truncate;
set lines 200
set pages 0
set feedback off
spool move.sql
select
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace USERS_TEMP' || ';'
from dba_segments
where tablespace_name='ODSADMIN_DATA01'
and segment_type in('TABLE','INDEX') order by owner, segment_type desc, segment_name;
spool off
set lines 200
set pages 0
set feedback off
spool move1.sql
select 'alter index '|| owner||'.'||segment_name || ' rebuild partition '|| partition_name ||' tablespace USERS_TEMP;'
from dba_segments where segment_type ='INDEX PARTITION' and tablespace_name='ODSADMIN_DATA01';
spool off
select table_name,index_name,column_name from user_ind_columns where table_name='SHSHTDTA'
CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/oraclebase/backup/TRACKER/dump';
GRANT READ, WRITE ON DIRECTORY EXPDP_DIR TO sys;
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
expdp \'/ as sysdba\' tables=OMSTG.BASE_ASSIGNMENTS directory=EXPDP_DEV_DIR dumpfile=test.dmp logfile=test.log parallel=1
compression=all
___________________________________________________________________
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '||owner||'.'|| table_name || ' move tablespace USERS_TEMP;' from dba_indexes where index_name in (
select segment_name from dba_segments
where tablespace_name='OMSTG_HIST' and segment_type='INDEX'and segment_name='KVI_SUMMARY_HIST');
spool off
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '|| owner || '.' ||table_name|| ' move LOB ('||COLUMN_NAME ||') STORE AS (tablespace USERS_TEMP);'
from DBA_LOBS where tablespace_name='USERS';
spool off
select table_name,column_name from dba_lobs where table_name='KVI_SUMMARY_HIST';
select segment_name from dba_segments where tablespace_name='OMSTG_HIST' and segment_type='INDEX');
select 'alter index '||owner||'.'||segment_name ||' rebuild subpartition ' || partition_name ||' tablespace USERS_TEMP;'
from dba_segments where tablespace_name='USERS' and segment_type = 'INDEX SUBPARTITION';
select segment_name,partition_name from dba_segments where tablespace_name='OMSTG_HIST' and segment_type = 'INDEX PARTITION';
select
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace USERS_TEMP' || ';'
from dba_segments
where tablespace_name='OMSTG_HIST'
and segment_type in('TABLE','INDEX') order by owner, segment_type desc, segment_name;
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('KVI_SUMMARY_HIST');
SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN ('KVI_SUMMARY_HIST');
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('KVI_SUMMARY_HIST');
SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';
__________________________________________________________________
Index
_____
sqlplus /nolog
connect system/password
select index_name from dba_indexes
where tablespace_name ='EXAMPLE' and
index_name like 'E%'
order by 1 asc;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
set heading off
set echo off
set flush off
set pagesize 9000
set linesize 80
set long 100000
spool rcindscr.sql
select 'select dbms_metadata.get_ddl ("INDEX", "'||index_name||'", "'||owner||'") from dual;' from dba_indexes where tablespace_name = 'EXAMPLE' and index_name like 'E%';
spool off
set heading off
set echo off
set flush off
set pagesize 8000
set linesize 80
set long 3999
set longc 3999
spool indsyntax.sql
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_NAME_IX', 'HR') from dual;
spool off
Partitioned tables
============================================
SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' || PARTITION_NAME|| ' TABLESPACE TESTTBS;' FROM ALL_tab_partitions
WHERE TABLE_OWNER = 'owner_name' AND table_NAME NOT LIKE 'BIN$%';
select 'alter table ' || table_name || ' move partition ' || partition_name|| ' tablespace ODSADMIN_DATA01 parallel 10;' "PARTITION_MOVE_SCRIPT" from dba_tab_partitions where table_name='SALES_PART';
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
SELECT table_name,
partition_name,
subpartition_name,
num_rows
FROM dba_tab_subpartitions where table_name='KVI_SUMMARY_HIST';
column owner format a20;
column SEGMENT_NAME format a20;
column PARTITION_NAME format a20;
column SEGMENT_TYPE format a20
column TABLESPACE_NAME format a20;
column TABLESPACE_NAME format 99999999999999;
SELECT OWNER , SEGMENT_NAME , PARTITION_NAME , SEGMENT_TYPE,TABLESPACE_NAME , BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE '&TABLE_NAME';
Partitioned indexes
============================================
SELECT 'ALTER INDEX '||I.INDEX_NAME||'REBUILD PARITION'|| S.PARTITION_NAME || ' TABLESPACE TESTTBS '
FROM DBA_INDEXES I, DBA_SEGMENTS S
WHERE I.INDEX_NAME = S.SEGMENT_NAME
AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP')
-- AND I.OWNER = 'owner_name'
and S.SEGMENT_NAME='TABLE_NAME';
___________________________________________________________________
connect / as sysdba
set time off timing off echo off pagesize 10000 linesize 150 feed off
SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' || PARTITION_NAME|| ' TABLESPACE OMSTG_TEMP;' FR
OM ALL_tab_partitions
WHERE TABLE_NAME = 'KVI_SUMMARY_HIST' AND table_NAME NOT LIKE 'BIN$%';
___________________________________________________________________________________________
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME|| ' TABLESPACE OMSTG_TEMP;'
FROM dba_tab_subpartitions
WHERE TABLE_NAME ='DISCRETE_HIST' AND table_NAME NOT LIKE 'BIN$%';
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select NAME from V$DATABASE;
select table_owner,table_name,subpartition_name,tablespace_name from dba_tab_subpartitions where table_name='W_RP_MED_ASSIGNMENT_F';
select segment_name,partition_name from dba_segments where segment_name='W_RP_MED_ASSIGNMENT_F';
select partition_name,SUBPARTITION_NAME from dba_tab_subpartitions where table_name='W_RP_MED_ASSIGNMENT_F';
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME|| ' TABLESPACE OMSTG_TEMP;' FROM dba_tab_subpartitions
WHERE TABLE_NAME ='W_RP_MED_ASSIGNMENT_F' AND table_NAME NOT LIKE 'BIN$%';
subpartition;
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select NAME from V$DATABASE;
alter table OMEDW.W_RP_MED_ASSIGNMENT_F enable row movement;
ALTER TABLE OMEDW.W_RP_MED_ASSIGNMENT_F MOVE SUBPARTITION SYS_SUBP169979 TABLESPACE OMSTG_TEMP parallel 6;
ALTER TABLE OMEDW.W_RP_MED_ASSIGNMENT_F MOVE SUBPARTITION SYS_SUBP169980 TABLESPACE OMSTG_TEMP parallel 6;
__________________________________________________________________________
synonyms
SELECT OWNER, CONSTRAINT_NAME , STATUS FROM dba_constraints WHERE
CONSTRAINT_NAME LIKE 'FK1_BRANCH_ITEM_CHANGE_LOG%'
UNION
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS
WHERE
SYNONYM_NAME ='BRANCH_ITEM'
;
select distinct object_type from dba_objects where owner='OMSTG';
SET HEADING OFF
SET pagesize 200
SET linesize 200
select 'ALTER TABLE <owner>.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_lobs where TABLESPACE_NAME='<Tablespace_name>';
SET HEADING OFF
SET PAGESIZE 200
SET LINESIZE 200
select ' ALTER TABLE OMEDW.'||TABLE_NAME||' MOVE TABLESPACE OMSTG_HIST parallel 5 nologging;' from dba_tables where owner='OMEDW';
SET HEADING OFF
SET long 9999
SET linesize 200
select 'alter index <owner>.'||index_name||' from dba_indexes 'rebuild tablespace <Tablespace_name>) online parallel 3 nologging;' where owner='<owner>.';
Index
_____
sqlplus /nolog
connect system/password
select index_name from dba_indexes
where tablespace_name ='EXAMPLE' and
index_name like 'E%'
order by 1 asc;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
set heading off
set echo off
set flush off
set pagesize 9000
set linesize 80
set long 100000
spool rcindscr.sql
select 'select dbms_metadata.get_ddl ("INDEX", "'||index_name||'", "'||owner||'") from dual;' from dba_indexes where tablespace_name = 'EXAMPLE' and index_name like 'E%';
spool off
set heading off
set echo off
set flush off
set pagesize 8000
set linesize 80
set long 3999
set longc 3999
spool indsyntax.sql
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_NAME_IX', 'HR') from dual;
spool off
Partitioned tables
============================================
SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' || PARTITION_NAME|| ' TABLESPACE TESTTBS;' FROM ALL_tab_partitions
WHERE TABLE_OWNER = 'owner_name' AND table_NAME NOT LIKE 'BIN$%';
select 'alter table ' || table_name || ' move partition ' || partition_name|| ' tablespace ODSADMIN_DATA01 parallel 10;' "PARTITION_MOVE_SCRIPT" from dba_tab_partitions where table_name='SALES_PART';
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
SELECT table_name,
partition_name,
subpartition_name,
num_rows
FROM dba_tab_subpartitions where table_name='KVI_SUMMARY_HIST';
column owner format a20;
column SEGMENT_NAME format a20;
column PARTITION_NAME format a20;
column SEGMENT_TYPE format a20
column TABLESPACE_NAME format a20;
column TABLESPACE_NAME format 99999999999999;
SELECT OWNER , SEGMENT_NAME , PARTITION_NAME , SEGMENT_TYPE,TABLESPACE_NAME , BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE '&TABLE_NAME';
Partitioned indexes
============================================
SELECT 'ALTER INDEX '||I.INDEX_NAME||'REBUILD PARITION'|| S.PARTITION_NAME || ' TABLESPACE TESTTBS '
FROM DBA_INDEXES I, DBA_SEGMENTS S
WHERE I.INDEX_NAME = S.SEGMENT_NAME
AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP')
-- AND I.OWNER = 'owner_name'
and S.SEGMENT_NAME='TABLE_NAME';
___________________________________________________________________
connect / as sysdba
set time off timing off echo off pagesize 10000 linesize 150 feed off
SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' || PARTITION_NAME|| ' TABLESPACE OMSTG_TEMP;' FR
OM ALL_tab_partitions
WHERE TABLE_NAME = 'KVI_SUMMARY_HIST' AND table_NAME NOT LIKE 'BIN$%';
___________________________________________________________________________________________
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME|| ' TABLESPACE OMSTG_TEMP;'
FROM dba_tab_subpartitions
WHERE TABLE_NAME ='DISCRETE_HIST' AND table_NAME NOT LIKE 'BIN$%';
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select NAME from V$DATABASE;
select table_owner,table_name,subpartition_name,tablespace_name from dba_tab_subpartitions where table_name='W_RP_MED_ASSIGNMENT_F';
select segment_name,partition_name from dba_segments where segment_name='W_RP_MED_ASSIGNMENT_F';
select partition_name,SUBPARTITION_NAME from dba_tab_subpartitions where table_name='W_RP_MED_ASSIGNMENT_F';
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME|| ' TABLESPACE OMSTG_TEMP;' FROM dba_tab_subpartitions
WHERE TABLE_NAME ='W_RP_MED_ASSIGNMENT_F' AND table_NAME NOT LIKE 'BIN$%';
subpartition;
connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select NAME from V$DATABASE;
alter table OMEDW.W_RP_MED_ASSIGNMENT_F enable row movement;
ALTER TABLE OMEDW.W_RP_MED_ASSIGNMENT_F MOVE SUBPARTITION SYS_SUBP169979 TABLESPACE OMSTG_TEMP parallel 6;
ALTER TABLE OMEDW.W_RP_MED_ASSIGNMENT_F MOVE SUBPARTITION SYS_SUBP169980 TABLESPACE OMSTG_TEMP parallel 6;
__________________________________________________________________________
synonyms
SELECT OWNER, CONSTRAINT_NAME , STATUS FROM dba_constraints WHERE
CONSTRAINT_NAME LIKE 'FK1_BRANCH_ITEM_CHANGE_LOG%'
UNION
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS
WHERE
SYNONYM_NAME ='BRANCH_ITEM'
;
select distinct object_type from dba_objects where owner='OMSTG';
SET HEADING OFF
SET pagesize 200
SET linesize 200
select 'ALTER TABLE <owner>.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_lobs where TABLESPACE_NAME='<Tablespace_name>';
SET HEADING OFF
SET PAGESIZE 200
SET LINESIZE 200
select ' ALTER TABLE OMEDW.'||TABLE_NAME||' MOVE TABLESPACE OMSTG_HIST parallel 5 nologging;' from dba_tables where owner='OMEDW';
SET HEADING OFF
SET long 9999
SET linesize 200
select 'alter index <owner>.'||index_name||' from dba_indexes 'rebuild tablespace <Tablespace_name>) online parallel 3 nologging;' where owner='<owner>.';
