In order to access DD tables that begin with “DBA_”, your userid would need to have been granted the DBA role. Output displayed in the examples below will NOT necessarily look like the output that you will get—your output will vary depending upon what objects you have defined/created in your schema.
/* use this query to find out what tablespaces have been created for the */
/* oracle database you are accessing. the 6 shown below are the default */
/* tablespaces that are created when, as a part of the installation of po8 */
/* you have oracle create a default database */
/* in practice, you may want to create multiple DATA tablespaces (such as */
/* PAYROLL_DATA) and point developers, applications, tables, etc. to these */
/* specific tablespaces instead of lumping all tables into USERS tablespace */
select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
RBS
USERS
TEMP
TOOLS
INDX
6 rows selected.
/* issue this query to find out what roles have been created for the */
/* oracle db you are accessing. the 1st 19 roles are default roles that */
/* are created when you install oracle; the 20th role is one I created */
select * from dba_roles;
ROLE PASSWORD
------------------------------ -------
CONNECT NO
RESOURCE NO
DBA NO
EXP_FULL_DATABASE NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
AQ_USER_ROLE NO
IMP_FULL_DATABASE NO
RECOVERY_CATALOG_OWNER NO
AQ_ADMINISTRATOR_ROLE NO
SNMPAGENT NO
OEM_MONITOR NO
JAVADEBUGPRIV NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
TYPICAL_USER NO
20 rows selected.
/* use this query to find out what users have been defined for the database, and what default tablespace they have been assigned. The Default for any non-oracle-created users is the users tablespace, unless you specify otherwise in the create user cmd */
/*The first 10 users were created as a part of the oracle installation. The DBA created gina_green using the system-manager login. Then created kimberly, michael, and gw using the gina_green login. */
select username, default_tablespace from dba_users;
OR
Select username, default_tablespace, created from dba_users
Where created > ’31-dec-2006’;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM TOOLS
OUTLN SYSTEM
DEMO USERS
DBSNMP SYSTEM
SCOTT USERS
PO8 SYSTEM
AURORA$ORB$UNAUTHENTICATED SYSTEM
AURORA$JIS$UTILITY$ SYSTEM
OSE$HTTP$ADMIN SYSTEM
GINA_GREEN USERS
KIMBERLY USERS
MICHAEL USERS
GW USERS
14 rows selected.
/* use this query to find what usrs (grantees) have been granted what roles */
/* typical_user is a role I created */
select grantee, granted_role from dba_role_privs where grantee not in
('SYS', 'SYSTEM') order by grantee;
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
AURORA$JIS$UTILITY$ RESOURCE
DBA DELETE_CATALOG_ROLE
DBA EXECUTE_CATALOG_ROLE
DBA EXP_FULL_DATABASE
DBA IMP_FULL_DATABASE
DBA JAVA_ADMIN
DBA JAVA_DEPLOY
DBA SELECT_CATALOG_ROLE
DBSNMP CONNECT
DBSNMP RESOURCE
DBSNMP SNMPAGENT
DEMO CONNECT
DEMO RESOURCE
EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE SELECT_CATALOG_ROLE
GINA_GREEN AQ_ADMINISTRATOR_ROLE
GINA_GREEN AQ_USER_ROLE
GINA_GREEN CONNECT
GINA_GREEN DBA
GINA_GREEN DELETE_CATALOG_ROLE
GINA_GREEN EXECUTE_CATALOG_ROLE
GINA_GREEN EXP_FULL_DATABASE
GINA_GREEN IMP_FULL_DATABASE
GINA_GREEN JAVADEBUGPRIV
GINA_GREEN JAVAIDPRIV
GINA_GREEN JAVASYSPRIV
GINA_GREEN JAVAUSERPRIV
GINA_GREEN JAVA_ADMIN
GINA_GREEN JAVA_DEPLOY
GINA_GREEN OEM_MONITOR
GINA_GREEN RECOVERY_CATALOG_OWNER
GINA_GREEN RESOURCE
GINA_GREEN SELECT_CATALOG_ROLE
GINA_GREEN SNMPAGENT
GINA_GREEN TYPICAL_USER
GW CONNECT
GW DBA
IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE
IMP_FULL_DATABASE SELECT_CATALOG_ROLE
JAVASYSPRIV JAVAUSERPRIV
MICHAEL TYPICAL_USER
OEM_MONITOR CONNECT
OEM_MONITOR SELECT_CATALOG_ROLE
OSE$HTTP$ADMIN JAVAUSERPRIV
OSE$HTTP$ADMIN RESOURCE
OUTLN CONNECT
OUTLN RESOURCE
PO8 DBA
SCOTT CONNECT
SCOTT RESOURCE
50 rows selected.
/* there are 2 queries you can use to find out what tables you own. */
/* For this one, change USERNAME to whatever username you use to */
/* log into sql+; must use caps */
select owner, table_name from all_tables where owner = 'USERNAME';
OWNER TABLE_NAME
------------------------------ ------------------------------
GINA_GREEN CARRIER
GINA_GREEN CUSTOMER
GINA_GREEN CUSTOMER_SHIPMENT
GINA_GREEN CUSTOMER_T
GINA_GREEN CUST_ORDER
GINA_GREEN INVENTORY
GINA_GREEN INVENTORY_SHIPPING
GINA_GREEN ITEM
GINA_GREEN ORDER_T
GINA_GREEN PRODUCT_T
10 rows selected.
/* the other query that does the same thing is the following... */
select table_name from user_tables;
TABLE_NAME
------------------------------
CARRIER
CUSTOMER
CUSTOMER_SHIPMENT
CUSTOMER_T
CUST_ORDER
INVENTORY
INVENTORY_SHIPPING
ITEM
ORDER_T
PRODUCT_T
10 rows selected.
/* use this query to see tables that were created by others (=owner),*/
/* that you have been granted access too, including what privileges */
/* you were granted (=privilege) and whether you can grant access to */
/* others (=GRA) */
select * from user_tab_privs_recd;
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
---------------- -------------------- --------- ----------------- ---
SYS IMAGES_DIR SYS READ YES
Posts
Social: