SQL> alter table SCOTT.DEPT move tablespace users;
Table altered.
SQL> alter index SCOTT.PK_DEPT rebuild tablespace users;
Index altered.
You can move tables and indexes with the database opened, but these objects will be locked while being moved so this might be a problem with big objects heavily used. Also, after moving a table you have to rebuild all its indexes because they become invalid after the table movement.
You can also move partitioned tables and indexes but you have to move the partitions (or subpartitions) because the partitioned tables and indexes are placed in no tablespace:
SQL> select distinct TABLE_NAME from DBA_TAB_PARTITIONS where TABLE_OWNER='SH';
TABLE_NAME
------------------------------------------------------------------------------------------
COSTS
SALES
SQL> select distinct SUBPARTITION_COUNT from DBA_TAB_PARTITIONS where TABLE_OWNER='SH';
SUBPARTITION_COUNT
------------------
0
SQL> column TABLE_OWNER format a30
SQL> column TABLE_NAME format a30
SQL> select TABLE_OWNER, TABLE_NAME, PARTITION_NAME from DBA_TAB_PARTITIONS where
TABLE_OWNER='SH' and TABLE_NAME='SALES' order by TABLE_OWNER, TABLE_NAME, PARTITION_POSITION;
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SH SALES SALES_1995
SH SALES SALES_1996
SH SALES SALES_H1_1997
SH SALES SALES_H2_1997
...
SQL> alter table SH.SALES move partition SALES_1995 tablespace example;
Table altered.
SQL> select distinct INDEX_NAME from DBA_IND_PARTITIONS where INDEX_OWNER='SH';
INDEX_NAME
------------------------------------------------------------------------------------------
SALES_CHANNEL_BIX
SALES_CUST_BIX
SALES_PROMO_BIX
SALES_PROD_BIX
COSTS_PROD_BIX
SALES_TIME_BIX
COSTS_TIME_BIX
7 rows selected.
SQL> select distinct SUBPARTITION_COUNT from DBA_IND_PARTITIONS where INDEX_OWNER='SH';
SUBPARTITION_COUNT
------------------
0
SQL> select INDEX_OWNER, INDEX_NAME, PARTITION_NAME from DBA_IND_PARTITIONS where
INDEX_OWNER='SH' and INDEX_NAME like 'SALES%'
order by INDEX_OWNER, INDEX_NAME, PARTITION_POSITION;
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SH SALES_CHANNEL_BIX SALES_1995
SH SALES_CHANNEL_BIX SALES_1996
SH SALES_CHANNEL_BIX SALES_H1_1997
SH SALES_CHANNEL_BIX SALES_H2_1997
...
SQL> alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_1995 tablespace example;
Index altered.
As long as you cannot move one whole schema (or even a single partitioned table or index) with just one SQL sentence, you can generate the SQL sentences needed to move objects this way:
SQL> select 'alter table '||OWNER||'.'||TABLE_NAME||' move tablespace example;'
from dba_tables where TABLESPACE_NAME='USERS' order by OWNER, TABLE_NAME;
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVETABLESPACEEXAMPLE;'
------------------------------------------------------------------------------------
alter table SCOTT.BONUS move tablespace example;
alter table SCOTT.DEPT move tablespace example;
alter table SCOTT.EMP move tablespace example;
alter table SCOTT.SALGRADE move tablespace example;
4 rows selected.
SQL> select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild tablespace example;'
from dba_indexes where TABLESPACE_NAME='USERS' order by OWNER, INDEX_NAME;
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDTABLESPACEEXAMPLE;'
------------------------------------------------------------------------------------
alter index SCOTT.PK_DEPT rebuild tablespace example;
alter index SCOTT.PK_EMP rebuild tablespace example;
2 rows selected.
SQL> select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace example;'
from DBA_TAB_PARTITIONS where TABLE_OWNER='SH' and TABLE_NAME='SALES'
order by TABLE_OWNER, TABLE_NAME, PARTITION_POSITION;
'ALTERTABLE'||TABLE_OWNER||'.'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE;'
------------------------------------------------------------------------------------
alter table SH.SALES move partition SALES_1995 tablespace example;
alter table SH.SALES move partition SALES_1996 tablespace example;
alter table SH.SALES move partition SALES_H1_1997 tablespace example;
alter table SH.SALES move partition SALES_H2_1997 tablespace example;
...
SQL> select 'alter index '||INDEX_OWNER||'.'||INDEX_NAME||' rebuild partition '||PARTITION_NAME||' tablespace example;'
from DBA_IND_PARTITIONS where INDEX_OWNER='SH' and INDEX_NAME like 'SALES%'
order by INDEX_OWNER, INDEX_NAME, PARTITION_POSITION;
'ALTERINDEX'||INDEX_OWNER||'.'||INDEX_NAME||'REBUILDPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE;'
-------------------------------------------------------------------------------------------------
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_1995 tablespace example;
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_1996 tablespace example;
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_H1_1997 tablespace example;
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_H2_1997 tablespace example;
...
And if you need to know information about tablespaces, indexes and datafiles you can get it like this:
SQL> select i.TABLE_OWNER, t.TABLE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and t.TABLESPACE_NAME in ('USERS')
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME;
TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME TABLESPACE_NAME
------------- ------------- -------------- ------------- ------------------
SCOTT DEPT SCOTT PK_DEPT USERS
SCOTT EMP SCOTT PK_EMP USERS
SQL> select i.TABLE_OWNER, t.TABLE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and t.TABLESPACE_NAME in ('USERS') and i.TABLESPACE_NAME not in ('USERS')
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME;
no rows selected
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, t.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.TABLESPACE_NAME in ('USERS')
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME;
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
SCOTT PK_DEPT SCOTT DEPT USERS
SCOTT PK_EMP SCOTT EMP USERS
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, t.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.TABLESPACE_NAME in ('USERS') and t.TABLESPACE_NAME not in ('USERS')
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME;
no rows selected
SQL> select i.TABLE_OWNER, t.TABLE_NAME, pt.TABLESPACE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.PARTITION_NAME, pi.TABLESPACE_NAME
from dba_tables t, dba_indexes i, DBA_IND_PARTITIONS pi, (select distinct TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME from DBA_TAB_PARTITIONS) pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pt.TABLESPACE_NAME in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME, pi.PARTITION_POSITION;
TABLE_OWNER TABLE_NAME TABLESPACE_NAME INDEX_OWNER INDEX_NAME PARTITION_NAME TABLESPACE_NAME
------------ ------------- ----------------- ------------- -------------------- ----------------- -----------------
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_1995 EXAMPLE
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_1996 EXAMPLE
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_H1_1997 EXAMPLE
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_H2_1997 EXAMPLE
...
SQL> select i.TABLE_OWNER, t.TABLE_NAME, pt.TABLESPACE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.PARTITION_NAME, pi.TABLESPACE_NAME
from dba_tables t, dba_indexes i, DBA_IND_PARTITIONS pi, (select distinct TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME from DBA_TAB_PARTITIONS) pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pt.TABLESPACE_NAME in ('EXAMPLE') and pi.TABLESPACE_NAME not in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME, pi.PARTITION_POSITION;
no rows selected
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.TABLESPACE_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_NAME, pt.TABLESPACE_NAME
from dba_tables t, dba_indexes i, (select distinct INDEX_OWNER, INDEX_NAME, TABLESPACE_NAME from DBA_IND_PARTITIONS) pi, DBA_TAB_PARTITIONS pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pi.TABLESPACE_NAME in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_POSITION;
INDEX_OWNER INDEX_NAME TABLESPACE_NAME TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------ -------------------- ----------------- ------------- ------------- ----------------- -----------------
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_1995 EXAMPLE
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_1996 EXAMPLE
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_H1_1997 EXAMPLE
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_H2_1997 EXAMPLE
...
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.TABLESPACE_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_NAME, pt.TABLESPACE_NAME
from dba_tables t, dba_indexes i, (select distinct INDEX_OWNER, INDEX_NAME, TABLESPACE_NAME from DBA_IND_PARTITIONS) pi, DBA_TAB_PARTITIONS pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pi.TABLESPACE_NAME in ('EXAMPLE') and pt.TABLESPACE_NAME not in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_POSITION;
no rows selected
SQL> select d.TABLESPACE_NAME, d.TMBYTES, e.UMBYTES, round(e.UMBYTES/d.TMBYTES*100) as PERCENTAGE from
(select TABLESPACE_NAME, round(sum(BYTES)/1024/1024) as TMBYTES
from dba_data_files group by TABLESPACE_NAME) d, (select TABLESPACE_NAME, round(sum(BYTES)/1024/1024) as UMBYTES from dba_extents group by TABLESPACE_NAME) e
where d.TABLESPACE_NAME=e.TABLESPACE_NAME order by d.TABLESPACE_NAME;
TABLESPACE_NAME TMBYTES UMBYTES PERCENTAGE
------------------------------ ---------- ---------- ----------
EXAMPLE 100 68 68
SYSAUX 240 232 97
SYSTEM 480 473 99
UNDOTBS1 30 3 10
USERS 5 3 60
SQL> select OWNER, TABLESPACE_NAME, round(sum(BYTES)/1024/1024) as MBYTES from dba_segments group by OWNER, TABLESPACE_NAME order by OWNER, TABLESPACE_NAME;
OWNER TABLESPACE_NAME MBYTES
--------------- ------------------------------ ----------
CTXSYS SYSAUX 5
DBSNMP SYSAUX 2
DMSYS SYSAUX 0
EXFSYS SYSAUX 4
HR EXAMPLE 2
IX EXAMPLE 2
MDSYS SYSAUX 32
OE EXAMPLE 6
OE USERS 3
OLAPSYS SYSAUX 16
ORDSYS SYSAUX 1
OUTLN SYSTEM 1
PM EXAMPLE 3
SCOTT USERS 0
SH EXAMPLE 56
SYS SYSAUX 61
SYS SYSTEM 457
SYS UNDOTBS1 3
SYSMAN SYSAUX 50
SYSTEM SYSAUX 7
SYSTEM SYSTEM 15
TSMSYS SYSAUX 0
WMSYS SYSAUX 7
XDB SYSAUX 49
24 rows selected.
No comments:
Post a Comment