2. How to Use SQL and PL/SQL in Oracle ----------------------------------- - Server side processing. SQL*Plus basics. Programming in SQL*Plus. Dynamic generation of temporary SQL*Plus routines. Tips and techniques. !!! IMPORTANT NOTE: Oracle sometimes fetches rows from rollback segment even when the table is modified by the same transaction!!! Example 1 (SQL script): create table TEST (N number(2), C char(10)); create table TEMP (N number(2), C char(10)); declare cursor T is select N, C from TEST; I number(2); VC char(10); VN number(2); begin -- Insert 2 rows into table TEST insert into TEST values (1,'A'); insert into TEST values (2,'B'); commit; -- Open cursor. Begin of the next transaction Open T; -- Parse, execute, bind and describe phases - are performed ONLY ONES for I in 1..2 loop fetch T into VN, VC; -- 2 fetches - one execute if VN = 1 then -- When the 1-st row fetched, update -- the second row update TEST set C = 'BB' where N = 2; end if; insert into temp values (VN, VC); end loop; close T; commit; -- end of transaction end; / spool t.tmp prompt Table TEST select * from TEST; prompt Table TEMP select * from TEMP; drop table TEST; drop table TEMP; exit; Content of table T after the script executed N C ------- ---------- 1 A 2 B The second row has been taken from a rollback segment. Example 2: All the rows are taken from the data file. create table TEST (N number(2), C char(10)); create table TEMP (N number(2), C char(10)); declare I number(2); VC char(10); VN number(2); begin insert into TEST values (1,'A'); insert into TEST values (2,'B'); commit; for I in 1..2 loop -- the following statement performs parse, execute, -- bind, describe and fetch as a BUNDLE -- (one execute - one fetch scheme): select N, C into VN, VC from test where N = I; if VN = 1 then update TEST set C = 'BB' where N = 2; end if; insert into temp values (VN, VC); end loop; commit; end; / spool t.tmp prompt Table TEST select * from TEST; prompt Table TEMP select * from TEMP; drop table test; drop table temp; exit Content of table T after the script executed N C ------- ---------- 1 A 2 BB The second row has been taken from the table's segment. !!! END OF IMPORTANT NOTE - Server side PL/SQL blocks, procedures, functions and packages. Calling RDBMS procedures from various tools. Debugging PL/SQL code. Oracle written standard routines library. - Client side PL/SQL in SQL*Forms/Menu and CDE tools. Calling server procedures form client side PL/SQL routines. - Tuning of applications. SQL statement tuning is normally performed by application developers and includes: - Data Design - Oracle optimizer (see below) - Indexes and Hashing - Row-level locking - Sequences - Clusters - Array processing - Stored procedures - Database triggers (are not stored in P-code in database. (Parsing and compiling REQUIRED untill Oracle 7.3. You should call DB procedure(s) from trigger body.) - Declarative integrity constraints Example: alter table add constaint foreign key (Key_Name) references (.); - Discrete transactions [STUN72, 4-8 - 4-18] - Parallel query facilities ([PH95 p#160v2], [STUN72,chapter 6]) - SQL statements processing principles [STUN72, p.5-2, 5-8...]: - Types of SQL Statements: - simple statements - INSERT, UPDATE, DELETE, SELECT with single table - simple queries - joins - equijoins - nonequijoins - anti-joins (not in) - outer joins (only one (+) sign in join predicate allowed) - cartesian products - a join with no join conditions - complex statement - INSERT, UPDATE, DELETE and SELECT statements containing subqueries - compound queries - UNION, UNION ALL, INTERSECT, MINUS - statements accessing views - all above with views - distributed statements - evaluation of expressions and conditions ([STUN72, p.5-10-5-13] optimizer tries to evaluate expressions and conditions containing CONSTRAINTS and fully as possible. - LIKE - IN - ANY (or SOME) where Sal > ANY (:first_sal, :second_sal) <==> where Sal > :first_sal OR :second_sal) - ALL where Sal > ALL (:first_sal, :second_sal) <==> where Sal > :first_sal AND :second_sal) - BETWEEN - Transitivity From: ... from EMP, DEPT where EMP.Deptno = 20 and EMP.Edptno = DET.Deptno optimizer infers: ... DEPT.Deptno = 20 - Transforming [STUN72, pp.5-15 - 5-18]: - ORs into UNION ALL - complex statements (correlated subqueries) into equivalent join statement - Optimizing statements that access views [STUN72, pp. 5-18-5-26] view merging - optimizer often merges the query in the statement with that view and then optimizes the result ([STUN72, p.5-18, 5-19]. - optimization approaches - cost-based and rule-based - In rule-based optimization, the optimizer chooses an execution plan based on a ranking (see 15 rules above) of the operations involved. In fact, it is syntax-based one - In cost-based optimization, the optimizer uses statistics collected on the accessed tables, clusters and indexes to chose the most efficient execution plan. Oracle can not collect statistics automatically. DBA or user should periodically issue ANALYZE commands for each table, cluster and index. It may take substantial time for large tables. - To switch between optimizer modes on instance level DBA should use OPTIMIZER_MODE = { CHOOSE | RULE | COST | ALL_ROWS | FIRST_ROWS} INIT.ORA parameter. Default - COST. If there are no statistics available then RULE is used. User can switch RULE/COST at the session level with ALTER SESSION SET OPTIMIZER_GOAL = { }. - choice of access paths [STUN72, p.5-28 - 5-56]: - choice of join orders - if more then two tables take a part in joins optimizer chooses which pair of tables is joined first. - choice of join operations - for any join optimizer chooses the operation to perform join - Access methods (the basic methods by which Oracle can access data) [STUN72, p.5-28]: - Full Table Scan - Table access by Rowid - Cluster Scans - Hash Scans - Index Scans Set of Oracle's standard access paths: 1. Single row by rowid 2. Single row by cluster join 3. Single row by hash cluster key with unique or primary key 5. Cluster join 6. Hash cluster join 7. Indexed cluster join 8. Composite key 9. Single-column indexes 10. Bounded range scan on indexed columns 11. Unbounded range scan on indexed columns 12. Sort-marge join 13. MAX or MIN of indexed columns 14. ORDER BY on indexed columns 15. Full table scan - Tuning SQL statements [STUN72, chapter 7]] - How to use indexes [STUN72, 7-2 - 7-7] - Not more then 25% of table's row. - Index on parent key allows Oracle to modify data in the child table WITHOUT locking the parent table [STUN72,7-4] - Till Oracle v7.1 the number of occurrences of each distinct column is NOT available to the optimizer which supposes UNIFORM column distribution. In 7.3 histograms are available to the optimizer. - How to select candidates columns for indexing [COR95]: - Low selectivity (percent of rows that have the same column's value). - The only candidates for indexing are columns that are mentioned in WHERE and AND select's clause. - Index is NOT USED in functions (Floor, To_Number, ...) - Composite index will ONLY be used to satisfy a query when the leftmost column is mentioned in WHERE or AND. - Oracle provides two SQL*Plus scripts to assess the CANDIDATE columns for indexing: @OTLOIDXS.sql @OTLSIDXS.sql These scripts must be run one after another and they create several tables (their names begin with INDEX$). These scripts provide output on the screen (if you want to direct output to the file use pipes (>) or insert "Spool " and "spool off" commands in the second script body). Th output contains useful information on underlining column to be concerned as a candidate for an index: - Various statistics (number of rows in the table, average number of rows per key, max and min number of rows per key, total distinct keys) - BADNESS - number of equal values in this column per key value (selectivity). - KEY_COUNT - the number of keys with the same BADNESS - ROW_PERCENT - percentage of rows with the same key value in this column - KEY_PERCENT - percentage of column values with the same key value.- If BADNESS is low then this column is a good candidate for an index. - How to use clusters [STUN72, 7-7 - 7-8] - Do NOT USE cluster if the data from all tables with the same cluster key exceeds MORE THEN ONE or TWO Oracle blocks because Oracle reads ALL the blocks containing rows with the same cluster. - How to use hashing [STUN72, 7-8 - 7-9] - How to use choose optimization approach [STUN72, 7-10 - 7-13]. - How to initiate SQL_TRACE: USER_DUMP_DESTINATION = INIT.ORA TIMED_STATISTICS = TRUE parameter + INIT.ORA SQL_TRACE = TRUE or ALTER SESSION set SQL_TRACE=TRUE or package DBMS_SESSION (e.g. dbms_session.set_sql_trace(TRUE/FALSE) dbms_session.set_sql_trace_in_session(sid,serial#, TRUE/FALSE) For UNIX platform INIT.ORA parameter: _TRACE_FILES_PUBLIC = TRUE - gives PUBLIC READ PERMISSION on trace file. -How to find SQL-trace filename: >>> Begin script create or replace function SQL_Trace_Filename (SessionId in integer) return varchar2 as -- -- Note: -- -- Session Id of the currenr parameter can be obtained by -- mean of USERENV('SESSIONID') function -- -- You must have explicit grant select on V$PARAMETER and -- V$SESSION or explicitly granted SELECT ANY TABLE privilege -- User_dump_dest varchar2(100); Fnum varchar2(6); begin -- Get USER_DUMP_DEST select Value into User_dump_dest from V$PARAMETER where Name = 'user_dump_dest'; -- For Win NT filename has a sintax: ORAnnnnn.TRC -- where nnnnn - zero left padded Spid in decimal select Spid into Fnum from V$PROCESS where Addr in (select Paddr from V$SESSION where Audsid = SessionId); -- For Win NT and Win 95 filename is 8 char and num -- must be leftpadded to 5 chars. return User_dump_dest || '\ORA' || lpad (to_char(hex2dec(Fnum)),5,'0') || '.TRC'; -- For UNIX platforms (REMOVE COMMENTS): -- return User_dump_dest || '/ORA_' -- || -- || '.trc'; end; / >>> End of script - How to interpret EXPLAIN PLAN output Common execution steps: AND-EQUALS - Index Merge: two or more indexes are userd to obtain rows from a SINGLE table. A list of matching rows is objtained from each index, and rows in all lists are returned. This step sometimes indicates the abcence of appropriate CONCATENETED index and can be an inefficient operation. INDEX UNIQUE SCAN - Get a single unique value from an index. This is usually high efficient. INDEX RANGE SCAN - Get one or more matching rows from an index. The efficiency of this step depends on ho wmany rows need to be retrieved. Some of these rows may need to be elimineted during a later table access. MERGE JOIN - Two tables (or result set) are sorted and the sorted rows merged. This step sometimes indicates that a join is being performed in the ABSENCE of an appropriate index. NESTAED LOOP - For each row in the first table or result set, a corresponding row is fetched from the second table or result set, USUALLY via an index operation. HASH JOIN - hash join works by creating of a hash table for one of the tables in the join. This hash table is used as and on-the-fly index to speed the join. HAS JOIN ANTI/ - This method allows merge join or hash MERGE JOIN ANTI join techniques to be applied to NOT IN subquery SORT ORDER BY/ - A result set is sorted to satisfy SORT GROUP BY either ORDER BY or GROUP BY clause. Although a sort like this is often unavoidable, you can sometimes dispanse with sort by using an appropriate index. TABLE ACCESS FULL - A full table scan. Avoid these except for very small tables (less then DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter or for processing more then 10-25 percent of table rows. TABLE ACCESS BY ROWID - This step can be seen either where the WHERE CURRENT OF CURSOR construct is used or where an previous INDEX operation has occured. - How to get trace statistics from SQL*Plus v >= 3.2 SET AUTOTRACE {OFF | ON | EXPLAIN | STATISTICS | TRACEONLY} - How to use hints. Hints can be user with COST-based optimizer only for: - the optimization approach (rule/cost) for a SQL statement. - the goal of the cost-based optimizer (first_rows/all_rows - the access path for a table accessed by the statement - the join order for a join statement - a join operation in a join statement - Hints apply only to the optimization of the STATEMENT BLOCK in which they appear [STUN72, p.7-13]. The STATEMENT BLOCK may be one of the following: - a simple SELECT, UPDATE, or DELETE statement - a PARENT statement or SUBQUERY of a complex statement - a part of a COMPOUND (union, intersect, minus) query - The hint have no effect if the subquery is applied to a REMOTE table or one that is joined using a MERGE join If a query or an subquery does not have WHERE clause yu can not use hints to forse Oracle to use indexes in the excution plan. - Syntax: -----<------ | | ______DELETE______ /*+ __ hint __v_ Text____|__-__*/ |____SELECT |____UPDATE or -----<------ | | ______DELETE______ --+ __ hint __v_ Text____|_______ |___SELECT |___UPDATE - Oracle IGNORES hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keywords - Oracle IGNORES hints containing syntax errors, but considers other correctly specified hints within the SAME comment - Oracle IGNORES combination of the conflicting hints. - Oracle IGNORES hints in all SQL statements in environments that uses PL/SQL Version 1, such as SQL*Forms Version 3 triggers. - Hints syntax and usage [STUN72, 7-15 - 7-29]: - Hints for optimization approach and goals (see also OPTIMIZER_GOAL session parameter and OPTIMIZR_MODE INIT.ORA parameter. Important note: --------------- If data dictionary contains no statistics when optimizer uses cost-based approach, optimizer chooses ALL_ROWS! - RULE hint - rule based optimizer is used for this statement. ALL OTHER HINTS ARE IGNORED. - CHOOSE hint - optimizer chooses between the rule-based and cost-based approaches based on the presence of the statistics for the tables accessed by the statement. - ALL_ROWS hint - minimal total resource consumption - sort/merge join is preferable - FIRST_ROWS hint - minimal total resource consumption. - nested loop join is preferable - if an index scan is made available by an ORDER BY clause, it may be chosen to avoid sort operation. - the optimizer IGNORES this hint in DELETE and UPDATE statement blocks and in SELECT containing any of the following: - set operators (UNION [ALL], INTERSECT, MINUS) - GROUP BY - FOR UPDATE (!) - group functions (count, min, max ...) - DISTINCT operator - Hints for access methods - Important notes: --------------- - Optimizer chooses the given access path only if the path is available (otherwise the path ignored) - If the statement uses an alias for the table (e.g. FROM EMP E, DEPT D), you MUST USE THE ALIASES (E and D in this case), rather then table name, in the hint. - Schema names (e.g. SCOTT.EMP) CAN NOT BE USED in the hints, ONLY table names or aliases are acceptable!. - The name or alias MUST represent a table or a synonym for a table on your LOCAL database. View name is not acceptable. - FULL hint - explicitly chooses a full table scan Syntax: FULL(table) Example: select /*+ FULL(A) Don't use index */ from ACCOUNTS A where Account_no = 7086854; - ROWID hint - explicitly chooses a scan by ROWID for the specified table. Syntax: ROWID(table) - CLUSTER hint - explicitly chooses a cluster scan to access a specified table. Syntax: CLUSTER(table) Example: select /*+ CLUSTER(EMP) */ ???? Check ???? from EMP, DEPT where Dept_no = 10 and EMP.Dept_no = DEPT.Deptno; - HASH hint - explicitly chooses a hash scan to access a specified table. Syntax: HASH(table) - INDEX hint - explicitly chooses an index scan to access a specified table. Syntax: >___INDEX(table _____________________)_> | | ^ v |___index ____| - If this hint specifies a single available index, the optimizer performs a scan on the index. The optimizer does NOT consider a full table scan or a scan on another index on the table. - If this hint specifies A LIST of available indexes, the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer may also choose to scan multiple indexes from the list and MERGE the results, if such an access path has the lowest cost. The optimizer does NOT consider a full table scan or a scan on an index not listed in the hint. - If this hint specified NO INDEXES, the optimizer considers the cost of a scan on EACH AVAILABLE INDEX on the table and then performs the index scan with the lower cost. The optimizer may also choose to scan multiple indexes and MERGE the results, if such an access path has the lowest cost. - INDEX_ASC hint - explicitly chooses an index scan to for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. Syntax: >___INDEX_ASC(table _____________________)__ | | ^ v |___index ____| - INDEX_DESC hint - explicitly chooses an index scan to for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. Syntax: >___INDEX_DESC(table ___________________)__ | | v ^ |___index ____| Interesting example: Index presence and ordering can be explicitely used in query semantic! (But it is not a good practice...) create table TANK_READINGS (Time date constraint UN_TIME unique, Temperature number); Case I: Select the most recent temperature reading: select Temperature from TANK_READINGS where Time = (select max(Time) from TANK_READINGS where Time <= to_date(:t)); Case II: (the SAME as case I, but much more faster): select /*+ INDEX_DESC(TANK_READINGS UN_TIME) */ Temperature from TANK_READINGS where Time <= to_date(:t) and Rownum = 1 order by Time DESC; see also [STUN72, p.7-21 - 7-24] - AND_EQUAL hint - explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. Syntax: _>_AND_EQUALC(table___index ___index _____> >__________________________________________)_>< | | | | | | | | | | | | |__index _| |__index _| |__index _| Up to 5 indexes are allowed. You should specify minimum 2 indexes. - USE_CONCAT hint - forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation incurs only if the cost of the query using the concatenations is cheaper than the cost without them. Syntax: USE_CONCAT - Hints for join orders To join two row sources, Oracle must perform one of these operations: - Nested loop join: 1. Choosing OUTER (or DRIVING) table. Other table is called INNER (or DRIVEN) table. 2. For each row in the outer table, Oracle finds (via index or full table scan) all rows in the inner table that satisfy the join condition. 3. Oracle combines the data in each pair of rows that satisfy the join condition and returns the resulting row. - Sort-merge join: 1. Oracle sorts each row source to be joined if they have not been sorted already by a previous operation. The rows are sorted on the values of the columns used in the join condition. 2. Oracle merges the two sources so that each pair of rows are combined and returned as the resulting row source. Oracle can only perform a sort-merge join for an equijoins. - Cluster join: Only for equijoins. Equates cluster key columns of two tables in the same cluster. - ORDERED hint - causes oracle to join tables in the order in which they appear in the FROM clause (from LEFT table which is considered to be the outer table to RIGHT one considering inner one). You may use ORDERED to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. So you can choose the inner and outer table better then optimizer could. Syntax: ORDERED - USE_NL hint - causes Oracle to join each specified table to another row source with a nested loop join using the specified table as the INNER (driven) table. -----<------- | | Syntax: >___USE_NL(___|__table ___|_________)__>< - USE_MERGE hint - causes Oracle to join each specified table to another row source with a sort-merge join using the specified table as the INNER (driven) table. -----<------- | | Syntax: >___USE_MERGE(___|__table ___|_________)__>< - Hints for parallel query executions - PARALLEL hint - specify desired number of concurrent query servers that can be used for the query. Syntax: >>__ PARALLEL (table ______________________________)_>< | ,integer| | ,integer| | ,default| | ,default| | , | ----------- ----------- The first value is the degree of parallelism for a given table. The second value specifies how the table to be split among the instances of a Parallel Server. 'Default' supposes applying initialization parameters. Example: select /*+ FULL(SCOTT_EMP) PARALLEL(SCOTT_EMP,5) */ from SCOTT.EMP SCOTT_EMP; - NOPARALLEL hint - disabling parallel scanning Syntax: >>__ NOPARALLEL ___(____table ___)_____________>< /* NOPARALLEL (scott_emp) */ is equivalent to /* PARALLEL (scott_emp,1,1) */ - CACHE hint spcifies that the blocks retrieved from the table in the hint are palced at the least recently used end of the LRU list in the buffer cache when full table scan is performed. Table may not be more then CACHE_SIZE_THRESHOLD. - NOCACHE - disable caching the table - PUSH_SUBQ hint - causes nonmerged subqueries to evaluated at the earliest possible place in the execution plan. Normally subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpencive and reduces the number of rows significantly, it will improve performance to evaluate subquery earlier. - Collecting storage statistics with ANALYZE: - The ANALYZE SQL-statement has three distinct uses for objects (indexes, tables and clusters): (1) collecting (compute, estimate and delete) statistics on; (2) validating structures and (3) listing chained and migrating rows. (VALIDATE INDEX command also has been inherited from ORACLE V6 but you should uses ANALYZE INDEX instead.) - Statistics can be ESTIMATED or COMPUTED EXACTLY - Estimate statistics: Requires scan and sort only of all the rows of the requested sample of the table. - much faster then computing - samples row estimation (never samples more then 1064 rows). - Compute statistics are exact. Can take longer than estimation (time is in order of full table scan). - To perform a computation, Oracle requires enough space for scanning and SORT of all the rows of the table. - Generated statistics is always updates any existing statistics in the data dictionary. - Data dictionary views ALL-,USER-, DBA_TABLES (_COLUMNS,_INDEXES, _CLUSTERS) contain statistics generated by ANALYZE and used by optimizer. Statistics contain columns: _TABLES.Num_rows, Avg_space, Avg_row_len, _COLUMNS.Highval, Lowval ... - Oracle INVALIDATES ANY CURRENTLY parsed SQL statements that access any newly analyzed objects. - Introduction to SQL optimizer. Navigating (selecting access paths). Syntax (rule)-based and cost-based optimization. "Explain plan" and TKPROF facilities. - Data integrity. Types of integrity: - Nulls - Primary and unique keys - referential integrity (primary-foreign key value) - database triggers - Data consistency and concurrency Overview of locking mechanism. Row locks and table locks (RX, RS, SRX, S, X). Latches. There are two types of locks: standard DBMS locks and UL - user defined locks. DBMS locks are described in Server Administering materials. UL locks are intended for an user applications. These applications MUST use calls to DBMS_LOCK package to hold and release user defined UL locks (see DBMSLOCK.SQL file in $ORACLE_HOME/rdbms/admin) - Data dictionary view (DBA_, ALL_, USER_) to tracking Oracle Objects. DICT - contains dictionary views description DICT_COLUMNS - description of columns of dictionary views V$FIXED_TABLE - description or V$views ALL_, DBA_, USER_OBJECTS - Tables ALL_, DBA_, USER_TABLES - Clusters ALL_, DBA_, USER_CLUSTERS - Rollback Segments ALL_, DBA_, USER_ROLLBACK_SEGS, V$ROLLNAME, V$ROLLSTAT Important INIT.ORA parameter _CORRUPTED_ROLLBACK_SEGMENTS = (... , ..., ...) - Tablespaces DBA_TABLESPACES - Segments DBA_SEGMENTS - Extents DBA_EXTENTS - Files DBA_DATA_FILES, V$FILESTAT V$DATA_FILE - Triggers ALL_, DBA_, USER_TRIGGERS Long column called Trigger_Body holds the source code for triggers - Mutating and constraining tables (Application Developer's Guide, chapter 8) - Mutating table is a table that is currently being modified by an UPDATE, DELETE and INSERT statement, or a tables that might need to be updated by the effects od declarative DELETE CASCADE referential integrity action. - Constraining table is a table that a triggering statement might need to read either directly, for SQL statement, or indirectly, for declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress. A table is NOT mutating or constraining for STATEMENT triggers, but for ROW triggers there are TWO important restrictions: - the statements of a row trigger CAN NOT READ or MODIFY a mutating table of a TRIGGERING statement - the statements of a row trigger CAN NOT change the PRIMARY, FOREIGN, or UNIQUE key columns of a constraining table of the TRIGGERING statement. - :old.xxx and :new.xxx variables values: - before and after INSERT for each row: :old.xxx = NULL , :new.xxx = values from VALUES (..., xxx,...) clause if xxx column is not presented in VALUES (...) clause then :new.xxx = NULL. if xxx column is not presented in VALUES (...) clause then :new.xxx is took from DEFAULT clause if xxx is described as NOT NULL and xxx is omitted in VALUES( ) or setted to NULL explicitly then constraint checking occurs BEFORE ANY TRIGGER PROCESSING it is NOT POSSIBLE to recognise programmatically whether xxx is ommited in VALUES clause or specified as NULL explicitly. - before and after UPDATE for each row: if xxx is ommited in SET clause of UPDATE stmt then :new.xxx = :old.xxx As in INSERT stmt constraints first and triggers then. it is NOT POSSIBLE to recognise programmatically whether xxx is ommited in the SET clause or specified to the SAME value in the SET clause explicitly. - before and after DELETE for each row: :new.xxx is always NULL - Procedures, functions and packages: (Oracle7 Application Developer's Guide, chapter 9) ALL_, DBA_, USER_SOURCE - source code (Not USER_sourceS!) ALL_, DBA_, USER_OBJECTS - object (procedure, function, package). Status is VALID/INVALID ALL_, DBA_, USER_DEPENDENCIES - info about DIRECT dependencies among objects. Script UTLDTREE.SQL - gives dependencies among schema objects. Creates DEPTREE_TEMPTAB (Object_id number, referenced_object_id number, Nest_level number, Seq# number); When connected Internally, additional dependency info gathered for dependent CURSORS (shared SQL areas). Procedure DEPTREE_FILL (Object_Type char, Object_owner char, Object_name char) - clears and creates dependencies in DEPTREE_TEMPTAB table in user schema. All objects that recursively reference the specified object are listed DEPTREE view - lists dependency information in the DEPTREE_TEMPTAB table. Parent object (Nested_level=0) and dependent (Nested_Level>0) are listed IDEPTREE view - the same as DEPTREE but output is in graphical format (dependent objects are indented) DEPTREE_SEQ - sequence for unique identification sets of dependency information. V$SQLAREA - library cache V$SGASTAT - reports the components and sizes of SGA DBA_OBJECT_SIZE - allows to estimate packages/procedures' space requirements The source code is stored in varchar column Text. Each row stores a single line of source code. Select Text from ALL_SOURCE where Name = upper('') order by Line If there were error during compiling you should check the table DBA_, USER_ERRORS order by Line or show error command ALTER VIEW COMPILE; ALTER PROCEDURE COMPILE; ALTER FUNCTION COMPILE; ALTER VIEW COMPILE; ALTER PACKAGE COMPILE BODY; ALTER PACKAGE COMPILE PACKAGE; Example: Create or replace function FUNC1 (I in number, J out number, C varchar2, /* IN assumed*/, D In Out varchar2) return number as W varchar2(30); begin; . . . . . . end; - Declarative integrity constraints ALL_, USER_, DBA_CONSTRAINTS, DBA_CONS_COLUMNS - Grants (Plenty of them??????) - Privileges (Plenty of them??????) - Roles (Plenty of them??????) - Auditing (Plenty of them??????) See: Unobtrusive Audit Trail, PH95p#8