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 !!!!
====================
========================================================
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;
