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)

FROM v$controlfile a

union

SELECT

'[ BLOCK TRACKING]'||a.status

, a.filename

, TO_NUMBER(null)

, null

, TO_NUMBER(null)

FROM V$BLOCK_CHANGE_TRACKING a

union

SELECT

'[ FLASHBACK DATABASE]'

, a.name

, a.bytes/1024/1024

, 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)

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;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Thursday, 29 December 2016

Oracle datafile info

Oracle datafile and other Imp file information ..

Oracle DBA

anuj blog Archive