Transcript lecture 6

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).
• 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 object
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.
• 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 userfriendly 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 objects
Viewing Tables in the Database
Viewing 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 dictionary
SYS.USER_TS_QUOTAS
lists all of the tablespaces and how much can be used/is used
SYS.USER_OBJECTS
SYS.USER_TABLES
SYS.USER_VIEWS
lists objects created in the user’s schema
lists tables created in the user’s schema
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
SYS.USER_ROLE_PRIVS
lists system privileges
lists roles granted to the user
• SQL> desc SYS.DICTIONARY
• Name
Null? Type
• ------------------------------- -------- ---• TABLE_NAME
VARCHAR2(30)
• COMMENTS
VARCHAR2(4000)
Examples of built-in views
of Oracle
To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using,
query the data dictionary view PRODUCT_COMPONENT_VERSION.
A sample query follows.
Other product release levels may increment independent of the database server.
COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;
(You can also query the V$VERSION view to see component-level information.)
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba004.htm
• select constraint_name from
user_constraints;
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
SQL> desc 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)
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
• SQL> select owner, constraint_name,
constraint_type from user_constraints
where table_name=‘employee';
• no rows selected
Views
• Oracle supports views as specified in
SQL.
• To find out what views you have created,
use:
– select view_name from user_views;
• 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.
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.
•
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
– …
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.
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;
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;
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
SQL> desc USER_OBjects
Name
Null? Type
------------------------------- -------- ---OBJECT_NAME
VARCHAR2(128)
SUBOBJECT_NAME
VARCHAR2(30)
OBJECT_ID
NUMBER
DATA_OBJECT_ID
NUMBER
OBJECT_TYPE
VARCHAR2(19)
CREATED
DATE
LAST_DDL_TIME
DATE
TIMESTAMP
VARCHAR2(19)
STATUS
VARCHAR2(7)
TEMPORARY
VARCHAR2(1)
GENERATED
VARCHAR2(1)
SECONDARY
VARCHAR2(1)
NAMESPACE
NUMBER
EDITION_NAME
VARCHAR2(30)
• Check
• select object_name, LAST_DDL_TIME
from USER_objects
• after truncate your table.
• A link to Oracle Dictionary