reorganize tables or tablespaces online
sometimes it makes sense to reorganize tables.
for example changing from smallfile to bigfile tablespaces, or a lot of deleted datas.
to do this you can do a create table as select, expdp/imdp and so on.
this requires a downtime and the dependend objects (e.g. indexes ) needs to be created manually too.
with redefinition its possible to do this online without a downtime – just a short exclusive lock is required
you can also modify during these steps the new table structure and so on. To do this you need to run several steps.. but it works fine (see my blog https://mygraeff.wordpress.com/2011/04/30/reorganize-tables-online-with-dbms_redefinition/#more-91 )
starting with Oracle 12.2 a new functionality came with dbms_redefinition
dbms_redefinition.REDEF_TABLE
redef_table copies the existing table and the dependent objects as well with just one command !
this makes it quite easy to move an existing table!
REDEF_TABLE creates a copy of the existing table, creates the dependend objects – all online without changing the original table. after the copy is done a exclusive lock is required to change the names from original to the copied table.
No dropping of the original table is required. this is all mananaged by REDEF_TABLE!
the challenge in my case was to change from smallfile to bigfile tablespaces, a lot of small and large tables. Due to to large downtime was possible, REDEF_TABLE was the best solution to get this done.
DBMS_REDEFINITION.REDEF_TABLE (
uname IN VARCHAR2,
tname IN VARCHAR2,
table_compression_type IN VARCHAR2 := NULL,
table_part_tablespace IN VARCHAR2 := NULL,
index_key_compression_type IN VARCHAR2 := NULL,
index_tablespace IN VARCHAR2 := NULL,
lob_compression_type IN VARCHAR2 := NULL,
lob_tablespace IN VARCHAR2 := NULL,
lob_store_as IN VARCHAR2 := NULL,
refresh_dep_mviews IN VARCHAR2 := 'N',
dml_lock_timeout IN PLS_INTEGER := NULL);
this shows the options you have with REDEF_TABLE, you can change tablespace where to copy the new table to, also seperate indexes and lobs or lob-storage (basicfile / securefile) or compression
instead of a sql script I created a procedure in the database.
the latest release of this procedure and additional scripts can be found on github
create procedure
required parameters :
– SOURCETBLSP
tablespace where the table(s) are located
– TARBETTBLSP
destination tablespace of the new tables, you can use the same tablespace also
– TARGETINDEXTBLSP
you can use the same tablespace as the tables or a seperate tablespace
– TARGETLOBTBLSP
in which tablespace the LOBS should be stored – If you have no LOBS use the same tablespace as TARGETTBLS
optional parameters :
– SOURCEOWNER
default is % (all), you can also specify schema, wildcards (%) allowed
– SOURCETABLE
default is % (all), wildcards (%) can be used also
– LOBSTORE
default is NULL – no change
BASICFILE or SECUREFILE is possible
– TABLE_COUNT
default is 10 – so you have the control how much tables should be moved
some examples how to use the procedure:
if you run this in sqlplus, set serveroutput on timing on
— example to move all tables from source to target tablespace and store as securefile, only 1 table
— options LOBSTORE : BASICFILE, SECUREFILE
exec move_tables_2_tablespace ( –
SOURCETBLSP => ‘TBLSPA’, –
TARGETTBLSP => ‘TBLSPB’, –
TARGETINDEXTBLSP => ‘TBLSPBLOB’, –
TARGETLOBTBLSP => ‘TBLSPBLOB’, –
LOBSTORE => ‘SECUREFILE’, –
TABLE_COUNT => 1 –
)
— example to move 300 tables
exec move_tables_2_tablespace ( –
SOURCETBLSP => ‘TBLSPA’, –
TARGETTBLSP => ‘TBLSPB’, –
TARGETINDEXTBLSP => ‘TBLSPB’, –
TARGETLOBTBLSP => ‘TBLSPBLOB’, –
LOBSTORE => ‘SECUREFILE’, –
TABLE_COUNT => 300 –
)
— example to move schema based objects without modifing LOBSTORAGE
exec move_tables_2_tablespace ( –
SOURCEOWNER => ‘RGF’, –
SOURCETBLSP => ‘TBLSPA’, –
TARGETTBLSP => ‘TBLSPB’, –
TARGETINDEXTBLSP => ‘TBLSPB’, –
TARGETLOBTBLSP => ‘TBLSPBLOB’, –
TABLE_COUNT => 300 –
)
— example to move specific tables only
exec move_tables_2_tablespace ( –
SOURCEOWNER => ‘RGF’, –
SOURCETABLE => ‘TESTTAB%’, –
SOURCETBLSP => ‘TBLSPA’, –
TARGETTBLSP => ‘TBLSPB’, –
TARGETINDEXTBLSP => ‘TBLSPB’, –
TARGETLOBTBLSP => ‘TBLSPBLOB’, –
TABLE_COUNT => 300 –
)
after the source tablespace is empty you can easy change the interims tablespace to the original name
If you don’t use OMF ( oracle managed files) you need to modify the datafile names also ( if this is necessary of desired).
Tested this with Oracle 19c in container and non-container databases.
statistics are also gathered (no force option)
Feedback or optimizations always welcome
Roland Gräff
oracle documentation dbms_redefinition
github
https://github.com/mygraeff/oracle/blob/main/storage/reorg_tablespace_online.sql
mygraeff.com 