[Dica] Tabelas Internas - Dicionário de Dados

Scripts Diversos para o Oracle SQL*Plus. (Relacionado a ferramenta Oracle SQL*Plus, para questões de SQL, usar o forum SQL)
Responder
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:

Script que pesquisa as tabelas do dicionário de dados do Oracle. Existe no SYS uma tabela DICTIONARY, com todas tabelas do dicionário e uma breve explicação do que ela é.

É claro, ela só mostra as tabelas que o usuário tem direito! (se você não tem privilégio de DBA, as DBA_tables nem vão aparecer)

Selecionar tudo

select
  table_name
, substr(comments,1,60) comments
from sys.dictionary
where table_name like upper('%&String%')
/
Exemplo de uso (gravei como dic.sql):

Selecionar tudo

SQL> @DIC
Entre o valor para string: TABLES

TABLE_NAME                     COMMENTS
------------------------------ ------------------------------------------------------------
ALL_ALL_TABLES                 Description of all object and relational tables accessible t
ALL_NESTED_TABLES              Description of nested tables in tables accessible to the use
ALL_OBJECT_TABLES              Description of all object tables accessible to the user
ALL_PART_TABLES
ALL_QUEUE_TABLES               All queue tables accessible to the user
ALL_TABLES                     Description of relational tables accessible to the user
DBA_ALL_TABLES                 Description of all object and relational tables in the datab
DBA_CACHEABLE_TABLES
DBA_CACHEABLE_TABLES_BASE
DBA_NESTED_TABLES              Description of nested tables contained in all tables
DBA_OBJECT_TABLES              Description of all object tables in the database
DBA_PART_TABLES
DBA_QUEUE_TABLES               All queue tables created in the database
DBA_TABLES                     Description of all relational tables in the database
DBA_TABLESPACES                Description of all tablespaces
USER_ALL_TABLES                Description of all object and relational tables owned by the
USER_NESTED_TABLES             Description of nested tables contained in the user's own tab
USER_OBJECT_TABLES             Description of the user's own object tables
USER_PART_TABLES
USER_QUEUE_TABLES              All queue tables created by the user
USER_TABLES                    Description of the user's own relational tables
USER_TABLESPACES               Description of accessible tablespaces
GV$TABLESPACE                  Synonym for GV_$TABLESPACE
V$TABLESPACE                   Synonym for V_$TABLESPACE
SQL> 
Agora, buscando todas PRIVS

Selecionar tudo

SQL> @DIC
Enter value for string: PRIVS
TABLE_NAME                     COMMENTS
------------------------------ ------------------------------------------------------------
ALL_COL_PRIVS                  Grants on columns for which the user is the grantor, grantee
ALL_COL_PRIVS_MADE             Grants on columns for which the user is owner or grantor
ALL_COL_PRIVS_RECD             Grants on columns for which the user, PUBLIC or enabled role
ALL_TAB_PRIVS                  Grants on objects for which the user is the grantor, grantee
ALL_TAB_PRIVS_MADE             User's grants and grants on user's objects
ALL_TAB_PRIVS_RECD             Grants on objects for which the user, PUBLIC or enabled role
DBA_COL_PRIVS                  All grants on columns in the database
DBA_ROLE_PRIVS                 Roles granted to users and roles
DBA_RSRC_CONSUMER_GROUP_PRIVS  Switch privileges for consumer groups
DBA_RSRC_MANAGER_SYSTEM_PRIVS  system privileges for the resource manager
DBA_SYS_PRIVS                  System privileges granted to users and roles
DBA_TAB_PRIVS                  All grants on objects in the database
USER_COL_PRIVS                 Grants on columns for which the user is the owner, grantor o
USER_COL_PRIVS_MADE            All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD            Grants on columns for which the user is the grantee
USER_ROLE_PRIVS                Roles granted to current user
USER_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups for the user
USER_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager for the user
USER_SYS_PRIVS                 System privileges granted to current user
USER_TAB_PRIVS                 Grants on objects for which the user is the owner, grantor o
USER_TAB_PRIVS_MADE            All grants on objects owned by the user
USER_TAB_PRIVS_RECD            Grants on objects for which the user is the grantee
ROLE_ROLE_PRIVS                Roles which are granted to roles
ROLE_SYS_PRIVS                 System privileges granted to roles
ROLE_TAB_PRIVS                 Table privileges granted to roles
SESSION_PRIVS                  Privileges which the user currently has set
GV$ENABLEDPRIVS                Synonym for GV_$ENABLEDPRIVS
V$ENABLEDPRIVS                 Synonym for V_$ENABLEDPRIVS

28 rows selected.

SQL> 
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 6 visitantes