show database files with only ONLINE datafile


SET pagesize 2000
SET heading OFF 
SET feedback OFF
Prompt ******** DATA Files********
SELECT a.tablespace_name ||' '||a.file_name||' '||TO_CHAR(ROUND( a.bytes /1024/1024) ) FROM dba_data_files a ,dba_tablespaces b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME AND
 b.STATUS='ONLINE'
 ORDER BY a.tablespace_name,a.file_name; 

 
Prompt ****** Control Files ********
SELECT NAME FROM v$controlfile;

Prompt ******* Redo logs *********
SELECT MEMBER FROM v$logfile;

Prompt *****Temp DATA Files ******
SELECT NAME||' '||TO_CHAR(ROUND( bytes /1024/1024) ) FROM v$tempfile;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: