Transcript Schema Objects

Oracle Database Administration
Schema objects
Schema objects
• The following types of schema objects exist:
–
–
–
–
–
–
–
–
–
Tables
Indexes
Views
Clusters
Synonyms
Sequences
Database links
Materialized views
PL/SQL objects: procedures, functions, packages, object
types
– Java objects
Tables
• Tables are used to store data
• Tables can be permanent and temporary
• Temporary tables – two types:
– data is only stored for a duration of a session
– data is only stored for a duration of a transaction
• Table data is stored in a data segment in a
single tablespace
• Table can be a part of a cluster
– when using clusters, several tables can be stored
together in a single cluster segment
Tables - examples
• Permanent table:
CREATE TABLE perm (ID NUMBER);
• Temporary table:
CREATE GLOBAL TEMPORARY TABLE temp1
(ID NUMBER) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE temp2
(ID NUMBER) ON COMMIT DELETE ROWS;
Tables cont.
• Tables can be organized:
– as heap – default
CREATE TABLE as_heap (ID NUMBER);
– as index – table data stored together with primary
key data
CREATE TABLE as_index
(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100))
ORGANIZATION INDEX;
Table columns
• Oracle supports the following data types:
– CHAR – fixed length character data, maximum
size: 2000 bytes or characters
– VARCHAR2 – variable length character data,
maximum length: 4000 bytes or characters, size
must be specified
– NCHAR – fixed length Unicode data
– NVARCHAR2 – variable length Unicode data, size
must be specified (max. 4000 bytes)
– NUMBER – number, can include precision and
scale
Table columns
– DATE – stores date and time, time is stored with a
precision up to a second
– TIMESTAMP – contains date and time, time is
stored with a precision up to fraction of a second
– TIMESTAMP WITH TIME ZONE – same as
TIMESTAMP, but time zone information is stored
– TIMESTAMP WITH LOCAL TIME ZONE – data
is normalized to the database time zone, clients see
data in their time zone
– INTERVAL DAY TO SECOND – stores time
interval
Table columns
– RAW – raw binary data, size is up to 2000 bytes,
size is required
– LONG RAW – deprecated, BLOB type is
recommended instead
– ROWID, UROWID – types that store Oracle row
identifiers
– CLOB – large character data, maximum size over
4GB
– BLOB – large binary data
– NCLOB – large character data stored as Unicode
– BFILE – contains locator to large binary file stored
outside of the database
Table columns
– BINARY_FLOAT – 32 bit floating point number,
occupies 5 bytes
– BINARY_DOUBLE – 64 bit floating point
number, occupies 9 bytes
Character columns
• Difference between CHAR and VARCHAR2:
CREATE TABLE test (
c1
CHAR(10),
c2
VARCHAR2(10));
INSERT INTO test VALUES ('text', 'text');
-- the same as:
INSERT INTO test VALUES ('text
', 'text');
– CHAR texts are padded with spaces to the
maximum length
– VARCHAR2 texts are stored unchanged
Character column length
• Character column length can be specified in
bytes or characters:
CREATE TABLE test (
c1
CHAR(10 CHAR),
c2
VARCHAR2(10 CHAR),
c3
CHAR(10 BYTE),
c4
VARCHAR2(10 BYTE),
c5
CHAR); -- default to 1 BYTE
• Difference between CHAR and BYTE is
important when using UTF8 encoding
Number column
• Number column can include:
– precision – number of significant digits
– scale – number of digits after decimal point (can be
negative)
– if value exceeds precision – error
– if values exceeds scale – value is rounded
CREATE TABLE test (
n1
NUMBER, -- maximum range and precision
n2
NUMBER(10,2),
n3
NUMBER(3)) -- same as NUMBER(3,0)
Number column
Value inserted
123.45
123.45
123.89
123.45
123.45
Column specification
NUMBER
NUMBER(3)
NUMBER(3)
NUMBER(4,2)
NUMBER(4,-2)
Stored as
123.45
123
124
error
100
Date, timestamp
• Date columns use client date and time
formatting, e.g.:
INSERT INTO test VALUES ('2005-05-03',
'2005-05-03 15:30:05');
-- converted by the client before sending to the server
• To use date in client independent format, use
TO_DATE, TO_CHAR functions:
INSERT INTO test VALUES
(TO_DATE('2005-05-03', 'YYYY-MM-DD')
TO_DATE('2005-05-03 15:30:05',
'YYYY-MM-DD HH24:MI:SS')
SELECT TO_CHAR(d1, 'YYYY-MM-DD'),
TO_CHAR(d2, 'YYYY-MM-DD HH24:MI:SS')
Date, timestamp cont.
• Timestamp columns accept fractional seconds:
CREATE TABLE test (d1 DATE, d2 TIMESTAMP(6));
INSERT INTO test VALUES (SYSDATE, SYSDATE);
SELECT * FROM test;
-------------------------------------------------2006-02-09 08:16:27 2006-02-09 08:16:27,000000
DELETE FROM test;
INSERT INTO test values (SYSTIMESTAMP,
SYSTIMESTAMP);
SELECT * FROM test;
-------------------------------------------------2006-02-09 08:17:35 2006-02-09 08:17:35,076000
LOB columns
• LOB columns include:
– BLOB – large binary object
– CLOB, NCLOB – large text objects
• LOBs are stored separate from the rest of the
data record
• Each LOB column requires two additional
segments (possibly in another tablespace)
Indexes
• Indexes are used to:
– enforce primary key and unique constraints
– help validate foreign key constraints
– improve performance of queries
• Two types of indexes exist in Oracle:
– B-Tree indexes (default)
– Bitmap indexes
• B-Tree indexes can be:
– unique
– non-unique
Indexes cont
• NULL values are ignored by an index (even unique
index):
CREATE
INSERT
INSERT
INSERT
INSERT
TABLE test (id NUMBER UNIQUE);
INTO test VALUES (1);
INTO test VALUES (1); -- error
INTO test VALUES (NULL);
INTO test VALUES (NULL); -- ok
• Composite index value is null if all columns are null:
CREATE TABLE test (id1 NUMBER, id2 NUMBER,
UNIQUE(id1, id2));
INSERT INTO test VALUES (1, 1);
INSERT INTO test VALUES (1, 1); -- error
INSERT INTO test VALUES (1, NULL);
INSERT INTO test VALUES (1, NULL); -- error
INSERT INTO test VALUES (NULL, NULL);
INSERT INTO test VALUES (NULL, NULL); -- ok
Compound indexes
• Column values are combined together in the
order they appear in CREATE INDEX statement
Statements:
CREATE INDEX IND1 ON EMP(ID, SALARY);
CREATE INDEX IND2 ON EMP(SALARY,ID);
create two different indexes
Views
• View is a SQL query definition
• Views are used to:
– simplify complex SQL statements
– provide additional security
• View security:
– It is possible to grant privileges for querying a
view, to users that do not have privileges to view
the base table(s)
• View storage:
– view is stored as text SQL definition, no data is
stored
View examples
CREATE TABLE test (id NUMBER UNIQUE, name
VARCHAR2(256), description VARCHAR2(4000));
CREATE VIEW test_view AS SELECT id, name FROM test;
GRANT SELECT ON test_view TO some_user;
-- some_user can select data from view test_view,
-- but cannot select data from table test
CREATE VIEW test1_view AS
SELECT sum(salary), dept_id
FROM employees
GROUP BY dept_id
HAVING sum(salary) > 10000
Updating a view
• Some views can be updated. Updating a view
causes base table to be updated
• Updateable view cannot contain:
– Set Operators (INTERSECT, MINUS, UNION, UNION ALL)
– DISTINCT
– Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM,
etc.)
– GROUP BY Clause
– ORDER BY Clause
– CONNECT BY Clause
– START WITH Clause
– Collection Expression In A Select List
– Subquery In A Select List
– Join Query
Synonyms
• Synonym is an alias for a schema object
• Synonyms enable to:
– hide the name and owner of the object
– move the underlying object without modifying
statements that refer to synonyms
• Synonyms and security:
– synonym is just a simple alias, the privilege to
access the underlying object is required (unlike
with views)
Synonyms cont.
• Synonyms can be public and private
• Private synonyms:
– affect only one user, exist in this user schema, e.g.:
CREATE SYNONYM s1 FOR test1.table1;
SELECT * FROM s1; -- you still need privilege to
-- access test1.table1
• Public synonyms
CREATE PUBLIC SYNONYM s1 FOR test1.table1;
-- any user
SELECT * FROM s1; -- the user still needs privilege
-- to access test1.table1
• Example: USER_TABLES is a public synonym
Synonyms cont.
• To create synonym, user needs:
– CREATE SYNONYM or CREATE ANY
SYNONYM privilege
– CREATE PUBLIC SYNONYM
• To drop synonym user needs:
– to drop user’s own private synonym - no special
privilege
– to drop other user’s private synonym - DROP ANY
SYNONYM
– to drop public synonym - DROP PUBLIC
SYNONYM
Sequences
• Sequence:
– generates sequential numbers
– used to generate primary keys
• Examples:
– sequence with default parameters:
CREATE SEQUENCE seq1;
– sequence with non-default parameters
CREATE SEQUENCE seq2
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
Using sequences
• Sequences are used to generate primary keys:
• It is possible to use:
– one sequence for one table
– one sequence for a group of tables or for entire
application
• Sequence behaves like a function, e.g.:
INSERT INTO test1 (date_col) VALUES (SYSDATE);
-- sysdate function used
CREATE SEQUENCE s1;
INSERT INTO test1 (id) VALUES (s1.NEXTVAL);
-- s1.NEXTVAL function used
The DUAL table
• Oracle has special DUAL table with one
record.
• Dual table is useful for getting function values:
SELECT SYSDATE FROM DUAL
-- SYSDATE function computed for every
-- row in DUAL table. Content of DUAL table
–- is ignored, only function value is
-- computed
SELECT s1.NEXTVAL FROM DUAL
-- next sequence value returned for every
-- row in DUAL table
Nextval and Currval
• Each sequence has two functions:
– nextval - gets next value from a sequence
– currval - gets current sequence value (last sequence
value returned for current session)
• To insert table row using a sequence and find
out what was inserted use:
INSERT INTO table1 (id) VALUES (s1.NEXTVAL);
SELECT s1.CURRVAL FROM DUAL;
OR
SELECT s1.NEXTVAL FROM DUAL;
INSERT INTO table1 (id)
VALUES (<value from the previous statement>);
Remaining schema objects
• Materialized views - views that cache data,
used to improve performance
• Database links - for connections between
databases, selecting data from remote
databases
• PL/SQL objects - procedures, functions,
packages, object types
• Java objects - starting from Oracle 8i it is
possible to store Java objects as stored
procedures, functions etc.
Constraints
• 5 types of contraints:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
NOT NULL constraint
• Disallows empty (NULL) values in a table
column
• Single column constraint
• In Oracle empty string ('') is considered NULL,
empty strings cannot be inserted into columns
with NOT NULL constraints
Unique constraint
• Disallows multiple non-NULL values in a
table column or set of columns
• Multiple NULL values are allowed (NULLs
are ignored by unique constraint check)
• Can be created on a single column or on a set
of columns
Primary key constraint
• Disallows duplicate and null values
• There can only be one primary key on a single
table
• Can be created on a single column or on a set
of columns
Foreign key constraint
• Requires each value in a column or set of
columns to match a value in related tables's
UNIQUE or PRIMARY KEY.
• Specifies what should be done if the data in the
related table is modified:
– default – raise exception
– on delete cascade – delete child records
– on delete set null – set column value to null
Foreign key constraint
ALTER TABLE table1 ADD CONSTRAINT FK_1
FOREIGN KEY (col1) REFERENCES table2(id)
ON DELETE CASCADE;
INSERT INTO table2 VALUES (1);
INSERT INTO table1(id, col1) VALUES (1, 1);
INSERT INTO table1(id, col1) VALUES (1, 2); -- error
–- referenced record does not exist in table2
DELETE FROM table2 WHERE id = 1; -- record removed
-- from table1
Check constraint
• Disallows values that do not satisfy the logical
expression of the constraint
• Example:
ALTER TABLE TABLE1
ADD CONSTRAINT CHK_1
CHECK (column1 IN ('A', 'B', 'C'))
ALTER TABLE EVENTS_TABLE
ADD CONSTRAINT CHK_2
CHECK (start_date < end_date)
Constraint names
• Each constraint in Oracle has a name
• If name is not provided when the constraint is
created, Oracle creates default: 'SYSxxxxx'
• Names are useful because:
– easier to enable, disable, modify constraints
– easier to find constraints in dictionary tables
– when exporting and importing data constraint
names don’t change
Constraint names
• To specify constraint name create constraints
like that:
CREATE TABLE tab1 (
id NUMBER CONSTRAINT pk_tab1 PRIMARY KEY,
col1 VARCHAR2(256) CONSTRAINT unk_tab1 UNIQUE,
col2 VARCHAR2(10) CONSTRAINT chk_tab1
CHECK (col2 in ('A', 'AB', 'ABC')),
CONSTRAINT chk_tab2
CHECK (length(col1) < length(col2))
);
Deferred constraints
• Constraints can be:
– immediate – checked at the end of each DML statement
– deferred – checked when the transaction is committed
• By default constraints are immediate – validated after
each statement
• Session can request constraints to be deferred:
– SET CONSTRAINTS ALL DEFERRED
• Deferred mode is ended with:
– SET CONSTRAINTS ALL IMMEDIATE
– COMMIT
Deferred constraints
Example:
create table table1 (id number primary key);
create table table2 (id number primary key,
col1 number references table1 deferrable);
set constraints all deferred;
insert into table2 values(1, 1);
insert into table1 values(1);
commit; -- ok
set constraints all deferred;
insert into table2 values(2, 2);
commit; -- error
set constraints all deferred;
insert into table2 values(2, 2);
set constraints all immediate; -- error
Constraint states
• Constraint can be in one of the following
states:
– ENABLE VALIDATE (same as ENABLE) – constraint is true
for all rows in the table
– ENABLE NOVALIDATE – checked for new or modified rows
– DISABLE NOVALIDATE (same as DISABLE) – constraint not
checked, ignored
– DISABLE VALIDATE – constraint not checked, but
disallows modifications to the constrained rows
Enabling, disabling constraints
alter table table2 enable novalidate primary key;
• Constraint that is enabled novalidate:
– new data is checked against the constraint
– old data is not checked, some values in the table
can violate the constraint
Security and privileges
• Two types of privileges:
– system privileges - privilege to perform some
action, e.g. create table, drop user, alter index etc.
• when the database is created, user SYS is granted all
system privileges
– object privileges - privilege to access some object
in some user’s schema, e.g. insert data into table,
select value from a sequence, execute procedure
etc.
• when the object is created, no object privileges are
granted. Object owner can always do any action on the
object
Security and privileges
• All privileges are granted using the GRANT
statement:
–
–
–
–
–
GRANT
GRANT
GRANT
GRANT
GRANT
CREATE
SELECT
SELECT
ALL ON
CREATE
SESSION TO user1;
ON table1 TO user2;
ON table1 TO PUBLIC
table1 TO user2 WITH GRANT OPTION
USER TO user1 WITH ADMIN OPTION
• Privileges are revoked using the REVOKE
statement:
– REVOKE CREATE SESSION FROM user1;
– REVOKE SELECT ON table1 FROM user2;
– REVOKE ALL ON table1 FROM user2;
System privileges
• To grant or revoke system privilege:
– you must have been granted that privilege with the
ADMIN OPTION, or
– you must have GRANT ANY PRIVILEGE system
privilege
System privileges
• Granting system privilege:
GRANT CREATE USER TO user1;
CONNECT user1;
CREATE USER user2 IDENTIFIED BY user2; -- ok
GRANT CREATE USER TO user2; -- error
CONNECT SYSTEM
GRANT CREATE USER TO user1 WITH ADMIN OPTION;
CONNECT user1;
GRANT CREATE USER TO user2; -- ok
CONNECT SYSTEM
GRANT GRANT ANY PRIVILEGE TO user2;
CONNECT user2;
GRANT CREATE USER TO user3 WITH ADMIN OPTION;
REVOKE CREATE USER FROM user3;
ANY privileges
• Special ANY privileges (CREATE ANY xxx,
DROP ANY xxx, ALTER ANY xxx) enable
access to other user schema:
– CREATE ANY xxx - enables creation of objects in
other user schema (e.g. CREATE ANY TABLE)
– DROP ANY xxx - enables dropping other user’s
objects
– ALTER ANY xxx - enables modifying other user’s
objects
Object privileges
• Object privilege - permission to perform action
on a specific schema object
• Owner of the object always has all privileges
to the owned object, object privileges are
granted to other users
• Privilege can be granted with GRANT
OPTION - grantee can later grant privilege to
other users
GRANT SELECT ON
GRANT SELECT ON
-- user3
-- grant
table1 TO user1, user2;
table1 TO user3 WITH GRANT OPTION;
can select data from table1 and can
SELECT privilege to other users
Revoking object privileges
• Example:
– owner grants privilege to user1 with grant option
GRANT SELECT ON table1 TO user1 WITH GRANT OPTION;
– user1 grants privilege to user2
GRANT SELECT ON table1 TO user2
– owner revokes privilege from user1
REVOKE SELECT ON table1 FROM user1
-- privilege is also revoked from user2
Table privileges
• Two types of table privileges:
– DML (Data Modification Language):
• SELECT - permission to select data from a table
• UPDATE - permission to update a table, can be granted on specific
columns
• INSERT - permission to insert rows, can be granted on specific
columns
• DELETE - permission to delete rows
– DDL (Data Definition Language)
• INDEX - permission to create index on the table
• ALTER - permission to alter the table
• REFERENCES - permission to create foreign keys, can be granted
on specific columns
– ALL – grants all DML and DDL privileges
Privileges for creating a view
• In order to create a view user needs:
– create view system privilege or create any view
system privilege
– explicitly granted object privileges on all base
tables not owned by the creator of the view (ability
to select data from all base tables), or explicitly
granted SELECT ANY TABLE system privilege
– to grant other users privilege on a view, base table
privileges must be granted with GRANT OPTION
• Create view privilege is included in the
Resource role
Privileges for creating a view
CREATE USER test1/test1; GRANT DBA TO test1;
CREATE USER test2/test2; GRANT DBA TO test2;
CONNECT test1/test1;
CREATE TABLE table1 (id NUMBER);
CONNECT test2/test2;
SELECT * FROM test1.table1; -- ok, SELECT ANY DATA
CREATE VIEW test_view AS SELECT * FROM test1.table1;
-- error - insufficient privileges
CONNECT TEST1/TEST1;
GRANT SELECT ON table1 TO test2;
CONNECT TEST2/TEST2;
CREATE VIEW test_view AS SELECT * FROM TEST1.table1;
-- ok, explicitly granted privilege
GRANT SELECT ON test_view TO TEST3;
-- test3 cannot access test_view
Sequences and procedures
• Sequence privileges:
– SELECT - permission to select next value from a
sequence
– ALTER - permission to modify a sequence
GRANT SELECT ON s1 TO user1 WITH GRANT OPTION
• Procedure/function/package privilege:
– EXECUTE - permission to execute specific
procedure
GRANT EXECUTE ON proc1 TO user1
Roles
• Roles are used to group privileges
• Role can contain:
– System privileges
– Object privileges
– Other roles
• Predefined roles in every Oracle database:
– CONNECT – permissions needed to connect to the
database
– RESOURCE – permissions needed to create tables,
views, sequences in user's own schema
– DBA – database administrator privileges
Roles
• Role is created using CREATE ROLE statement:
CREATE ROLE role1
• Privileges are added to role using GRANT statement:
GRANT CREATE USER TO role1;
GRANT SELECT ON table1 TO role1;
GRANT CONNECT TO role1;
• Role is granted using the GRANT statement:
GRANT DBA TO user1;
• Role is revoked using the REVOKE statement:
REVOKE DBA FROM user1;
Granting roles
• To grant or revoke a role you need:
– GRANT ANY ROLE system privilege, or
– having the role granted with ADMIN OPTION
• Roles can be granted to other roles
• To grant privilege to a role the same privileges are
required as when granting privilege to a user
• Default database roles:
–
–
–
–
DBA, CONNECT, RESOURCE
EXP_FULL_DATABASE – for exporting
IMP_FULL_DATABASE – for importing
SELECT_CATALOG_ROLE – privileges for reading
dictionary views (DBA_ views)