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
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 -- Make Table Back to Read Write.
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
Query to find out size of the 10 Largest Tables sitting inside an Oracle database which is greater than 1GB in size
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
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');