Find Blocks in the Buffer cache

Below is the query to find how many blocks  for each segment are currently in the buffer Cache.

RAC Database:

select o.owner, o.object_name,v.inst_id, count(*) number_of_blocks
from dba_objects o, gv$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name,v.inst_id
order by o.object_name,v.inst_id,count(*);

Non-RAC Database:

select o.owner, o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name
order by o.object_name,count(*);

Excluding Tablespace from RMAN Backup

You have a tablespace with test data that you don't need to backup. You can exclude such tablespaces from a full backup of the database.


Below command shows the list of tablespaces that are already configured to be excluded from backups:

RMAN> show exclude;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TEST are:

RMAN configuration has no stored or default parameters

Use following command to exclude tablespace from full database backup.

RMAN> configure exclude  for tablespace users;

Tablespace USERS will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored


To confirm that the tablespace is excluded RMAN backup, run the below command

RMAN>  show exclude;

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';


Now, if you want to include a previously excluded tablespace in your backup and that is done by using the following command.

RMAN> configure exclude  for tablespace users clear;

Tablespace USERS will be included in future whole database backups

old RMAN configuration parameters are successfully deleted

RMAN> show exclude;

RMAN configuration parameters for database with db_unique_name TEST are:

RMAN configuration has no stored or default parameters

Now, if you want include all tablespaces that are configured in "exclude tablespace",  You can use the 'noexclude' option as part of a backup database command.

RMAN>backup database noexclude;

Find user's IP address

select sid, machine,UTL_INADDR.get_host_address
substr(machine,instr(machine,'\')+1))ip from v$session
where type='USER' and username is not null order by sid;

Drop Database using RMAN

Connect to a target database and make sure that the database is in "mount exclusive" state and not open. You need to start in the RESTRICT mode.

RMAN> connect target /

connected to target database: TEST (DBID=233344476, not open)

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

using target database control file instead of recovery catalog

sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "TEST" and DBID is 233344476

Do you really want to drop all backups and the database (enter YES or NO)? YES

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=421 device type=DISK

specification does not match any backup in the repository

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=421 device type=DISK

specification does not match any datafile copy in the repository

specification does not match any control file copy in the repository

specification does not match any control file copy in the repository

specification does not match any archived log in the repository

database name is "TEST" and DBID is 233344476

database dropped

Database Size

For Non-RAC database:

  select DATA.TOTAL/1024/1024 "DataFile Size Mb",
  LOG.TOTAL/1024/1024 "Redo Log Size Mb",
  CONTROL.TOTAL/1024/1024 "Control File Size Mb",
  (DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
  (select sum(a.bytes) TOTAL from dba_data_files a) DATA,
  (select sum(b.bytes) TOTAL from v$log b) LOG,
  (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;


For RAC database:

select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from gv$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;