Migrating Dictionar managed tablespace to locally managed tablespace

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 160504432 bytes
Fixed Size 453232 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT * FROM V$TABLESPACE;
TS# NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 USERS YES
5 USERSD YES
6 TEMPD YES
7 SMALL YES
8 TEMPL YES
8 rows selected.
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE SMALL READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE USERSD READ ONLY;
Tablespace altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
DROP TABLESPACE TEMPD
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPL ;
Database altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
USERSD
SMALL
TEMPL
7 rows selected.
SQL> DROP TABLESPACE &1;
Enter value for 1: USERS
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERS
Tablespace dropped.
SQL> /
Enter value for 1: USERSD
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERSD
DROP TABLESPACE USERSD
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> DROP TABLESPACE USERSD INCLUDING CONTENTS ;
Tablespace dropped.
SQL> DROP TABLESPACE &1;
Enter value for 1: SAMLL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SAMLL
DROP TABLESPACE SAMLL
*
ERROR at line 1:
ORA-00959: tablespace 'SAMLL' does not exist

SQL> /
Enter value for 1: SMALL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SMALL
Tablespace dropped.
SQL> /
Enter value for 1: TEMPL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE TEMPL
DROP TABLESPACE TEMPL
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
TEMPL
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMP;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
PL/SQL procedure successfully completed.
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD'
3 EXTENT MANAGEMENT DICTIONARY ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> SHOW PARAMETER READ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_threads_per_cpu integer 2
read_only_open_delayed boolean FALSE
thread integer 0
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD' ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-01119: error in creating database file 'TESTD'
ORA-17610: file 'TESTD' does not exist and no size specified
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> ED
Wrote file afiedt.buf
1 CREATE TABLESPACE TESTD
2* DATAFILE 'TESTD' SIZE 1M
3
SQL>
SQL> /
Tablespace created.
SQL> ALTER TABLESPACE TESTD READ ONLY ;
Tablespace altered.

moving-tablespartitions-9i-onwards

-1 Move table from one tablespace to another-- (check for unusable indexes after that).
alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME
;

--2 Move table partition from one tablespace to another
-- (check for unusable indexes and partitoned indexes after that).
alter table $OWNER.$TABLE_NAME
move partition $TAB_PART_NAME tablespace $NEW_TS_NAME
;

--3 Move table subpartition from one tablespace to another
-- (check for unusable indexes, partitioned indexes, and subpartitioned indexes).
alter table $OWNER.$TABLE_NAME
move subpartition $TAB_SUBPART_NAME tablespace $NEW_TS_NAME
;

moving-schema-tablesindexes

-1 Move group of TABLE segments (check for unusable indexes after that)
select 'alter table '||owner||'.'||segment_name||' move '||
decode(segment_type,
'TABLE PARTITION','partition '||partition_name,
'TABLE SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--2 Move group of INDEX segments
select 'alter index '||owner||'.'||segment_name||' rebuild '||
decode(segment_type,
'INDEX PARTITION','partition '||partition_name,
'INDEX SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'INDEX%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--3 List segments that will fail to expand
select /*+ all_rows */ segs.*
from
dba_segments segs,
sys.seg& s,
(select ts#,max(length) m from sys.fet& group by ts#) f
where s.ts#=f.ts# and extsize>m
and segs.header_file=s.file# and segs.header_block=s.block#
;

--4 List of fragmented segments
select segs.*
from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
;

Query to get Used,Free & total Soace of Each Tablespace

SELECT Total.name "Tablespace Name",

Free_space,

(total_space-Free_space) Used_space,

total_space

FROM

(select tablespace_name, sum(bytes/1024/1024) Free_Space

from sys.dba_free_space

group by tablespace_name

) Free,

(select b.name, sum(bytes/1024/1024) TOTAL_SPACE

from sys.v_$datafile a, sys.v_$tablespace B

where a.ts# = b.ts#

group by b.name

) Total

WHERE Free.Tablespace_name = Total.name;

Get Oracle Table Size

First, we must remember that table size is constantly changing as DML adds and removes rows. Also, we have many other factors that determine the "actual" size of a table:

The high water mark for the Oracle table
The number of allocated extents
The amount of spaced reserved on each block for row expansion (PCTFREE)


You need to ask yourself exactly which Oracle table size, you wish to query:

Do you want only the row space consumed? ( select avg_row_len*num_rows from dba_tables)

Do you want to include allocated file space for the table? (select . . . from dba_segments)

Do you want to include un-used extent space? (select . . . from dba_data_files, dba_extents . . )

Do you want to include un-used space up to the high water mark? This may over-estimate the real Oracle table size.

Do you want table sizes for Oracle growth monitoring?