database objects User schema DCL Oracle dictionarySchemaSchema = metadataOracle8i User AccountsDatabase objectsSlide 6Slide 7User schemaSlide 9Slide 10Oracle Data DictionarySlide 12Slide 13Viewing Information About TablesViewing Tables in the DatabaseViewing Constraints on One TableSlide 17Slide 18Slide 19Slide 20Slide 21Slide 22ViewsSlide 24PseudocolumnsCommonly used pseudo columnsWhat is dual ?Slide 28Systimestamp is a special system functionSlide 30database objects User schemaDCLOracle dictionarySchema•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.Schema = metadata•Metadata (meta data, or sometimes metainformation) is "data about data", of any sort in any media.Oracle8i User Accounts•User account - identified by a 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 objectDatabase 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.•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.)•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–…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•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.•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.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.•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.•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)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 objectsViewing Tables in the DatabaseViewing Constraints on One Table•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)SYS.DICTIONARY lists all objects that make up the data dictionarySYS.USER_TS_QUOTAS lists all of the tablespaces and how much can be used/is usedSYS.USER_OBJECTS lists objects created in the user’s schemaSYS.USER_TABLES lists tables created in the user’s schemaSYS.USER_VIEWS lists views created in the user’s schemaSYS.USER_CONSTRAINTS lists all the constraints (e.g. Check, PK, FK, Unique) created on user objectsSYS.USER_SYS_PRIVS lists system privilegesSYS.USER_ROLE_PRIVS lists roles granted to the user SYS.DICTIONARY lists all objects that make up the data dictionarySYS.USER_TS_QUOTAS lists all of the tablespaces and how much can be used/is usedSYS.USER_OBJECTS lists objects created in the user’s schemaSYS.USER_TABLES lists tables created in the user’s schemaSYS.USER_VIEWS lists views created in the user’s schemaSYS.USER_CONSTRAINTS lists all the constraints (e.g. Check, PK, FK, Unique) created on user objectsSYS.USER_SYS_PRIVS lists system