oracle dictionary info dict.sql

dict comment
dict info
dictionary comments
@dict.sql
dict.sql


break on sort_key skip1 nodup
col sort_key noprint
col comments format a78 word

def table = &&1

WITH dict_view AS
( SELECT CASE
WHEN SUBSTR(table_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(table_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(table_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, table_name
, comments
FROM dictionary
WHERE table_name LIKE UPPER('%&&1%')
UNION
SELECT CASE
WHEN SUBSTR(v.view_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(v.view_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(v.view_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, NVL(s.synonym_name,v.view_name)
, NVL(d.comments,c.comments)
FROM dba_views v
LEFT OUTER JOIN dictionary d
ON d.table_name = v.view_name
LEFT OUTER JOIN all_synonyms s
ON s.table_name = v.view_name
AND s.table_owner = v.owner
AND s.owner = 'PUBLIC'
LEFT OUTER JOIN dba_tab_comments c
ON c.table_name = v.view_name
AND c.owner = v.owner
WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
AND d.table_name IS NULL )
SELECT sort_key, table_name, comments
FROM ( SELECT sort_key, table_name, comments , ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY table_name NULLS LAST) AS seq
FROM dict_view )
WHERE seq = 1
ORDER BY sort_key, table_name
/
undefine 1



SQL> @dict
Enter value for 1: file
old 11: WHERE table_name LIKE UPPER('%&&1%')
new 11: WHERE table_name LIKE UPPER('%file%')
old 31: WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
new 31: WHERE ( v.view_name LIKE UPPER('V\_$%file%') ESCAPE '\'
old 32: OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
new 32: OR v.view_name LIKE UPPER('GV\_$file%') ESCAPE '\'
old 33: OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
new 33: OR v.view_name LIKE UPPER('DBA\_$file%') ESCAPE '\' )

TABLE_NAME COMMENTS
------------------------------ ------------------------------------------------------------------------------
USER_DBFS_HS_FILES
USER_FILE_GROUPS Details about file groups
USER_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
USER_FILE_GROUP_FILES Details about file group files
USER_FILE_GROUP_TABLES Details about the tables in the file group repository
USER_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
USER_FILE_GROUP_VERSIONS Details about file group versions
USER_SCHEDULER_FILE_WATCHERS Scheduler file watch requests owned by the current user

ALL_FILE_GROUPS Details about file groups
ALL_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
ALL_FILE_GROUP_FILES Details about file group files
ALL_FILE_GROUP_TABLES Details about the tables in the file group repository
ALL_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
ALL_FILE_GROUP_VERSIONS Details about file group versions
ALL_SCHEDULER_FILE_WATCHERS Scheduler file watch requests visible to the current user

DBA_DATA_FILES Information about database data files
DBA_EXP_FILES Description of export files
DBA_FILE_GROUPS Details about file groups
DBA_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
DBA_FILE_GROUP_FILES Details about file group files
DBA_FILE_GROUP_TABLES Details about the tables in the file group repository
DBA_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
DBA_FILE_GROUP_VERSIONS Details about file group versions
DBA_HIST_DATAFILE Names of Datafiles
DBA_HIST_FILEMETRIC_HISTORY File Metrics History
DBA_HIST_FILESTATXS Datafile Historical Statistics Information
DBA_HIST_IOSTAT_FILETYPE Historical I/O statistics by file type
DBA_HIST_IOSTAT_FILETYPE_NAME File type names for historical I/O statistics
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_PROFILES Display all profiles and their limits
DBA_SCHEDULER_FILE_WATCHERS All scheduler file watch requests in the database
DBA_SQL_PROFILES set of sql profiles
DBA_TEMP_FILES Information about database temp files

GV$ASM_FILE Synonym for GV_$ASM_FILE
GV$ASM_FILESYSTEM Synonym for GV_$ASM_FILESYSTEM
GV$BACKUP_DATAFILE Synonym for GV_$BACKUP_DATAFILE
GV$BACKUP_SPFILE Synonym for GV_$BACKUP_SPFILE
GV$CONTROLFILE Synonym for GV_$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION Synonym for GV_$CONTROLFILE_RECORD_SECTION