home *** CD-ROM | disk | FTP | other *** search
Text File | 1990-10-12 | 172.9 KB | 5,666 lines
Rem Copyright (c) 1988 by Oracle Corporation Rem Rem NAME Rem CATALOG.SQL Rem FUNCTION Rem Creates data dictionary views. Rem NOTES Rem Replaces old CATALOG.ORA. Rem Must be run when connected to SYS. Rem MODIFIED Rem Mendelsohn 10/12/90 - bug 47910: remove password column from dba_db_links Rem Chaudhr 05/31/90 - Assorted bug fixes. Rem - 18656: OWNER in XXX_SEGMENTS and DBA_ROLLBACK_SEGS Rem - 31160: Changed the comment on DATA_PRECISION col's Rem - 33810: Added PCT_FREE col to USER/ALL/DBA_INDEXES Rem - 35876: Added TEXT_LENGTH & TEXT col's to ALL_VIEWS Rem - 38207: Change comment on tables USER/DBA_CROSS_REFS Rem Harmon 09/26/89 - replication hook Rem Wijaya 05/11/89 - add v$rowcache Rem Loaiza 05/05/89 - add index statistics Rem Mendels 12/12/88 - fix 18656: use SYS/PUBLIC as rollback seg owner Rem Bulchan 10/31/88 - Remove spurious '0' Rem Mendels 10/25/88 - not exists errors: add 1418, 1435, 2289; drop 1719 Rem Bulchan 10/21/88 - add views from mon file and rollback Rem Wijaya 10/17/88 - improve constraint views Rem Wijaya 10/11/88 - add TABLE_NAME column to CONS_COLUMNS Rem Mendels 10/07/88 - fix public synonym for all_sequences, dba_audit_dba Rem - delete user_sys_aud_opts Rem Wijaya 10/05/88 - add [USER|ALL|DBA]_CONSTRAINTS and Rem [USER|ALL|DBA]_CONS_COLUMNS Rem Bulchan 10/02/88 - Make it work in dbsdrv Rem Wijaya 09/29/88 - remove ACGRA, UCGRA, UTCOM Rem Hong 09/24/88 - fix export view Rem Wijaya 09/22/88 - fix DATA_SCALE Rem Mendels 09/08/88 - bug 15135 (add actions 34,49 to audit_actions) Rem Mendels 08/30/88 - fix bug 14772 (tab returns indexes); Rem remove weird to_date's in %_objects; Rem remove some useless (+); Rem wrong column name in some comments on columns; Rem remove tabspaces and systabspaces views; Rem rename password_changed to expires; Rem fix 9981: decode type 69 as rowid in col; Rem Mendels 08/07/88 - fix all/dba_indexes and all/dba_ind_columns views Rem to return indexes on public tables; Rem fix %_ind_columns views to use col# not pos# and Rem to return pos# Rem add %_indexes comments on max_extents. Rem Baldwin 08/01/88 - Bad table name in all_ind_columns. Rem Wrong col name in comment on user_cluster. Rem Baldwin 07/31/88 - Add password_change col to user_user and dba_user. Rem Baldwin 07/28/88 - Enhance audit trail views. Rem See notes$:audit_views.txt. Rem Peeler 07/27/88 - Missing ' in user_tab_columns, accessible_columns. Rem Bulchan 07/22/88 - Add v_$sga stuff Rem Peeler 07/21/88 - cleanup various bugs in definitions Rem Wijaya 06/21/88 - add PUBLIC synonyms/views for fixed views Rem Moore 06/16/88 - fix missing ' and assorted other goodies Rem Moore 06/16/88 - add synonym for dual Rem Moore 06/02/88 - add comments to audit views (TGRAYSON) Rem Moore 05/31/88 - add V5 audit views Rem Moore 05/18/88 - integrate new segment views (dba,user) Rem Wijaya 04/29/88 - fix comments Rem Kabcenell 03/21/88 - dba_exp_files.dba_exp_version -> exp_version Rem Grayson 03/21/88 - Creation remark remark FAMILY "AUDIT" remark Views for inspecting the audit trail. remark For views of the audit options see the families: tab_audit_opts, remark sys_audit_opts, and def_audit_opts. remark remark User's are not allowed to see audit info for other people's remark objects, so there are no all_ members in this family. remark Users can see audit info for objects they own. remark / remark remark AUDIT_ACTIONS maps an action number to the action name. remark The table is accessible to public. remark / drop table AUDIT_ACTIONS / create table AUDIT_ACTIONS( action number not null, name char(27) not null) / comment on table AUDIT_ACTIONS is 'Description table for audit trail action type codes. Maps action type numbers to action type names' / comment on column AUDIT_ACTIONS.ACTION is 'Numeric audit trail action type code' / comment on column AUDIT_ACTIONS.NAME is 'Name of the type of audit trail action' / insert into audit_actions values (0, 'UNKNOWN'); insert into audit_actions values (1, 'CREATE TABLE'); insert into audit_actions values (2, 'INSERT'); insert into audit_actions values (3, 'SELECT'); insert into audit_actions values (4, 'CREATE CLUSTER'); insert into audit_actions values (5, 'ALTER CLUSTER'); insert into audit_actions values (6, 'UPDATE'); insert into audit_actions values (7, 'DELETE'); insert into audit_actions values (8, 'DROP CLUSTER'); insert into audit_actions values (9, 'CREATE INDEX'); insert into audit_actions values (10, 'DROP INDEX'); insert into audit_actions values (11, 'ALTER INDEX'); insert into audit_actions values (12, 'DROP TABLE'); insert into audit_actions values (13, 'CREATE SEQUENCE'); insert into audit_actions values (14, 'ALTER SEQUENCE'); insert into audit_actions values (15, 'ALTER TABLE'); insert into audit_actions values (16, 'DROP SEQUENCE'); insert into audit_actions values (17, 'GRANT OBJECT'); insert into audit_actions values (18, 'REVOKE OBJECT'); insert into audit_actions values (19, 'CREATE SYNONYM'); insert into audit_actions values (20, 'DROP SYNONYM'); insert into audit_actions values (21, 'CREATE VIEW'); insert into audit_actions values (22, 'DROP VIEW'); insert into audit_actions values (23, 'VALIDATE INDEX'); insert into audit_actions values (26, 'LOCK'); insert into audit_actions values (27, 'UNDEFINED'); insert into audit_actions values (28, 'RENAME'); insert into audit_actions values (29, 'COMMENT'); insert into audit_actions values (30, 'AUDIT OBJECT'); insert into audit_actions values (31, 'NOAUDIT OBJECT'); insert into audit_actions values (32, 'CREATE DATABASE LINK'); insert into audit_actions values (33, 'DROP DATABASE LINK'); insert into audit_actions values (34, 'CREATE DATABASE'); insert into audit_actions values (35, 'ALTER DATABASE'); insert into audit_actions values (36, 'CREATE ROLLBACK SEG'); insert into audit_actions values (37, 'ALTER ROLLBACK SEG'); insert into audit_actions values (38, 'DROP ROLLBACK SEG'); insert into audit_actions values (39, 'CREATE TABLESPACE'); insert into audit_actions values (40, 'ALTER TABLESPACE'); insert into audit_actions values (41, 'DROP TABLESPACE'); insert into audit_actions values (42, 'ALTER SESSION'); insert into audit_actions values (43, 'ALTER USER'); insert into audit_actions values (49, 'ALTER SYSTEM'); insert into audit_actions values (60, 'LOGON'); insert into audit_actions values (61, 'LOGOFF'); insert into audit_actions values (62, 'CLEANUP'); insert into audit_actions values (63, 'SESSION'); insert into audit_actions values (64, 'AUDIT SYSTEM'); insert into audit_actions values (65, 'NOAUDIT SYSTEM'); insert into audit_actions values (66, 'AUDIT DEFAULT'); insert into audit_actions values (67, 'NOAUDIT DEFAULT'); insert into audit_actions values (68, 'GRANT SYSTEM'); insert into audit_actions values (69, 'REVOKE SYSTEM'); insert into audit_actions values (70, 'CREATE PUBLIC SYNONYM'); insert into audit_actions values (71, 'DROP PUBLIC SYNONYM'); insert into audit_actions values (72, 'CREATE PUBLIC DATABASE LINK'); insert into audit_actions values (73, 'DROP PUBLIC DATABASE LINK'); insert into audit_actions values (80, 'USER COMMENT'); commit; create unique index audact$uid on audit_actions(action,name) nocompress / drop public synonym AUDIT_ACTIONS / create public synonym AUDIT_ACTIONS for AUDIT_ACTIONS / grant select on AUDIT_ACTIONS to public / remark remark USER_AUDIT_TRAIL remark The raw audit trail of all information related to the user remark or the objects owned by the user. Some columns are only filled remark in by certain statements (e.g., only rename uses new_name). remark remark DBA_AUDIT_TRAIL is implemented as a synonym for user_audit_trail. remark This means that users with dba privilege have a hard time viewing remark the audit info for just the objects they own because they always remark see all the audit info. Simplifying the implementation and remark maintanence of these views seems more important. remark A user with dba privilege sees all entries. remark / drop view USER_AUDIT_TRAIL / create view USER_AUDIT_TRAIL ( username, userhost, terminal, timestamp, owner, obj_name, action, action_name, new_name, privilege, grantee, ses_actions, logoff_time, logoff_lread, logoff_pread, logoff_lwrite, logoff_dlock, comment_text, sessionid, entryid, statementid, returncode ) as select userid, userhost, terminal, timestamp, t.obj$creator, t.obj$name, t.action, act.name, new$name, auth$privileges, auth$grantee, ses$actions, logoff$time, logoff$lread, logoff$pread, logoff$lwrite, logoff$dead, comment$text, sessionid, entryid, statement, returncode from sys.aud$ t, audit_actions act where t.action = act.action and ((t.obj$creator = user) or (t.obj$creator is null and t.userid = user) or exists (select null from sys.user$ where user# = uid and dba$ = 1) ) / comment on table USER_AUDIT_TRAIL is 'Audit trail entries relevant to the user' / comment on column USER_AUDIT_TRAIL.USERNAME is 'Name (not ID number) of the user whose actions were audited' / comment on column USER_AUDIT_TRAIL.USERHOST is 'Numeric instance ID for the Oracle instance from which the user is accessing the database. Used only in environments with distributed file systems and shared database files (e.g., clustered Oracle on DEC VAX/VMS clusters)' / comment on column USER_AUDIT_TRAIL.TERMINAL is 'Identifier for the user''s terminal' / comment on column USER_AUDIT_TRAIL.TIMESTAMP is 'Timestamp for the creation of the audit trail entry (Timestamp for the user''s logon for entries created by AUDIT CONNECT)' / comment on column USER_AUDIT_TRAIL.OWNER is 'Creator of object affected by the action' / comment on column USER_AUDIT_TRAIL.OBJ_NAME is 'Name of the object affected by the action' / comment on column USER_AUDIT_TRAIL.ACTION is 'Numeric action type code. The corresponding name of the action type (CREATE TABLE, INSERT, etc.) is in the column ACTION_NAME' / comment on column USER_AUDIT_TRAIL.ACTION_NAME is 'Name of the action type corresponding to the numeric code in ACTION' / comment on column USER_AUDIT_TRAIL.NEW_NAME is 'The new name of an object renamed by a RENAME statement' / comment on column USER_AUDIT_TRAIL.PRIVILEGE is 'Privileges granted/revoked by a GRANT/REVOKE statement' / remark There is one audit entry for each grantee. / comment on column USER_AUDIT_TRAIL.GRANTEE is 'The name of the grantee specified in a GRANT/REVOKE statement' / comment on column USER_AUDIT_TRAIL.SES_ACTIONS is 'Session summary. A string of 11 characters, one for each action type, in this order: Alter, Audit, Comment, Delete, Grant, Index, Insert, Lock, Rename, Select, Update. Values: "-" = None, "S" = Success, "F" = Failure, "B" = Both' / remark A single audit entry describes both the logon and logoff. remark The logoff_* columns are null while a user is logged in. / comment on column USER_AUDIT_TRAIL.LOGOFF_TIME is 'Timestamp for user logoff' / comment on column USER_AUDIT_TRAIL.LOGOFF_LREAD is 'Logical reads for the session' / comment on column USER_AUDIT_TRAIL.LOGOFF_PREAD is 'Physical reads for the session' / comment on column USER_AUDIT_TRAIL.LOGOFF_LWRITE is 'Logical writes for the session' / comment on column USER_AUDIT_TRAIL.LOGOFF_DLOCK is 'Deadlocks detected during the session' / comment on column USER_AUDIT_TRAIL.COMMENT_TEXT is 'Text comment on the audit trail entry' / comment on column USER_AUDIT_TRAIL.SESSIONID is 'Numeric ID for each Oracle session' / comment on column USER_AUDIT_TRAIL.ENTRYID is 'Numeric ID for each audit trail entry in the session' / comment on column USER_AUDIT_TRAIL.STATEMENTID is 'Numeric ID for each statement run (a statement may cause many actions)' / comment on column USER_AUDIT_TRAIL.RETURNCODE is 'Oracle error code generated by the action. Zero if the action succeeded' / drop public synonym USER_AUDIT_TRAIL / create public synonym USER_AUDIT_TRAIL for USER_AUDIT_TRAIL / grant select on USER_AUDIT_TRAIL to public / drop public synonym DBA_AUDIT_TRAIL / create public synonym DBA_AUDIT_TRAIL for USER_AUDIT_TRAIL / remark remark USER_AUDIT_CONNECT remark Lists the audit trail entries produced by AUDIT CONNECT. remark DBA's see all entries, while ordinary users only remark see entries for their own logins/logoffs. remark remark DBA_AUDIT_CONNECT is implemented as synonym of user_audit_connect, remark which in turn is implemented in terms of user_audit_trail. remark drop view USER_AUDIT_CONNECT / create view USER_AUDIT_CONNECT as select username, userhost, terminal, timestamp, action_name, logoff_time, logoff_lread, logoff_pread, logoff_lwrite, logoff_dlock, sessionid, returncode from user_audit_trail where action between 60 and 62 / comment on table USER_AUDIT_CONNECT is 'Audit trail entries for user logons/logoffs' / comment on column USER_AUDIT_CONNECT.USERNAME is 'Name (not ID number) of the user whose actions were audited' / comment on column USER_AUDIT_CONNECT.USERHOST is 'Numeric instance ID for the Oracle instance from which the user is accessing the database. Used only in environments with distributed file systems and shared database files (e.g., clustered Oracle on DEC VAX/VMS clusters)' / comment on column USER_AUDIT_CONNECT.TERMINAL is 'Identifier for the user''s terminal' / comment on column USER_AUDIT_CONNECT.TIMESTAMP is 'Timestamp for the user''s logon' / comment on column USER_AUDIT_CONNECT.ACTION_NAME is 'Name of the action type corresponding to the numeric code in ACTION' / remark A single audit entry describes both the logon and logoff. remark The logoff_* columns are null while a user is logged in. / comment on column USER_AUDIT_CONNECT.LOGOFF_TIME is 'Timestamp for user logoff' / comment on column USER_AUDIT_CONNECT.LOGOFF_LREAD is 'Logical reads for the session' / comment on column USER_AUDIT_CONNECT.LOGOFF_PREAD is 'Physical reads for the session' / comment on column USER_AUDIT_CONNECT.LOGOFF_LWRITE is 'Logical writes for the session' / comment on column USER_AUDIT_CONNECT.LOGOFF_DLOCK is 'Deadlocks detected during the session' / comment on column USER_AUDIT_CONNECT.SESSIONID is 'Numeric ID for each Oracle session' / comment on column USER_AUDIT_CONNECT.RETURNCODE is 'Oracle error code generated by the action. Zero if the action succeeded' / drop public synonym USER_AUDIT_CONNECT / create public synonym USER_AUDIT_CONNECT for USER_AUDIT_CONNECT / grant select on USER_AUDIT_CONNECT to public / drop public synonym DBA_AUDIT_CONNECT / create public synonym DBA_AUDIT_CONNECT for USER_AUDIT_CONNECT / remark remark DBA_AUDIT_EXISTS remark Only dba's can see audit info about objects that do not exist. remark remark Lists audit trail entries produced by AUDIT NOT EXISTS. remark This is all audit trail entries with return codes of remark 942, 943, 959, 1418, 1432, 1434, 1435, 2019 and 2289. remark This view is accessible to DBAs only. remark drop view DBA_AUDIT_EXISTS / create view DBA_AUDIT_EXISTS as select username, userhost, terminal, timestamp, owner, obj_name, action_name, new_name, privilege, grantee, sessionid, entryid, statementid, returncode from user_audit_trail where returncode in (942, 943, 959, 1418, 1432, 1434, 1435, 2019, 2289) / comment on column DBA_AUDIT_EXISTS.USERNAME is 'Name (not ID number) of the user whose actions were audited' / comment on column DBA_AUDIT_EXISTS.USERHOST is 'Numeric instance ID for the Oracle instance from which the user is accessing the database. Used only in environments with distributed file systems and shared database files (e.g., clustered Oracle on DEC VAX/VMS clusters)' / comment on column DBA_AUDIT_EXISTS.TERMINAL is 'Identifier for the user''s terminal' / comment on column DBA_AUDIT_EXISTS.TIMESTAMP is 'Timestamp for the creation of the audit trail entry' / comment on column DBA_AUDIT_EXISTS.OWNER is 'Intended creator of the non-existent object' / comment on column DBA_AUDIT_EXISTS.OBJ_NAME is 'Name of the object affected by the action' / comment on column DBA_AUDIT_EXISTS.ACTION_NAME is 'Name of the action type corresponding to the numeric code in ACTION' / comment on column DBA_AUDIT_EXISTS.NEW_NAME is 'The new name of an object renamed by a RENAME statement' / comment on column DBA_AUDIT_EXISTS.PRIVILEGE is 'Privileges granted/revoked by a GRANT/REVOKE statement' / remark There is one audit entry for each grantee. / comment on column DBA_AUDIT_EXISTS.GRANTEE is 'The name of the grantee specified in a GRANT/REVOKE statement' / comment on column DBA_AUDIT_EXISTS.SESSIONID is 'Numeric ID for each Oracle session' / comment on column DBA_AUDIT_EXISTS.ENTRYID is 'Numeric ID for each audit trail entry in the session' / comment on column DBA_AUDIT_EXISTS.STATEMENTID is 'Numeric ID for each statement run (a statement may cause many actions)' / comment on column DBA_AUDIT_EXISTS.RETURNCODE is 'Oracle error code generated by the action. Zero if the action succeeded' / drop public synonym DBA_AUDIT_EXISTS / create public synonym DBA_AUDIT_EXISTS for DBA_AUDIT_EXISTS / remark remark USER_AUDIT_RESOURCE remark DBA_AUDIT_RESOURCE implemented in terms of user_audit_resource. remark DBA Sees all. remark remark Lists audit trail entries produced by AUDIT RESOURCE. remark / drop view USER_AUDIT_RESOURCE / create view USER_AUDIT_RESOURCE as select username, userhost, terminal, timestamp, owner, obj_name, action_name, sessionid, entryid, statementid, returncode from user_audit_trail where action in (1, 4, 8, 9, 10, 12, 13, 16, 19, 20, 21, 22, 32, 33, 36, 38, 39, 41, 70, 71, 72, 73) / comment on column USER_AUDIT_RESOURCE.USERNAME is 'Name (not ID number) of the user whose actions were audited' / comment on column USER_AUDIT_RESOURCE.USERHOST is 'Numeric instance ID for the Oracle instance from which the user is accessing the database. Used only in environments with distributed file systems and shared database files (e.g., clustered Oracle on DEC VAX/VMS clusters)' / comment on column USER_AUDIT_RESOURCE.TERMINAL is 'Identifier for the user''s terminal' / comment on column USER_AUDIT_RESOURCE.TIMESTAMP is 'Timestamp for the creation of the audit trail entry' / comment on column USER_AUDIT_RESOURCE.OWNER is 'Intended creator of the non-existent object' / comment on column USER_AUDIT_RESOURCE.OBJ_NAME is 'Name of the object affected by the action' / comment on column USER_AUDIT_RESOURCE.ACTION_NAME is 'Name of the action type corresponding to the numeric code in ACTION' / comment on column USER_AUDIT_RESOURCE.SESSIONID is 'Numeric ID for each Oracle session' / comment on column USER_AUDIT_RESOURCE.ENTRYID is 'Numeric ID for each audit trail entry in the session' / comment on column USER_AUDIT_RESOURCE.STATEMENTID is 'Numeric ID for each statement run (a statement may cause many actions)' / comment on column USER_AUDIT_RESOURCE.RETURNCODE is 'Oracle error code generated by the action. Zero if the action succeeded' / drop public synonym USER_AUDIT_RESOURCE / create public synonym USER_AUDIT_RESOURCE for USER_AUDIT_RESOURCE / grant select on USER_AUDIT_RESOURCE to public / drop public synonym DBA_AUDIT_RESOURCE / create public synonym DBA_AUDIT_RESOURCE for USER_AUDIT_RESOURCE / remark remark DBA_AUDIT_DBA remark This view is only accessible to DBAs. remark Lists audit trail entries produced by AUDIT DBA remark (all operations with an action code of 24, 25, or remark 64 through 73). remark The connect, resource and dba columns are filled in for remark entries related to granting/revoking system-wide privileges. remark The value 'Y' indicates the priv was granted/revoked. If not, remark a '-' appears. remark / drop view DBA_AUDIT_DBA / create view DBA_AUDIT_DBA as select username, userhost, terminal, timestamp, owner, obj_name, action, action_name, substr(privilege,1,1) connect_priv, substr(privilege,2,1) dba_priv, substr(privilege,3,1) resource_priv, grantee, sessionid, entryid, statementid, returncode from user_audit_trail where action between 24 and 25 or action between 64 and 73 / comment on table DBA_AUDIT_DBA is 'Audit trail entries created by AUDIT DBA' / comment on column DBA_AUDIT_DBA.USERNAME is 'Name (not ID number) of the user whose actions were audited' / comment on column DBA_AUDIT_DBA.USERHOST is 'Numeric instance ID for the Oracle instance from which the user is accessing the database. Used only in environments with distributed file systems and shared database files (e.g., clustered Oracle on DEC VAX/VMS clusters)' / comment on column DBA_AUDIT_DBA.TERMINAL is 'Identifier for the user''s terminal' / comment on column DBA_AUDIT_DBA.TIMESTAMP is 'Timestamp for the creation of the audit trail entry (Timestamp for the user''s logon for entries created by AUDIT CONNECT)' / comment on column DBA_AUDIT_DBA.OWNER is 'Creator of object affected by the action' / comment on column DBA_AUDIT_DBA.OBJ_NAME is 'Name of the object affected by the action' / comment on column DBA_AUDIT_DBA.ACTION is 'Numeric action type code. The corresponding name of the action type (CREATE TABLE, INSERT, etc.) is in the column ACTION_NAME' / comment on column DBA_AUDIT_DBA.ACTION_NAME is 'Name of the action type corresponding to the numeric code in ACTION' / comment on column DBA_AUDIT_DBA.CONNECT_PRIV is 'Y or - for CONNECT privilege did or did not appear in GRANT/REVOKE statement' / comment on column DBA_AUDIT_DBA.DBA_PRIV is 'Y or - for DBA privilege did or did not appear in GRANT/REVOKE statement' / comment on column DBA_AUDIT_DBA.RESOURCE_PRIV is 'Y or - for RESOURCE privilege did or did not appear in GRANT/REVOKE statement' / remark There is one audit entry for each grantee. / comment on column DBA_AUDIT_DBA.GRANTEE is 'The name of the grantee specified in a GRANT/REVOKE statement' / comment on column DBA_AUDIT_DBA.SESSIONID is 'Numeric ID for each Oracle session' / comment on column DBA_AUDIT_DBA.ENTRYID is 'Numeric ID for each audit trail entry in the session' / comment on column DBA_AUDIT_DBA.STATEMENTID is 'Numeric ID for each statement run (a statement may cause many actions)' / comment on column DBA_AUDIT_DBA.RETURNCODE is 'Oracle error code generated by the action. Zero if the action succeeded' / drop public synonym DBA_AUDIT_DBA / create public synonym DBA_AUDIT_DBA for DBA_AUDIT_DBA / remark remark FAMILY "CATALOG" remark Objects which may be used as tables in SQL statements: remark Tables, Views, Synonyms. remark remark add DUAL drop table DUAL / create table DUAL(DUMMY char(1)) / insert into DUAL values( 'X' ); commit; create public synonym DUAL for DUAL / grant select on DUAL to public / drop view USER_CATALOG / create view USER_CATALOG (TABLE_NAME, TABLE_TYPE) as select o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED') from sys.obj$ o where o.owner# = uid and o.type in (2, 4, 5, 6) / comment on table USER_CATALOG is 'Tables, Views, Synonyms, Sequences accessible to the user' / comment on column USER_CATALOG.TABLE_NAME is 'Name of the object' / comment on column USER_CATALOG.TABLE_TYPE is 'Type of the object' / drop public synonym USER_CATALOG / create public synonym USER_CATALOG for USER_CATALOG / drop public synonym CAT / create public synonym CAT for USER_CATALOG / grant select on USER_CATALOG to PUBLIC / drop view ALL_CATALOG / remark remark This view shows all tables, views, synonyms, and sequences owned by the remark user and those tables, views, synonyms, and sequences that PUBLIC remark has been granted access. remark create view ALL_CATALOG (OWNER, TABLE_NAME, TABLE_TYPE) as select u.name, o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED') from sys.user$ u, sys.obj$ o where o.owner# = u.user# and o.type in (2, 4, 5, 6) and (o.owner# in (uid, 1) or obj# in ( select obj# from sys.tabauth$ where grantee# in ( uid,1 ) ) ) / comment on table ALL_CATALOG is 'All tables, views, synonyms, sequences accesible to the user' / comment on column ALL_CATALOG.OWNER is 'Owner of the object' / comment on column ALL_CATALOG.TABLE_NAME is 'Name of the object' / comment on column ALL_CATALOG.TABLE_TYPE is 'Type of the object' / drop public synonym ALL_CATALOG / create public synonym ALL_CATALOG for ALL_CATALOG / grant select on ALL_CATALOG to PUBLIC / drop view ACCESSIBLE_TABLES / remark remark This view shows all tables and views owned by the remark user, plus all tables and views to which the user or PUBLIC remark has been granted access. remark create view ACCESSIBLE_TABLES (OWNER, TABLE_NAME, TABLE_TYPE) as select u.name, o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED') from sys.user$ u, sys.obj$ o where o.owner# = u.user# and o.type in (2, 4) and (o.owner# in (uid, 1) or obj# in ( select obj# from sys.tabauth$ where grantee# in ( uid,1 ) ) ) / comment on table ACCESSIBLE_TABLES is 'Tables and Views accessible to the user' / comment on column ACCESSIBLE_TABLES.OWNER is 'Owner of the object' / comment on column ACCESSIBLE_TABLES.TABLE_NAME is 'Name of the object' / comment on column ACCESSIBLE_TABLES.TABLE_TYPE is 'Type of the object' / drop public synonym ACCESSIBLE_TABLES / create public synonym ACCESSIBLE_TABLES for ACCESSIBLE_TABLES / grant select on ACCESSIBLE_TABLES to PUBLIC / drop view DBA_CATALOG / create view DBA_CATALOG (OWNER, TABLE_NAME, TABLE_TYPE) as select u.name, o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED') from sys.user$ u, sys.obj$ o where o.owner# = u.user# and o.type in (2, 4, 5, 6) / comment on table DBA_CATALOG is 'All database Tables, Views, Synonyms, Sequences' / comment on column DBA_CATALOG.OWNER is 'Owner of the object' / comment on column DBA_CATALOG.TABLE_NAME is 'Name of the object' / comment on column DBA_CATALOG.TABLE_TYPE is 'Type of the object' / remark remark FAMILY "CLUSTERS" remark CREATE CLUSTER parameters. remark This family has no "ALL" member. remark drop view USER_CLUSTERS / create view USER_CLUSTERS (CLUSTER_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED, KEY_SIZE, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE) as select o.name, ts.name, c.pctfree$, c.pctused$, c.size$,c.initrans,c.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct from sys.ts$ ts, sys.seg$ s, sys.clu$ c, sys.obj$ o where o.owner# = uid and o.obj# = c.obj# and c.ts# = ts.ts# and c.ts# = s.ts# and c.file# = s.file# and c.block# = s.block# / comment on table USER_CLUSTERS is 'Descriptions of user''s own clusters' / comment on column USER_CLUSTERS.CLUSTER_NAME is 'Name of the cluster' / comment on column USER_CLUSTERS.TABLESPACE_NAME is 'Name of the tablespace containing the cluster' / comment on column USER_CLUSTERS.PCT_FREE is 'Minimum percentage of free space in a block' / comment on column USER_CLUSTERS.PCT_USED is 'Minimum percentage of used space in a block' / comment on column USER_CLUSTERS.KEY_SIZE is 'Estimated size of cluster key plus associated rows' / comment on column USER_CLUSTERS.INI_TRANS is 'Initial number of transactions' / comment on column USER_CLUSTERS.MAX_TRANS is 'Maximum number of transactions' / comment on column USER_CLUSTERS.INITIAL_EXTENT is 'Size of the initial extent in bytes' / comment on column USER_CLUSTERS.NEXT_EXTENT is 'Size of secondary extents in bytes' / comment on column USER_CLUSTERS.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column USER_CLUSTERS.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column USER_CLUSTERS.PCT_INCREASE is 'Percentage increase in extent size' / drop public synonym USER_CLUSTERS / create public synonym USER_CLUSTERS for USER_CLUSTERS / drop public synonym CLU / create public synonym CLU for USER_CLUSTERS / grant select on USER_CLUSTERS to PUBLIC / drop view DBA_CLUSTERS / create view DBA_CLUSTERS (OWNER, CLUSTER_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED, KEY_SIZE, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE) as select u.name, o.name, ts.name, c.pctfree$, c.pctused$, c.size$,c.initrans,c.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.clu$ c, sys.obj$ o where o.owner# = u.user# and o.obj# = c.obj# and c.ts# = ts.ts# and c.ts# = s.ts# and c.file# = s.file# and c.block# = s.block# / comment on table DBA_CLUSTERS is 'Description of all clusters in the database' / comment on column DBA_CLUSTERS.OWNER is 'Owner of the cluster' / comment on column DBA_CLUSTERS.CLUSTER_NAME is 'Name of the cluster' / comment on column DBA_CLUSTERS.TABLESPACE_NAME is 'Name of the tablespace containing the cluster' / comment on column DBA_CLUSTERS.PCT_FREE is 'Minimum percentage of free space in a block' / comment on column DBA_CLUSTERS.PCT_USED is 'Minimum percentage of used space in a block' / comment on column DBA_CLUSTERS.KEY_SIZE is 'Estimated size of cluster key plus associated rows' / comment on column DBA_CLUSTERS.INI_TRANS is 'Initial number of transactions' / comment on column DBA_CLUSTERS.MAX_TRANS is 'Maximum number of transactions' / comment on column DBA_CLUSTERS.INITIAL_EXTENT is 'Size of the initial extent in bytes' / comment on column DBA_CLUSTERS.NEXT_EXTENT is 'Size of secondary extents in bytes' / comment on column DBA_CLUSTERS.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column DBA_CLUSTERS.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column DBA_CLUSTERS.PCT_INCREASE is 'Percentage increase in extent size' / remark remark FAMILY "CLU_COLUMNS" remark Mapping of cluster columns to table columns. remark This family has no ALL member. remark Creator: Vicken Khachadourian remark drop view USER_CLU_COLUMNS / create view USER_CLU_COLUMNS (CLUSTER_NAME, CLU_COLUMN_NAME, TABLE_NAME, TAB_COLUMN_NAME) as select oc.name, cc.name, ot.name, tc.name from sys.obj$ oc, sys.col$ cc, sys.obj$ ot, sys.col$ tc, sys.tab$ t where oc.obj# = cc.obj# and t.clu# = oc.obj# and t.obj# = tc.obj# and tc.segcol# = cc.segcol# and t.obj# = ot.obj# and oc.type = 3 and oc.owner# = uid / comment on table USER_CLU_COLUMNS is 'Mapping of table columns to cluster columns' / comment on column USER_CLU_COLUMNS.CLUSTER_NAME is 'Cluster name' / comment on column USER_CLU_COLUMNS.CLU_COLUMN_NAME is 'Key column in the cluster' / comment on column USER_CLU_COLUMNS.TABLE_NAME is 'Clustered table name' / comment on column USER_CLU_COLUMNS.TAB_COLUMN_NAME is 'Key column in the table' / drop public synonym USER_CLU_COLUMNS / create public synonym USER_CLU_COLUMNS for USER_CLU_COLUMNS / grant select on USER_CLU_COLUMNS to PUBLIC / drop view DBA_CLU_COLUMNS / create view DBA_CLU_COLUMNS (OWNER, CLUSTER_NAME, CLU_COLUMN_NAME, TABLE_NAME, TAB_COLUMN_NAME) as select u.name, oc.name, cc.name, ot.name, tc.name from sys.user$ u, sys.obj$ oc, sys.col$ cc, sys.obj$ ot, sys.col$ tc, sys.tab$ t where oc.owner# = u.user# and oc.obj# = cc.obj# and t.clu# = oc.obj# and t.obj# = tc.obj# and tc.segcol# = cc.segcol# and t.obj# = ot.obj# and oc.type = 3 / comment on table DBA_CLU_COLUMNS is 'Mapping of table columns to cluster columns' / comment on column DBA_CLU_COLUMNS.OWNER is 'Owner of the cluster' / comment on column DBA_CLU_COLUMNS.CLUSTER_NAME is 'Cluster name' / comment on column DBA_CLU_COLUMNS.CLU_COLUMN_NAME is 'Key column in the cluster' / comment on column DBA_CLU_COLUMNS.TABLE_NAME is 'Clustered table name' / comment on column DBA_CLU_COLUMNS.TAB_COLUMN_NAME is 'Key column in the table' / remark remark FAMILY "COL_COMMENTS" remark Comments on columns of tables and views. remark drop view USER_COL_COMMENTS / create view USER_COL_COMMENTS (TABLE_NAME, COLUMN_NAME, COMMENTS) as select o.name, c.name, co.comment$ from sys.obj$ o, sys.col$ c, sys.com$ co where o.owner# = uid and o.type in (2, 4) and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.col# = co.col#(+) / comment on table USER_COL_COMMENTS is 'Comments on columns of user''s tables and views' / comment on column USER_COL_COMMENTS.TABLE_NAME is 'Object name' / comment on column USER_COL_COMMENTS.COLUMN_NAME is 'Column name' / comment on column USER_COL_COMMENTS.COMMENTS is 'Comment on the column' / drop public synonym USER_COL_COMMENTS / create public synonym USER_COL_COMMENTS for USER_COL_COMMENTS / grant select on USER_COL_COMMENTS to PUBLIC / drop view ALL_COL_COMMENTS / create view ALL_COL_COMMENTS (OWNER, TABLE_NAME, COLUMN_NAME, COMMENTS) as select u.name, o.name, c.name, co.comment$ from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co where o.owner# = u.user# and o.type in (2, 4) and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.col# = co.col#(+) and (o.owner# = uid or o.obj# in (select obj# from sys.tabauth$ where grantee# in (uid, 1)) ) / comment on table ALL_COL_COMMENTS is 'Comments on columns of accessible tables and views' / comment on column ALL_COL_COMMENTS.OWNER is 'Owner of the object' / comment on column ALL_COL_COMMENTS.TABLE_NAME is 'Name of the object' / comment on column ALL_COL_COMMENTS.COLUMN_NAME is 'Name of the column' / comment on column ALL_COL_COMMENTS.COMMENTS is 'Comment on the column' / drop public synonym ALL_COL_COMMENTS / create public synonym ALL_COL_COMMENTS for ALL_COL_COMMENTS / grant select on ALL_COL_COMMENTS to PUBLIC / drop view DBA_COL_COMMENTS / create view DBA_COL_COMMENTS (OWNER, TABLE_NAME, COLUMN_NAME, COMMENTS) as select u.name, o.name, c.name, co.comment$ from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co where o.owner# = u.user# and o.type in (2, 4) and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.col# = co.col#(+) / comment on table DBA_COL_COMMENTS is 'Comments on columns of all tables and views' / comment on column DBA_COL_COMMENTS.OWNER is 'Name of the owner of the object' / comment on column DBA_COL_COMMENTS.TABLE_NAME is 'Name of the object' / comment on column DBA_COL_COMMENTS.COLUMN_NAME is 'Name of the column' / comment on column DBA_COL_COMMENTS.COMMENTS is 'Comment on the object' / remark remark FAMILY "COL_GRANTS" remark Grants on columns. remark drop view USER_COL_GRANTS / create view USER_COL_GRANTS (GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select ue.name, u.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ca.obj# = o.obj# and ca.grantor# = ur.user# and ca.grantee# = ue.user# and u.user# = o.owner# and uid in (ca.grantor#, ca.grantee#, o.owner#) / comment on table USER_COL_GRANTS is 'Grants on columns for which the user is the owner, grantor or grantee' / comment on column USER_COL_GRANTS.GRANTEE is 'Name of the user to whom access was granted' / comment on column USER_COL_GRANTS.OWNER is 'Username of the owner of the object' / comment on column USER_COL_GRANTS.TABLE_NAME is 'Name of the object' / comment on column USER_COL_GRANTS.COLUMN_NAME is 'Name of the column' / comment on column USER_COL_GRANTS.GRANTOR is 'Name of the user who performed the grant' / remark comment on column USER_COL_GRANTS.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column USER_COL_GRANTS.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column USER_COL_GRANTS.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column USER_COL_GRANTS.CREATED is 'Timestamp for the grant' / drop public synonym USER_COL_GRANTS / create public synonym USER_COL_GRANTS for USER_COL_GRANTS / grant select on USER_COL_GRANTS to PUBLIC / drop view COLUMN_PRIVILEGES / create view COLUMN_PRIVILEGES (GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select ue.name, u.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ca.obj# = o.obj# and ca.grantor# = ur.user# and ca.grantee# = ue.user# and u.user# = o.owner# and (uid in (ca.grantor#, ca.grantee#, o.owner#) or ca.grantee# = 1) / comment on table COLUMN_PRIVILEGES is 'Grants on columns for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee' / comment on column COLUMN_PRIVILEGES.GRANTEE is 'Name of the user to whom access was granted' / comment on column COLUMN_PRIVILEGES.OWNER is 'Username of the owner of the object' / comment on column COLUMN_PRIVILEGES.TABLE_NAME is 'Name of the object' / comment on column COLUMN_PRIVILEGES.COLUMN_NAME is 'Name of the column' / comment on column COLUMN_PRIVILEGES.GRANTOR is 'Name of the user who performed the grant' / remark comment on column COLUMN_PRIVILEGES.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column COLUMN_PRIVILEGES.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column COLUMN_PRIVILEGES.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column COLUMN_PRIVILEGES.CREATED is 'Timestamp for the grant' / drop public synonym COLUMN_PRIVILEGES / create public synonym COLUMN_PRIVILEGES for COLUMN_PRIVILEGES / drop public synonym ALL_COL_GRANTS / create public synonym ALL_COL_GRANTS for COLUMN_PRIVILEGES / grant select on COLUMN_PRIVILEGES to PUBLIC / drop view DBA_COL_GRANTS / create view DBA_COL_GRANTS (GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select ue.name, u.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ca.obj# = o.obj# and ca.grantor# = ur.user# and ca.grantee# = ue.user# and u.user# = o.owner# / comment on table DBA_COL_GRANTS is 'All grants on columns in the database' / comment on column DBA_COL_GRANTS.GRANTEE is 'Name of the user to whom access was granted' / comment on column DBA_COL_GRANTS.OWNER is 'Username of the owner of the object' / comment on column DBA_COL_GRANTS.TABLE_NAME is 'Name of the object' / comment on column DBA_COL_GRANTS.COLUMN_NAME is 'Name of the column' / comment on column DBA_COL_GRANTS.GRANTOR is 'Name of the user who performed the grant' / remark comment on column DBA_COL_GRANTS.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column DBA_COL_GRANTS.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column DBA_COL_GRANTS.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column DBA_COL_GRANTS.CREATED is 'Timestamp for the grant' / remark remark FAMILY "COL_GRANTS_MADE" remark Grants on columns made by the user. remark This family has no DBA member. remark drop view USER_COL_GRANTS_MADE / create view USER_COL_GRANTS_MADE (GRANTEE, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select ue.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ ue, sys.user$ ur where ca.obj# = o.obj# and ca.grantor# = ur.user# and ca.grantee# = ue.user# and o.owner# = uid / comment on table USER_COL_GRANTS_MADE is 'All grants on columns of objects owned by the user' / comment on column USER_COL_GRANTS_MADE.GRANTEE is 'Name of the user to whom access was granted' / comment on column USER_COL_GRANTS_MADE.TABLE_NAME is 'Name of the object' / comment on column USER_COL_GRANTS_MADE.COLUMN_NAME is 'Name of the column' / comment on column USER_COL_GRANTS_MADE.GRANTOR is 'Name of the user who performed the grant' / remark comment on column USER_COL_GRANTS_MADE.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column USER_COL_GRANTS_MADE.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column USER_COL_GRANTS_MADE.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column USER_COL_GRANTS_MADE.CREATED is 'Timestamp for the grant' / drop public synonym USER_COL_GRANTS_MADE / create public synonym USER_COL_GRANTS_MADE for USER_COL_GRANTS_MADE / grant select on USER_COL_GRANTS_MADE to PUBLIC / drop view ALL_COL_GRANTS_MADE / create view ALL_COL_GRANTS_MADE (GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select ue.name, u.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ca.obj# = o.obj# and ca.grantor# = ur.user# and ca.grantee# = ue.user# and u.user# = o.owner# and uid in (o.owner#, ca.grantor#) / comment on table ALL_COL_GRANTS_MADE is 'Grants on columns for which the user is owner or grantor' / comment on column ALL_COL_GRANTS_MADE.GRANTEE is 'Name of the user to whom access was granted' / comment on column ALL_COL_GRANTS_MADE.OWNER is 'Username of the owner of the object' / comment on column ALL_COL_GRANTS_MADE.TABLE_NAME is 'Name of the object' / comment on column ALL_COL_GRANTS_MADE.COLUMN_NAME is 'Name of the column' / comment on column ALL_COL_GRANTS_MADE.GRANTOR is 'Name of the user who performed the grant' / remark comment on column ALL_COL_GRANTS_MADE.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column ALL_COL_GRANTS_MADE.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column ALL_COL_GRANTS_MADE.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column ALL_COL_GRANTS_MADE.CREATED is 'Timestamp for the grant' / drop public synonym ALL_COL_GRANTS_MADE / create public synonym ALL_COL_GRANTS_MADE for ALL_COL_GRANTS_MADE / grant select on ALL_COL_GRANTS_MADE to PUBLIC / remark remark FAMILY "COL_GRANTS_RECD" remark Received grants on columns remark drop view USER_COL_GRANTS_RECD / create view USER_COL_GRANTS_RECD (OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select u.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ u, sys.user$ ur where ca.obj# = o.obj# and ca.grantor# = ur.user# and u.user# = o.owner# and ca.grantee# = uid / comment on table USER_COL_GRANTS_RECD is 'Grants on columns for which the user is the grantee' / comment on column USER_COL_GRANTS_RECD.OWNER is 'Username of the owner of the object' / comment on column USER_COL_GRANTS_RECD.TABLE_NAME is 'Name of the object' / comment on column USER_COL_GRANTS_RECD.COLUMN_NAME is 'Name of the column' / comment on column USER_COL_GRANTS_RECD.GRANTOR is 'Name of the user who performed the grant' / remark comment on column USER_COL_GRANTS_RECD.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column USER_COL_GRANTS_RECD.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column USER_COL_GRANTS_RECD.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column USER_COL_GRANTS_RECD.CREATED is 'Timestamp for the grant' / drop public synonym USER_COL_GRANTS_RECD / create public synonym USER_COL_GRANTS_RECD for USER_COL_GRANTS_RECD / grant select on USER_COL_GRANTS_RECD to PUBLIC / drop view ALL_COL_GRANTS_RECD / create view ALL_COL_GRANTS_RECD (GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR, /* INSERT_PRIV, */ UPDATE_PRIV, REFERENCES_PRIV, CREATED) as select ue.name, u.name, o.name, ca.name, ur.name, /* decode(ca.insert$, 0, 'N', 2, 'Y', 3, 'G', '?'), */ decode(ca.update$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ca.references$, 0, 'N', 2, 'Y', 3, 'G', '?'), ca.time from sys.colauth$ ca, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ca.obj# = o.obj# and ca.grantor# = ur.user# and ca.grantee# = ue.user# and u.user# = o.owner# and ca.grantee# in (uid, 1) / comment on table ALL_COL_GRANTS_RECD is 'Grants on columns for which the user or PUBLIC is the grantee' / comment on column ALL_COL_GRANTS_RECD.GRANTEE is 'Name of the user to whom access was granted' / comment on column ALL_COL_GRANTS_RECD.OWNER is 'Username of the owner of the object' / comment on column ALL_COL_GRANTS_RECD.TABLE_NAME is 'Name of the object' / comment on column ALL_COL_GRANTS_RECD.COLUMN_NAME is 'Name of the column' / comment on column ALL_COL_GRANTS_RECD.GRANTOR is 'Name of the user who performed the grant' / remark comment on column ALL_COL_GRANTS_RECD.INSERT_PRIV is remark 'Permission to INSERT into the column?' remark / comment on column ALL_COL_GRANTS_RECD.UPDATE_PRIV is 'Permission to UPDATE the column?' / comment on column ALL_COL_GRANTS_RECD.REFERENCES_PRIV is 'Permission to make REFERENCES to the column?' / comment on column ALL_COL_GRANTS_RECD.CREATED is 'Timestamp for the grant' / drop public synonym ALL_COL_GRANTS_RECD / create public synonym ALL_COL_GRANTS_RECD for ALL_COL_GRANTS_RECD / grant select on ALL_COL_GRANTS_RECD to PUBLIC / remark remark FAMILY "CROSS_REFS" remark Object cross-referencing information remark Creator: Brian G. Hirano remark drop view USER_CROSS_REFS / create view USER_CROSS_REFS (TABLE_NAME, TABLE_TYPE, REF_OWNER, REF_TABLE_NAME, REF_DB_LINK) as select x.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), x.rowner, x.rname, x.rnode from sys.xref$ x, sys.obj$ o where o.owner# = uid and x.name = o.name and x.owner = user / comment on table USER_CROSS_REFS is 'Cross references for user''s views and synonyms' / comment on column USER_CROSS_REFS.TABLE_NAME is 'Name of the referencing object' / comment on column USER_CROSS_REFS.TABLE_TYPE is 'Type of the referencing object' / comment on column USER_CROSS_REFS.REF_OWNER is 'Owner of the referenced object' / comment on column USER_CROSS_REFS.REF_TABLE_NAME is 'Name of the referenced object' / comment on column USER_CROSS_REFS.REF_DB_LINK is 'Database link of the referenced object' / drop public synonym USER_CROSS_REFS / create public synonym USER_CROSS_REFS for USER_CROSS_REFS / grant select on USER_CROSS_REFS to PUBLIC / drop view DBA_CROSS_REFS / create view DBA_CROSS_REFS (OWNER, TABLE_NAME, TABLE_TYPE, REF_OWNER, REF_TABLE_NAME, REF_DB_LINK) as select x.owner, x.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), x.rowner, x.rname, x.rnode from sys.xref$ x, sys.obj$ o, sys.user$ u where o.owner# = u.user# and x.name = o.name and x.owner = u.name / comment on table DBA_CROSS_REFS is 'Cross reference of all views and synonyms' / comment on column DBA_CROSS_REFS.OWNER is 'Owner of the referencing object' / comment on column DBA_CROSS_REFS.TABLE_NAME is 'Name of the referencing object' / comment on column DBA_CROSS_REFS.TABLE_TYPE is 'Type of the referencing object' / comment on column DBA_CROSS_REFS.REF_OWNER is 'Owner of the referenced object' / comment on column DBA_CROSS_REFS.REF_TABLE_NAME is 'Name of the referenced object' / comment on column DBA_CROSS_REFS.REF_DB_LINK is 'Database link of the referenced object' / remark remark FAMILY "DATA_FILES" remark Information about database files. remark This family has a DBA member only. remark drop view DBA_DATA_FILES / create view DBA_DATA_FILES (FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, BLOCKS, STATUS) as select v.name, f.file#, ts.name, ts.blocksize * f.blocks, f.blocks, decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED') from sys.file$ f, sys.ts$ ts, sys.v$dbfile v where v.file# = f.file# (+) and f.ts# = ts.ts# (+) / comment on table DBA_DATA_FILES is 'Information about database files' / comment on column DBA_DATA_FILES.FILE_NAME is 'Name of the database file' / comment on column DBA_DATA_FILES.FILE_ID is 'ID of the database file' / comment on column DBA_DATA_FILES.TABLESPACE_NAME is 'Name of the tablespace to which the file belongs' / comment on column DBA_DATA_FILES.BYTES is 'Size of the file in bytes' / comment on column DBA_DATA_FILES.BLOCKS is 'Size of the file in ORACLE blocks' / comment on column DBA_DATA_FILES.STATUS is 'File status: "INVALID" or "AVAILABLE"' / remark remark FAMILY "DB_LINKS" remark All relevant information about database links. remark drop view USER_DB_LINKS / create view USER_DB_LINKS (DB_LINK, USERNAME, PASSWORD, HOST, CREATED) as select l.name, l.userid, l.password, l.host, l.ctime from sys.link$ l where l.owner# = uid / comment on table USER_DB_LINKS is 'Database links owned by the user' / comment on column USER_DB_LINKS.DB_LINK is 'Name of the database link' / comment on column USER_DB_LINKS.USERNAME is 'Name of user to log on as' / comment on column USER_DB_LINKS.PASSWORD is 'Password for logon' / comment on column USER_DB_LINKS.HOST is 'SQL*Net string for connect' / comment on column USER_DB_LINKS.CREATED is 'Creation time of the database link' / drop public synonym USER_DB_LINKS / create public synonym USER_DB_LINKS for USER_DB_LINKS / grant select on USER_DB_LINKS to PUBLIC / drop view ALL_DB_LINKS / create view ALL_DB_LINKS (OWNER, DB_LINK, USERNAME, HOST, CREATED) as select u.name, l.name, l.userid, l.host, l.ctime from sys.link$ l, sys.user$ u where l.owner# in (uid, 1) and l.owner# = u.user# / comment on table ALL_DB_LINKS is 'Database links accessible to the user' / comment on column ALL_DB_LINKS.DB_LINK is 'Name of the database link' / comment on column ALL_DB_LINKS.USERNAME is 'Name of user to log on as' / comment on column ALL_DB_LINKS.HOST is 'SQL*Net string for connect' / comment on column ALL_DB_LINKS.CREATED is 'Creation time of the database link' / drop public synonym ALL_DB_LINKS / create public synonym ALL_DB_LINKS for ALL_DB_LINKS / grant select on ALL_DB_LINKS to PUBLIC / drop view DBA_DB_LINKS / create view DBA_DB_LINKS (OWNER, DB_LINK, USERNAME, HOST, CREATED) as select u.name, l.name, l.userid, l.host, l.ctime from sys.link$ l, sys.user$ u where l.owner# = u.user# / comment on table DBA_DB_LINKS is 'All database links in the database' / comment on column DBA_DB_LINKS.DB_LINK is 'Name of the database link' / comment on column DBA_DB_LINKS.USERNAME is 'Name of user to log on as' / comment on column DBA_DB_LINKS.HOST is 'SQL*Net string for connect' / comment on column DBA_DB_LINKS.CREATED is 'Creation time of the database link' / remark remark FAMILY "DEF_AUDIT_OPTS" remark Single row view indicating the default auditing options remark for newly created objects. remark This family has an ALL member only, since the default is remark system-wide and applies to all accessible objects. remark drop view ALL_DEF_AUDIT_OPTS / create view ALL_DEF_AUDIT_OPTS (ALT, AUD, COM, DEL, GRA, IND, INS, LOC, REN, SEL, UPD) as select substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1), substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1), substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1), substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1), substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1), substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1), substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1), substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1), substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1), substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1), substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1) from sys.obj$ o, sys.tab$ t where o.obj# = t.obj# and o.owner# = 0 and o.name = '_default_auditing_options_' / comment on table ALL_DEF_AUDIT_OPTS is 'Auditing options for newly created objects' / comment on column ALL_DEF_AUDIT_OPTS.ALT is 'Auditing ALTER WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.AUD is 'Auditing AUDIT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.COM is 'Auditing COMMENT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.DEL is 'Auditing DELETE WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.GRA is 'Auditing GRANT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.IND is 'Auditing INDEX WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.INS is 'Auditing INSERT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.LOC is 'Auditing LOCK WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.REN is 'Auditing RENAME WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.SEL is 'Auditing SELECT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column ALL_DEF_AUDIT_OPTS.UPD is 'Auditing UPDATE WHENEVER SUCCESSFUL / UNSUCCESSFUL' / drop public synonym ALL_DEF_AUDIT_OPTS / create public synonym ALL_DEF_AUDIT_OPTS for ALL_DEF_AUDIT_OPTS / grant select on ALL_DEF_AUDIT_OPTS to PUBLIC / remark remark VIEW "DICTIONARY" remark Online documentation for data dictionary tables and views. remark This view exists outside of the family schema. remark drop view DICTIONARY / create view DICTIONARY (TABLE_NAME, COMMENTS) as select o.name, c.comment$ from sys.obj$ o, sys.com$ c where o.obj# = c.obj#(+) and c.col# is null and o.owner# = 0 and o.type = 4 and (o.name like 'USER%' or o.name like 'ALL%' or (o.name like 'DBA%' and uid in (select u.user# from sys.user$ u where u.dba$ = 1) ) ) union select o.name, c.comment$ from sys.obj$ o, sys.com$ c where o.obj# = c.obj#(+) and o.owner# = 0 and o.name in ('AUDIT_ACTIONS', 'DICTIONARY', 'DICT_COLUMNS', 'DUAL', /* ANSI views */ 'ACCESSIBLE_TABLES', 'TABLE_PRIVILEGES', 'ACCESSIBLE_COLUMNS', 'COLUMN_PRIVILEGES', 'CONSTRAINT_DEFS', 'CONSTRAINT_COLUMNS') and c.col# is null union /* Find the names of public synonyms for views owned by SYS that have names different from the synonym name. This allows the user to see the short-hand synonyms we have created. */ select so.name, 'Synonym for ' || sy.name from sys.obj$ ro, sys.syn$ sy, sys.obj$ so where so.type = 5 and so.owner# = 1 and so.obj# = sy.obj# and so.name <> sy.name and sy.owner = 'SYS' and sy.name = ro.name and ro.owner# = 0 and ro.type = 4 / comment on table DICTIONARY is 'Description of data dictionary tables and views' / comment on column DICTIONARY.TABLE_NAME is 'Name of the object' / comment on column DICTIONARY.COMMENTS is 'Text comment on the object' / drop public synonym DICTIONARY / create public synonym DICTIONARY for DICTIONARY / drop public synonym DICT / create public synonym DICT for DICTIONARY / grant select on DICTIONARY to PUBLIC / remark remark VIEW "DICT_COLUMNS" remark Online documentation for columns in data dictionary tables and views. remark This view exists outside of the family schema. remark drop view DICT_COLUMNS / create view DICT_COLUMNS (TABLE_NAME, COLUMN_NAME, COMMENTS) as select o.name, c.name, co.comment$ from sys.com$ co, sys.col$ c, sys.obj$ o where o.owner# = 0 and o.type = 4 and (o.name like 'USER%' or o.name like 'ALL$' or (o.name like 'DBA%' and uid in (select u.user# from sys.user$ u where u.dba$ = 1) ) ) and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.col# = co.col#(+) union select o.name, c.name, co.comment$ from sys.com$ co, sys.col$ c, sys.obj$ o where o.owner# = 0 and o.name in ('AUDIT_ACTIONS','DUAL','DICTIONARY', 'DICT_COLUMNS', /* ANSI views */ 'ACCESSIBLE_TABLES', 'TABLE_PRIVILEGES', 'ACCESSIBLE_COLUMNS', 'COLUMN_PRIVILEGES', 'CONSTRAINT_DEFS', 'CONSTRAINT_COLUMNS') and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.col# = co.col#(+) union /* Find the column comments for public synonyms for views owned by SYS that have names different from the synonym name. This allows the user to see the columns of the short-hand synonyms we have created. */ select so.name, c.name, co.comment$ from sys.com$ co,sys.col$ c, sys.obj$ ro, sys.syn$ sy, sys.obj$ so where so.type = 5 and so.owner# = 1 and so.obj# = sy.obj# and so.name <> sy.name and sy.owner = 'SYS' and sy.name = ro.name and ro.owner# = 0 and ro.type = 4 and ro.obj# = c.obj# and c.col# = co.col#(+) and c.obj# = co.obj#(+) / comment on table DICT_COLUMNS is 'Description of columns in data dictionary tables and views' / comment on column DICT_COLUMNS.TABLE_NAME is 'Name of the object that contains the column' / comment on column DICT_COLUMNS.COLUMN_NAME is 'Name of the column' / comment on column DICT_COLUMNS.COMMENTS is 'Text comment on the object' / drop public synonym DICT_COLUMNS / create public synonym DICT_COLUMNS for DICT_COLUMNS / grant select on DICT_COLUMNS to PUBLIC / remark remark FAMILY "EXP_OBJECTS" remark Objects that have been incrementally exported. remark This family has a DBA member only. remark Creator: Vicken Khachadourian remark drop view DBA_EXP_OBJECTS / create view DBA_EXP_OBJECTS (OWNER, OBJECT_NAME, OBJECT_TYPE, CUMULATIVE, INCREMENTAL, EXPORT_VERSION) as select u.name, o.name, decode(o.type, 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), o.ctime, o.itime, o.expid from sys.incexp o, sys.user$ u where o.owner# = u.user# / comment on table DBA_EXP_OBJECTS is 'Objects that have been incrementally exported' / comment on column DBA_EXP_OBJECTS.OWNER is 'Owner of exported object' / comment on column DBA_EXP_OBJECTS.OBJECT_NAME is 'Name of exported object' / comment on column DBA_EXP_OBJECTS.OBJECT_TYPE is 'Type of exported object' / comment on column DBA_EXP_OBJECTS.CUMULATIVE is 'Timestamp of last cumulative export' / comment on column DBA_EXP_OBJECTS.INCREMENTAL is 'Timestamp of last incremental export' / comment on column DBA_EXP_OBJECTS.EXPORT_VERSION is 'The id of the export session' / remark remark FAMILY "EXP_VERSION" remark Version number of last incremental export remark This family has a DBA member only. remark Creator: Vicken Khachadourian remark drop view DBA_EXP_VERSION / create view DBA_EXP_VERSION (EXP_VERSION) as select o.expid from sys.incvid o / comment on table DBA_EXP_VERSION is 'Version number of the last export session' / comment on column DBA_EXP_VERSION.EXP_VERSION is 'Version number of the last export session' / remark remark FAMILY "EXP_FILES" remark Files created by incremental exports. remark This family has a DBA member only. remark Creator: Vicken Khachadourian remark drop view DBA_EXP_FILES / create view DBA_EXP_FILES (EXP_VERSION, EXP_TYPE, FILE_NAME, USER_NAME, TIMESTAMP) as select o.expid, decode(o.exptype, 'X', 'COMPLETE', 'C', 'CUMULATIVE', 'I', 'INCREMENTAL', 'UNDEFINED'), o.expfile, o.expuser, o.expdate from sys.incfil o / comment on table DBA_EXP_FILES is 'Description of export files' / comment on column DBA_EXP_FILES.EXP_VERSION is 'Version number of the export session' / comment on column DBA_EXP_FILES.FILE_NAME is 'Name of the export file' / comment on column DBA_EXP_FILES.USER_NAME is 'Name of user who executed export' / comment on column DBA_EXP_FILES.TIMESTAMP is 'Timestamp of the export session' / remark remark FAMILY "FREE_SPACE" remark Free extents. remark This family has no ALL member. remark drop view USER_FREE_SPACE / create view USER_FREE_SPACE (TABLESPACE_NAME, FILE_ID, BLOCK_ID, BYTES, BLOCKS) as select ts.name, f.file#, f.block#, f.length * ts.blocksize, f.length from sys.fet$ f, sys.ts$ ts where f.ts# = ts.ts# and (ts.ts# in (select tsq.ts# from sys.tsq$ tsq where tsq.user# = uid) or uid in (select u.user# from sys.user$ u where u.resource$ = 1) ) / comment on table USER_FREE_SPACE is 'Free extents in tablespaces accessible to the user' / comment on column USER_FREE_SPACE.TABLESPACE_NAME is 'Name of the tablespace containing the extent' / comment on column USER_FREE_SPACE.FILE_ID is 'ID number of the file containing the extent' / comment on column USER_FREE_SPACE.BLOCK_ID is 'Starting block number of the extent' / comment on column USER_FREE_SPACE.BYTES is 'Size of the extent in bytes' / comment on column USER_FREE_SPACE.BLOCKS is 'Size of the extent in ORACLE blocks' / drop public synonym USER_FREE_SPACE / create public synonym USER_FREE_SPACE for USER_FREE_SPACE / grant select on USER_FREE_SPACE to PUBLIC / drop view DBA_FREE_SPACE / create view DBA_FREE_SPACE (TABLESPACE_NAME, FILE_ID, BLOCK_ID, BYTES, BLOCKS) as select ts.name, f.file#, f.block#, f.length * ts.blocksize, f.length from sys.fet$ f, sys.ts$ ts where f.ts# = ts.ts# (+) / comment on table DBA_FREE_SPACE is 'Free extents in all tablespaces' / comment on column DBA_FREE_SPACE.TABLESPACE_NAME is 'Name of the tablespace containing the extent' / comment on column DBA_FREE_SPACE.FILE_ID is 'ID number of the file containing the extent' / comment on column DBA_FREE_SPACE.BLOCK_ID is 'Starting block number of the extent' / comment on column DBA_FREE_SPACE.BYTES is 'Size of the extent in bytes' / comment on column DBA_FREE_SPACE.BLOCKS is 'Size of the extent in ORACLE blocks' / remark remark FAMILY "INDEXES" remark CREATE INDEX parameters. remark drop view USER_INDEXES / create view USER_INDEXES (INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE) as select o.name, iu.name, io.name, decode(io.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), decode(i.unique$, 0, 'NONUNIQUE', 1, 'UNIQUE', 'UNDEFINED'), ts.name, i.initrans, i.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, i.pctfree$ from sys.ts$ ts, sys.seg$ s, sys.user$ iu, sys.obj$ io, sys.ind$ i, sys.obj$ o where o.owner# = uid and o.obj# = i.obj# and i.bo# = io.obj# and io.owner# = iu.user# and i.ts# = ts.ts# and i.file# = s.file# and i.block# = s.block# / comment on table USER_INDEXES is 'Description of the user''s own indexes' / comment on column USER_INDEXES.INDEX_NAME is 'Name of the index' / comment on column USER_INDEXES.TABLE_OWNER is 'Owner of the indexed object' / comment on column USER_INDEXES.TABLE_NAME is 'Name of the indexed object' / comment on column USER_INDEXES.TABLE_TYPE is 'Type of the indexed object' / comment on column USER_INDEXES.UNIQUENESS is 'Uniqueness status of the index: "UNIQUE" or "NONUNIQUE"' / comment on column USER_INDEXES.TABLESPACE_NAME is 'Name of the tablespace containing the index' / comment on column USER_INDEXES.INI_TRANS is 'Initial number of transactions' / comment on column USER_INDEXES.MAX_TRANS is 'Maximum number of transactions' / comment on column USER_INDEXES.INITIAL_EXTENT is 'Size of the initial extent in bytes' / comment on column USER_INDEXES.NEXT_EXTENT is 'Size of secondary extents in bytes' / comment on column USER_INDEXES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column USER_INDEXES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column USER_INDEXES.PCT_INCREASE is 'Percentage increase in extent size' / comment on column USER_INDEXES.PCT_FREE is 'Minimum precentage of free space in a block' / drop public synonym USER_INDEXES / create public synonym USER_INDEXES for USER_INDEXES / drop public synonym IND / create public synonym IND for USER_INDEXES / grant select on USER_INDEXES to PUBLIC / remark remark This view does not include cluster indexes on clusters remark containing tables which are accessible to the user. remark drop view ALL_INDEXES / create view ALL_INDEXES (OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE) as select u.name, o.name, iu.name, io.name, decode(io.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), decode(i.unique$, 0, 'NONUNIQUE', 1, 'UNIQUE', 'UNDEFINED'), ts.name, i.initrans, i.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct , i.pctfree$ from sys.ts$ ts, sys.seg$ s, sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o where u.user# = o.owner# and o.obj# = i.obj# and i.bo# = io.obj# and io.owner# = iu.user# and i.ts# = ts.ts# and i.file# = s.file# and i.block# = s.block# and (io.owner# = uid or io.obj# in ( select obj# from tabauth$ where grantee# in ( uid, 1 ) ) ) / comment on table ALL_INDEXES is 'Descriptions of indexes on tables accessible to the user' / comment on column ALL_INDEXES.OWNER is 'Username of the owner of the index' / comment on column ALL_INDEXES.INDEX_NAME is 'Name of the index' / comment on column ALL_INDEXES.TABLE_OWNER is 'Owner of the indexed object' / comment on column ALL_INDEXES.TABLE_NAME is 'Name of the indexed object' / comment on column ALL_INDEXES.TABLE_TYPE is 'Type of the indexed object' / comment on column ALL_INDEXES.UNIQUENESS is 'Uniqueness status of the index: "UNIQUE" or "NONUNIQUE"' / comment on column ALL_INDEXES.TABLESPACE_NAME is 'Name of the tablespace containing the index' / comment on column ALL_INDEXES.INI_TRANS is 'Initial number of transactions' / comment on column ALL_INDEXES.MAX_TRANS is 'Maximum number of transactions' / comment on column ALL_INDEXES.INITIAL_EXTENT is 'Size of the initial extent' / comment on column ALL_INDEXES.NEXT_EXTENT is 'Size of secondary extents' / comment on column ALL_INDEXES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column ALL_INDEXES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column ALL_INDEXES.PCT_INCREASE is 'Percentage increase in extent size' / comment on column ALL_INDEXES.PCT_FREE is 'Minimum percentage of free space in a block' / drop public synonym ALL_INDEXES / create public synonym ALL_INDEXES for ALL_INDEXES / grant select on ALL_INDEXES to PUBLIC / drop view DBA_INDEXES / create view DBA_INDEXES (OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE) as select u.name, o.name, iu.name, io.name, decode(io.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), decode(i.unique$, 0, 'NONUNIQUE', 1, 'UNIQUE', 'UNDEFINED'), ts.name, i.initrans, i.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, i.pctfree$ from sys.ts$ ts, sys.seg$ s, sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o where u.user# = o.owner# and o.obj# = i.obj# and i.bo# = io.obj# and io.owner# = iu.user# and i.ts# = ts.ts# and i.file# = s.file# and i.block# = s.block# / comment on table DBA_INDEXES is 'Description for all indexes in the database' / comment on column DBA_INDEXES.OWNER is 'Username of the owner of the index' / comment on column DBA_INDEXES.INDEX_NAME is 'Name of the index' / comment on column DBA_INDEXES.TABLE_OWNER is 'Owner of the indexed object' / comment on column DBA_INDEXES.TABLE_NAME is 'Name of the indexed object' / comment on column DBA_INDEXES.TABLE_TYPE is 'Type of the indexed object' / comment on column DBA_INDEXES.UNIQUENESS is 'Uniqueness status of the index: "UNIQUE" or "NONUNIQUE"' / comment on column DBA_INDEXES.TABLESPACE_NAME is 'Name of the tablespace containing the index' / comment on column DBA_INDEXES.INI_TRANS is 'Initial number of transactions' / comment on column DBA_INDEXES.MAX_TRANS is 'Maximum number of transactions' / comment on column DBA_INDEXES.INITIAL_EXTENT is 'Size of the initial extent' / comment on column DBA_INDEXES.NEXT_EXTENT is 'Size of secondary extents' / comment on column DBA_INDEXES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column DBA_INDEXES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column DBA_INDEXES.PCT_INCREASE is 'Percentage increase in extent size' / comment on column DBA_INDEXES.PCT_FREE is 'Minimum percentage of free space in a block' / remark remark FAMILY "IND_COLUMNS" remark Displays information on which columns are contained in which remark indexes remark drop view USER_IND_COLUMNS / create view USER_IND_COLUMNS (INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMN_LENGTH) as select idx.name, base.name, c.name, ic.pos#, c.length from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic where base.obj# = c.obj# and ic.bo# = base.obj# and ic.col# = c.col# and (base.owner# = uid or idx.owner# = uid) and ic.obj# = idx.obj# / comment on table USER_IND_COLUMNS is 'COLUMNs comprising user''s INDEXes or on user''s TABLES' / comment on column USER_IND_COLUMNS.INDEX_NAME is 'Index name' / comment on column USER_IND_COLUMNS.TABLE_NAME is 'Table or cluster name' / comment on column USER_IND_COLUMNS.COLUMN_NAME is 'Column name' / comment on column USER_IND_COLUMNS.COLUMN_POSITION is 'Position of column within index' / comment on column USER_IND_COLUMNS.COLUMN_LENGTH is 'Indexed length of the column' / drop public synonym USER_IND_COLUMNS / create public synonym USER_IND_COLUMNS for USER_IND_COLUMNS / grant select on USER_IND_COLUMNS to PUBLIC / drop view ALL_IND_COLUMNS / create view ALL_IND_COLUMNS (INDEX_OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMN_LENGTH) as select io.name, idx.name, bo.name, base.name, c.name, ic.pos#, c.length from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic, sys.user$ io, sys.user$ bo where base.obj# = c.obj# and ic.col# = c.col# and ic.bo# = base.obj# and io.user# = idx.owner# and bo.user# = base.owner# and ic.obj# = idx.obj# and (idx.owner# = uid or base.owner# = uid or base.obj# in ( select obj# from sys.tabauth$ where grantee# in ( uid, 1 ) ) ) / comment on table ALL_IND_COLUMNS is 'COLUMNs comprising INDEXes on accessible TABLES' / comment on column ALL_IND_COLUMNS.INDEX_OWNER is 'Index owner' / comment on column ALL_IND_COLUMNS.INDEX_NAME is 'Index name' / comment on column ALL_IND_COLUMNS.TABLE_OWNER is 'Table or cluster owner' / comment on column ALL_IND_COLUMNS.TABLE_NAME is 'Table or cluster name' / comment on column ALL_IND_COLUMNS.COLUMN_NAME is 'Column name' / comment on column ALL_IND_COLUMNS.COLUMN_POSITION is 'Position of column within index' / comment on column ALL_IND_COLUMNS.COLUMN_LENGTH is 'Indexed length of the column' / drop public synonym ALL_IND_COLUMNS / create public synonym ALL_IND_COLUMNS for ALL_IND_COLUMNS / grant select on ALL_IND_COLUMNS to PUBLIC / drop view DBA_IND_COLUMNS / create view DBA_IND_COLUMNS (INDEX_OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMN_LENGTH) as select io.name, idx.name, bo.name, base.name, c.name, ic.pos#, c.length from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic, sys.user$ io, sys.user$ bo where base.obj# = c.obj# and ic.col# = c.col# and ic.bo# = base.obj# and io.user# = idx.owner# and bo.user# = base.owner# and ic.obj# = idx.obj# / comment on table DBA_IND_COLUMNS is 'COLUMNs comprising INDEXes on all TABLEs and CLUSTERs' / comment on column DBA_IND_COLUMNS.INDEX_OWNER is 'Index owner' / comment on column DBA_IND_COLUMNS.INDEX_NAME is 'Index name' / comment on column DBA_IND_COLUMNS.TABLE_OWNER is 'Table or cluster owner' / comment on column DBA_IND_COLUMNS.TABLE_NAME is 'Table or cluster name' / comment on column DBA_IND_COLUMNS.COLUMN_NAME is 'Column name' / comment on column DBA_IND_COLUMNS.COLUMN_POSITION is 'Position of column within index' / comment on column DBA_IND_COLUMNS.COLUMN_LENGTH is 'Indexed length of the column' / drop view INDEX_STATS / create view INDEX_STATS as select kdxstrot+1 height, s.blocks, o.name, kdxstlrw lf_rows, kdxstlbk lf_blks, kdxstlln lf_rows_len, kdxstlub lf_blk_len, kdxstbrw br_rows, kdxstbbk br_blks, kdxstbln br_rows_len, kdxstbub br_blk_len, kdxstdrw del_lf_rows, kdxstdln del_lf_rows_len, kdxstdis distinct_keys, kdxstmrl most_repeated_key, kdxstlbk*kdxstlub+kdxstbbk*kdxstbub btree_space, kdxstlln+kdxstbln used_space, ceil(((kdxstlln+kdxstbln)*100)/ (kdxstlbk*kdxstlub+kdxstbbk*kdxstbub)) pct_used, kdxstlrw/kdxstdis rows_per_key, kdxstrot+1+(kdxstlrw+kdxstdis)/(kdxstdis*2) blks_gets_per_access from obj$ o, ind$ i, seg$ s, x$kdxst where kdxstfil = s.file# and kdxstblk = s.block# and s.file# = i.file# and s.block# = i.block# and i.obj# = o.obj# / comment on column index_stats.height is 'height of the b-tree' / comment on column index_stats.blocks is 'blocks allocated to the segment' / comment on column index_stats.name is 'name of the index' / comment on column index_stats.lf_rows is 'number of leaf rows (values in the index)' / comment on column index_stats.lf_blks is 'number of leaf blocks in the b-tree' / comment on column index_stats.lf_rows_len is 'sum of the lengths of all the leaf rows' / comment on column index_stats.lf_blk_len is 'useable space in a leaf block' / comment on column index_stats.br_rows is 'number of branch rows' / comment on column index_stats.br_blks is 'number of branch blocks in the b-tree' / comment on column index_stats.br_rows_len is 'sum of the lengths of all the branch blocks in the b-tree' / comment on column index_stats.br_blk_len is 'useable space in a branch block' / comment on column index_stats.del_lf_rows is 'number of deleted leaf rows in the index' / comment on column index_stats.del_lf_rows_len is 'total length of all deleted rows in the index' / comment on column index_stats.distinct_keys is 'number of distinct keys in the index' / comment on column index_stats.most_repeated_key is 'how many times the most repeated key is repeated' / comment on column index_stats.btree_space is 'total space currently allocated in the b-tree' / comment on column index_stats.used_space is 'total space that is currently being used in the b-tree' / comment on column index_stats.pct_used is 'percent of space allocated in the b-tree that is being used' / comment on column index_stats.rows_per_key is 'average number of rows per distinct key' / comment on column index_stats.blks_gets_per_access is 'Expected number of consistent mode block gets per row. This assumes that a row chosen at random from the table is being searched for using the index' / drop public synonym INDEX_STATS / create public synonym INDEX_STATS for INDEX_STATS / grant select on INDEX_STATS to public / drop view INDEX_HISTOGRAM / create view INDEX_HISTOGRAM as select hist.indx * power(2, stats.kdxstscl-4) repeat_count, hist.kdxhsval keys_with_repeat_count from x$kdxst stats, x$kdxhs hist / comment on column index_histogram.repeat_count is 'number of times that a key is repeated' / comment on column index_histogram.keys_with_repeat_count is 'number of keys that are repeated REPEAT_COUNT times' / drop public synonym INDEX_HISTOGRAM / create public synonym INDEX_HISTOGRAM for INDEX_HISTOGRAM / grant select on INDEX_HISTOGRAM to public / remark remark FAMILY "OBJECTS" remark List of objects, including creation and modify times. remark drop view USER_OBJECTS / create view USER_OBJECTS (OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, MODIFIED) as select o.name, o.obj#, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), o.ctime, o.mtime from sys.obj$ o where o.owner# = uid union select l.name, to_number(null), 'DATABASE LINK', l.ctime, to_date(null) from sys.link$ l where l.owner# = uid / comment on table USER_OBJECTS is 'Objects owned by the user' / comment on column USER_OBJECTS.OBJECT_NAME is 'Name of the object' / comment on column USER_OBJECTS.OBJECT_ID is 'Object number of the object' / comment on column USER_OBJECTS.OBJECT_TYPE is 'Type of the object' / comment on column USER_OBJECTS.CREATED is 'Timestamp for the creation of the object' / comment on column USER_OBJECTS.MODIFIED is 'Timestamp for the last DDL change to the object' / drop public synonym USER_OBJECTS / create public synonym USER_OBJECTS for USER_OBJECTS / drop public synonym OBJ / create public synonym OBJ for USER_OBJECTS / grant select on USER_OBJECTS to PUBLIC / drop view ALL_OBJECTS / create view ALL_OBJECTS (OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, MODIFIED) as select u.name, o.name, o.obj#, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), o.ctime, o.mtime from sys.obj$ o, sys.user$ u where o.owner# = u.user# and (o.owner# in (uid, 1) or o.obj# in ( select obj# from sys.tabauth$ where grantee# in ( uid, 1 ) ) ) / comment on table ALL_OBJECTS is 'Objects accessible to the user' / comment on column ALL_OBJECTS.OWNER is 'Username of the owner of the object' / comment on column ALL_OBJECTS.OBJECT_NAME is 'Name of the object' / comment on column ALL_OBJECTS.OBJECT_ID is 'Object number of the object' / comment on column ALL_OBJECTS.OBJECT_TYPE is 'Type of the object' / comment on column ALL_OBJECTS.CREATED is 'Timestamp for the creation of the object' / comment on column ALL_OBJECTS.MODIFIED is 'Timestamp for the last DDL change to the object' / drop public synonym ALL_OBJECTS / create public synonym ALL_OBJECTS for ALL_OBJECTS / grant select on ALL_OBJECTS to PUBLIC / drop view DBA_OBJECTS / create view DBA_OBJECTS (OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, MODIFIED) as select u.name, o.name, o.obj#, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'), o.ctime, o.mtime from sys.obj$ o, sys.user$ u where o.owner# = u.user# union select u.name, l.name, to_number(null), 'DATABASE LINK', l.ctime, to_date(null) from sys.link$ l, sys.user$ u where l.owner# = u.user# / comment on table DBA_OBJECTS is 'All objects in the database' / comment on column DBA_OBJECTS.OWNER is 'Username of the owner of the object' / comment on column DBA_OBJECTS.OBJECT_NAME is 'Name of the object' / comment on column DBA_OBJECTS.OBJECT_ID is 'Object number of the object' / comment on column DBA_OBJECTS.OBJECT_TYPE is 'Type of the object' / comment on column DBA_OBJECTS.CREATED is 'Timestamp for the creation of the object' / comment on column DBA_OBJECTS.MODIFIED is 'Timestamp for the last DDL change to the object' / remark remark FAMILY "ROLLBACK_SEGS" remark CREATE ROLLBACK SEGMENT parameters. remark This family has a DBA member only. remark drop view DBA_ROLLBACK_SEGS / create view DBA_ROLLBACK_SEGS (SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, BLOCK_ID, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS) as select un.name, decode(un.user#,1,'PUBLIC','SYS'), ts.name, un.us#, un.file#, un.block#, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, decode(un.status$, 1, 'INVALID', 2, 'AVAILABLE', 3, 'IN USE', 4, 'OFFLINE', 5, 'NEEDS RECOVERY', 'UNDEFINED') from sys.undo$ un, sys.seg$ s, sys.ts$ ts where un.file# = s.file# (+) and un.block# = s.block# (+) and s.type = 1 and s.ts# = ts.ts# / comment on table DBA_ROLLBACK_SEGS is 'Description of rollback segments' / comment on column DBA_ROLLBACK_SEGS.SEGMENT_NAME is 'Name of the rollback segment' / comment on column DBA_ROLLBACK_SEGS.OWNER is 'Owner of the rollback segment' / comment on column DBA_ROLLBACK_SEGS.TABLESPACE_NAME is 'Name of the tablespace containing the rollback segment' / comment on column DBA_ROLLBACK_SEGS.SEGMENT_ID is 'ID number of the rollback segment' / comment on column DBA_ROLLBACK_SEGS.FILE_ID is 'ID number of the file containing the segment header' / comment on column DBA_ROLLBACK_SEGS.FILE_ID is 'ID number of the block containing the segment header' / comment on column DBA_ROLLBACK_SEGS.INITIAL_EXTENT is 'Initial extent size in bytes' / comment on column DBA_ROLLBACK_SEGS.NEXT_EXTENT is 'Secondary extent size in bytes' / comment on column DBA_ROLLBACK_SEGS.MIN_EXTENTS is 'Minimum number of extents' / comment on column DBA_ROLLBACK_SEGS.MAX_EXTENTS is 'Maximum number of extents' / comment on column DBA_ROLLBACK_SEGS.PCT_INCREASE is 'Percent increase for extent size' / comment on column DBA_ROLLBACK_SEGS.STATUS is 'Rollback segment status' / remark FAMILY "SEGMENTS" remark Storage for all types of segments remark This family has no ALL member. drop view SYS_OBJECTS / remark define SYS_OBJECTS for use by segments views create view SYS_OBJECTS (OBJECT_TYPE, OBJECT_TYPE_ID, SEGMENT_TYPE_ID, OBJECT_ID, HEADER_FILE, HEADER_BLOCK) as select 'TABLE', 2, 5, t.obj#, t.file#, t.block# from sys.tab$ t where t.clu# is null /* exclude clustered tables */ union select 'CLUSTER', 3, 5, c.obj#, c.file#, c.block# from sys.clu$ c union select 'INDEX', 1, 6, i.obj#, i.file#, i.block# from sys.ind$ i / drop view USER_SEGMENTS / create view USER_SEGMENTS (SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS, MAX_EXTENTS) as select o.name, so.object_type, ts.name, s.blocks * ts.blocksize, s.blocks, s.extents, s.maxexts from sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s where s.file# = so.header_file and s.block# = so.header_block and s.ts# = ts.ts# and o.obj# = so.object_id and o.owner# = uid and s.type = so.segment_type_id and o.type = so.object_type_id union select un.name, 'ROLLBACK', ts.name, s.blocks * ts.blocksize, s.blocks, s.extents, s.maxexts from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s where s.file# = un.file# and s.block# = un.block# and s.ts# = ts.ts# and s.user# = uid and s.type = 1 union select to_char(s.file#) || '.' || to_char(s.block#), decode(s.type, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY', 4, 'CACHE', 'UNDEFINED'), ts.name, s.blocks * ts.blocksize, s.blocks, s.extents, s.maxexts from sys.ts$ ts, sys.seg$ s where s.ts# = ts.ts# and s.user# = uid and s.type not in (1, 5, 6) / comment on table USER_SEGMENTS is 'Storage allocated for all database segments' / comment on column USER_SEGMENTS.SEGMENT_NAME is 'Name, if any, of the segment' / comment on column USER_SEGMENTS.SEGMENT_TYPE is 'Type of segment: "TABLE", "CLUSTER", "INDEX", "ROLLBACK", "DEFERRED ROLLBACK", "TEMPORARY", or "CACHE"' / comment on column USER_SEGMENTS.TABLESPACE_NAME is 'Name of the tablespace containing the segment' / comment on column USER_SEGMENTS.BYTES is 'Size, in bytes, of the segment' / comment on column USER_SEGMENTS.BLOCKS is 'Size, in Oracle blocks, of the segment' / comment on column USER_SEGMENTS.EXTENTS is 'Number of extents allocated to the segment' / comment on column USER_SEGMENTS.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / drop public synonym USER_SEGMENTS / create public synonym USER_SEGMENTS for USER_SEGMENTS / grant select on USER_SEGMENTS to PUBLIC / drop view DBA_SEGMENTS / create view DBA_SEGMENTS (OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS, MAX_EXTENTS) as select u.name, o.name, so.object_type, ts.name, s.file#, s.block#, s.blocks * ts.blocksize, s.blocks, s.extents, s.maxexts from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s where s.file# = so.header_file and s.block# = so.header_block and s.ts# = ts.ts# and o.obj# = so.object_id and o.owner# = u.user# and s.type = so.segment_type_id and o.type = so.object_type_id union select u.name, un.name, 'ROLLBACK', ts.name, s.file#, s.block#, s.blocks * ts.blocksize, s.blocks, s.extents, s.maxexts from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s where s.file# = un.file# and s.block# = un.block# and s.ts# = ts.ts# and s.user# = u.user# and s.type = 1 union select u.name, to_char(s.file#) || '.' || to_char(s.block#), decode(s.type, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY', 4, 'CACHE', 'UNDEFINED'), ts.name, s.file#, s.block#, s.blocks * ts.blocksize, s.blocks, s.extents, s.maxexts from sys.user$ u, sys.ts$ ts, sys.seg$ s where s.ts# = ts.ts# and s.user# = u.user# and s.type not in (1, 5, 6) / comment on table DBA_SEGMENTS is 'Storage allocated for all database segments' / comment on column DBA_SEGMENTS.OWNER is 'Username of the segment owner' / comment on column DBA_SEGMENTS.SEGMENT_NAME is 'Name, if any, of the segment' / comment on column DBA_SEGMENTS.SEGMENT_TYPE is 'Type of segment: "TABLE", "CLUSTER", "INDEX", "ROLLBACK", "DEFERRED ROLLBACK", "TEMPORARY", or "CACHE"' / comment on column DBA_SEGMENTS.TABLESPACE_NAME is 'Name of the tablespace containing the segment' / comment on column DBA_SEGMENTS.HEADER_FILE is 'ID of the file containing the segment header' / comment on column DBA_SEGMENTS.HEADER_BLOCK is 'ID of the block containing the segment header' / comment on column DBA_SEGMENTS.BYTES is 'Size, in bytes, of the segment' / comment on column DBA_SEGMENTS.BLOCKS is 'Size, in Oracle blocks, of the segment' / comment on column DBA_SEGMENTS.EXTENTS is 'Number of extents allocated to the segment' / comment on column DBA_SEGMENTS.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / remark remark FAMILY "EXTENTS" remark Extents associated with their segments. remark drop view USER_EXTENTS / create view USER_EXTENTS (SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS) as select ds.segment_name, ds.segment_type, ds.tablespace_name, e.ext#, e.length * ds.bytes / ds.blocks, e.length from sys.uet$ e, sys.dba_segments ds where e.segfile# = ds.header_file and e.segblock# = ds.header_block and ds.owner = user / comment on table USER_EXTENTS is 'Extents comprising segments owned by the user' / comment on column USER_EXTENTS.SEGMENT_NAME is 'Name of the segment associated with the extent' / comment on column USER_EXTENTS.TABLESPACE_NAME is 'Name of the tablespace containing the extent' / comment on column USER_EXTENTS.SEGMENT_TYPE is 'Type of the segment' / comment on column USER_EXTENTS.EXTENT_ID is 'Extent number in the segment' / comment on column USER_EXTENTS.BYTES is 'Size of the extent in bytes' / comment on column USER_EXTENTS.BLOCKS is 'Size of the extent in ORACLE blocks' / drop public synonym USER_EXTENTS / create public synonym USER_EXTENTS for USER_EXTENTS / grant select on USER_EXTENTS to PUBLIC / drop view DBA_EXTENTS / create view DBA_EXTENTS (OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS) as select ds.owner, ds.segment_name, ds.segment_type, ds.tablespace_name, e.ext#, e.file#, e.block#, e.length * ds.bytes / ds.blocks, e.length from sys.uet$ e, sys.dba_segments ds where e.segfile# = ds.header_file and e.segblock# = ds.header_block / comment on table DBA_EXTENTS is 'Extents comprising all segments in the database' / comment on column DBA_EXTENTS.OWNER is 'Owner of the segment associated with the extent' / comment on column DBA_EXTENTS.SEGMENT_NAME is 'Name of the segment associated with the extent' / comment on column DBA_EXTENTS.TABLESPACE_NAME is 'Name of the tablespace containing the extent' / comment on column DBA_EXTENTS.SEGMENT_TYPE is 'Type of the segment' / comment on column DBA_EXTENTS.FILE_ID is 'Name of the file containing the extent' / comment on column DBA_EXTENTS.BLOCK_ID is 'Starting block number of the extent' / comment on column DBA_EXTENTS.EXTENT_ID is 'Extent number in the segment' / comment on column DBA_EXTENTS.BYTES is 'Size of the extent in bytes' / comment on column DBA_EXTENTS.BLOCKS is 'Size of the extent in ORACLE blocks' / remark remark FAMILY "SEQUENCES" remark CREATE SEQUENCE information. remark drop view USER_SEQUENCES / create view USER_SEQUENCES (SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, LAST_NUMBER) as select o.name, s.minvalue, s.maxvalue, s.increment$, decode (s.cycle, 0, 'N', 1, 'Y'), decode (s.order$, 0, 'N', 1, 'Y'), s.cache, s.highwater from sys.seq$ s, sys.obj$ o where o.owner# = uid and o.obj# = s.obj# / comment on table USER_SEQUENCES is 'Description of the user''s own SEQUENCEs' / comment on column USER_SEQUENCES.SEQUENCE_NAME is 'SEQUENCE name' / comment on column USER_SEQUENCES.INCREMENT_BY is 'Value by which sequence is incremented' / comment on column USER_SEQUENCES.MIN_VALUE is 'Minimum value of the sequence' / comment on column USER_SEQUENCES.MAX_VALUE is 'Maximum value of the sequence' / comment on column USER_SEQUENCES.CYCLE_FLAG is 'Does sequence wrap around on reaching limit?' / comment on column USER_SEQUENCES.ORDER_FLAG is 'Are sequence numbers generated in order?' / comment on column USER_SEQUENCES.CACHE_SIZE is 'Number of sequence numbers to cache' / comment on column USER_SEQUENCES.LAST_NUMBER is 'Last sequence number written to disk' / drop public synonym USER_SEQUENCES / create public synonym USER_SEQUENCES for USER_SEQUENCES / drop public synonym SEQ / create public synonym SEQ for USER_SEQUENCES / grant select on USER_SEQUENCES to PUBLIC / drop view ALL_SEQUENCES / create view ALL_SEQUENCES (SEQUENCE_OWNER, SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, LAST_NUMBER) as select u.name, o.name, s.minvalue, s.maxvalue, s.increment$, decode (s.cycle, 0, 'N', 1, 'Y'), decode (s.order$, 0, 'N', 1, 'Y'), s.cache, s.highwater from sys.seq$ s, sys.obj$ o, sys.user$ u where u.user# = o.owner# and o.obj# = s.obj# and (o.owner# = uid or o.obj# in (select ta.obj# from sys.tabauth$ ta where ta.grantee# in (uid, 1)) ) / comment on table ALL_SEQUENCES is 'Description of SEQUENCEs accessible to the user' / comment on column ALL_SEQUENCES.SEQUENCE_OWNER is 'Name of the owner of the sequence' / comment on column ALL_SEQUENCES.SEQUENCE_NAME is 'SEQUENCE name' / comment on column ALL_SEQUENCES.INCREMENT_BY is 'Value by which sequence is incremented' / comment on column ALL_SEQUENCES.MIN_VALUE is 'Minimum value of the sequence' / comment on column ALL_SEQUENCES.MAX_VALUE is 'Maximum value of the sequence' / comment on column ALL_SEQUENCES.CYCLE_FLAG is 'Does sequence wrap around on reaching limit?' / comment on column ALL_SEQUENCES.ORDER_FLAG is 'Are sequence numbers generated in order?' / comment on column ALL_SEQUENCES.CACHE_SIZE is 'Number of sequence numbers to cache' / comment on column ALL_SEQUENCES.LAST_NUMBER is 'Last sequence number written to disk' / drop public synonym ALL_SEQUENCES / create public synonym ALL_SEQUENCES for ALL_SEQUENCES / grant select on ALL_SEQUENCES to PUBLIC / drop view DBA_SEQUENCES / create view DBA_SEQUENCES (SEQUENCE_OWNER, SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, LAST_NUMBER) as select u.name, o.name, s.minvalue, s.maxvalue, s.increment$, decode (s.cycle, 0, 'N', 1, 'Y'), decode (s.order$, 0, 'N', 1, 'Y'), s.cache, s.highwater from sys.seq$ s, sys.obj$ o, sys.user$ u where u.user# = o.owner# and o.obj# = s.obj# / comment on table DBA_SEQUENCES is 'Description of all SEQUENCEs in the database' / comment on column DBA_SEQUENCES.SEQUENCE_OWNER is 'Name of the owner of the sequence' / comment on column DBA_SEQUENCES.SEQUENCE_NAME is 'SEQUENCE name' / comment on column DBA_SEQUENCES.INCREMENT_BY is 'Value by which sequence is incremented' / comment on column DBA_SEQUENCES.MIN_VALUE is 'Minimum value of the sequence' / comment on column DBA_SEQUENCES.MAX_VALUE is 'Maximum value of the sequence' / comment on column DBA_SEQUENCES.CYCLE_FLAG is 'Does sequence wrap around on reaching limit?' / comment on column DBA_SEQUENCES.ORDER_FLAG is 'Are sequence numbers generated in order?' / comment on column DBA_SEQUENCES.CACHE_SIZE is 'Number of sequence numbers to cache' / comment on column DBA_SEQUENCES.LAST_NUMBER is 'Last sequence number written to disk' / remark remark FAMILY "SYNONYMS" remark CREATE SYNONYM information. remark drop view USER_SYNONYMS / create view USER_SYNONYMS (SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK) as select o.name, s.owner, s.name, s.node from sys.syn$ s, sys.obj$ o where o.obj# = s.obj# and o.type = 5 and o.owner# = uid / comment on table USER_SYNONYMS is 'The user''s private synonyms' / comment on column USER_SYNONYMS.SYNONYM_NAME is 'Name of the synonym' / comment on column USER_SYNONYMS.TABLE_OWNER is 'Owner of the object referenced by the synonym' / comment on column USER_SYNONYMS.TABLE_NAME is 'Name of the object referenced by the synonym' / comment on column USER_SYNONYMS.DB_LINK is 'Database link referenced in a remote synonym' / drop public synonym SYN / create public synonym SYN for USER_SYNONYMS / drop public synonym USER_SYNONYMS / create public synonym USER_SYNONYMS for USER_SYNONYMS / grant select on USER_SYNONYMS to PUBLIC / drop view ALL_SYNONYMS / remark remark This view shows all synonyms owned by the user (private synonyms), remark plus all public synonyms. remark create view ALL_SYNONYMS (OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK) as select u.name, o.name, s.owner, s.name, s.node from sys.user$ u, sys.syn$ s, sys.obj$ o where o.obj# = s.obj# and o.type = 5 and o.owner# = u.user# and o.owner# in (uid, 1) / comment on table ALL_SYNONYMS is 'All synonyms accessible to the user' / comment on column ALL_SYNONYMS.OWNER is 'Owner of the synonym' / comment on column ALL_SYNONYMS.SYNONYM_NAME is 'Name of the synonym' / comment on column ALL_SYNONYMS.TABLE_OWNER is 'Owner of the object referenced by the synonym' / comment on column ALL_SYNONYMS.TABLE_NAME is 'Name of the object referenced by the synonym' / comment on column ALL_SYNONYMS.DB_LINK is 'Name of the database link referenced in a remote synonym' / drop public synonym ALL_SYNONYMS / create public synonym ALL_SYNONYMS for ALL_SYNONYMS / grant select on ALL_SYNONYMS to PUBLIC / drop view DBA_SYNONYMS / create view DBA_SYNONYMS (OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK) as select u.name, o.name, s.owner, s.name, s.node from sys.user$ u, sys.syn$ s, sys.obj$ o where o.obj# = s.obj# and o.type = 5 and o.owner# = u.user# / comment on table DBA_SYNONYMS is 'All synonyms in the database' / comment on column DBA_SYNONYMS.OWNER is 'Username of the owner of the synonym' / comment on column DBA_SYNONYMS.SYNONYM_NAME is 'Name of the synonym' / comment on column DBA_SYNONYMS.TABLE_OWNER is 'Owner of the object referenced by the synonym' / comment on column DBA_SYNONYMS.TABLE_NAME is 'Name of the object referenced by the synonym' / comment on column DBA_SYNONYMS.DB_LINK is 'Name of the database link referenced in a remote synonym' / remark remark FAMILY "SYS_AUDIT_OPTS" remark This view is only accessible to DBAs. remark Single row view indicating the current system auditing options. remark drop view DBA_SYS_AUDIT_OPTS / create view DBA_SYS_AUDIT_OPTS (CONNECT_ACTION, DBA_ACTION, NOT_EXISTS, RESOURCE_ACTION) as select decode(substr(t.audit$, 1, 2), 'S-', 'A/-', '-S', '-/A', 'SS', 'A/A', '--', '-/-', '?'), decode(substr(t.audit$, 3, 2), 'S-', 'A/-', '-S', '-/A', 'SS', 'A/A', '--', '-/-', '?'), decode(substr(t.audit$, 5, 2), 'S-', 'A/-', '-S', '-/A', 'SS', 'A/A', '--', '-/-', '?'), decode(substr(t.audit$, 7, 2), 'S-', 'A/-', '-S', '-/A', 'SS', 'A/A', '--', '-/-', '?') from sys.obj$ o, sys.tab$ t where o.obj# = t.obj# and o.owner# = 0 and o.name = '_system_auditing_options_' / comment on table DBA_SYS_AUDIT_OPTS is 'Describes current system auditing options' / comment on column DBA_SYS_AUDIT_OPTS.CONNECT_ACTION is 'Auditing CONNECT/DISCONNECT actions' / comment on column DBA_SYS_AUDIT_OPTS.DBA_ACTION is 'Auditing actions requiring DBA privilege' / comment on column DBA_SYS_AUDIT_OPTS.NOT_EXISTS is 'Auditing actions on objects that do NOT EXIST' / comment on column DBA_SYS_AUDIT_OPTS.RESOURCE_ACTION is 'Auditing RESOURCE actions' / remark remark FAMILY "TABLES" remark CREATE TABLE parameters. remark drop view USER_TABLES / create view USER_TABLES (TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, BACKED_UP) as select o.name, ts.name, co.name, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, decode(t.modified, 0, 'Y', 1, 'N', '?') from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o where o.owner# = uid and o.obj# = t.obj# and t.clu# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) / comment on table USER_TABLES is 'Description of the user''s own tables' / comment on column USER_TABLES.TABLE_NAME is 'Name of the table' / comment on column USER_TABLES.TABLESPACE_NAME is 'Name of the tablespace containing the table' / comment on column USER_TABLES.CLUSTER_NAME is 'Name of the cluster, if any, to which the table belongs' / comment on column USER_TABLES.PCT_FREE is 'Minimum percentage of free space in a block' / comment on column USER_TABLES.PCT_USED is 'Minimum percentage of used space in a block' / comment on column USER_TABLES.INI_TRANS is 'Initial number of transactions' / comment on column USER_TABLES.MAX_TRANS is 'Maximum number of transactions' / comment on column USER_TABLES.INITIAL_EXTENT is 'Size of the initial extent in bytes' / comment on column USER_TABLES.NEXT_EXTENT is 'Size of secondary extents in bytes' / comment on column USER_TABLES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column USER_TABLES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column USER_TABLES.PCT_INCREASE is 'Percentage increase in extent size' / comment on column USER_TABLES.BACKED_UP is 'Has table been backed up since last modification?' / drop public synonym USER_TABLES / create public synonym USER_TABLES for USER_TABLES / drop public synonym TABS / create public synonym TABS for USER_TABLES / grant select on USER_TABLES to PUBLIC / drop view ALL_TABLES / create view ALL_TABLES (OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, BACKED_UP) as select u.name, o.name, ts.name, co.name, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, decode(t.modified, 0, 'Y', 1, 'N', '?') from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o where o.owner# = u.user# and o.obj# = t.obj# and t.clu# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) and (o.owner# = uid or o.obj# in (select ta.obj# from sys.tabauth$ ta where ta.grantee# in (uid, 1)) ) / comment on table ALL_TABLES is 'Description of tables accessible to the user' / comment on column ALL_TABLES.OWNER is 'Owner of the table' / comment on column ALL_TABLES.TABLE_NAME is 'Name of the table' / comment on column ALL_TABLES.TABLESPACE_NAME is 'Name of the tablespace containing the table' / comment on column ALL_TABLES.CLUSTER_NAME is 'Name of the cluster, if any, to which the table belongs' / comment on column ALL_TABLES.PCT_FREE is 'Minimum percentage of free space in a block' / comment on column ALL_TABLES.PCT_USED is 'Minimum percentage of used space in a block' / comment on column ALL_TABLES.INI_TRANS is 'Initial number of transactions' / comment on column ALL_TABLES.MAX_TRANS is 'Maximum number of transactions' / comment on column ALL_TABLES.INITIAL_EXTENT is 'Size of the initial extent in bytes' / comment on column ALL_TABLES.NEXT_EXTENT is 'Size of secondary extents in bytes' / comment on column ALL_TABLES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column ALL_TABLES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column ALL_TABLES.PCT_INCREASE is 'Percentage increase in extent size' / comment on column ALL_TABLES.BACKED_UP is 'Has table been backed up since last modification?' / drop public synonym ALL_TABLES / create public synonym ALL_TABLES for ALL_TABLES / grant select on ALL_TABLES to PUBLIC / drop view DBA_TABLES / create view DBA_TABLES (OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, BACKED_UP) as select u.name, o.name, ts.name, co.name, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, decode(t.modified, 0, 'Y', 1, 'N', '?') from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o where o.owner# = u.user# and o.obj# = t.obj# and t.clu# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) / comment on table DBA_TABLES is 'Description of all tables in the database' / comment on column DBA_TABLES.OWNER is 'Owner of the table' / comment on column DBA_TABLES.TABLE_NAME is 'Name of the table' / comment on column DBA_TABLES.TABLESPACE_NAME is 'Name of the tablespace containing the table' / comment on column DBA_TABLES.CLUSTER_NAME is 'Name of the cluster, if any, to which the table belongs' / comment on column DBA_TABLES.PCT_FREE is 'Minimum percentage of free space in a block' / comment on column DBA_TABLES.PCT_USED is 'Minimum percentage of used space in a block' / comment on column DBA_TABLES.INI_TRANS is 'Initial number of transactions' / comment on column DBA_TABLES.MAX_TRANS is 'Maximum number of transactions' / comment on column DBA_TABLES.INITIAL_EXTENT is 'Size of the initial extent in bytes' / comment on column DBA_TABLES.NEXT_EXTENT is 'Size of secondary extents in bytes' / comment on column DBA_TABLES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment' / comment on column DBA_TABLES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment' / comment on column DBA_TABLES.PCT_INCREASE is 'Percentage increase in extent size' / comment on column DBA_TABLES.BACKED_UP is 'Has table been backed up since last modification?' / remark remark FAMILY "TABLESPACES" remark CREATE TABLESPACE parameters, except datafiles. remark This family has no ALL member. remark drop view USER_TABLESPACES / create view USER_TABLESPACES (TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS) as select ts.name, ts.blocksize * ts.dflinit, ts.blocksize * ts.dflincr, ts.dflminext, ts.dflmaxext, ts.dflextpct, decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE', 3, 'INVALID', 'UNDEFINED') from sys.ts$ ts where exists (select null from sys.tsq$ tsq where tsq.ts# = ts.ts# and tsq.user# = uid) or exists (select null from sys.user$ u where u.user# = uid and u.resource$ = 1) / comment on table USER_TABLESPACES is 'Description of accessible tablespaces' / comment on column USER_TABLESPACES.TABLESPACE_NAME is 'Tablespace name' / comment on column USER_TABLESPACES.INITIAL_EXTENT is 'Default initial extent size' / comment on column USER_TABLESPACES.NEXT_EXTENT is 'Default incremental extent size' / comment on column USER_TABLESPACES.MIN_EXTENTS is 'Default minimum number of extents' / comment on column USER_TABLESPACES.MAX_EXTENTS is 'Default maximum number of extents' / comment on column USER_TABLESPACES.PCT_INCREASE is 'Default percent increase for extent size' / comment on column USER_TABLESPACES.STATUS is 'Tablespace status: "ONLINE" or "OFFLINE"' / drop public synonym USER_TABLESPACES / create public synonym USER_TABLESPACES for USER_TABLESPACES / grant select on USER_TABLESPACES to PUBLIC / drop view DBA_TABLESPACES / create view DBA_TABLESPACES (TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS) as select ts.name, ts.blocksize * ts.dflinit, ts.blocksize * ts.dflincr, ts.dflminext, ts.dflmaxext, ts.dflextpct, decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE', 3, 'INVALID', 'UNDEFINED') from sys.ts$ ts / comment on table DBA_TABLESPACES is 'Description of all tablespaces' / comment on column DBA_TABLESPACES.TABLESPACE_NAME is 'Tablespace name' / comment on column DBA_TABLESPACES.INITIAL_EXTENT is 'Default initial extent size' / comment on column DBA_TABLESPACES.NEXT_EXTENT is 'Default incremental extent size' / comment on column DBA_TABLESPACES.MIN_EXTENTS is 'Default minimum number of extents' / comment on column DBA_TABLESPACES.MAX_EXTENTS is 'Default maximum number of extents' / comment on column DBA_TABLESPACES.PCT_INCREASE is 'Default percent increase for extent size' / comment on column DBA_TABLESPACES.STATUS is 'Tablespace status: "ONLINE" or "OFFLINE"' / remark remark FAMILY "TAB_AUDIT_OPTS" remark Auditing options on objects. Only "user_" and "dba_" members. remark A user is not allowed to see audit options for other people's objects. remark remark These views indicate what kind of audit trail entries (none, remark session-level, or access-level) are generated by the success or failure remark of each possible operation on a table or view (e.g., select, alter). remark remark The values in the columns ALT through UPD are three character remark strings like 'A/S', 'A/-'. The letters 'A', 'S', and '-' correspond to remark different levels of detail called Access, Session and None. The remark character before the slash determines the auditing level if the action remark is successful. The character after the slash determines auditing level remark if the operation fails for any reason. remark remark This compressed three character format has been chosen to make all remark the information fit on a single line. The column names are remark three chars long for the same reason. The alternative is to use long remark column names to improve readability, but remark serious users can get further documentation using the describe remark column statement. I do not expect novice users to be looking at audit remark information. Another alternative is to have separate columns for the remark success and failure settings. This would eliminate the need to remark use the substr function in views built on top of these views, remark but the advantage to users of making information fit on one line remark overrides the hassle to view-implementors of using the substr function. remark drop view USER_TAB_AUDIT_OPTS / create view USER_TAB_AUDIT_OPTS (TABLE_NAME, TABLE_TYPE, ALT, AUD, COM, DEL, GRA, IND, INS, LOC, REN, SEL, UPD) as select o.name, 'TABLE', substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1), substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1), substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1), substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1), substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1), substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1), substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1), substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1), substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1), substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1), substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1) from sys.obj$ o, sys.tab$ t where o.type = 2 and not (o.owner# = 0 and o.name in ('_default_auditing_options_', '_system_auditing_options_')) and o.owner# = uid and o.obj# = t.obj# union select o.name, 'VIEW', substr(v.audit$, 1, 1) || '/' || substr(v.audit$, 2, 1), substr(v.audit$, 3, 1) || '/' || substr(v.audit$, 4, 1), substr(v.audit$, 5, 1) || '/' || substr(v.audit$, 6, 1), substr(v.audit$, 7, 1) || '/' || substr(v.audit$, 8, 1), substr(v.audit$, 9, 1) || '/' || substr(v.audit$, 10, 1), substr(v.audit$, 11, 1) || '/' || substr(v.audit$, 12, 1), substr(v.audit$, 13, 1) || '/' || substr(v.audit$, 14, 1), substr(v.audit$, 15, 1) || '/' || substr(v.audit$, 16, 1), substr(v.audit$, 17, 1) || '/' || substr(v.audit$, 18, 1), substr(v.audit$, 19, 1) || '/' || substr(v.audit$, 20, 1), substr(v.audit$, 21, 1) || '/' || substr(v.audit$, 22, 1) from sys.obj$ o, sys.view$ v where o.type = 4 and o.owner# = uid and o.obj# = v.obj# / comment on table USER_TAB_AUDIT_OPTS is 'Auditing options for user''s own tables and views' / comment on column USER_TAB_AUDIT_OPTS.TABLE_NAME is 'Name of the object' / comment on column USER_TAB_AUDIT_OPTS.TABLE_TYPE is 'Type of the object: "TABLE" or "VIEW"' / comment on column USER_TAB_AUDIT_OPTS.ALT is 'Auditing ALTER WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.AUD is 'Auditing AUDIT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.COM is 'Auditing COMMENT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.DEL is 'Auditing DELETE WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.GRA is 'Auditing GRANT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.IND is 'Auditing INDEX WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.INS is 'Auditing INSERT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.LOC is 'Auditing LOCK WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.REN is 'Auditing RENAME WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.SEL is 'Auditing SELECT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column USER_TAB_AUDIT_OPTS.UPD is 'Auditing UPDATE WHENEVER SUCCESSFUL / UNSUCCESSFUL' / drop public synonym USER_TAB_AUDIT_OPTS / create public synonym USER_TAB_AUDIT_OPTS for USER_TAB_AUDIT_OPTS / grant select on USER_TAB_AUDIT_OPTS to PUBLIC / drop view DBA_TAB_AUDIT_OPTS / create view DBA_TAB_AUDIT_OPTS (OWNER, TABLE_NAME, TABLE_TYPE, ALT, AUD, COM, DEL, GRA, IND, INS, LOC, REN, SEL, UPD) as select u.name, o.name, 'TABLE', substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1), substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1), substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1), substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1), substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1), substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1), substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1), substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1), substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1), substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1), substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1) from sys.obj$ o, sys.user$ u, sys.tab$ t where o.type = 2 and not (o.owner# = 0 and o.name in ('_default_auditing_options_', '_system_auditing_options_')) and o.owner# = u.user# and o.obj# = t.obj# union select u.name, o.name, 'VIEW', substr(v.audit$, 1, 1) || '/' || substr(v.audit$, 2, 1), substr(v.audit$, 3, 1) || '/' || substr(v.audit$, 4, 1), substr(v.audit$, 5, 1) || '/' || substr(v.audit$, 6, 1), substr(v.audit$, 7, 1) || '/' || substr(v.audit$, 8, 1), substr(v.audit$, 9, 1) || '/' || substr(v.audit$, 10, 1), substr(v.audit$, 11, 1) || '/' || substr(v.audit$, 12, 1), substr(v.audit$, 13, 1) || '/' || substr(v.audit$, 14, 1), substr(v.audit$, 15, 1) || '/' || substr(v.audit$, 16, 1), substr(v.audit$, 17, 1) || '/' || substr(v.audit$, 18, 1), substr(v.audit$, 19, 1) || '/' || substr(v.audit$, 20, 1), substr(v.audit$, 21, 1) || '/' || substr(v.audit$, 22, 1) from sys.obj$ o, sys.user$ u, sys.view$ v where o.type = 4 and o.owner# = u.user# and o.obj# = v.obj# / comment on table DBA_TAB_AUDIT_OPTS is 'Auditing options for all tables and views' / comment on column DBA_TAB_AUDIT_OPTS.OWNER is 'Owner of the object' / comment on column DBA_TAB_AUDIT_OPTS.TABLE_NAME is 'Name of the object' / comment on column DBA_TAB_AUDIT_OPTS.TABLE_TYPE is 'Type of the object' / comment on column DBA_TAB_AUDIT_OPTS.ALT is 'Auditing ALTER WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.AUD is 'Auditing AUDIT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.COM is 'Auditing COMMENT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.DEL is 'Auditing DELETE WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.GRA is 'Auditing GRANT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.IND is 'Auditing INDEX WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.INS is 'Auditing INSERT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.LOC is 'Auditing LOCK WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.REN is 'Auditing RENAME WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.SEL is 'Auditing SELECT WHENEVER SUCCESSFUL / UNSUCCESSFUL' / comment on column DBA_TAB_AUDIT_OPTS.UPD is 'Auditing UPDATE WHENEVER SUCCESSFUL / UNSUCCESSFUL' / remark remark FAMILY "TAB_COLUMNS" remark The columns that make up objects: Tables, Views, Clusters remark Includes information specified or implied by user in remark CREATE/ALTER TABLE/VIEW/CLUSTER. remark drop view USER_TAB_COLUMNS / create view USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT) as select o.name, c.name, decode(c.type#, 1, 'CHAR', 2, decode(c.scale, null, decode(c.precision, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, 'VARCHAR', 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 'UNDEFINED'), c.length, c.precision, c.scale, decode(c.null$, 0, 'Y', 'N'), c.col#, c.deflength, c.default$ from sys.col$ c, sys.obj$ o where o.obj# = c.obj# and o.owner# = uid and o.type in (2, 3, 4) / comment on table USER_TAB_COLUMNS is 'Columns of user''s tables, views and clusters' / comment on column USER_TAB_COLUMNS.TABLE_NAME is 'Table, view or cluster name' / comment on column USER_TAB_COLUMNS.COLUMN_NAME is 'Column name' / comment on column USER_TAB_COLUMNS.DATA_LENGTH is 'Length of the column in bytes' / comment on column USER_TAB_COLUMNS.DATA_TYPE is 'Datatype of the column' / comment on column USER_TAB_COLUMNS.DATA_PRECISION is 'Length: decimal digits (NUMBER) or binary digits (FLOAT)' / comment on column USER_TAB_COLUMNS.DATA_SCALE is 'Digits to right of decimal point in a number' / comment on column USER_TAB_COLUMNS.NULLABLE is 'Does column allow NULL values?' / comment on column USER_TAB_COLUMNS.COLUMN_ID is 'Sequence number of the column as created' / comment on column USER_TAB_COLUMNS.DEFAULT_LENGTH is 'Length of default value for the column' / comment on column USER_TAB_COLUMNS.DATA_DEFAULT is 'Default value for the column' / drop public synonym USER_TAB_COLUMNS / create public synonym USER_TAB_COLUMNS for USER_TAB_COLUMNS / drop public synonym COLS / create public synonym COLS for USER_TAB_COLUMNS / grant select on USER_TAB_COLUMNS to PUBLIC / drop view ACCESSIBLE_COLUMNS / create view ACCESSIBLE_COLUMNS (OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT) as select u.name, o.name, c.name, decode(c.type#, 1, 'CHAR', 2, decode(c.scale, null, decode(c.precision, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, 'VARCHAR', 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 'UNDEFINED'), c.length, c.precision, c.scale, decode(c.null$, 0, 'Y', 'N'), c.col#, c.deflength, c.default$ from sys.col$ c, sys.obj$ o, sys.user$ u where o.obj# = c.obj# and o.owner# = u.user# and o.type in (2, 3, 4) and (o.owner# = uid or o.obj# in ( select obj# from sys.tabauth$ where grantee# in ( uid, 1 ) ) ) / comment on table ACCESSIBLE_COLUMNS is 'Columns of all tables, views and clusters' / comment on column ACCESSIBLE_COLUMNS.OWNER is 'Owner of the table, view or cluster' / comment on column ACCESSIBLE_COLUMNS.TABLE_NAME is 'Table, view or cluster name' / comment on column ACCESSIBLE_COLUMNS.COLUMN_NAME is 'Column name' / comment on column ACCESSIBLE_COLUMNS.DATA_LENGTH is 'Length of the column in bytes' / comment on column ACCESSIBLE_COLUMNS.DATA_TYPE is 'Datatype of the column' / comment on column ACCESSIBLE_COLUMNS.DATA_PRECISION is 'Length: decimal digits (NUMBER), binary digits (FLOAT)' / comment on column ACCESSIBLE_COLUMNS.DATA_SCALE is 'Digits to right of decimal point in a number' / comment on column ACCESSIBLE_COLUMNS.NULLABLE is 'Does column allow NULL values?' / comment on column ACCESSIBLE_COLUMNS.COLUMN_ID is 'Sequence number of the column as created' / comment on column ACCESSIBLE_COLUMNS.DEFAULT_LENGTH is 'Length of default value for the column' / comment on column ACCESSIBLE_COLUMNS.DATA_DEFAULT is 'Default value for the column' / drop public synonym ACCESSIBLE_COLUMNS / create public synonym ACCESSIBLE_COLUMNS for ACCESSIBLE_COLUMNS / drop public synonym ALL_TAB_COLUMNS / create public synonym ALL_TAB_COLUMNS for ACCESSIBLE_COLUMNS / grant select on ACCESSIBLE_COLUMNS to PUBLIC / drop view DBA_TAB_COLUMNS / create view DBA_TAB_COLUMNS (OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT) as select u.name, o.name, c.name, decode(c.type#, 1, 'CHAR', 2, decode(c.scale, null, decode(c.precision, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, 'VARCHAR', 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 'UNDEFINED'), c.length, c.precision, c.scale, decode(c.null$, 0, 'Y', 'N'), c.col#, c.deflength, c.default$ from sys.col$ c, sys.obj$ o, sys.user$ u where o.obj# = c.obj# and o.owner# = u.user# and o.type in (2, 3, 4) / comment on table DBA_TAB_COLUMNS is 'Columns of all tables, views and clusters' / comment on column DBA_TAB_COLUMNS.OWNER is 'Owner of the table, view or cluster' / comment on column DBA_TAB_COLUMNS.TABLE_NAME is 'Table, view or cluster name' / comment on column DBA_TAB_COLUMNS.COLUMN_NAME is 'Column name' / comment on column DBA_TAB_COLUMNS.DATA_LENGTH is 'Length of the column in bytes' / comment on column DBA_TAB_COLUMNS.DATA_TYPE is 'Datatype of the column' / comment on column DBA_TAB_COLUMNS.DATA_PRECISION is 'Length: decimal digits (NUMBER), binary digits (FLOAT)' / comment on column DBA_TAB_COLUMNS.DATA_SCALE is 'Digits to right of decimal point in a number' / comment on column DBA_TAB_COLUMNS.NULLABLE is 'Does column allow NULL values?' / comment on column DBA_TAB_COLUMNS.COLUMN_ID is 'Sequence number of the column as created' / comment on column DBA_TAB_COLUMNS.DEFAULT_LENGTH is 'Length of default value for the column' / comment on column DBA_TAB_COLUMNS.DATA_DEFAULT is 'Default value for the column' / remark remark FAMILY "TAB_COMMENTS" remark Comments on objects. remark drop view USER_TAB_COMMENTS / create view USER_TAB_COMMENTS (TABLE_NAME, TABLE_TYPE, COMMENTS) as select o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 'UNDEFINED'), c.comment$ from sys.obj$ o, sys.com$ c where o.owner# = uid and o.type in (2, 4) and o.obj# = c.obj#(+) and c.col#(+) is null / comment on table USER_TAB_COMMENTS is 'Comments on the tables and views owned by the user' / comment on column USER_TAB_COMMENTS.TABLE_NAME is 'Name of the object' / comment on column USER_TAB_COMMENTS.TABLE_TYPE is 'Type of the object: "TABLE" or "VIEW"' / comment on column USER_TAB_COMMENTS.COMMENTS is 'Comment on the object' / drop public synonym USER_TAB_COMMENTS / create public synonym USER_TAB_COMMENTS for USER_TAB_COMMENTS / grant select on USER_TAB_COMMENTS to PUBLIC / drop view ALL_TAB_COMMENTS / create view ALL_TAB_COMMENTS (OWNER, TABLE_NAME, TABLE_TYPE, COMMENTS) as select u.name, o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 'UNDEFINED'), c.comment$ from sys.obj$ o, sys.user$ u, sys.com$ c where o.owner# = u.user# and o.obj# = c.obj#(+) and c.col#(+) is null and o.type in (2, 4) and (o.owner# = uid or o.obj# in ( select obj# from sys.tabauth$ where grantee# in ( uid, 1 ) ) ) / comment on table ALL_TAB_COMMENTS is 'Comments on tables and views accessible to the user' / comment on column ALL_TAB_COMMENTS.OWNER is 'Owner of the object' / comment on column ALL_TAB_COMMENTS.TABLE_NAME is 'Name of the object' / comment on column ALL_TAB_COMMENTS.TABLE_TYPE is 'Type of the object' / comment on column ALL_TAB_COMMENTS.COMMENTS is 'Comment on the object' / drop public synonym ALL_TAB_COMMENTS / create public synonym ALL_TAB_COMMENTS for ALL_TAB_COMMENTS / grant select on ALL_TAB_COMMENTS to PUBLIC / drop view DBA_TAB_COMMENTS / create view DBA_TAB_COMMENTS (OWNER, TABLE_NAME, TABLE_TYPE, COMMENTS) as select u.name, o.name, decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 'UNDEFINED'), c.comment$ from sys.obj$ o, sys.user$ u, sys.com$ c where o.owner# = u.user# and o.type in (2, 4) and o.obj# = c.obj#(+) and c.col#(+) is null / comment on table DBA_TAB_COMMENTS is 'Comments on all tables and views in the database' / comment on column DBA_TAB_COMMENTS.OWNER is 'Owner of the object' / comment on column DBA_TAB_COMMENTS.TABLE_NAME is 'Name of the object' / comment on column DBA_TAB_COMMENTS.TABLE_TYPE is 'Type of the object' / comment on column DBA_TAB_COMMENTS.COMMENTS is 'Comment on the object' / remark remark FAMILY "TAB_GRANTS" remark Grants on objects. remark drop view USER_TAB_GRANTS / create view USER_TAB_GRANTS (GRANTEE, OWNER, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select ue.name, u.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ta.obj# = o.obj# and ta.grantor# = ur.user# and ta.grantee# = ue.user# and u.user# = o.owner# and uid in (ta.grantor#, ta.grantee#, o.owner#) / comment on table USER_TAB_GRANTS is 'Grants on objects for which the user is the owner, grantor or grantee' / comment on column USER_TAB_GRANTS.GRANTEE is 'Name of the user to whom access was granted' / comment on column USER_TAB_GRANTS.OWNER is 'Owner of the object' / comment on column USER_TAB_GRANTS.TABLE_NAME is 'Name of the object' / comment on column USER_TAB_GRANTS.GRANTOR is 'Name of the user who performed the grant' / comment on column USER_TAB_GRANTS.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column USER_TAB_GRANTS.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column USER_TAB_GRANTS.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column USER_TAB_GRANTS.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column USER_TAB_GRANTS.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column USER_TAB_GRANTS.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column USER_TAB_GRANTS.INDEX_PRIV is 'Permission to create/drop an INDEX on the object?' / comment on column USER_TAB_GRANTS.CREATED is 'Timestamp for the grant' / drop public synonym USER_TAB_GRANTS / create public synonym USER_TAB_GRANTS for USER_TAB_GRANTS / grant select on USER_TAB_GRANTS to PUBLIC / drop view TABLE_PRIVILEGES / create view TABLE_PRIVILEGES (GRANTEE, OWNER, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select ue.name, u.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ta.obj# = o.obj# and ta.grantor# = ur.user# and ta.grantee# = ue.user# and u.user# = o.owner# and (uid in (ta.grantor#, ta.grantee#, o.owner#) or ta.grantee# = 1) / comment on table TABLE_PRIVILEGES is 'Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee' / comment on column TABLE_PRIVILEGES.GRANTEE is 'Name of the user to whom access was granted' / comment on column TABLE_PRIVILEGES.OWNER is 'Owner of the object' / comment on column TABLE_PRIVILEGES.TABLE_NAME is 'Name of the object' / comment on column TABLE_PRIVILEGES.GRANTOR is 'Name of the user who performed the grant' / comment on column TABLE_PRIVILEGES.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column TABLE_PRIVILEGES.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column TABLE_PRIVILEGES.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column TABLE_PRIVILEGES.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column TABLE_PRIVILEGES.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column TABLE_PRIVILEGES.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column TABLE_PRIVILEGES.INDEX_PRIV is 'Permission to create/drop an INDEX on the object?' / comment on column TABLE_PRIVILEGES.CREATED is 'Timestamp for the grant' / drop public synonym TABLE_PRIVILEGES / create public synonym TABLE_PRIVILEGES for TABLE_PRIVILEGES / drop public synonym ALL_TAB_GRANTS / create public synonym ALL_TAB_GRANTS for TABLE_PRIVILEGES / grant select on TABLE_PRIVILEGES to PUBLIC / drop view DBA_TAB_GRANTS / create view DBA_TAB_GRANTS (GRANTEE, OWNER, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select ue.name, u.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ta.obj# = o.obj# and ta.grantor# = ur.user# and ta.grantee# = ue.user# and u.user# = o.owner# / comment on table DBA_TAB_GRANTS is 'All grants on objects in the database' / comment on column DBA_TAB_GRANTS.GRANTEE is 'User to whom access was granted' / comment on column DBA_TAB_GRANTS.OWNER is 'Owner of the object' / comment on column DBA_TAB_GRANTS.TABLE_NAME is 'Name of the object' / comment on column DBA_TAB_GRANTS.GRANTOR is 'Name of the user who performed the grant' / comment on column DBA_TAB_GRANTS.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column DBA_TAB_GRANTS.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column DBA_TAB_GRANTS.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column DBA_TAB_GRANTS.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column DBA_TAB_GRANTS.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column DBA_TAB_GRANTS.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column DBA_TAB_GRANTS.INDEX_PRIV is 'Permission to create/drop an INDEX on the object?' / comment on column DBA_TAB_GRANTS.CREATED is 'Timestamp for the grant' / remark remark FAMILY "TAB_GRANTS_MADE" remark Grants made on objects. remark This family has no DBA member. remark drop view USER_TAB_GRANTS_MADE / create view USER_TAB_GRANTS_MADE (GRANTEE, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select ue.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ ue, sys.user$ ur where ta.obj# = o.obj# and ta.grantor# = ur.user# and ta.grantee# = ue.user# and o.owner# = uid / comment on table USER_TAB_GRANTS_MADE is 'All grants on objects owned by the user' / comment on column USER_TAB_GRANTS_MADE.GRANTEE is 'Name of the user to whom access was granted' / comment on column USER_TAB_GRANTS_MADE.TABLE_NAME is 'Name of the object' / comment on column USER_TAB_GRANTS_MADE.GRANTOR is 'Name of the user who performed the grant' / comment on column USER_TAB_GRANTS_MADE.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column USER_TAB_GRANTS_MADE.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column USER_TAB_GRANTS_MADE.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column USER_TAB_GRANTS_MADE.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column USER_TAB_GRANTS_MADE.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column USER_TAB_GRANTS_MADE.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column USER_TAB_GRANTS_MADE.INDEX_PRIV is 'Permission to CREATE/DROP INDEX on the object?' / comment on column USER_TAB_GRANTS_MADE.CREATED is 'Timestamp for the grant' / drop public synonym USER_TAB_GRANTS_MADE / create public synonym USER_TAB_GRANTS_MADE for USER_TAB_GRANTS_MADE / grant select on USER_TAB_GRANTS_MADE to PUBLIC / drop view ALL_TAB_GRANTS_MADE / create view ALL_TAB_GRANTS_MADE (GRANTEE, OWNER, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select ue.name, u.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ta.obj# = o.obj# and ta.grantor# = ur.user# and ta.grantee# = ue.user# and u.user# = o.owner# and uid in (o.owner#, ta.grantor#) / comment on table ALL_TAB_GRANTS_MADE is 'User''s grants and grants on user''s objects' / comment on column ALL_TAB_GRANTS_MADE.GRANTEE is 'Name of the user to whom access was granted' / comment on column ALL_TAB_GRANTS_MADE.OWNER is 'Owner of the object' / comment on column ALL_TAB_GRANTS_MADE.TABLE_NAME is 'Name of the object' / comment on column ALL_TAB_GRANTS_MADE.GRANTOR is 'Name of the user who performed the grant' / comment on column ALL_TAB_GRANTS_MADE.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column ALL_TAB_GRANTS_MADE.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column ALL_TAB_GRANTS_MADE.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column ALL_TAB_GRANTS_MADE.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column ALL_TAB_GRANTS_MADE.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column ALL_TAB_GRANTS_MADE.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column ALL_TAB_GRANTS_MADE.INDEX_PRIV is 'Permission to CREATE/DROP INDEX on the object?' / comment on column ALL_TAB_GRANTS_MADE.CREATED is 'Timestamp for the grant' / drop public synonym ALL_TAB_GRANTS_MADE / create public synonym ALL_TAB_GRANTS_MADE for ALL_TAB_GRANTS_MADE / grant select on ALL_TAB_GRANTS_MADE to PUBLIC / remark remark FAMILY "TAB_GRANTS_RECD" remark Grants received on objects. remark This family has no DBA member. remark drop view USER_TAB_GRANTS_RECD / create view USER_TAB_GRANTS_RECD (OWNER, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select u.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ur where ta.obj# = o.obj# and ta.grantor# = ur.user# and u.user# = o.owner# and ta.grantee# = uid / comment on table USER_TAB_GRANTS_RECD is 'Grants on objects for which the user is the grantee' / comment on column USER_TAB_GRANTS_RECD.OWNER is 'Owner of the object' / comment on column USER_TAB_GRANTS_RECD.TABLE_NAME is 'Name of the object' / comment on column USER_TAB_GRANTS_RECD.GRANTOR is 'Name of the user who performed the grant' / comment on column USER_TAB_GRANTS_RECD.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column USER_TAB_GRANTS_RECD.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column USER_TAB_GRANTS_RECD.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column USER_TAB_GRANTS_RECD.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column USER_TAB_GRANTS_RECD.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column USER_TAB_GRANTS_RECD.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column USER_TAB_GRANTS_RECD.INDEX_PRIV is 'Permission to create/drop an INDEX on the object?' / comment on column USER_TAB_GRANTS_RECD.CREATED is 'Timestamp for the grant' / drop public synonym USER_TAB_GRANTS_RECD / create public synonym USER_TAB_GRANTS_RECD for USER_TAB_GRANTS_RECD / grant select on USER_TAB_GRANTS_RECD to PUBLIC / drop view ALL_TAB_GRANTS_RECD / create view ALL_TAB_GRANTS_RECD (GRANTEE, OWNER, TABLE_NAME, GRANTOR, SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV, CREATED) as select ue.name, u.name, o.name, ur.name, decode(ta.select$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.insert$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.delete$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.update$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.references$, 0, 'N', 1, 'S', 2, 'A', 3, 'G', '?'), decode(ta.alter$, 0, 'N', 2, 'Y', 3, 'G', '?'), decode(ta.index$, 0, 'N', 2, 'Y', 3, 'G', '?'), ta.time from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue where ta.obj# = o.obj# and ta.grantor# = ur.user# and ta.grantee# = ue.user# and u.user# = o.owner# and ta.grantee# in (uid, 1) / comment on table ALL_TAB_GRANTS_RECD is 'Grants on objects for which the user or PUBLIC is the grantee' / comment on column ALL_TAB_GRANTS_RECD.GRANTEE is 'Name of the user to whom access was granted' / comment on column ALL_TAB_GRANTS_RECD.OWNER is 'Owner of the object' / comment on column ALL_TAB_GRANTS_RECD.TABLE_NAME is 'Name of the object' / comment on column ALL_TAB_GRANTS_RECD.GRANTOR is 'Name of the user who performed the grant' / comment on column ALL_TAB_GRANTS_RECD.SELECT_PRIV is 'Permission to SELECT from the object?' / comment on column ALL_TAB_GRANTS_RECD.INSERT_PRIV is 'Permission to INSERT into the object?' / comment on column ALL_TAB_GRANTS_RECD.DELETE_PRIV is 'Permission to DELETE from the object?' / comment on column ALL_TAB_GRANTS_RECD.UPDATE_PRIV is 'Permission to UPDATE the object?' / comment on column ALL_TAB_GRANTS_RECD.REFERENCES_PRIV is 'Permission to make REFERENCES to the object?' / comment on column ALL_TAB_GRANTS_RECD.ALTER_PRIV is 'Permission to ALTER the object?' / comment on column ALL_TAB_GRANTS_RECD.INDEX_PRIV is 'Permission to create/drop an INDEX on the object?' / comment on column ALL_TAB_GRANTS_RECD.CREATED is 'Timestamp for the grant' / drop public synonym ALL_TAB_GRANTS_RECD / create public synonym ALL_TAB_GRANTS_RECD for ALL_TAB_GRANTS_RECD / grant select on ALL_TAB_GRANTS_RECD to PUBLIC / remark remark FAMILY "TS_QUOTAS" remark Tablespace quotas for users. remark This family has no ALL member. remark drop view USER_TS_QUOTAS / create view USER_TS_QUOTAS (TABLESPACE_NAME, BYTES, MAX_BYTES, BLOCKS, MAX_BLOCKS) as select ts.name, q.blocks * ts.blocksize, q.maxblocks * ts.blocksize, q.blocks, q.maxblocks from sys.tsq$ q, sys.ts$ ts where q.ts# = ts.ts# and q.user# = uid / comment on table USER_TS_QUOTAS is 'Tablespace quotas for the user' / comment on column USER_TS_QUOTAS.TABLESPACE_NAME is 'Tablespace name' / comment on column USER_TS_QUOTAS.BLOCKS is 'Number of ORACLE blocks charged to the user' / comment on column USER_TS_QUOTAS.MAX_BLOCKS is 'User''s quota in ORACLE blocks. NULL if no limit' / comment on column USER_TS_QUOTAS.BYTES is 'Number of bytes charged to the user' / comment on column USER_TS_QUOTAS.MAX_BYTES is 'User''s quota in bytes. NULL if no limit' / drop public synonym USER_TS_QUOTAS / create public synonym USER_TS_QUOTAS for USER_TS_QUOTAS / grant select on USER_TS_QUOTAS to PUBLIC / drop view DBA_TS_QUOTAS / create view DBA_TS_QUOTAS (TABLESPACE_NAME, USERNAME, BYTES, MAX_BYTES, BLOCKS, MAX_BLOCKS) as select ts.name, u.name, q.blocks * ts.blocksize, q.maxblocks * ts.blocksize, q.blocks, q.maxblocks from sys.tsq$ q, sys.ts$ ts, sys.user$ u where q.ts# = ts.ts# and q.user# = u.user# / comment on table DBA_TS_QUOTAS is 'Tablespace quotas for all users' / comment on column DBA_TS_QUOTAS.TABLESPACE_NAME is 'Tablespace name' / comment on column DBA_TS_QUOTAS.USERNAME is 'User with resource rights on the tablespace' / comment on column DBA_TS_QUOTAS.BLOCKS is 'Number of ORACLE blocks charged to the user' / comment on column DBA_TS_QUOTAS.MAX_BLOCKS is 'User''s quota in ORACLE blocks. NULL if no limit' / comment on column DBA_TS_QUOTAS.BYTES is 'Number of bytes charged to the user' / comment on column DBA_TS_QUOTAS.MAX_BYTES is 'User''s quota in bytes. NULL if no limit' / remark remark FAMILY "USERS" remark Users enrolled in the database. remark drop view USER_USERS / create view USER_USERS (USERNAME, USER_ID, CONNECT_PRIV, RESOURCE_PRIV, DBA_PRIV, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, EXPIRES) as select u.name, u.user#, u.connect$, u.resource$, u.dba$, dts.name, tts.name, u.ctime, u.ptime from sys.user$ u, sys.ts$ dts, sys.ts$ tts where u.datats# = dts.ts# and u.tempts# = tts.ts# and u.user# = uid / comment on table USER_USERS is 'Information about the current user' / comment on column USER_USERS.USERNAME is 'Name of the user' / comment on column USER_USERS.USER_ID is 'ID number of the user' / comment on column USER_USERS.CONNECT_PRIV is 'Does user have CONNECT privilege?' / comment on column USER_USERS.RESOURCE_PRIV is 'Does user have RESOURCE privilege?' / comment on column USER_USERS.DBA_PRIV is 'Does user have DBA Privilege?' / comment on column USER_USERS.DEFAULT_TABLESPACE is 'Default tablespace for data' / comment on column USER_USERS.TEMPORARY_TABLESPACE is 'Default tablespace for temporary tables' / comment on column USER_USERS.CREATED is 'User creation date' / comment on column USER_USERS.EXPIRES is 'Password expiration date' / drop public synonym USER_USERS / create public synonym USER_USERS for USER_USERS / drop public synonym MYPRIVS / create public synonym MYPRIVS for USER_USERS / grant select on USER_USERS to PUBLIC / drop view ALL_USERS / create view ALL_USERS (USERNAME, USER_ID, /* CONNECT_PRIV, RESOURCE_PRIV, DBA_PRIV, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, */ CREATED) as select u.name, u.user#, /* u.connect$, u.resource$, u.dba$, dts.name, tts.name, */ u.ctime from sys.user$ u, sys.ts$ dts, sys.ts$ tts where u.datats# = dts.ts# and u.tempts# = tts.ts# / comment on table ALL_USERS is 'Information about all users of the database' / comment on column ALL_USERS.USERNAME is 'Name of the user' / comment on column ALL_USERS.USER_ID is 'ID number of the user' / remark comment on column ALL_USERS.CONNECT_PRIV is remark 'Does the user have CONNECT privilege?' remark / remark comment on column ALL_USERS.RESOURCE_PRIV is remark 'Does the user have RESOURCE privilege?' remark / remark comment on column ALL_USERS.DBA_PRIV is remark 'Does the user have DBA privilege?' remark / remark comment on column ALL_USERS.DEFAULT_TABLESPACE is remark 'Default tablespace for data' remark / remark comment on column ALL_USERS.TEMPORARY_TABLESPACE is remark 'Default tablespace for temporary tables' remark / comment on column ALL_USERS.CREATED is 'User creation date' / drop public synonym ALL_USERS / create public synonym ALL_USERS for ALL_USERS / grant select on ALL_USERS to PUBLIC / drop view DBA_USERS / create view DBA_USERS (USERNAME, USER_ID, PASSWORD, CONNECT_PRIV, RESOURCE_PRIV, DBA_PRIV, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, EXPIRES) as select u.name, u.user#, u.password, u.connect$, u.resource$, u.dba$, dts.name, tts.name, u.ctime, u.ptime from sys.user$ u, sys.ts$ dts, sys.ts$ tts where u.datats# = dts.ts# and u.tempts# = tts.ts# / comment on table DBA_USERS is 'Information about all users of the database' / comment on column DBA_USERS.USERNAME is 'Name of the user' / comment on column DBA_USERS.USER_ID is 'ID number of the user' / comment on column DBA_USERS.PASSWORD is 'Encrypted password' / comment on column DBA_USERS.CONNECT_PRIV is 'Does the user have CONNECT privilege?' / comment on column DBA_USERS.RESOURCE_PRIV is 'Does the user have RESOURCE privilege?' / comment on column DBA_USERS.DBA_PRIV is 'Does the user have DBA privilege?' / comment on column DBA_USERS.DEFAULT_TABLESPACE is 'Default tablespace for data' / comment on column DBA_USERS.TEMPORARY_TABLESPACE is 'Default tablespace for temporary tables' / comment on column DBA_USERS.CREATED is 'User creation date' / comment on column DBA_USERS.EXPIRES is 'Password expiration date' / remark remark FAMILY "VIEWS" remark All relevant information about views, except columns. remark drop view USER_VIEWS / create view USER_VIEWS (VIEW_NAME, TEXT_LENGTH, TEXT) as select o.name, v.textlength, v.text from sys.obj$ o, sys.view$ v where o.obj# = v.obj# and o.owner# = uid / comment on table USER_VIEWS is 'Text of views owned by the user' / comment on column USER_VIEWS.VIEW_NAME is 'Name of the view' / comment on column USER_VIEWS.TEXT_LENGTH is 'Length of the view text' / comment on column USER_VIEWS.TEXT is 'View text' / drop public synonym USER_VIEWS / create public synonym USER_VIEWS for USER_VIEWS / grant select on USER_VIEWS to PUBLIC / drop view ALL_VIEWS / create view ALL_VIEWS (OWNER, VIEW_NAME, TEXT_LENGTH, TEXT) as select u.name, o.name, v.textlength, v.text from sys.obj$ o, sys.view$ v, sys.user$ u where o.obj# = v.obj# and o.owner# = u.user# and (o.owner# = uid or o.obj# in (select ta.obj# from sys.tabauth$ ta where ta.grantee# in (uid, 1)) ) / comment on table ALL_VIEWS is 'Text of views accessible to the user' / comment on column ALL_VIEWS.OWNER is 'Owner of the view' / comment on column ALL_VIEWS.VIEW_NAME is 'Name of the view' / comment on column ALL_VIEWS.TEXT_LENGTH is 'Length of the view text' / comment on column ALL_VIEWS.TEXT is 'View text' / drop public synonym ALL_VIEWS / create public synonym ALL_VIEWS for ALL_VIEWS / grant select on ALL_VIEWS to PUBLIC / drop view DBA_VIEWS / create view DBA_VIEWS (OWNER, VIEW_NAME, TEXT_LENGTH, TEXT) as select u.name, o.name, v.textlength, v.text from sys.obj$ o, sys.view$ v, sys.user$ u where o.obj# = v.obj# and o.owner# = u.user# / comment on table DBA_VIEWS is 'Text of all views in the database' / comment on column DBA_VIEWS.OWNER is 'Owner of the view' / comment on column DBA_VIEWS.VIEW_NAME is 'Name of the view' / comment on column DBA_VIEWS.TEXT_LENGTH is 'Length of the view text' / comment on column DBA_VIEWS.TEXT is 'View text' / remark remark FAMILY "CONSTRAINTS" remark drop view USER_CONSTRAINTS / create view USER_CONSTRAINTS (OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME) as select ou.name, oc.name, decode(c.type, 1, 'C', 2, 'P', 3, 'U', 4, 'R', 5, 'V', 6, 'RL', 7, 'RS', '?'), o.name, c.condition, ru.name, rc.name from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru, sys.obj$ o, sys.cdef$ c where oc.owner# = ou.user# and oc.con# = c.con# and c.obj# = o.obj# and c.rcon# = rc.con#(+) and rc.owner# = ru.user#(+) and o.owner# = uid / comment on table USER_CONSTRAINTS is 'Constraint definitions on accessible tables' / comment on column USER_CONSTRAINTS.OWNER is 'Owner of the table' / comment on column USER_CONSTRAINTS.CONSTRAINT_NAME is 'Name associated with constraint definition' / comment on column USER_CONSTRAINTS.CONSTRAINT_TYPE is 'Type of constraint definition' / comment on column USER_CONSTRAINTS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column USER_CONSTRAINTS.SEARCH_CONDITION is 'Text of search condition for table check' / comment on column USER_CONSTRAINTS.R_OWNER is 'Owner of table used in referential constraint' / comment on column USER_CONSTRAINTS.R_CONSTRAINT_NAME is 'Name of unique constraint definition for referenced table' / grant select on USER_CONSTRAINTS to public / drop public synonym USER_CONSTRAINTS / create public synonym USER_CONSTRAINTS for USER_CONSTRAINTS / drop view ALL_CONSTRAINTS / create view ALL_CONSTRAINTS (OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME) as select ou.name, oc.name, decode(c.type, 1, 'C', 2, 'P', 3, 'U', 4, 'R', 5, 'V', 6, 'RL', 7, 'RS', '?'), o.name, c.condition, ru.name, rc.name from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru, sys.obj$ o, sys.cdef$ c where oc.owner# = ou.user# and oc.con# = c.con# and c.obj# = o.obj# and c.rcon# = rc.con#(+) and rc.owner# = ru.user#(+) and (o.owner# = uid or o.obj# in (select obj# from sys.tabauth$ where grantee# in (1,uid) ) ) / comment on table ALL_CONSTRAINTS is 'Constraint definitions on accessible tables' / comment on column ALL_CONSTRAINTS.OWNER is 'Owner of the table' / comment on column ALL_CONSTRAINTS.CONSTRAINT_NAME is 'Name associated with constraint definition' / comment on column ALL_CONSTRAINTS.CONSTRAINT_TYPE is 'Type of constraint definition' / comment on column ALL_CONSTRAINTS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column ALL_CONSTRAINTS.SEARCH_CONDITION is 'Text of search condition for table check' / comment on column ALL_CONSTRAINTS.R_OWNER is 'Owner of table used in referential constraint' / comment on column ALL_CONSTRAINTS.R_CONSTRAINT_NAME is 'Name of unique constraint definition for referenced table' / grant select on ALL_CONSTRAINTS to public / drop public synonym ALL_CONSTRAINTS / create public synonym ALL_CONSTRAINTS for ALL_CONSTRAINTS / drop view DBA_CONSTRAINTS / create view DBA_CONSTRAINTS (OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME) as select ou.name, oc.name, decode(c.type, 1, 'C', 2, 'P', 3, 'U', 4, 'R', 5, 'V', 6, 'RL', 7, 'RS', '?'), o.name, c.condition, ru.name, rc.name from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru, sys.obj$ o, sys.cdef$ c where oc.owner# = ou.user# and oc.con# = c.con# and c.obj# = o.obj# and c.rcon# = rc.con#(+) and rc.owner# = ru.user#(+) / comment on table DBA_CONSTRAINTS is 'Constraint definitions on accessible tables' / comment on column DBA_CONSTRAINTS.OWNER is 'Owner of the table' / comment on column DBA_CONSTRAINTS.CONSTRAINT_NAME is 'Name associated with constraint definition' / comment on column DBA_CONSTRAINTS.CONSTRAINT_TYPE is 'Type of constraint definition' / comment on column DBA_CONSTRAINTS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column DBA_CONSTRAINTS.SEARCH_CONDITION is 'Text of search condition for table check' / comment on column DBA_CONSTRAINTS.R_OWNER is 'Owner of table used in referential constraint' / comment on column DBA_CONSTRAINTS.R_CONSTRAINT_NAME is 'Name of unique constraint definition for referenced table' / drop public synonym DBA_CONSTRAINTS / create public synonym DBA_CONSTRAINTS for DBA_CONSTRAINTS / remark remark FAMILY "CONS_COLUMNS" remark drop view USER_CONS_COLUMNS / create view USER_CONS_COLUMNS (OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION) as select u.name, c.name, o.name, col.name, cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd, sys.obj$ o where c.owner# = u.user# and c.con# = cd.con# and cd.con# = cc.con# and cc.obj# = col.obj# and cc.col# = col.col# and cc.obj# = o.obj# and c.owner# = uid / comment on table USER_CONS_COLUMNS is 'Information about accessible columns in constraint definitions' / comment on column USER_CONS_COLUMNS.OWNER is 'Owner of the constraint definition' / comment on column USER_CONS_COLUMNS.CONSTRAINT_NAME is 'Name associated with the constraint definition' / comment on column USER_CONS_COLUMNS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column USER_CONS_COLUMNS.COLUMN_NAME is 'Name associated with column specified in the constraint definition' / comment on column USER_CONS_COLUMNS.POSITION is 'Original position of column in definition' / grant select on USER_CONS_COLUMNS to public / drop public synonym USER_CONS_COLUMNS / create public synonym USER_CONS_COLUMNS for USER_CONS_COLUMNS / drop view ALL_CONS_COLUMNS / create view ALL_CONS_COLUMNS (OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION) as select u.name, c.name, o.name, col.name, cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd, sys.obj$ o where c.owner# = u.user# and c.con# = cd.con# and cd.con# = cc.con# and cc.obj# = col.obj# and cc.col# = col.col# and cc.obj# = o.obj# and (c.owner# = uid or cd.obj# in (select obj# from sys.tabauth$ where grantee# in (1,uid) ) ) / comment on table ALL_CONS_COLUMNS is 'Information about accessible columns in constraint definitions' / comment on column ALL_CONS_COLUMNS.OWNER is 'Owner of the constraint definition' / comment on column ALL_CONS_COLUMNS.CONSTRAINT_NAME is 'Name associated with the constraint definition' / comment on column ALL_CONS_COLUMNS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column ALL_CONS_COLUMNS.COLUMN_NAME is 'Name associated with column specified in the constraint definition' / comment on column ALL_CONS_COLUMNS.POSITION is 'Original position of column in definition' / grant select on ALL_CONS_COLUMNS to public / drop public synonym ALL_CONS_COLUMNS / create public synonym ALL_CONS_COLUMNS for ALL_CONS_COLUMNS / drop view DBA_CONS_COLUMNS / create view DBA_CONS_COLUMNS (OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION) as select u.name, c.name, o.name, col.name, cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd, sys.obj$ o where c.owner# = u.user# and c.con# = cd.con# and cd.con# = cc.con# and cc.obj# = col.obj# and cc.col# = col.col# and cc.obj# = o.obj# / comment on table DBA_CONS_COLUMNS is 'Information about accessible columns in constraint definitions' / comment on column DBA_CONS_COLUMNS.OWNER is 'Owner of the constraint definition' / comment on column DBA_CONS_COLUMNS.CONSTRAINT_NAME is 'Name associated with the constraint definition' / comment on column DBA_CONS_COLUMNS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column DBA_CONS_COLUMNS.COLUMN_NAME is 'Name associated with column specified in the constraint definition' / comment on column DBA_CONS_COLUMNS.POSITION is 'Original position of column in definition' / drop public synonym DBA_CONS_COLUMNS / create public synonym DBA_CONS_COLUMNS for DBA_CONS_COLUMNS / remark remark FAMILY "CONSTRAINT VIEWS" FOR ANSI remark drop view CONSTRAINT_DEFS / create view CONSTRAINT_DEFS (OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME) as select ou.name, oc.name, decode(c.type, 1, 'C', 2, 'P', 3, 'U', 4, 'R', 5, 'V', 6, 'RL', 7, 'RS', '?'), o.name, c.condition, ru.name, rc.name from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru, sys.obj$ o, sys.cdef$ c where oc.owner# = ou.user# and oc.con# = c.con# and c.obj# = o.obj# and c.rcon# = rc.con#(+) and rc.owner# = ru.user#(+) and (o.owner# = uid or o.obj# in (select obj# from sys.tabauth$ where grantee# in (1,uid) ) ) / comment on table CONSTRAINT_DEFS is 'Constraint Definitions on accessible tables' / comment on column CONSTRAINT_DEFS.OWNER is 'Owner of the table' / comment on column CONSTRAINT_DEFS.CONSTRAINT_NAME is 'Name associated with constraint definition' / comment on column CONSTRAINT_DEFS.CONSTRAINT_TYPE is 'Type of constraint definition' / comment on column CONSTRAINT_DEFS.TABLE_NAME is 'Name associated with table with constraint definition' / comment on column CONSTRAINT_DEFS.SEARCH_CONDITION is 'Text of search condition for table check' / comment on column CONSTRAINT_DEFS.R_OWNER is 'Owner of table used in referential constraint' / comment on column CONSTRAINT_DEFS.R_CONSTRAINT_NAME is 'Name of unique constraint definition for referenced table' / grant select on CONSTRAINT_DEFS to public / drop public synonym CONSTRAINT_DEFS / create public synonym CONSTRAINT_DEFS for CONSTRAINT_DEFS / drop view CONSTRAINT_COLUMNS / create view CONSTRAINT_COLUMNS (OWNER, CONSTRAINT_NAME, COLUMN_NAME, POSITION) as select u.name, c.name, col.name, cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd where c.owner# = u.user# and c.con# = cd.con# and cd.type in (2,3,4) and cd.con# = cc.con# and cc.obj# = col.obj# and cc.col# = col.col# and (c.owner# = uid or cd.obj# in (select obj# from sys.tabauth$ where grantee# in (1,uid) ) ) / comment on table CONSTRAINT_COLUMNS is 'Information about accessible columns in constraint definitions' / comment on column CONSTRAINT_COLUMNS.OWNER is 'Owner of the constraint definition' / comment on column CONSTRAINT_COLUMNS.CONSTRAINT_NAME is 'Name associated with the constraint definition' / comment on column CONSTRAINT_COLUMNS.COLUMN_NAME is 'Name associated with column specified in the constraint definition' / comment on column CONSTRAINT_COLUMNS.POSITION is 'Original position of column in definition' / grant select on CONSTRAINT_COLUMNS to public / drop public synonym CONSTRAINT_COLUMNS / create public synonym CONSTRAINT_COLUMNS for CONSTRAINT_COLUMNS / remark remark FAMILY "FIXED (VIRTUAL) VIEWS" remark drop view v_$process; create view v_$process as select * from v$process; drop public synonym v$process; create public synonym v$process for v_$process; drop view v_$bgprocess; create view v_$bgprocess as select * from v$bgprocess; drop public synonym v$bgprocess; create public synonym v$bgprocess for v_$bgprocess; drop view v_$session; create view v_$session as select * from v$session; drop public synonym v$session; create public synonym v$session for v_$session; drop view v_$transaction; create view v_$transaction as select * from v$transaction; drop public synonym v$transaction; create public synonym v$transaction for v_$transaction; drop view v_$latch; create view v_$latch as select * from v$latch; drop public synonym v$latch; create public synonym v$latch for v_$latch; drop view v_$latchname; create view v_$latchname as select * from v$latchname; drop public synonym v$latchname; create public synonym v$latchname for v_$latchname; drop view v_$latchholder; create view v_$latchholder as select * from v$latchholder; drop public synonym v$latchholder; create public synonym v$latchholder for v_$latchholder; drop view v_$resource; create view v_$resource as select * from v$resource; drop public synonym v$resource; create public synonym v$resource for v_$resource; drop view v_$_lock; create view v_$_lock as select * from v$_lock; drop public synonym v$_lock; create public synonym v$_lock for v_$_lock; drop view v_$lock; create view v_$lock as select * from v$lock; drop public synonym v$lock; create public synonym v$lock for v_$lock; drop view v_$sesstat; create view v_$sesstat as select * from v$sesstat; drop public synonym v$sesstat; create public synonym v$sesstat for v_$sesstat; drop view v_$sysstat; create view v_$sysstat as select * from v$sysstat; drop public synonym v$sysstat; create public synonym v$sysstat for v_$sysstat; drop view v_$statname; create view v_$statname as select * from v$statname; drop public synonym v$statname; create public synonym v$statname for v_$statname; drop view v_$access; create view v_$access as select * from v$access; drop public synonym v$access; create public synonym v$access for v_$access; drop view v_$dbfile; create view v_$dbfile as select * from v$dbfile; drop public synonym v$dbfile; create public synonym v$dbfile for v_$dbfile; drop view v_$filestat; create view v_$filestat as select * from v$filestat; drop public synonym v$filestat; create public synonym v$filestat for v_$filestat; drop view v_$logfile; create view v_$logfile as select * from v$logfile; drop public synonym v$logfile; create public synonym v$logfile for v_$logfile; drop view v_$rollname; create view v_$rollname as select * from v$rollname; drop public synonym v$rollname; create public synonym v$rollname for v_$rollname; drop view v_$rollstat; create view v_$rollstat as select * from v$rollstat; drop public synonym v$rollstat; create public synonym v$rollstat for v_$rollstat; drop view v_$sga; create view v_$sga as select * from v$sga; drop public synonym v$sga; create public synonym v$sga for v_$sga; drop view v_$parameter; create view v_$parameter as select * from v$parameter; drop public synonym v$parameter; create public synonym v$parameter for v_$parameter; drop view v_$rowcache; create view v_$rowcache as select * from v$rowcache; drop public synonym v$rowcache; create public synonym v$rowcache for v_$rowcache; rem rem V5 views required for other Oracle products rem drop view syscatalog_; create view syscatalog_ (tname, creator, creatorid, tabletype) as select o.name, u.name, o.owner#, decode(o.type, 2, 'TABLE', 4, 'VIEW', 6, 'SEQUENCE','?') from sys.user$ u, sys.obj$ o where u.user# = o.owner# and o.type in (2, 4, 6) and ( o.owner# = uid or o.obj# in (select tau.obj# from sys.tabauth$ tau where tau.grantee# in (uid, 1) ) ) / drop view syscatalog; create view syscatalog (tname, creator, tabletype) as select tname, creator, tabletype from syscatalog_ / grant select on syscatalog to public; create synonym system.syscatalog for syscatalog; rem rem The catalog view returns almost all tables accessible to the user rem except tables in SYS and SYSTEM ("dictionary tables"). rem drop view catalog; create view catalog (tname, creator, tabletype) as select tname, creator, tabletype from syscatalog_ where creatorid not in (0,2) / grant select on catalog to public; create synonym system.catalog for catalog; drop view tab; create view tab (tname, tabtype, clusterid) as select o.name, decode(o.type, 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM'), t.tab# from sys.tab$ t, sys.obj$ o where o.owner# = uid and o.type >=2 and o.type <=5 and o.obj# = t.obj# (+) / grant select on tab to public; create synonym system.tab for tab; drop view col; create view col (tname, colno, cname, coltype, width, scale, precision, nulls, defaultval) as select t.name, c.col#, c.name, decode(c.type#, 1, 'CHAR', 2, decode(c.scale, null, decode(c.precision, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, 'VARCHAR', 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 'UNDEFINED'), c.length, c.scale, c.precision, decode(c.null$, 0, 'NULL', 'NOT NULL'), c.default$ from sys.col$ c, sys.obj$ t where t.obj# = c.obj# and t.type in (2, 3, 4) and t.owner# = uid / grant select on col to public; create synonym system.col for col; drop view syssegobj; create view syssegobj (obj#, file#, block#, type, pctfree$, pctused$) as select obj#, file#, block#, 'TABLE', pctfree$, pctused$ from sys.tab$ union select obj#, file#, block#, 'CLUSTER', pctfree$, pctused$ from sys.clu$ union select obj#, file#, block#, 'INDEX', to_number(null), to_number(null) from sys.ind$ / grant select on syssegobj to public; drop view tabquotas; create view tabquotas (tname, type, objno, nextext, maxext, pinc, pfree, pused) as select t.name, so.type, t.obj#, s.extsize, s.maxexts, s.extpct, so.pctfree$, so.pctused$ from sys.seg$ s, sys.obj$ t, syssegobj so where t.owner# = uid and t.obj# = so.obj# and so.file# = s.file# and so.block# = s.block# / grant select on tabquotas to public; create synonym system.tabquotas for tabquotas; drop view sysfiles; create view sysfiles (tsname, fname, blocks) as select ts.name, dbf.name, f.blocks from sys.ts$ ts, sys.file$ f, sys.v$dbfile dbf where ts.ts# = f.ts#(+) and dbf.file# = f.file# and f.status$ = 2 / grant select on sysfiles to public; create synonym system.sysfiles for sysfiles; drop view synonyms; create view synonyms (sname, syntype, creator, tname, database, tabtype) as select s.name, decode (s.owner#,1,'PUBLIC','PRIVATE'), t.owner, t.name, 'LOCAL', decode(ot.type, 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED') from sys.obj$ s, sys.obj$ ot, sys.syn$ t, sys.user$ u where s.obj# = t.obj# and s.type = 5 and ot.name = t.name and t.owner = u.name and ot.owner# = u.user# and s.owner# in (1,uid) and t.node is null union select s.name, decode(s.owner#, 1, 'PUBLIC', 'PRIVATE'), t.owner, t.name, t.node, 'REMOTE' from sys.obj$ s, sys.syn$ t where s.obj# = t.obj# and s.type = 5 and s.owner# in (1, uid) and t.node is not null / grant select on synonyms to public; drop view publicsyn; create view publicsyn (sname, creator, tname, database, tabtype) as select sname, creator, tname, database, tabtype from synonyms where syntype = 'PUBLIC' / grant select on publicsyn to public; create synonym system.publicsyn for publicsyn; 
