#learnoracledba

Scripts to find table, schema and database size:

Application team will ask ORACLE DB’s to take export backup of tables, schema and database to import in another place. To take backup, we need to know what is the size of the table or schema or database. So that we can check if we have enough space at file system level for saving export backup. Below are SQL Queries,commands and scripts to find schema size, table size and database size.

To find…

Details should be taken before stopping ORACLE DB for planned activity:

In UNIX and Linux, any planned downtime activity at OS/database level needs the oracle database to shutdown prior to the activity. If its a planned activity, taking below details helps to verify the database after activity completed. Also, it helps to validate and find the reason if any critical error occured after downtime activity. Collect the details and save it in notepad which can be removed…

Troubleshooting ORACLE Database Out of Memory Errors-ORA 4031:

Below error will be registered in alert log of ORACLE database when out of memory error occurs: ORA 4031

An ORA-4031 error is raised when memory is unavailable for use or reuse in the System Global Area (SGA). The error message will indicate the memory pool getting errors and high level information about what kind of allocation failed and how much memory was unavailable.

The problem with…

How to find Archivelog destination?

If archivelog destination is 100% full then backup fails, which can be resolved by moving archivelog files to other location. Each and every database have unique archivelog destination.

The archive log location can be found by below commands. Either the directory name mentioned directly or stored in parameter file.

Scenario 1: IF archive location is mentioned in DB_Recovery

UNIX…

How to enable or disable Archive log mode in Oracle database 11g?

How to enable Archivelog mode in Oracle database 11g Mode of Logging

There are two types of logging modes in Oracle database :-

ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in…