Chapter 10

1. Chapter 10 Data Dictionary Basics

2. Data Dictionary • Information regarding objects and events within the database. • Super critical that as a DBA or developer you know how to leverage the information within the data dictionary. • The data dictionary is always a good place to look when troubleshooting or determining facts about your database. • This chapter gives you a foundation for understanding and querying the data dictionary.

3. Data Dictionary Contents • Data dictionary objects owned by SYS • Static views • Dynamic views

4. Static Views • There are three types or levels of static views: • USER • ALL • DBA • USER/ALL/DBA views contain metadata (information) describing the physical makeup of the database. • Use these views to view the structure of the database and information about users and corresponding objects. • Examples: DBA_USERS, DBA_TABLES, DBA_INDEXES, DBA_CONSTRAINTS, DBA_TABLESPACES, DBA_SEGMENTS, DBA_EXTENTS, and so on.

5. Creating Static Views

6. Dynamic Views • Dynamic performance data-dictionary views are often referred to as the V$ and GV$ views. • Continuously updated by Oracle and reflect the current condition of the instance and database. • Dynamic views are critical for diagnosing real-time performance issues.

7. Creating Dynamic Views

8. Viewing View Creation Details • Query v$fixed_view_definition select view_definition from v$fixed_view_definition where view_name='V$CONTROLFILE';

9. Derivable Database Documentation • DBA_OBJECTS • DICTIONARY • DICT_COLUMNS

10. Logical and Physical Database Structures

11. Database Space-Management Views • V$DATABASE • DBA/ALL/USER_USERS • DBA/USER_TABLESPACES • DBA_DATA_FILES • DBA/USER_FREE_SPACE • V$DATAFILE • V$DATAFILE_HEADER • DBA/ALL/USER_TABLES • DBA/ALL/USER_INDEXES • Many, many, more...

12. Displaying User Information • Currently connected users • Current SQL being executed by users • User accounts in the database

13. Currently Connected User Info SQL> show user; SQL> select * from user_users; SQL> select name from v$database; SQL> select instance_name, host_name from v$instance; select sys_context('USERENV','CURRENT_USER') usr ,sys_context('USERENV','AUTHENTICATION_METHOD') auth_mth ,sys_context('USERENV','HOST') host ,sys_context('USERENV','INSTANCE_NAME') inst from dual;

14. Users Currently Logged In select count(*) ,username from v$session group by username;

15. Currently Executing SQL Example 1: select a.sid ,a.username ,b.sql_text from v$session a ,v$sqltext_with_newlines b where a.sql_id = b.sql_id order by a.username ,a.sid ,b.piece;

16. Currently Executing SQL Example 2: select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s join v$sqltext_with_newlines q on s.sql_address = q.address where status='ACTIVE' and type <>'BACKGROUND' and last_call_et> 60 order by sid,serial#,q.piece;

17. User Accounts set lines 132 col username form a15 col default_tablespace form a18 col temporary_tablespace form a20 col account_status form a16 -- select username ,default_tablespace ,temporary_tablespace ,account_status ,created ,lock_date from dba_users order by 1;

18. Viewing Table Information select a.table_name ,b.created ,b.last_ddl_time ,a.last_analyzed from user_tables a, user_objects b where a.table_name = b.object_name;

19. Displaying Object Disk-Space Usage UNDEFINE owner COL summer FORM 999,999.999 SET LINES 132 TRIMSPOOL ON PAGES 100 SPO space.txt SELECT segment_name ,partition_name ,tablespace_name ,segment_type ,SUM(bytes)/1024/1024 summer FROM dba_extents WHERE owner = UPPER('&&owner') GROUP BY segment_name,partition_name,tablespace_name,segment_type ORDER BY segment_name,partition_name; SPO OFF;

20. Displaying Table Row Counts UNDEFINE user SPOOL tabcount_&&user..sql SET LINESIZE 132 PAGESIZE 0 TRIMSPO OFF VERIFY OFF FEED OFF TERM OFF SELECT 'SELECT RPAD(' || '''' || table_name || '''' ||',30)' || ',' || ' COUNT(*) FROM &&user..' || table_name || ';' FROM dba_tables WHERE owner = UPPER('&&user') ORDER BY 1; SPO OFF; SET TERM ON @@tabcount_&&user..sql SET VERIFY ON FEED ON

21. Manually Generating Statistics SQL> exec dbms_stats.gather_table_stats(ownname=>'INV',- tabname=>'F_SALES',- cascade=>true,estimate_percent=>20,degree=>4); SQL> exec dbms_stats.gather_schema_stats(ownname => 'INV',- estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,- degree => DBMS_STATS.AUTO_DEGREE,- cascade => true);

22. Displaying Indexes for a Table • Troubleshooting performance issues. • What indexes exist and on what columns? select a.index_name ,a.column_name ,b.status ,b.index_type ,a.column_position from user_ind_columns a ,user_indexes b where a.table_name = upper('&table_name') and a.index_name = b.index_name order by a.index_name, a.column_position;

23. Showing Foreign-Key Columns Not Indexed • Common to index foreign key columns. This query useful for displaying foreign key columns not indexed. select a.constraint_namecons_name ,a.table_nametab_name ,b.column_namecons_column ,nvl(c.column_name,'***No Index***') ind_column from user_constraints a join user_cons_columns b on a.constraint_name = b.constraint_name left outer join user_ind_columns c on b.column_name = c.column_name and b.table_name = c.table_name where constraint_type = 'R' order by 2,1;

24. Displaying Table Constraints • Troubleshooting or determining what types of constraints are in place. select table_name,(case constraint_type when 'P' then 'Primary Key' when 'R' then 'Foreign Key' when 'C' then 'Check' when 'U' then 'Unique' when 'O' then 'Read Only View' when 'V' then 'Check view' when 'H' then 'Hash expression' when 'F' then 'REF column' when 'S' then 'Supplemental logging' end) cons_type ,constraint_namecons_name,search_conditioncheck_cons,status from dba_constraints where owner like upper('&owner') and table_name like upper('&table_name') order by cons_type;

25. Showing Primary-Key and Foreign-Key Relationships • Sometimes need to determine for child table FKs what are associated PKs and visa versa. select a.constraint_typecons_type ,a.table_namechild_table ,a.constraint_namechild_cons ,b.table_nameparent_table ,b.constraint_nameparent_cons ,b.constraint_typecons_type from dba_constraints a ,dba_constraints b where a.owner = upper('&owner') and a.table_name = upper('&table_name') and a.constraint_type = 'R' and a.r_owner = b.owner and a.r_constraint_name = b.constraint_name;

26. Displaying Granted Roles select username ,granted_role from user_role_privs; select grantee ,granted_role from dba_role_privs where grantee = upper('&grantee') order by grantee;

27. Displaying System Privileges • Query these dba_sys_privs when troubleshooting privilege issues. select grantee ,privilege ,admin_option from dba_sys_privs where grantee = UPPER('&grantee') order by privilege;

28. Displaying Object Privileges • Query data dictionary when diagnosing object access issues. select owner ,table_name ,grantor ,privilege from user_tab_privs_recd;

29. Displaying Object Dependencies • Before dropping an object, it’s useful to determine which objects might have dependencies on the object to be dropped. select '+' || lpad(' ',level+2) || type || ' ' || owner || '.' || name dep_tree from dba_dependencies connect by prior owner = referenced_owner and prior name = referenced_name and prior type = referenced_type start with referenced_owner = upper('&object_owner') and referenced_name = upper('&object_name') and owner is not null;

30. V$ and GV$ Views • The V$ and GV$ views are used for performance tuning and database troubleshooting. • These are covered more in Chapter 22 Database Troubleshooting.

31. Summary • DBAs and developers must understand the data dictionary architecture and how to leverage the information to troubleshoot and maintain database environments. • Main focus of this chapter was the static data dictionary views. • Dynamic views covered more later in the book in the database troubleshooting chapter.