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
instance caging – a sample
Instance caging was introduced in 11.2 and desinged to limit the cpu-usage of an instance.
This makes sence when you run several instances on one machine.
In the past, while one instance makes trouble in cpu usage, all others instance got problems as well 😦
Instance caging based on the oracle resource manager.
It makes no sense to throttle a instance permanent down – the system should be well balanced and this feature should be an emergency button.
here’s an example:
some sql scripting hints
generate dynamic filenames
to generate dynamic spool filenames use (name will be set at the select):
col spoolfile new_val spoolfile select 'log_' ||to_char(sysdate, 'yymmdd_hh24miss' ) spoolfile from dual; spool &spoolfile
to be continued 😉
easy, fast cloning ORACLE_HOME
updated 27.4.2012
Building a master-installation for cloing has multiple benefits
- a new Oracle installation can be done very fast
- all clones from the master are identical – e.g. no one off is missing
- the installation can be scripted very easy, no creation of responsefile necessary
Quick overview
create tarfile from master
extract tarfile to the new location
start clone
that’s all 😉 Read more…
generate dumps – overview
Some nice – important dump options 🙂
and still under constuction
find sga ID
in Unix environments with mutiple instances running within the same os-use (e.g. oracle) you cannot see which memory segment is allocated to which instance.
sqlplus / as sysdba oradebug setmypid oradebug ipc oradebug tracefile_name
the column Shmid shows the dependend memory structures
Read more…
index usage monitoring / invisible indexes
In an application lifecycle some indexes will be added to a table, again and again.
But after a while nobody knows which index is really used or not.
Just delete the index ? hmmm who knows 😉
A simple option is to use index monitoring.
alter index <index_name> monitoring usage;
init.ora – parameters
AWR – automatic workload repository
to change the collection interval and retention policy
exec dbms_workload_repository.modify_snapshot_settings ( retention => 20160, - interval => 30);
example above will change to 14 days (value in minutes) with 30mins snapshots
retention (default 8 days) and interval (default 1 hour) in minutes
be aware – increasing the retention time or increase the frequence of snapshots the sysaux tablespace will increase also !
to show the current settings or the latest snapshot
col systimestamp format a35 col most_recent_snap_time format a25 col snap_interval format a17 col retention format a17 select systimestamp, most_recent_snap_time, snap_interval, retention from wrm$_wr_control where dbid = (select dbid from v$database);
create manually a snapshot
exec dbms_workload_repository.create_snapshot
enable/disable Diagnostic&Tuning pack
invalidate cursor in shared pool
Sometimes it’s necessary to invalidate a specific cursor in shared pool.
In the past you had to flush the complete shared pool – which caues ALL sql statements were flushed 😦
In the package dbms_shared_pool you can flush a SINGLE cursor.
online rebuild index fails ora-8104
When you kill a session which is rebuilding an index this will cause an ora-8104.
SQL> ALTER INDEX I_TEST REBUILD ONLINE ;
ALTER INDEX I_TEST REBUILD ONLINE
*
ERROR at line 1:
ORA-08104: this index object 11040 is being online built or rebuilt
Solution:
mygraeff.com 