image

how to find current size, available size, used size and total size in a Tablespace .

For used size: select sum(bytes) from dba_segments where tablespace_name=”; For available size: select sum(bytes) from dba_datafiles where tablespace_name=”; select df.tablespace_name “Tablespace”, totalusedspace “Used MB”, (df.totalspace – tu.totalusedspace) “Free MB”, df.totalspace “Total MB”, round(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace)) “Pct. Free” from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files where tablespace_name like ‘%xxxxxxxxxxxxxx%’ group … continue reading

image

How to know log sequence number/SCN of Archive log file for restoration purpose

Possible Solutions: select * from v$log; select sequence# from v$archived_log; RMAN>RESTORE DATABASE PREVIEW  

image

How to know number of databases installed in my oracle and how to know how many databases are up??

How to know the number of databases installed in  my Oracle ?? Solution: check corntab entry i.e. check oratab file /etc/oratab or We can know it from ORACLE_HOME/dbs location there we can find out how many databases are there in that conected perticular server.We can know it from Pfile & Spfile.If we create the database … continue reading

image

Difference between not in and not exists in oralce

Before moving to the difference between not in and not exists , i would like to say that its better to use not exists when these two result the same result for better performance. When you write a query using the NOT IN clause, you’re telling the rule-based optimizer that you want the inner query … continue reading

image

Recovering Oracle Database from the loss of spfile

NOTE : ALWAYS MAKE SURE YOU HAVE YOUR LATEST FULL BACKUP Lets first give a short look how to take the full backup $RMAN target / RMAN> BACKUP DATABASE plus ARCHIVELOG; Lets move towards the topic. Here i will show you how to recover the oracle database from the loss of the spfile. I will … continue reading

image

Checking the Availability of the Backup in ORACLE

The first step in any recovery procedure is to confirm the availability of the complete set of backup. By complete set I mean all the files required for the recovery. This is called the Redundancy Set. The set of files needed to recover an Oracle database from the failure of any of its files – … continue reading

image

Oracle 10g VS 11g(LISTAGG) SQL

Hello Friend i have a table like EMPID SKILLS SUBSKILL —— ——————– ——————– 22 oracle dba 22 oracle plsql i want to output that EMPID SKILLS SUBSKILL —— ——————– ——————– 22 oracle dba,plsql Solution: SELECT EMPID, SKILLS,LISTAGG(SUBSKILL, ‘,’) WITHIN GROUP (ORDER BY SUBSKILL) AS SUBSKILLFROM TABLENAMEGROUP BY EMPID. This solution will work for oracle 11gR2 … continue reading

image

ORA-01033: ORACLE initialization or shutdown in progress

Yesterday when i tried to login into my application , i could not login into my account . I was unable to login with the following error. But datbase was in open state. ORA-01033: ORACLE initialization or shutdown in progress Actually , while trying to find out the problem , the archive log was full … continue reading

image

How to Start Database Control (dbconsole) , Enterprise Manager do not Start

During the installation of the Database Control (Enterprise Manager) the following files are created: 1. Repository Files 2. Configuration Files These files contains the  information about the database, the listener and the hostname in which it is installed. Any changes in these three files after the database installation.If any problem arise then the solution is … continue reading

Visit Us On FacebookVisit Us On Google PlusVisit Us On Youtube