Move/Convert objects from BIGFILE To SMALLFILE Tablespace

Option-1:
=======

SHAIKDB>select tablespace_name,bigfile from dba_tablespaces ts;

TABLESPACE_NAME            BIG
------------------------------ ---
SYSTEM                   NO
SYSAUX                   NO
UNDOTBS1               NO
TEMP1                   NO
USERS                   NO
BIGTBS                   YES
LMTB                   NO
LMTB2                   NO

8 rows selected.

SHAIKDB>create table smalltab (col1 number) tablespace bigtbs;

Table created.

SHAIKDB>begin
  2  for i in 1..1000 loop
  3  insert into smalltab values (i);
  4  commit;
  5  end loop
  6  ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SHAIKDB>select count(*) from smalltab;

  COUNT(*)
----------
      1000



SHAIKDB>select segment_name,segment_type,tablespace_name from dba_segments where segment_name='SMALLTAB';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
SMALLTAB                                      TABLE          BIGTBS


SHAIKDB>create tablespace smalltbs datafile size 10m;

Tablespace created.



SHAIKDB>alter database datafile '/u01/app/oracle/mydb/MYDB/datafile/o1_mf_smalltbs_byxm65cz_.dbf' resize 100m;

Database altered.



SHAIKDB>select tablespace_name,bigfile from dba_tablespaces ts;

TABLESPACE_NAME            BIG
------------------------------ ---
SYSTEM                   NO
SYSAUX                   NO
UNDOTBS1               NO
TEMP1                   NO
USERS                   NO
BIGTBS                   YES
SMALLTBS               NO

7 rows selected.


SHAIKDB>alter table smalltab move tablespace smalltbs;

Table altered.

SHAIKDB>select segment_name,segment_type,tablespace_name from dba_segments where segment_name='SMALLTAB';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
SMALLTAB                                      TABLE          SMALLTBS

Option-2:  exp/imp using remap tablespace:

===============

SHAIKDB>create user TBS identified by tbs default tablespace bigtbs;

User created.

SHAIKDB>grant create session,create table to tbs;

Grant succeeded.

SHAIKDB>alter user tbs quota unlimited on bigtbs;

User altered.

SHAIKDB>alter user tbs quota unlimited on smalltbs;

User altered.



SHAIKDB>select username,default_tablespace from dba_users where username='TBS';

USERNAME               DEFAULT_TABLESPACE
------------------------------ ------------------------------
TBS                   BIGTBS



SHAIKDB>conn tbs/tbs;
Connected.

SHAIKDB>show user
USER is "TBS"

SHAIKDB>create table smalltab (col1 number) tablespace bigtbs;

Table created.

SHAIKDB>begin
  2  for i in 1..10000 loop
  3  insert into smalltab values(i);
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SHAIKDB>select count(*) from smalltab;

  COUNT(*)
----------
     10000


SHAIKDB>create index smallidx on smalltab(col1) tablespace bigtbs;

Index created.


SHAIKDB>select segment_name,segment_type,tablespace_name from dba_segments where segment_name like 'SMALL%';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
SMALLIDX                                      INDEX          BIGTBS
SMALLTAB                                      TABLE          BIGTBS


SHAIKDB>create directory data_dir as '/home/oracle/sshaik';

Directory created.


[oracle@collabn1 sshaik]$ expdp directory=data_dir dumpfile=move_tbs.dmp schemas=tbs

Export: Release 11.2.0.1.0 - Production on Tue Sep 8 08:17:37 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=data_dir dumpfile=move_tbs.dmp schemas=tbs
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TBS"."SMALLTAB"                            82.93 KB   10000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/sshaik/move_tbs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:17:58



SHAIKDB>drop user tbs cascade;

User dropped.


[oracle@collabn1 sshaik]$ impdp directory=data_dir dumpfile=move_tbs.dmp remap_tablespace=bigtbs:smalltbs

Import: Release 11.2.0.1.0 - Production on Tue Sep 8 08:19:25 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_dir dumpfile=move_tbs.dmp remap_tablespace=bigtbs:smalltbs
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TBS"."SMALLTAB"                            82.93 KB   10000 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 08:19:32




SHAIKDB>select username,default_tablespace from dba_users where username='TBS';

USERNAME               DEFAULT_TABLESPACE
------------------------------ ------------------------------
TBS                   SMALLTBS

SHAIKDB>select segment_name,segment_type,tablespace_name from dba_segments where segment_name like 'SMALL%';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
SMALLIDX                                      INDEX          SMALLTBS
SMALLTAB                                      TABLE          SMALLTBS



SHAIKDB>select index_name,status from dba_indexes where table_name='SMALLTAB';

INDEX_NAME               STATUS
------------------------------ --------
SMALLIDX               VALID

OPTION-3:dbms_redefinition:

========

options_flag
Indicates the type of redefinition method to use.

• If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys.
  
• If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.
  

SHAIKDB>exec dbms_redefinition.CAN_REDEF_TABLE('TBS','SMALLTAB');
BEGIN dbms_redefinition.CAN_REDEF_TABLE('TBS','SMALLTAB'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "TBS"."SMALLTAB" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1


SHAIKDB>exec dbms_redefinition.CAN_REDEF_TABLE('TBS','SMALLTAB',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

If the table is not a candidate for online redefinition, an error message is raised.

SHAIKDB>select segment_name,segment_type,tablespace_name,bigfile from dba_segments a,v$tablespace b where a.tablespace_name=b.name
  2   and segment_name like 'SMALL%';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME            BIG
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---
SMALLIDX                                      INDEX          BIGTBS                YES
SMALLTAB                                      TABLE          BIGTBS                YES

SHAIKDB>select * from DBA_REDEFINITION_ERRORS ;

no rows selected

Create  a new interim table with the desired changes:
SHAIKDB>create table tbs.smalltab_new tablespace smalltbs as select * from tbs.smalltab where 1=2;

Table created.

SHAIKDB>select count(*) from tbs.smalltab_new;

  COUNT(*)
----------
     0

SHAIKDB>select segment_name,segment_type,tablespace_name,bigfile from dba_segments a,v$tablespace b where a.tablespace_name=b.name and segment_name like 'SMALL%';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME            BIG
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---
SMALLIDX                                      INDEX          BIGTBS                YES
SMALLTAB                                      TABLE          BIGTBS                YES
SMALLTAB_NEW                                      TABLE          SMALLTBS                NO



SHAIKDB>exec dbms_redefinition.start_redef_table(UNAME=>'TBS',ORIG_TABLE=>'SMALLTAB',INT_TABLE=>'SMALLTAB_NEW',OPTIONS_FLAG=>dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.



SHAIKDB>select count(*) from tbs.smalltab_new;

  COUNT(*)
----------
     10000

SHAIKDB>alter index tbs.smallidx rebuild tablespace smalltbs;

SHAIKDB>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME=>'TBS',ORIG_TABLE=>'SMALLTAB',INT_TABLE=>'SMALLTAB_NEW');

PL/SQL procedure successfully completed.

SHAIKDB>select segment_name,segment_type,tablespace_name,bigfile from dba_segments a,v$tablespace b where a.tablespace_name=b.name and segment_name like 'SMALL%';

SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME            BIG
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---
SMALLIDX                                      INDEX          BIGTBS                YES
SMALLTAB_NEW                                      TABLE          BIGTBS                YES
SMALLTAB                                     TABLE          SMALLTBS                NO


SHAIKDB>select * from DBA_REDEFINITION_ERRORS;

no rows selected


SHAIKDB>DROP TABLE TBS.SMALLTAB_NEW;

Table dropped.



SHAIKDB>select segment_name,segment_type,tablespace_name,bigfile from dba_segments a,v$tablespace b where a.tablespace_name=b.name and segment_name like 'SMALL%';
SEGMENT_NAME                                      SEGMENT_TYPE         TABLESPACE_NAME            BIG
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---
SMALLIDX                                      INDEX         SMALLTBS                NO
SMALLTAB                                      TABLE          SMALLTBS                NO


You can query the DBA_REDEFINITION_OBJECTS & DBA_REDEFINITION_ERRORS;