Archived Log File

When Oracle Software Directory Is Full

Posted on
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 stopping database. Check Archived Log Destination At this moment, you should make sure the destinations of archived logs and data files are in the same directory with software or not. […]
Temp File

How to Take Temporary Tablespaces Offline

Posted on
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 should take each temp file offline. SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ offline;Database altered.If the temporary tablespace is not the default one. You can take all temp files offline in the […]
SQL

How to Move Tables by SQL Script

Posted on
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 related to this table.So I wrote a SQL script to handle this automatically, one table at a time.[oracle@test ~]$ cat alter_table_move.sqlset escape on;accept table_owner char prompt ‘Table Owner: ‘;accept table_name […]
Tablespace

How to Resolve ORA-23515: materialized views and/or their indices exist in the tablespace

Posted on
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 constraints; drop tablespace example including contents and datafiles cascade constraints * ERROR at line 1: ORA-23515: materialized views and/or their indices exist in the tablespace Let’s see the content of […]
File

How to Change Oracle Database File Locations

Posted on
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 several specific location in $ORACLE_HOME to startup the instance.If you really want to change the location of parameter file because of $ORACLE_HOME has been changed, please modify /etc/oratab as well.So we don’t change parameter […]
Performance

How to Find Top IO on Data Files

Posted on
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 know what data files are really hot in terms of current disk IO.Here I take advantage of Statspack’s data to retrieve valuable information of IO on data files.# sqlplus “/ […]
Data File

How to Move Data Files to Another Place

Posted on
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 common job for DBA. Here I simplify the scenario to a basic procedure that can be easy to follow. Some data files will be moved to the new location. Their […]
Space

Must Dos after Moving a Table

Posted on
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 stored optimally in terms of performance and space. But if you found the performance was degraded on this table after moving, it might be that your indexes have not been […]
Backup and Recovery

When Redo Log Files Are All Missing

Posted on
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 mount the database, I mean you can only startup the database to mount state, and let the control file knows all the situation.SQL> startup mount;ORACLE instance started….Database mounted.Recover the database […]