Oracle datafile info

Oracle datafile and other Imp file information ..

Datafile Info 

file info ...

datafile info 

COLUMN  today NOPRINT New_Value strToday

SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;

COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName

SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;

TTITLE LEFT     '______________________________________________________________________________________________________________________' -

SKIP 2 CENTER   strToday -

SKIP CENTER     'File Report (all physical files)' -

SKIP CENTER     strDatabaseName -

SKIP LEFT       '______________________________________________________________________________________________________________________' -

SKIP LEFT       ''

SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'

COLUMN filename        FORMAT a80                    HEADING 'Filename'

COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'

COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'

COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'

COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'

BREAK ON report

COMPUTE sum OF filesize  ON report

SELECT /*+ ordered */

    d.tablespace_name                     tablespace

  , d.file_name                           filename

  , d.bytes/1024/1024                     filesize_mb

  , d.autoextensible                      autoextensible

  , (d.increment_by * e.value)/1024/1024  increment_by

  , d.maxbytes/1024/1024                  max_mb

FROM sys.dba_data_files d , v$datafile v

  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e

WHERE  (d.file_name = v.name)

UNION

SELECT

    d.tablespace_name                     tablespace 

  , d.file_name                           filename

  , d.bytes/1024/1024                     filesize_mb

  , d.autoextensible                      autoextensible

  , (d.increment_by * e.value)/1024/1024  increment_by

  , d.maxbytes/1024/1024                  max_mb

FROM  sys.dba_temp_files d

  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e

UNION

SELECT

    '[ ONLINE REDO LOG ]'

  , a.member

  , b.bytes/1024/1024

  , null

  , TO_NUMBER(null)

  , b.bytes/1024/1024

FROM  v$logfile a, v$log b

WHERE   a.group# = b.group#

UNION

SELECT

    '[ CONTROL FILE    ]'

  , a.name

  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  v$controlfile a

union

SELECT

    '[ BLOCK TRACKING]'||a.status

  , a.filename

  , TO_NUMBER(null)

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  V$BLOCK_CHANGE_TRACKING a

union 

SELECT

    '[ FLASHBACK DATABASE]'

  , a.name

  , a.bytes/1024/1024

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  V$FLASHBACK_DATABASE_LOGFILE a

union

SELECT

    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')

  , a.DISPLAY_VALUE

  , TO_NUMBER(null)

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  sys.v_$parameter a WHERE name = 'spfile'

ORDER BY 1,2

/
========================================================

On ASM !!!!!!!!!!!!!!!!!!!

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set linesize 300 pagesize 300
col full_alias_path for a80
col system_created for a15
col alias_directory for a15
col FILE_TYPE for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,  system_created, alias_directory, file_type,CREATION_DATE
from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory,c.type file_type,c.CREATION_DATE
       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
       where a.group_number = b.group_number
             and a.group_number = c.group_number(+)
             and a.file_number = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     )
where alias_directory = 'N'
-- and file_type ='ARCHIVELOG'
-- and file_type ='DATAFILE'
-- and file_type ='TEMPFILE'
-- and file_type ='PARAMETERFILE'
-- and file_type ='CONTROLFILE'
-- and file_type ='ONLINELOG'
-- and file_type ='PASSWORD'
-- and file_type in ( 'ARCHIVELOG','DATAFILE','TEMPFILE','PARAMETERFILE','CONTROLFILE','ONLINELOG','PASSWORD')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in
                ( select a.reference_index
                  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        -- and a.name = 'DATABASENAME'
                )
connect by prior rindex = pindex;
=====

----- with PDBS Datafiles 

COLUMN  today NOPRINT New_Value strToday
SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;
COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName
SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'File Report (all physical files)' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'
COLUMN filename        FORMAT a75                    HEADING 'Filename'
COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'
COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'
COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'
BREAK ON report
COMPUTE sum OF filesize  ON report
SELECT  /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.dba_data_files d , v$datafile v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM  sys.dba_temp_files d
  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e
UNION
SELECT 
   v.PDB_NAME||'.'|| d.tablespace_name    tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.cdb_data_files d , DBA_PDBS v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.CON_ID = v.PDB_ID)
union
SELECT
    '[ ONLINE REDO LOG ]'
  , a.member
  , b.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , b.bytes/1024/1024
FROM  v$logfile a, v$log b
WHERE   a.group# = b.group#
UNION
SELECT
    '[ CONTROL FILE    ]'
  , a.name
  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  v$controlfile a
union
SELECT
    '[ BLOCK TRACKING]'||a.status
  , a.filename
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$BLOCK_CHANGE_TRACKING a
union 
SELECT
    '[ FLASHBACK DATABASE]'
  , a.name
  , a.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$FLASHBACK_DATABASE_LOGFILE a
union
SELECT
    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')
  , a.DISPLAY_VALUE
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  sys.v_$parameter a WHERE name = 'spfile'
ORDER BY 1,2
/


===============
---- pluggable datafile !!!!

COLUMN  today NOPRINT New_Value strToday
SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;
COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName
SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'File Report (all physical files)' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'
COLUMN filename        FORMAT a75                    HEADING 'Filename'
COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'
COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'
COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'
BREAK ON report
COMPUTE sum OF filesize  ON report
SELECT  /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.dba_data_files d , v$datafile v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM  sys.dba_temp_files d
  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e
UNION
SELECT 
   v.PDB_NAME||'.'|| d.tablespace_name    tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.cdb_data_files d , DBA_PDBS v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.CON_ID = v.PDB_ID)
union
SELECT
    '[ ONLINE REDO LOG ]'
  , a.member
  , b.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , b.bytes/1024/1024
FROM  v$logfile a, v$log b
WHERE   a.group# = b.group#
UNION
SELECT
    '[ CONTROL FILE    ]'
  , a.name
  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  v$controlfile a
union
SELECT
    '[ BLOCK TRACKING]'||a.status
  , a.filename
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$BLOCK_CHANGE_TRACKING a
union 
SELECT
    '[ FLASHBACK DATABASE]'
  , a.name
  , a.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$FLASHBACK_DATABASE_LOGFILE a
union
SELECT
    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')
  , a.DISPLAY_VALUE
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  sys.v_$parameter a WHERE name = 'spfile'
ORDER BY 1,2
/

====================

alter session set nls_date_format='dd-mon-rr hh24:mi';

undefine tablespace_name

set pages 500 lines 250 term off

col fn new_value fname

select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;

col file_name           for a60

col AUTOEXTENSIBLE      for a18

col BIGFILE             for a15

col ENCRYPTED           for a10

col TABLESPACE_NAME     for a20  ---

col COMPRESS_FOR        for a10

col file_name         for &fname   heading "File Name"

select file_id, b.block_size, a.tablespace_name,a.file_name, trunc(a.bytes/(1024*1024*1024),2) size_gb, trunc(a.maxbytes/(1024*1024*1024),2) max_gb , a.autoextensible

,bigfile

--,encrypted,compress_for

,contents,a.status,creation_time 

from dba_data_files a ,dba_tablespaces b ,v$datafile v

where 1=1  

and a.tablespace_name=b.tablespace_name

and a.file_id=v.file#

--and file_id=31

-- and a.tablespace_name in ('UNDOTBS1')

-- and a.tablespace_name = upper( decode('&&tablespace_name',null,a.tablespace_name,'&&tablespace_name'))

order by 2;

==============

set linesize 300 pagesize 300

col Datafile_name for a50 

SELECT   t.tablespace_name "Tablespace", 'Datafile' "File Type",

         t.status "Tablespace Status", d.status "File Status",

         ROUND ((d.max_bytes - NVL (f.sum_bytes, 0)) / 1024 / 1024) "Used MB",

         ROUND (NVL (f.sum_bytes, 0) / 1024 / 1024) "Free MB",

         (d.bytes/1024/1024) file_mb,

         (d.maxbytes/1024/1024) MaxMB,

         t.initial_extent "Initial Extent", t.next_extent "Next Extent",

         t.min_extents "Min Extents", t.max_extents "Max Extents",

         t.pct_increase "Pct Increase",

         d.file_name "Datafile_name",

         d.file_id,

d.autoextensible

    FROM (SELECT   tablespace_name, file_id, SUM (BYTES) sum_bytes

              FROM dba_free_space

          GROUP BY tablespace_name, file_id) f,

         (SELECT   tablespace_name, file_name, file_id, MAX (BYTES) max_bytes, bytes, maxbytes,

                   status, autoextensible

              FROM dba_data_files

          GROUP BY tablespace_name, file_name, file_id, bytes, maxbytes, status, autoextensible) d,

         dba_tablespaces t

   WHERE t.tablespace_name = d.tablespace_name

     AND f.tablespace_name(+) = d.tablespace_name

     AND f.file_id(+) = d.file_id

     AND t.tablespace_name like upper(nvl('%&tbsp_name%',t.tablespace_name))

     AND d.file_name like nvl('%&file_name%',d.file_name)

order by 1;

 

Tablespace                     File Typ Tablespac File Stat    Used MB    Free MB    FILE_MB      MAXMB Initial Extent Next Extent Min Extents Max Extents Pct Increase Datafile_name                                         FILE_ID AUT

------------------------------ -------- --------- --------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ -------------------------------------------------- ---------- ---

USERS                          Datafile ONLINE    AVAILABLE      21747       4676    26422.5 32767.9844          65536                       1  2147483645              +DATA/iirac/users01.dbf                                     7 YES

====

select distinct substr(name, 1, instr(name, '/',-1)) PATH

from (

select name from v$datafile

union all

select NAME from v$controlfile

union all

select MEMBER name from v$logfile

union all

select name from v$tempfile

union all

SELECT FILENAME FROM V$BLOCK_CHANGE_TRACKING

union all

select name from v$flashback_database_logfile

) order by 1;

set pagesize 0

SELECT 'D,' || c.NAME || ':' || t.NAME || ':' || c.dbId  || ',' || d.STATUS || ',' || d.ENABLED || ',' || TO_CHAR(d.BYTES) || ',' || 

TO_CHAR(d.BYTES - NVL(ff.fbytes,0)) || ',' || TRIM(' ' FROM d.NAME) || ',' || TRIM(' ' FROM d.FILE#) AS PDB_TS_DF 

FROM v$datafile d, v$tablespace t, v$CONTAINERS c,(SELECT f.CON_ID CON_ID, f.file_id file_id, SUM(f.bytes) fbytes 

FROM CDB_FREE_SPACE f GROUP BY f.file_id,f.CON_ID) ff 

WHERE c.CON_ID = d.CON_ID and d.CON_ID = t.CON_ID 

and d.TS#=t.TS# AND ff.file_id (+)= d.FILE#  

ORDER BY PDB_TS_DF;