Oracle PL/SQL Tutorial/System Tables Data Dictionary/user objects
Содержание
- 1 Behavior of dependent objects.
- 2 Check function/procedure status
- 3 Check the package status in user_objects
- 4 Check the status for all stored procedures is by using the Oracle data dictionary view USER_OBJECTS
- 5 Check user_objects for object name
- 6 Create drop command by query the user_objects table
- 7 Create grant permission command by querying user_objects table
- 8 Finding, Validating, and Describing Packages
- 9 Get all invalid user objects in user_objects table
- 10 Get invalid table objects by joining user_segments and user_objects
- 11 Get Object id from user_objects
- 12 If procedure is valid
- 13 Join sys.col$ and user_objects
- 14 Query user_objects for all java classes
- 15 Query user_objects for all packages, procedures and functions
- 16 Query user_objects for all PL/SQL code
- 17 Query user_objects for object status
- 18 Query user_objects for user-defined data type
- 19 Query user_objects for your functions and procedures
- 20 Recompile all invalid package
- 21 Show the procedure is marked invalid **
- 22 User and its average object id
Behavior of dependent objects.
SQL>
SQL> COLUMN object_name FORMAT a20
SQL>
SQL> CREATE TABLE simple_table (f1 NUMBER);
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE Dependee AS
2 PROCEDURE Example(p_Val IN NUMBER);
3 END Dependee;
4 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS
2 PROCEDURE Example(p_Val IN NUMBER) IS
3 BEGIN
4 INSERT INTO simple_table VALUES (p_Val);
5 END Example;
6 END Dependee;
7 /
Package body created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Depender(p_Val IN NUMBER) AS
2 BEGIN
3 Dependee.Example(p_Val + 1);
4 END Depender;
5 /
Procedure created.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE");
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
DEPENDEE PACKAGE VALID
DEPENDEE PACKAGE BODY VALID
DEPENDER PROCEDURE VALID
SIMPLE_TABLE TABLE VALID
4 rows selected.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS
2 PROCEDURE Example(p_Val IN NUMBER) IS
3 BEGIN
4 INSERT INTO simple_table VALUES (p_Val - 1);
5 END Example;
6 END Dependee;
7 /
Package body created.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE");
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
DEPENDEE PACKAGE VALID
DEPENDEE PACKAGE BODY VALID
DEPENDER PROCEDURE VALID
SIMPLE_TABLE TABLE VALID
4 rows selected.
SQL>
SQL> DROP TABLE simple_table;
Table dropped.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE");
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
DEPENDEE PACKAGE VALID
DEPENDEE PACKAGE BODY INVALID
DEPENDER PROCEDURE VALID
3 rows selected.
SQL>
Check function/procedure status
SQL>
SQL> create or replace function getArea (i_rad NUMBER)
2 return NUMBER
3 is
4 begin
5 return 3.14*(i_rad**2);
6 end;
7 /
Function created.
SQL>
SQL>
SQL>
SQL> alter function getArea compile;
Function altered.
SQL>
SQL> select status
2 from user_objects
3 where object_name = "GETAREA";
/
STATUS
----------
VALID
Check the package status in user_objects
SQL>
SQL> create or replace package pkg_a is
2 v_a number:=0;
3 function a1 return NUMBER;
4 end;
5 /
Package created.
SQL> create or replace package body pkg_a is
2 function a1 return NUMBER is
3 begin
4 return 0;
5 end;
6 end;
7 /
Package body created.
SQL> create or replace package pkg_b is
2 function b1 return NUMBER;
3 end;
4 create or replace package body pkg_b is
5 function b1 return NUMBER is
6 begin
7 return pkg_a.a1+1;
8 end;
9 end;
10 /
Warning: Package created with compilation errors.
SQL>
SQL> select object_name||" "||object_type
2 from user_objects
3 where status = "INVALID";
OBJECT_NAME||""||OBJECT_TYPE
---------------------------------------
ADDNEWEMPLOYEE PROCEDURE
ADDNEWROW PROCEDURE
ADD_NEW_EMPLOYEE PROCEDURE
ALMOSTFULL FUNCTION
AUTHORS_SEL PROCEDURE
AUTHOR_FIRST_NAME_UPD PROCEDURE
AUTHOR_SEL PROCEDURE
BIGUTIL PACKAGE
BIGUTIL PACKAGE BODY
BIND_TEST PROCEDURE
COMPILE_WARNING PROCEDURE
DELETEEMPLOYEE FUNCTION
DELETEMYEMPLOYEE PROCEDURE
EMPLOYEEDATA PACKAGE
EMPLOYEEINFO FUNCTION
EMPLOYEEOPS PACKAGE
EMPLOYEEOPS PACKAGE BODY
EMPLOYEEPACKAGE PACKAGE
EMPLOYEEPACKAGE PACKAGE BODY
EMPLOYEE_COUNT PROCEDURE
EMPLOYEE_PACKAGE PACKAGE BODY
EMPLOYEE_VIEW VIEW
EMPLOYEE_VIEW_TRIGGER TRIGGER
EMP_CHANGE_S PROCEDURE
EMP_DATA VIEW
EMP_NAME_CHANGE TRIGGER
FULLNAME FUNCTION
F_EMP_DSP FUNCTION
F_GETDIFF FUNCTION
F_GETDNAME FUNCTION
F_GETEMPS FUNCTION
F_MAKEADDRESS FUNCTION
F_VALIDATESALARY FUNCTION
HELLOFLE PROCEDURE
INSERT_NUMBERS PROCEDURE
LOG_SHUTDOWN TRIGGER
LOG_STARTUP TRIGGER
MEASURE_USAGE PROCEDURE
MYPROCEDURE PROCEDURE
NAMED_BLOCK PROCEDURE
NEWYORKEMP VIEW
PKG PACKAGE
PKG_ADIFFERENTUTIL PACKAGE BODY
PKG_B PACKAGE
PKG_B PACKAGE BODY
PKG_EMP PACKAGE
PKG_EMP PACKAGE BODY
PKG_UTIL PACKAGE
PRODUCTTYPE TYPE BODY
PRODUCT_PACKAGE PACKAGE
PRODUCT_PACKAGE PACKAGE BODY
P_PRINTEMPS PROCEDURE
P_UPDATELOC PROCEDURE
P_VALIDATESALARY PROCEDURE
RECORDFULLCLASSES PROCEDURE
TEST_TIME PROCEDURE
UPDATEEMPLOYEE FUNCTION
UPDATEMYEMPLOYEE PROCEDURE
UPDATE_EMP PROCEDURE
V_EMP VIEW
V_EMPLOYEE VIEW
V_EMP_IU TRIGGER
62 rows selected.
SQL>
SQL>
SQL>
Check the status for all stored procedures is by using the Oracle data dictionary view USER_OBJECTS
select object_type, object_name, status
from user_objects
where object_type in ("FUNCTION","PROCEDURE","PACKAGE","PACKAGE BODY","TRIGGER")
order by object_type,object_name;
Check user_objects for object name
SQL>
SQL>
SQL> SELECT object_name, object_type, status
2 FROM user_objects
3 WHERE OBJECT_NAME IN ("TEST_PACK", "CALL_TEST_PROC")
4 and rownum < 50
5 ORDER BY object_name, object_type;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
CALL_TEST_PROC
PROCEDURE VALID
TEST_PACK
PACKAGE VALID
TEST_PACK
PACKAGE BODY VALID
SQL>
Create drop command by query the user_objects table
SQL>
SQL>
SQL>
SQL>
SQL> SELECT "DROP " || object_type || " " || object_name || ";"
2 FROM user_objects
3 WHERE object_type IN ("PACKAGE","PACKAGE BODY","PROCEDURE",
4 "FUNCTION","TRIGGER")
5 and rownum < 50
6 ORDER BY object_type;
DROP FUNCTION CHITIME;
DROP FUNCTION RULERSTR;
DROP FUNCTION TO_NUMBER_OR_NULL;
DROP FUNCTION TRACEIT;
DROP FUNCTION TO_MMSDDSYYYY_OR_NULL;
DROP FUNCTION IS_OVERDUE;
DROP FUNCTION RECEIVE_MESSAGE;
DROP FUNCTION TABCOUNT;
DROP FUNCTION VALUE_IN;
DROP PACKAGE PARAMETERS;
DROP PACKAGE ONECUR;
DROP PACKAGE WORKER_TS;
DROP PACKAGE DATES;
DROP PACKAGE WORKPLACE_TYPE_TS;
DROP PACKAGE WORKER_TYPE_TS;
DROP PACKAGE VALERR;
DROP PACKAGE SCOPES;
DROP PACKAGE CLASSPACKAGE;
DROP PACKAGE GENDER_TS;
DROP PACKAGE BODY WORKER_TYPE_TS;
DROP PACKAGE BODY WORKER_TS;
DROP PACKAGE BODY GENDER_TS;
DROP PACKAGE BODY SCOPES;
DROP PACKAGE BODY PARAMETERS;
DROP PACKAGE BODY WORKPLACE_TYPE_TS;
DROP PACKAGE BODY DATES;
DROP PACKAGE BODY CLASSPACKAGE;
DROP PACKAGE BODY SALESPKG;
DROP PACKAGE BODY VALERR;
DROP PROCEDURE DROPIT;
DROP PROCEDURE FLUSH;
DROP PROCEDURE BULK_TEST;
DROP PROCEDURE CLOSECUR;
DROP PROCEDURE ROW_AT_A_TIME_TEST;
DROP PROCEDURE SAVESTRINGTOFILE;
DROP PROCEDURE PUT_BOOLEAN;
DROP PROCEDURE CREINDX;
DROP PROCEDURE GET_LINE;
DROP PROCEDURE P_RUN_INSERT;
DROP PROCEDURE P1;
DROP PROCEDURE WAIT;
DROP PROCEDURE SEND_MESSAGE;
DROP PROCEDURE PRINTLN;
DROP PROCEDURE DROP_IF_EXISTS;
DROP PROCEDURE SHOWEMPS;
DROP PROCEDURE DELETE_CUST;
DROP PROCEDURE FCREATE;
DROP PROCEDURE RUNDDL;
DROP PROCEDURE PROCA;
49 rows selected.
SQL>
SQL>
Create grant permission command by querying user_objects table
SQL>
SQL>
SQL>
SQL> SELECT "GRANT EXECUTE ON " || object_name || " TO " ||
2 UPPER("&user_role") || ";"
3 FROM user_objects
4 WHERE object_type IN ("PACKAGE","PROCEDURE","FUNCTION")
5 and rownum < 50
6 ORDER BY object_type, object_name;
Enter value for user_role:
old 2: UPPER("&user_role") || ";"
new 2: UPPER("") || ";"
GRANT EXECUTE ON CHITIME TO ;
GRANT EXECUTE ON GRPVAL TO ;
GRANT EXECUTE ON IS_OVERDUE TO ;
GRANT EXECUTE ON RECEIVE_MESSAGE TO ;
GRANT EXECUTE ON RULERSTR TO ;
GRANT EXECUTE ON TABCOUNT TO ;
GRANT EXECUTE ON TO_MMSDDSYYYY_OR_NULL TO ;
GRANT EXECUTE ON TO_NUMBER_OR_NULL TO ;
GRANT EXECUTE ON TRACEIT TO ;
GRANT EXECUTE ON VALUE_IN TO ;
GRANT EXECUTE ON CLASSPACKAGE TO ;
GRANT EXECUTE ON DATES TO ;
GRANT EXECUTE ON GENDER_TS TO ;
GRANT EXECUTE ON MYDATE TO ;
GRANT EXECUTE ON ONECUR TO ;
GRANT EXECUTE ON PARAMETERS TO ;
GRANT EXECUTE ON SALESPKG TO ;
GRANT EXECUTE ON SCOPES TO ;
GRANT EXECUTE ON TIMER TO ;
GRANT EXECUTE ON VALERR TO ;
GRANT EXECUTE ON WORKER_TS TO ;
GRANT EXECUTE ON WORKER_TYPE_TS TO ;
GRANT EXECUTE ON WORKPLACE_TYPE_TS TO ;
GRANT EXECUTE ON BULK_TEST TO ;
GRANT EXECUTE ON CLOSECUR TO ;
GRANT EXECUTE ON CREINDX TO ;
GRANT EXECUTE ON DELETE_CUST TO ;
GRANT EXECUTE ON DROPIT TO ;
GRANT EXECUTE ON DROP_IF_EXISTS TO ;
GRANT EXECUTE ON FCREATE TO ;
GRANT EXECUTE ON FLUSH TO ;
GRANT EXECUTE ON GET_LINE TO ;
GRANT EXECUTE ON P1 TO ;
GRANT EXECUTE ON PRINTLN TO ;
GRANT EXECUTE ON PROC0 TO ;
GRANT EXECUTE ON PROC1 TO ;
GRANT EXECUTE ON PROC2 TO ;
GRANT EXECUTE ON PROC3 TO ;
GRANT EXECUTE ON PROCA TO ;
GRANT EXECUTE ON PROCB TO ;
GRANT EXECUTE ON PUT_BOOLEAN TO ;
GRANT EXECUTE ON P_RUN_INSERT TO ;
GRANT EXECUTE ON ROW_AT_A_TIME_TEST TO ;
GRANT EXECUTE ON RUNDDL TO ;
GRANT EXECUTE ON SAVESTRINGTOFILE TO ;
GRANT EXECUTE ON SEND_MESSAGE TO ;
GRANT EXECUTE ON SHOWEMPS TO ;
GRANT EXECUTE ON SHOW_FILE TO ;
GRANT EXECUTE ON WAIT TO ;
49 rows selected.
Finding, Validating, and Describing Packages
SQL>
SQL> COLUMN object_name FORMAT A10
SQL> SELECT object_name
2 , object_type
3 , last_ddl_time
4 , timestamp
5 , status
6 FROM user_objects
7 WHERE object_name IN ("PIPELINED","PF");
no rows selected
SQL>
Get all invalid user objects in user_objects table
SQL>
SQL>
SQL> SELECT object_type, object_name, status, created, last_ddl_time
2 FROM user_objects
3 WHERE object_type IN ("PROCEDURE", "FUNCTION", "PACKAGE",
4 "PACKAGE BODY", "TRIGGER")
5 AND status = "INVALID"
6 and rownum < 50;
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE DELETE_CUST INVALID 17-JUL-08
24-JUL-08
PROCEDURE WAIT INVALID 16-JUL-08
24-JUL-08
PACKAGE WORKPLACE_TYPE_TS INVALID 16-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY WORKPLACE_TYPE_TS INVALID 16-JUL-08
24-JUL-08
PACKAGE WORKER_TYPE_TS INVALID 16-JUL-08
24-JUL-08
PACKAGE GENDER_TS INVALID 16-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY GENDER_TS INVALID 16-JUL-08
24-JUL-08
PACKAGE WORKER_TS INVALID 16-JUL-08
24-JUL-08
PACKAGE BODY WORKER_TS INVALID 16-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY WORKER_TYPE_TS INVALID 16-JUL-08
24-JUL-08
PROCEDURE P_RUN_INSERT INVALID 17-JUL-08
24-JUL-08
PROCEDURE ROW_AT_A_TIME_TEST INVALID 17-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE BULK_TEST INVALID 17-JUL-08
24-JUL-08
PACKAGE CLASSPACKAGE INVALID 28-JUN-08
24-JUL-08
PACKAGE BODY CLASSPACKAGE INVALID 28-JUN-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE FCREATE INVALID 14-JUL-08
24-JUL-08
PROCEDURE CLOSECUR INVALID 14-JUL-08
24-JUL-08
PROCEDURE SAVESTRINGTOFILE INVALID 14-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE PUT_BOOLEAN INVALID 14-JUL-08
24-JUL-08
PROCEDURE CREINDX INVALID 14-JUL-08
24-JUL-08
PROCEDURE GET_LINE INVALID 14-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION RECEIVE_MESSAGE INVALID 14-JUL-08
24-JUL-08
FUNCTION TRACEIT INVALID 14-JUL-08
24-JUL-08
PROCEDURE SEND_MESSAGE INVALID 14-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE ONECUR INVALID 14-JUL-08
24-JUL-08
PROCEDURE SHOWEMPS INVALID 14-JUL-08
24-JUL-08
PROCEDURE PROCA INVALID 14-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE PROCB INVALID 14-JUL-08
24-JUL-08
PACKAGE BODY MYDATE INVALID 14-JUL-08
24-JUL-08
PROCEDURE SHOW_FILE INVALID 14-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION FOPEN INVALID 14-JUL-08
24-JUL-08
FUNCTION EMP_COUNT INVALID 14-JUL-08
24-JUL-08
PROCEDURE ORG_PROC INVALID 15-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION F_CURSOR_EXP INVALID 15-JUL-08
24-JUL-08
PROCEDURE P_PRINT_REPORT INVALID 15-JUL-08
24-JUL-08
FUNCTION F_CURSOR_EXP_COMPLEX INVALID 15-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION GETALLHIERARCHIES INVALID 15-JUL-08
24-JUL-08
PACKAGE BODY ORGMASTER INVALID 15-JUL-08
24-JUL-08
FUNCTION F_GET_FORMATTED_ORG_NAME INVALID 15-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY RFPKG INVALID 15-JUL-08
24-JUL-08
PACKAGE BODY RFPKG2 INVALID 15-JUL-08
24-JUL-08
PACKAGE NOCOPYPKG INVALID 15-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY NOCOPYPKG INVALID 15-JUL-08
24-JUL-08
PROCEDURE UPDATE_DYN_GLOBAL_BULK2 INVALID 16-JUL-08
24-JUL-08
PACKAGE BODY ORGMASTER2 INVALID 15-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE UPDATE_DYN_GLOBAL_BULK INVALID 16-JUL-08
24-JUL-08
PACKAGE SRPKG2 INVALID 15-JUL-08
24-JUL-08
PACKAGE BODY SRPKG2 INVALID 15-JUL-08
24-JUL-08
OBJECT_TYPE OBJECT_NAME STATUS CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE LOG_ERROR INVALID 15-JUL-08
25-JUL-08
49 rows selected.
SQL>
Get invalid table objects by joining user_segments and user_objects
SQL> column object_name format a30
SQL> column tablespace_name format a30
SQL> column object_type format a12
SQL> column status format a1
SQL> column status format a10
SQL> break on object_type skip 1
SQL>
SQL> select object_type, object_name,
2 decode(status,"INVALID","*","") status,
3 tablespace_name
4 from user_objects a, user_segments b
5 where a.object_name = b.segment_name (+)
6 and a.object_type = b.segment_type (+)
7 and rownum < 50
8 order by object_type, object_name
9 /
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
FUNCTION COMPUTE_DISCOUNTS
DELETEEMPLOYEE *
EXITFUNC
F_GETDIFF *
GETAREA
INDEX BIN$fKX+qNArQt+8GLZvNQJlrQ==$0 SYSTEM
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PACKAGE PKG *
PKG_TEST1
PACKAGE BODY PKG_TEST1
PROCEDURE ADD_NEW_EMPLOYEE *
clear columns;
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PROCEDURE AUTHORS_SEL *
AUTHOR_FIRST_NAME_UPD *
AUTHOR_SEL *
BIND_TEST *
CHANGEPRICE
COMPILE_WARNING *
DELETEMYEMPLOYEE *
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PROCEDURE EMPLOYEE_COUNT *
EMP_CHANGE_S *
HELLO
HELLOFLE *
HIKE_PRICES
INSERT_NUMBERS *
MEASURE_USAGE *
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PROCEDURE MULTIPLE_CURSORS_PROC
MYPROCEDURE *
MY_FIRST_PROC
NAMED_BLOCK *
P
P_HELLO
P_HELLOTO
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
TABLE BIN$dgfyftgCSZSBnu0LqVJzrQ==$0 USERS
BIN$zbJqSAxnQA2up7MHjGauHw==$0 USERS
EMPLOYEE_TAB
EMP_DEPT SYSTEM
PEOPLE SYSTEM
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
TRIGGER EMP_NAME_CHANGE *
LOG_SHUTDOWN *
LOG_STARTUP *
TYPE ADDRESSTYPE
ADDRESS_OBJ
AOBJ
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
TYPE EMPLOYEETYPE
PERSONTYPE
PRODUCTTYPE
TYPE BODY AOBJ
EMPLOYEETYPE
OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
VIEW EMP_DATA *
NEWYORKEMP *
49 rows selected.
SQL>
Get Object id from user_objects
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
Type created.
SQL>
SQL> create or replace type emp_tab_type as table of emp_type
2 /
Type created.
SQL>
SQL> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
Table created.
SQL>
SQL> alter table emps_nt add constraint emps_empno_unique unique(empno)
2 /
Table altered.
SQL> alter table emps_nt add constraint mgr_fk foreign key(mgr) references emps_nt(empno);
alter table emps_nt add constraint mgr_fk foreign key(mgr) references emps_nt(empno)
*
ERROR at line 1:
ORA-30730: referential constraint not allowed on nested table column
SQL>
SQL> insert into dept_and_emp
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm
4 from emp
5 where emp.deptno = dept.deptno ) AS emp_tab_type )
6 from dept
7 /
4 rows created.
SQL>
SQL>
SQL>
SQL>
SQL> select name
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = "DEPT_AND_EMP" )
6 /
NAME
------------------------------
DEPTNO
DNAME
EMPS
LOC
SYS_NC0000400005$
SQL>
SQL>
SQL>
SQL>
SQL> drop table dept cascade constraint;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
SQL>
SQL>
If procedure is valid
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> create or replace procedure P1 is
2 v_cnt number;
3 begin
4 select count(*) into v_cnt from emp where empno > 0;
5 end;
6 /
SQL>
SQL> select object_name, status from user_objects where object_name in ("P1","P2","P3","P4");
Enter...
P1
VALID
P1
INVALID
P2
INVALID
P2
INVALID
P3
INVALID
P4
INVALID
6 rows selected.
SQL>
SQL> select object_name, status from user_objects where object_name in ("P1","P2","P3","P4");
Enter...
P1
VALID
P1
INVALID
P2
INVALID
P2
INVALID
P3
INVALID
P4
INVALID
6 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
Join sys.col$ and user_objects
SQL>
SQL> create or replace type address_type as object
2 ( city varchar2(30),
3 street varchar2(30),
4 state varchar2(2),
5 zip number
6 )
7 /
SQL>
SQL> create or replace type person_type as object
2 ( name varchar2(30),
3 dob date,
4 home_address address_type,
5 work_address address_type
6 )
7 /
Type created.
SQL> create table people of person_type
2 /
Table created.
SQL> desc people
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
SQL>
SQL>
SQL> select * from people;
no rows selected
SQL>
SQL> select name, p.home_address.city from people p;
no rows selected
SQL>
SQL> select name, segcollength from sys.col$
2 where obj# = ( select object_id from user_objects where object_name = "PEOPLE" )
3 /
NAME SEGCOLLENGTH
------------------------------ ------------
SYS_NC_OID$ 16
SYS_NC_ROWINFO$ 1
NAME 30
DOB 7
HOME_ADDRESS 1
SYS_NC00006$ 30
SYS_NC00007$ 30
SYS_NC00008$ 2
SYS_NC00009$ 22
WORK_ADDRESS 1
SYS_NC00011$ 30
SYS_NC00012$ 30
SYS_NC00013$ 2
SYS_NC00014$ 22
14 rows selected.
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>
Query user_objects for all java classes
SQL>
SQL> SELECT object_name shortname,
2 DBMS_JAVA.LONGNAME (object_name) longname
3 FROM USER_OBJECTS
4 WHERE object_type = "JAVA CLASS"
5 AND object_name != DBMS_JAVA.LONGNAME (object_name);
AND object_name != DBMS_JAVA.LONGNAME (object_name)
*
ERROR at line 5:
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
SQL>
SQL>
SQL>
Query user_objects for all packages, procedures and functions
SQL>
SQL>
SQL>
SQL>
SQL> SELECT "CREATE PUBLIC SYNONYM " || object_name || " FOR " ||
2 object_name || ";"
3 FROM user_objects
4 WHERE object_type IN ("PACKAGE","PROCEDURE","FUNCTION")
5 and rownum < 50
6 ORDER BY object_type, object_name;
CREATE PUBLIC SYNONYM CHITIME FOR CHITIME;
CREATE PUBLIC SYNONYM GRPVAL FOR GRPVAL;
CREATE PUBLIC SYNONYM IS_OVERDUE FOR IS_OVERDUE;
CREATE PUBLIC SYNONYM RECEIVE_MESSAGE FOR RECEIVE_MESSAGE;
CREATE PUBLIC SYNONYM RULERSTR FOR RULERSTR;
CREATE PUBLIC SYNONYM TABCOUNT FOR TABCOUNT;
CREATE PUBLIC SYNONYM TO_MMSDDSYYYY_OR_NULL FOR TO_MMSDDSYYYY_OR_NULL;
CREATE PUBLIC SYNONYM TO_NUMBER_OR_NULL FOR TO_NUMBER_OR_NULL;
CREATE PUBLIC SYNONYM TRACEIT FOR TRACEIT;
CREATE PUBLIC SYNONYM VALUE_IN FOR VALUE_IN;
CREATE PUBLIC SYNONYM CLASSPACKAGE FOR CLASSPACKAGE;
CREATE PUBLIC SYNONYM DATES FOR DATES;
CREATE PUBLIC SYNONYM GENDER_TS FOR GENDER_TS;
CREATE PUBLIC SYNONYM MYDATE FOR MYDATE;
CREATE PUBLIC SYNONYM ONECUR FOR ONECUR;
CREATE PUBLIC SYNONYM PARAMETERS FOR PARAMETERS;
CREATE PUBLIC SYNONYM SALESPKG FOR SALESPKG;
CREATE PUBLIC SYNONYM SCOPES FOR SCOPES;
CREATE PUBLIC SYNONYM TIMER FOR TIMER;
CREATE PUBLIC SYNONYM VALERR FOR VALERR;
CREATE PUBLIC SYNONYM WORKER_TS FOR WORKER_TS;
CREATE PUBLIC SYNONYM WORKER_TYPE_TS FOR WORKER_TYPE_TS;
CREATE PUBLIC SYNONYM WORKPLACE_TYPE_TS FOR WORKPLACE_TYPE_TS;
CREATE PUBLIC SYNONYM BULK_TEST FOR BULK_TEST;
CREATE PUBLIC SYNONYM CLOSECUR FOR CLOSECUR;
CREATE PUBLIC SYNONYM CREINDX FOR CREINDX;
CREATE PUBLIC SYNONYM DELETE_CUST FOR DELETE_CUST;
CREATE PUBLIC SYNONYM DROPIT FOR DROPIT;
CREATE PUBLIC SYNONYM DROP_IF_EXISTS FOR DROP_IF_EXISTS;
CREATE PUBLIC SYNONYM FCREATE FOR FCREATE;
CREATE PUBLIC SYNONYM FLUSH FOR FLUSH;
CREATE PUBLIC SYNONYM GET_LINE FOR GET_LINE;
CREATE PUBLIC SYNONYM P1 FOR P1;
CREATE PUBLIC SYNONYM PRINTLN FOR PRINTLN;
CREATE PUBLIC SYNONYM PROC0 FOR PROC0;
CREATE PUBLIC SYNONYM PROC1 FOR PROC1;
CREATE PUBLIC SYNONYM PROC2 FOR PROC2;
CREATE PUBLIC SYNONYM PROC3 FOR PROC3;
CREATE PUBLIC SYNONYM PROCA FOR PROCA;
CREATE PUBLIC SYNONYM PROCB FOR PROCB;
CREATE PUBLIC SYNONYM PUT_BOOLEAN FOR PUT_BOOLEAN;
CREATE PUBLIC SYNONYM P_RUN_INSERT FOR P_RUN_INSERT;
CREATE PUBLIC SYNONYM ROW_AT_A_TIME_TEST FOR ROW_AT_A_TIME_TEST;
CREATE PUBLIC SYNONYM RUNDDL FOR RUNDDL;
CREATE PUBLIC SYNONYM SAVESTRINGTOFILE FOR SAVESTRINGTOFILE;
CREATE PUBLIC SYNONYM SEND_MESSAGE FOR SEND_MESSAGE;
CREATE PUBLIC SYNONYM SHOWEMPS FOR SHOWEMPS;
CREATE PUBLIC SYNONYM SHOW_FILE FOR SHOW_FILE;
CREATE PUBLIC SYNONYM WAIT FOR WAIT;
49 rows selected.
SQL>
Query user_objects for all PL/SQL code
SQL>
SQL> SET PAGESIZE 66
SQL> COLUMN object_type FORMAT A20
SQL> COLUMN object_name FORMAT A30
SQL> COLUMN status FORMAT A10
SQL> BREAK ON object_type SKIP 1
SQL>
SQL> SELECT object_type, object_name, status
2 FROM user_objects
3 WHERE object_type IN ("PACKAGE",
4 "PACKAGE BODY",
5 "FUNCTION",
6 "PROCEDURE",
7 "TYPE",
8 "TYPE BODY",
9 "TRIGGER"
10 )
11 and rownum < 50
12 ORDER BY object_type, status, object_name
13 /
OBJECT_TYPE OBJECT_NAME STATUS
-------------------- ------------------------------ ----------
FUNCTION ACCOUNT_BALANCE INVALID
AUTHORIZED_EMPS INVALID
BIZDAYS_BETWEEN INVALID
BOOKTITLE INVALID
ADD_TWO VALID
BETWEENSTRING VALID
BETWNSTR VALID
BIZDAYS_BETWN VALID
PACKAGE AA_TYPES INVALID
ANYNUMS_PKG INVALID
BIDIR INVALID
BOOK_INFO INVALID
AUDIT_TRAIL_PKG VALID
AUTHORS_PKG VALID
BT VALID
PACKAGE BODY ANYNUMS_PKG INVALID
AUDIT_TRAIL_PKG INVALID
BIDIR INVALID
BOOK_INFO INVALID
AUTHORS_PKG VALID
BT VALID
PROCEDURE A INVALID
ADJUST_SALARY INVALID
APPLY_BALANCE INVALID
AUDIT_CEO_COMP INVALID
AUTONFAIL INVALID
AUTONOMOUS_INSERT INVALID
AUTO_PROC INVALID
AUTO_PROC1 INVALID
AUTO_PROC2 INVALID
B INVALID
BACKGROUND_ALERT INVALID
BACKGROUND_SEND_MAIL INVALID
BIND INVALID
BULK_EXCEPTIONS INVALID
BULK_TEST INVALID
AA VALID
ADD_UP VALID
ANALYZE_MY_TABLES VALID
ASSIGN VALID
ASSIGNDT VALID
BB VALID
BUILD_SCHEDULE VALID
CALCULATE_TOTALS VALID
TRIGGER APPLICATION_USERS_IOI INVALID
TYPE ASCII_TABLE_TYPE INVALID
ADDRESS2 VALID
ADD_LIST VALID
TYPE BODY ADDRESS2 VALID
49 rows selected.
SQL>
SQL>
Query user_objects for object status
SQL>
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace view v as select * from t;
View created.
SQL>
SQL> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t )
5 loop
6 null;
7 end loop;
8 end;
9 /
SQL>
SQL> create or replace function f return number
2 as
3 countValue number;
4 begin
5 select count(*) into countValue from t;
6 return countValue;
7 end;
8 /
Function created.
SQL>
SQL>
SQL> alter table t add y number
2 /
Table altered.
SQL>
SQL> select object_name, object_type, status from user_objects
2 where rownum < 10
3 /
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
MYSTATS
INDEX VALID
MYSTATS
TABLE VALID
S
SEQUENCE VALID
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
P_ADD_ITEMS
PROCEDURE INVALID
WORKING_CUSTOMERS
TABLE VALID
ADDTUPLE3
PROCEDURE INVALID
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
P_ADD_ORDERS
PROCEDURE INVALID
P_ADD_PROD
PROCEDURE INVALID
UPDATE_PRODUCT_PRICE
PROCEDURE INVALID
9 rows selected.
SQL>
SQL> drop table t;
Table dropped.
Query user_objects for user-defined data type
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20),
3 line2 VARCHAR2(20),
4 city VARCHAR2(20),
5 state_code VARCHAR2(2),
6 zip VARCHAR2(13),
7 MEMBER FUNCTION get_address RETURN VARCHAR2,
8 MEMBER PROCEDURE set_address
9 (addressLine1 VARCHAR2,
10 addressLine2 VARCHAR2,
11 address_city VARCHAR2,
12 address_state VARCHAR2,
13 address_zip VARCHAR2)
14 );
15 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER FUNCTION get_address RETURN VARCHAR2
3 IS
4 BEGIN
5 RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
6 END get_address;
7 MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
8 addressLine2 VARCHAR2,
9 address_city VARCHAR2,
10 address_state VARCHAR2,
11 address_zip VARCHAR2)
12 IS
13 BEGIN
14 line1 :=addressLine1;
15 line2 :=addressLine2;
16 city :=address_city;
17 state_code :=address_state;
18 zip :=address_zip;
19 END set_address;
20 END;
21 /
Type body created.
SQL>
SQL>
SQL> column object_name format a30;
SQL>
SQL> select object_name,object_type from user_objects where object_name like "ADDRESS%FOR%";
no rows selected
SQL>
SQL>
SQL>
Query user_objects for your functions and procedures
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace view v as select * from t;
View created.
SQL>
SQL> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t )
5 loop
6 null;
7 end loop;
8 end;
9 /
SQL>
SQL> create or replace function f return number
2 as
3 countValue number;
4 begin
5 select count(*) into countValue from t;
6 return countValue;
7 end;
8 /
Function created.
SQL>
SQL>
SQL> select object_name, object_type, status from user_objects
2 where rownum < 10
3 /
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
MYSTATS
INDEX VALID
MYSTATS
TABLE VALID
S
SEQUENCE VALID
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
P_ADD_ITEMS
PROCEDURE INVALID
WORKING_CUSTOMERS
TABLE VALID
ADDTUPLE3
PROCEDURE INVALID
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
P_ADD_ORDERS
PROCEDURE INVALID
P_ADD_PROD
PROCEDURE INVALID
UPDATE_PRODUCT_PRICE
PROCEDURE INVALID
9 rows selected.
SQL>
SQL>
SQL> drop table t;
Table dropped.
Recompile all invalid package
SQL>
SQL>
SQL> declare
2 cursor myCursor is select object_type, object_name from SYS.USER_OBJECTS where status = "INVALID"
3 order by 1, 2;
4 sqlString varchar2(100);
5 begin
6 for r1 in myCursor loop
7 begin
8 if r1.object_type = "PACKAGE BODY" then
9
10 sqlString := "alter PACKAGE "||r1.object_name||" compile BODY";
11
12 elsif r1.object_type = "TYPE BODY" then
13
14 sqlString := "alter TYPE "||r1.object_name||" compile BODY";
15
16 else
17
18 sqlString := "alter "||r1.object_type||" "||r1.object_name||" compile";
19
20 end if;
21
22 execute immediate sqlString;
23
24 dbms_output.put_line(r1.object_type||" "||r1.object_name||" compiled successfully");
25
26 exception
27
28 when OTHERS then
29
30 dbms_output.put_line(SQLERRM||" on "||sqlString);
31
32 end;
33
34 end loop;
35
36 end;
37 /
PL/SQL procedure successfully completed.
SQL>
Show the procedure is marked invalid **
SQL>
SQL> COL object_name FORMAT A15
SQL> COL status FORMAT A10
SQL>
SQL> SELECT object_name, status FROM user_objects WHERE object_name = "COMPILEERROR";
OBJECT_NAME STATUS
--------------- ----------
COMPILEERROR VALID
1 row selected.
User and its average object id
SQL>
SQL> select a.username, (select count(*) from all_objects b where b.owner = a.username) cnt,
2 (select avg(object_id ) from all_objects b where b.owner = a.username) avg
3 from all_users a
4 /
USERNAME CNT AVG
------------------------------ ---------- ----------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME CNT AVG
------------------------------ ---------- ----------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
sqle 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME CNT AVG
------------------------------ ---------- ----------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>