/* the next two queries help you find out other objects you own. */
/* use this query to find out what procedures, functions, and triggers */
/* you have previously created */
select object_type, object_name from user_objects
where object_type = 'PROCEDURE' or object_type = 'FUNCTION'
or object_type = 'TRIGGER';
OBJECT_TYPE OBJECT_NAME
----------- --------------------
TRIGGER ADD_ORDER_PLACED_AIR
TRIGGER REMOVE_ORDER_PLACED_BDR
PROCEDURE SHOWCUST
/* this next query tells you all objects you have previously created */
/* including procedures, functions, and triggers as well as tables, */
/* indexes, constraints, etc. */
select object_type, object_name from user_objects;
OBJECT_TYPE OBJECT_NAME
------------------ --------------------
INDEX CUSTOMER_PK
TABLE CUSTOMER_T
INDEX ORDER_DATE_IDX
INDEX ORDER_LINE_PK
TABLE ORDER_LINE_T
INDEX ORDER_PK
TABLE ORDER_T
INDEX PRODUCT_PK
TABLE PRODUCT_T
9 rows selected.
/* to find out what tablespace a user’s table is located in… */
select table_name, tablespace_name from user_tables;
/* to find out storage info for a table… */
Select tablespace_name, initial_extent, next_extent, avg_space, avg_row_len
from user_tables
where table_name = 'PRODUCT_T';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT AVG_SPACE AVG_ROW_LEN
------------------------------ -------------- ----------- ---------- -----------
USERS 65536 8003 42
/* to find out what constraints you have created… */
select constraint_name, table_name, constraint_type, search_condition from user_constraints;
(output below does not include table_name)
CONSTRAINT_NAME C SEARCH_CONDITION
----------------------- - ------------------------------------------------------
SYS_C005368 C "ORDER_ID" IS NOT NULL
ORDER_DATE_CC C Order_Date >= TO_DATE ('01-JAN-2000', 'DD-MON-YYYY')
ORDER_PK P
/* in addition to using the DESCRIBE command, you can use the */
/* following command to find out what columns are in a table */
column fancy_type format a20select column_name, data_type || '(' || data_length || ')' as fancy_typefrom user_tab_columnswhere table_name = 'USERS'order by column_id;select table_name, column_name, data_type, data_length, nullable, data_default
from user_tab_columns
where table_name = ‘ORDER_T’;
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_DEFAULT
----------------- ------------------- ------------- --------------------------
ORDER_T ORDER_ID NUMBER
ORDER_T ORDER_DATE DATE SYSDATE
ORDER_T CUSTOMER_ID NUMBER
/* and here’s what you can use to find out what table and column(s) */
/* an index is based on */
select index_name, table_name, column_name
from user_ind_columns
where index_name = ‘SSN_CALLNO_PK’;
/* to find out what privileges you have granted to what users */
select grantee, table_name, privilege from dba_tab_privs where grantor like 'YOURUSERNAME%';
GRANTEE TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ -------------------
PUBLIC STUDENT SELECT
PUBLIC PROF SELECT
PUBLIC COURSE SELECT
PUBLIC CLASS SELECT
PUBLIC REGISTRATION SELECT
PUBLIC VENDOR SELECT
PUBLIC PRODUCT SELECT
PUBLIC CUSTOMER SELECT
PUBLIC INVOICE SELECT
PUBLIC PMT SELECT
PUBLIC LINE SELECT
…
…
/* to find what system privileges have been granted to roles */
Select privilege, admin_option from role_sys_privs where role like ‘ROLENAME%’;
/* to find what roles have been granted to roles */
Select granted_role, admin_option from role_role_privs where role like ‘ROLENAME%’;
/* to find what table privileges have been granted to roles */
Select * from role_tab_privs where role like ‘ROLENAME%’;
/* to find what system privileges have been granted to users and roles */
Select * from dba_sys_privs where grantee like ‘USERORROLENAME;
/* to determine the code in an existing trigger or procedure */
select TEXT
from USER_SOURCE
where name = 'SHOWCUST'
and type = 'PROCEDURE'
order by LINE;
select TEXT
from USER_SOURCE
where name = 'ADD_ORDER_PLACED_AIR'
and type = 'TRIGGER'
order by LINE;
/* to determine whether a tablespace is dictionary or locally managed */
select tablespace_name,extent_management from dba_tablespaces
where tablespace_name = '';
where tablespace_name = '
/* to see what user name is logged on */
Show user;
/* to view how much space is occupied by files within tablespaces */
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY file_name;
FROM dba_data_files
ORDER BY file_name;
/* to view rowid's of records; this one gives you the absolute file number */
select customer_id, rowid,
dbms_rowid.rowid_to_absolute_fno (rowid,'GINA_GREEN','CUSTOMER_T') AS "FILE"
FROM GINA_GREEN.CUSTOMER_T;
CUSTOMER_ID ROWID FILE
----------- ------------------ ----------
1 AAAFx1AADAAAAFmAAA 3
2 AAAFx1AADAAAAFmAAB 3
3 AAAFx1AADAAAAFmAAC 3
4 AAAFx1AADAAAAFmAAD 3
5 AAAFx1AADAAAAFmAAE 3
6 AAAFx1AADAAAAFmAAF 3
7 AAAFx1AADAAAAFmAAG 3
8 AAAFx1AADAAAAFmAAH 3
9 AAAFx1AADAAAAFmAAI 3
10 AAAFx1AADAAAAFmAAJ 3
11 AAAFx1AADAAAAFmAAK 3
12 AAAFx1AADAAAAFmAAL 3
13 AAAFx1AADAAAAFmAAM 3
14 AAAFx1AADAAAAFmAAN 3
15 AAAFx1AADAAAAFmAAO 3
15 rows selected.
/* another (simpler) query that gives just the rowid */
select customer_id, rowid
from gina_green.customer_t;
/* this query tells you the last time tables you own have been analyzed */
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'GINA_GREEN';
/* to see what sequences you have created */
Select * from user_sequences;
/* to list the physical datafiles that comprise the currently-logged-onto db */
/* you need to know these in case you ever want to delete a db—you can’t drop */
/* a db, so you’ll need to physically delete these files and remove the */
/* instance OS service */
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile;
/* to find out which oracle instance you’re logged onto */
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
Posts
Social: