Data Dictionary View Categories

13. Data Dictionary View Categories The data dictionary consists of three main sets of static views distinguished from each other by their scope: • DBA: What is in all the schemas • ALL: What the user can access • USER: What is in the user's schema

14. Views with the DBA Prefix • Views with the DBA prefix show a global view of the entire database. • They are meant to be queried only by database administrators. • Any user granted the system privilege SELECT ANY TABLE can query the DBA-prefixed views of the data dictionary. • To query on all objects in the database, the DBA could issue the following statement: SELECT owner, object_name, object_type FROM dba_objects;

15. Views with the ALL Prefix • Views with the ALL prefix refer to the user's overall perspective of the database. • These views return information about schema objects to which the user has access by way of public or explicit grants of privileges and role, in addition to schema objects that the user owns. • For example, the following query returns information about all the objects to which a user has access: SELECT owner, object_name, object_type FROM all_objects;

16. Views with USER prefix The views most likely to be of interest to typical database users are those with the USER prefix . These views: • Refer to the user’s own private environment in the database • Generally refer to objects owned by the current user • Have columns identical to the other views, except that the column OWNER is implied to be the current user • Return a subset of the information in the ALL views • Can have abbreviated pubic synonyms for convenience For example, the following query returns all the objects contained in the users schema: SELECT owner, object_name, object_type FROM users_objects;

17. For example, the DBA_USERS table holds user information. • SQL> describe dba_users; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000)

18. An example of a table holding the more internal features of the DBMS is the USER_CONSTRAINTS table. This (extensively) documents the constraints which exist between tables in the database. A summary of the attributes is shown below. • SQL> describe user_constraints; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) ...... DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) ...... LAST_CHANGE DATE ......

19. select owner,table_name,constraint_name,constraint_type from all_constraints where owner = 'DBRW'and table_name in ('EMPLOYEE','JOBHISTORY','DEPARTMENT') The constraints indicate that the DEPARTMENT has only a PRIMARY KEY constraint. EMPLOYEE and JOBHISTORY also have primary key constraints, but also have some foreign key referential integrity constraints (R). The constraint names are automatically generated when the tables are created.

35. The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. • The COUNT field shows the number of unused columns in the table. SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT --------------------------- --------------------------- ----- HR ADMIN_EMP 2

41. Create a Table from a Table • Use the create table ... as select-statm. drop table RAIN; create table RAIN as select * from BADWEATHER; You can use the name of an existing object.

42. Create a Table from a Table • To copy only the structure -and no data- do as in the example below create table RAIN as select * from BADWEATHER where true = false; The condition is false and results in no row selected

43. Index-Only Tables • An index-only table is stored as if the entire table were an index. • They can be used in order to • increase performance • enforce uniqueness of data • Only one key can be used to index the table. • Good idea only when data is very stable.

44. Index-Only Tables drop table WORKER3; create table WORKER3 ( Name VARCHAR2(25), Age NUMBER, Address VARCHAR(15) constraint WORKER3_PK PRIMARY KEY (Name) ) ORGANIZATION INDEX; /

45. Creating an Index-Organized Table • The following statement creates an index-organized table: CREATE TABLE admin_docindex( token char(20), doc_id NUMBER, token_frequency NUMBER, token_offsets VARCHAR2(512), CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id)) ORGANIZATION INDEX TABLESPACE admin_tbs OVERFLOW TABLESPACE admin_tbs2;

46. Parallelizing Index-Organized Table Creation • The CREATE TABLE...AS SELECT statement enables you to create an index-organized table and load data from an existing table into it. By including the PARALLEL clause, the load can be done in parallel. • The following statement creates an index-organized table in parallel by selecting rows from the conventional table hr.jobs: CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l) ORGANIZATION INDEX PARALLEL AS SELECT * FROM hr.jobs; • This statement provides an alternative to parallel bulk-load using SQL*Loader

47. Partitioned Tables • Large tables could be split into more than one physical table-location. Partitioning may improve performance. create table WORKER5 ( Name VARCHAR2(25), Age NUMBER, Address VARCHAR(15), constraint WORKER5_PK PRIMARY KEY (Name) ) PARTITIONby range (Name) (partition PART1 values less than ('F') tablespace Ohio1_TS, partition PART2 values less than (MAXVALUE) tablespace Ohio2_TS);