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 … Continue reading Move Table to Another Tablespace Online Oracle Database 12c

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. … Continue reading Restore Table from RMAN Backup Oracle 12c

Make Table Read Only Oracle

-- Make Table Read Only [sourcecode language="sql"] alter table <table_name> read only; [/sourcecode] -- Make Table Back to Read Write. [sourcecode language="sql"] alter table <table_name> read write; [/sourcecode]

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 … Continue reading Oracle External Table

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 [code language="sql"] 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 < … Continue reading 10 Largest Tables in Oracle with Size Greater than 1GB

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 … Continue reading Tables Larger than 1 GB in Size in Oracle Database

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');

Create a copy of an Oracle table without copying the data?

Do a "create table" as command without selecting any rows create table abc_new as select * from abc where 1=0;