Oracle tablespace status

hussainkhan22
hussainkhan22 used Ask the Experts™
on
Hey what is the command or procedure to check if tablespace are in begin backup mode for hotbackup. Please let me know thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Top Expert 2013

Commented:
select * from v$backup;

wmp

Commented:
select file#, status from v$backup;

if the status is active then the tablespace in backup mode

select tablespace_name from dba_data_files
      where file_id = file_no_from_above_Query;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Are you manually backing up or are you using RMAN?  RMAN uses different views for backup status.
Top Expert 2011
Commented:
- once you have taken the tablespace to backup mode, you can use the following query to display all tablespace, the datafile and the status. the one in backup mode is status mark as active:

SELECT b.name tablespace, a.file# datafile#, a.name datafile, c.status
FROM V$DATAFILE a, V$TABLESPACE b, V$BACKUP c
WHERE a.ts#=b.ts#
AND c.file#=a.file#

Open in new window


OR the following query is to display tablespace with status active only:

SELECT b.name tablespace, a.file# datafile#, a.name datafile, c.status
FROM V$DATAFILE a, V$TABLESPACE b, V$BACKUP c
WHERE a.ts#=b.ts#
AND c.file#=a.file#
AND c.status='ACTIVE';

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial