When Oracle Software Directory Is Full
What will happen if the mount point of oracle software is 100% used or full? I guess you may feel panic due to a potentially… Read More »When Oracle Software Directory Is Full
Space Management
How to Take Temporary Tablespaces Offline
Basically, you can't take temporary tablespaces offline.SQL> alter tablespace temp offline;alter tablespace temp offline*ERROR at line 1:ORA-03217: invalid option for alter of TEMPORARY TABLESPACEInstead, you… Read More »How to Take Temporary Tablespaces Offline
How to Move Tables by SQL Script
I usually move some tables for data reorganization, and I found the procedure is pretty routine:Moving the table.Rebuilding indexes related to this table.Analyzing all segments… Read More »How to Move Tables by SQL Script
How to Resolve ORA-23515: materialized views and/or their indices exist in the tablespace
ORA-23515 I got this error when I was trying to drop a tablespace with all options. SQL> drop tablespace example including contents and datafiles cascade… Read More »How to Resolve ORA-23515: materialized views and/or their indices exist in the tablespace
How to Change Oracle Database File Locations
In this post, we don't change parameter file location, because we are seldom able to change $ORACLE_HOME. Oracle always search for pfile or spfile in… Read More »How to Change Oracle Database File Locations
How to Get Tablespace Definition (DDL)
We can compose the execution statement for getting the definition of all tablespaces by the following.SQL> select 'exec dbms_output.put_line(dbms_metadata.get_ddl(''TABLESPACE'',''' || tablespace_name || '''));' ddl from… Read More »How to Get Tablespace Definition (DDL)
How to Find Top IO on Data Files
In my case, I was planning to move some hot data files to a new mount point with SSD disks underneath, so I have to… Read More »How to Find Top IO on Data Files
How to Move Data Files to Another Place
Moving data files from a nearly full mount point to other mount points which could have more space to accommodate growing data files seems a… Read More »How to Move Data Files to Another Place
Must Dos after Moving a Table
Moving a table is actually a reorganization process, The database will copy the original data to the new place. In which, the data will be… Read More »Must Dos after Moving a Table
When Redo Log Files Are All Missing
If your redo logs are all missing, then you have to know you will lose some data and no longer to recover it. Now let's… Read More »When Redo Log Files Are All Missing