Search This Blog

Total Pageviews

Thursday 29 December 2016

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



Oracle DBA

anuj blog Archive