Download(datafilesforts.sql)
REM +======================================================================+ REM REM File Name: datafilesforts.sql REM REM Description: REM Query To Get Datafile Details for a Given Tablespace REM REM Notes: REM Usage: sqlplus "/ as sysdba" @datafilesforts.sql REM REM This Query Requires Tablespace Name as an Input REM REM +======================================================================+ ACCEPT TS_NAME PROMPT "Enter Tablespace Name: ";clear columns set lines 180 set pages 100 col AUTOEXTENSIBLE for a16 col DATA_FILE_NAME for a60 WORD_WRAPPED SELECT SUBSTR (df.file_name, 1, 60) DATA_FILE_NAME, df.bytes / 1024 / 1024 Size_MB, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb,df.AUTOEXTENSIBLE, df.increment_by * (df.bytes/df.blocks) /1024/1024 "NEXT_EXTENT_MB", df.MAXBYTES / 1024 / 1024 MAXSIZE_MB FROM dba_data_files df, dba_free_space dfs WHERE df.FILE_ID = dfs.file_id(+) AND df.TABLESPACE_NAME = '&TS_NAME' GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes,df.AUTOEXTENSIBLE,df.MAXBYTES,df.increment_by,df.blocks ORDER by df.file_name;undef TS_NAME
No comments:
Post a Comment