Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

EXEC
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’
);
END;
/

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement

Advertisements

Restore Table from RMAN Backup Oracle 12c

 

One of the amazing features of Oracle Database 12c is that now you can do a logical restore from a physical backup. Before 12c it was not possible to restore a table from a RMAN backup. This feature is amazing speciall when you have a large database and needs to restore a small table. One of the pre-requisites is you must have a Full Backup of the database and also archivelogs after the fully backup must be available. We can proceed to do the restore with below RMAN command

$ rman target /

RECOVER TABLE HR.EMP
UNTIL TIME ‘SYSDATE-0.22’
AUXILIARY DESTINATION ‘/backups/rmanbkp’
REMAP TABLE ‘HR’.’EMP’:’EMP_RECVR’

HR.EMP is the table to be recovered from the RMAN backup

SYSDATE-0.22 is the time till where the table will be rolled back; this is to specify the point in time for recovery: UNTIL TIME, UNTIL SCN, or UNTIL SEQUENCE can be used

Auxiliary Destination is destination where a instance is created for the recovery process

REMAP TABLE clause is used to specify to restore the table with another name

Using this method even individual table paritions can be restored.

Make Table Read Only Oracle

— Make Table Read Only

alter table <table_name> read only;

— Make Table Back to Read Write.

 alter table <table_name> read write;

Oracle External Table

Oracle allows the database to access a flat file as a table. You can have a flat file with a delimiter, it can be accessed from Oracle. This is one of the best methods to load Data into Oracle database. In this excercise we will create an external table from a flat file and then make into a permanent Oracle table with a simple CTAS.

Name of Flat File with Comma Delimiter = CSTF.TXT

eg:

000010,1111000000000,OA,JOHN DOE
000011,111100000001,OA,MARY ANN

— Create Directory where the flat file is copied —

create or replace directory ext_dir as ‘F:\data’;

grant read, write on directory ext_dir to public;

— Create Table from the Flat File —

create table ext_table_csv (
basic_nbr varchar(10),
account_nbr Varchar2(20),
account_type Varchar2(20),
cust_name Varchar2(20)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
badfile ext_dir:’as400.bad’
logfile ext_dir:’as400.log’
fields terminated by ‘,’
missing field values are null
)
location (‘CSTF.TXT’)
)
reject limit unlimited;

— Do a Select to Check —

select * from ext_table_csv;

— Create Internal Table from External table —

create table internal_table as select * from ext_table_csv;

………….And your done importing data from the flat file (text file) into an Oracle database without going through the hassles of creating CTL files from SQL*LOADER etc. I use this method for loading millions of records in a table on a daily basis. And trust me it is a breeze with this method. You can use CSV or DAT files also. Only thing is the delimiter should be consistent for the records to be imported.

10 Largest Tables in Oracle with Size Greater than 1GB

Query to find out size of the 10 Largest Tables sitting inside an Oracle database which is greater than 1GB in size


COLUMN segment_nm FORMAT A45
COLUMN used_size FORMAT A16
Set Lines 999
SELECT
segment_nm,
segment_type,
LPAD( CASE
WHEN bytes < 1024
THEN ROUND( bytes, 2 ) || ' B'
WHEN bytes < POWER( 1024, 2 )
THEN ROUND( ( bytes / 1024 ), 2 ) || ' KB'
WHEN bytes < POWER( 1024, 3)
THEN ROUND( ( bytes / 1024 / 1024 ), 2 ) || ' MB'
WHEN bytes < POWER( 1024, 4 )
THEN ROUND( ( bytes / 1024 / 1024 / 1024 ), 2 ) || ' GB'
ELSE ROUND( ( bytes / 1024 / 1024 / 1024 / 1024 ), 2 ) || ' TB'
END, 15 ) AS used_size,
tablespace_name
FROM
(
SELECT
owner || '.' || LOWER( segment_name ) AS segment_nm,
segment_type,
bytes,
tablespace_name,
DENSE_RANK() OVER ( ORDER BY bytes DESC ) AS dr
FROM
dba_segments
) A
WHERE
dr <= 10 /* top-10 may have more then 10 */
ORDER BY /* lots of ordering in cases of ties */
bytes DESC,
dr ASC,
segment_nm ASC;

Tables Larger than 1 GB in Size in Oracle Database

Sometimes it is required to find the largest tables residing in your database. It helps to identify candidates for table Paritioning. This is the query you can run to identify largest segments in database.

 

select segment_name as “Table Name” , round (bytes/1024/1024/1024) || ‘ GB’ as “Size” from user_segments where bytes > 1073741824 order by bytes desc;

Check size of table in Oracle

SQL Query to check size of Table

 

— Check Size in GB

SELECT bytes/1024/1024/1024
FROM USER_SEGMENTS
WHERE SEGMENT_NAME in (‘TABLE1′,’TABLE2’);

 

— Check Size in MB

SELECT bytes/1024/1024
FROM USER_SEGMENTS
WHERE SEGMENT_NAME in (‘TABLE1′,’TABLE2’);

 

— Check Size in KB

SELECT bytes/1024
FROM USER_SEGMENTS
WHERE SEGMENT_NAME in (‘TABLE1′,’TABLE2’);