1 / 30

database objects User schema DCL Oracle dictionary

database objects User schema DCL Oracle dictionary. Schema. Pronounce skee-ma, the structure of a database system, described in a formal language supported by the database management system (DBMS).

duena
Download Presentation

database objects User schema DCL Oracle dictionary

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. database objects User schemaDCLOracle dictionary

  2. Schema • Pronounce skee-ma, the structure of a database system, described in a formal language supported by the database management system (DBMS). • In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables. • Schemas are generally stored in a data dictionary.

  3. Schema = metadata • Metadata (meta data, or sometimes metainformation) is "data about data", of any sort in any media.

  4. Oracle8i User Accounts • User account - identified bya unique username and password • User schema - all of the objects that the user creates and stores in the database • Object owner has privileges to perform all possible actions on an object

  5. Database objects • Database objects are the logical entities which Oracle manages for users. • Users will interact with them through Oracle. • Each object has a unique name. • These objects fit together to form a database.

  6. Of course the backbone of a database consists of tables and constraints. However, a database also contains other objects like indexes, views etc. • Almost all DDL commands will create some kinds of objects in an Oracle database. (In the OOP point of view, Oracle DDL parser, implemented in C++, will dynamically call the New operator.)

  7. Types (classes) of database objects includes: • Tables. You can think of a concrete table as an instance of a table class, from an OOP point of view. Actually it is. • Constraints, similarly, an instance of a universal constraint class. Ditto the rest of the classes. • Views • Sequences • Indexes • Synonyms • Stored procedures and packages (you will lean by the end of the semester) • Etc. • Also objects you won’t be bothered to know at this moment for this course such as • Partitions • Clusters • User-defined data types • Tablespaces • Java objects • …

  8. User schema • An Oracle sever can host multiple database instances. • Each database can have multiple user accounts. • User schema - all of the objects that the user creates and stores in the database forms the user schema for that user. • By default, the user is the owner of the objects created by the user. • Object owner has privileges to perform all possible actions on an object

  9. An Oracle database object privilege is a permission granted to an Oracle database user or role to perform some action on a database object. • These object privileges include • SELECT, INSERT, UPDATE, DELETE on tables and views • EXECUTE on procedures, functions, packages, and Java objects. • They can be granted directly using Oracle commands in SQLPLUS or various more user-friendly GUI tools.

  10. A schema-level privilege is granted to a user or group to perform some action on a schema, for example, inserting rows in any table in the schema or modifying the definition of objects in the schema. • You can grant privileges at the object or schema level. Object-level privileges are granted on an object-by-object basis and apply only to Oracle database user accounts or roles. Schema level privileges are granted on a schema-by-schema basis and apply to Oracle Portal users or groups.

  11. Oracle Data Dictionary • The data dictionary is the set of tables that Oracle uses to manage the database, these tables hold metadata (data about data). • The data dictionary can tell you about database objects, permissions, rights, indexes, synonyms, sequences, constraints, users and audit information.

  12. The data dictionary is created by the CREATE DATABASE statement and is available from then on. A regular user should have no privilege to issue create database statement. • Oracle itself has update rights to the tables (which are owned by user SYS). • Regular database users access data dictionary data by using standard SELECT statements.

  13. Many of the tables hold internal information which is difficult to interpret but Oracle provides various views of the data which interpret the information for you. • Views are named with a prefix which indicates the class of user that can view them. • Read textbook (pages 273-274)

  14. Viewing Information About Tables • describe tablename: displays column names and data types • Data dictionary: tables that contain information about the structure of the database. • USER: shows the objects in the current user’s schema • ALL: shows both objects in the current user’s schema and objects that the user has privileges to manipulate • DBA: allows users who are database administrators to view information about all database objects

  15. Viewing Tables in the Database

  16. Viewing Constraints on One Table

  17. Store all the information that is used to manage the objects in the database • Source of valuable information for developers and db users • USER_* , ALL_* , DBA_* • They are views on the Oracle data Dictionary (managed in a relational way)

  18. SYS.DICTIONARY lists all objects that make up the data dictionary SYS.USER_TS_QUOTAS lists all of the tablespaces and how much can be used/is used SYS.USER_OBJECTS lists objects created in the user’s schema SYS.USER_TABLES lists tables created in the user’s schema SYS.USER_VIEWS lists views created in the user’s schema SYS.USER_CONSTRAINTS lists all the constraints (e.g. Check, PK, FK, Unique) created on user objects SYS.USER_SYS_PRIVS lists system privileges SYS.USER_ROLE_PRIVS lists roles granted to the user

  19. SQL> desc SYS.DICTIONARY • Name Null? Type • ------------------------------- -------- ---- • TABLE_NAME VARCHAR2(30) • COMMENTS VARCHAR2(4000)

  20. select constraint_name from user_constraints;

  21. SQL> descuser_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) • SEARCH_CONDITION LONG • R_OWNER VARCHAR2(30) • R_CONSTRAINT_NAME VARCHAR2(30) • DELETE_RULE VARCHAR2(9) • STATUS VARCHAR2(8) • DEFERRABLE VARCHAR2(14) • DEFERRED VARCHAR2(9) • VALIDATED VARCHAR2(13) • GENERATED VARCHAR2(14) • BAD VARCHAR2(3) • RELY VARCHAR2(4) • LAST_CHANGE DATE

  22. SQL> select owner, constraint_name, constraint_type from user_constraints where table_name=‘employee'; • no rows selected

  23. Views • Oracle supports views as specified in SQL. • To find out what views you have created, use:     • select view_name from user_views;

  24. To find out what indexes you have, use     • select index_name from user_indexes; • USER_INDEXES is another system table just like USER_TABLES. • This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. • You might also see weird names of the indexes created automatically and transparently by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.

  25. Pseudocolumns • In Oracle, pseudo columns are associated with any table as extra and universal columns, but nothing to do with table data. • Their values can be retrieved from any table like they are retrieved from the table directly, but the values are not saved in any table.

  26. Commonly used pseudo columns • Some of the Most commonly used Pseudo columns in Oracle are • SYSDATE: It shows the Current date from the local or remore database . We can use the CURRENT_DATE also with for the same purpose. • ROWID:Rowid is a pseudo column that uniquely identifies a row within a table, but not within a database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid • ROWNUM: Rownum numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum. • USER:a pseudo column that returns the name of the user currently connected to the session. • UID:returns the id number of a user currently connected to the session • …

  27. What is dual ? • Dual is a table which is created by oracle along with the data dictionary. • It consists of exactly one column whose name is dummy and one record (row). The value of that record is X. • desc dual • select * from dual; • The owner of dual is SYS but dual can be accessed by every user. • Although it is possible to delete the one record, or insert additional records, or even fiddle with the table structure etc., one really should not do that!. • Even sys should not modify it. • Since dual is supposed to be always there and only 1 row, dual is the preferred table to select a single pseudo column (such as sysdate etc.) using other tables for this selecting pseduo columns is also ok, but make sure the table exists and is easy to remember. • select sysdate from dual

  28. Commonly used pseudo columns Select user From dual; Select uid From dual; Select level From dual; Select rowid from dual; Select rownum from dual; select user, uid from dual; Select SYSDATE, ROWID, ROWNUM, USER, UID From dual; Select SYSDATE, ROWID, ROWNUM, USER, UID From tabsuser;

  29. Systimestamp is a special system function • The SYSTIMESTAMP function returns the current date and time as a TIMESTAMP_TZ value. • can also treated as a pseduo column, since it does not take input. • SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL;

  30. A link to Oracle Dictionary

More Related