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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Tuesday, September 8, 2015

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;

No comments: