Oracle Database Administration

1
Oracle Database Administration

• Schema objects

2
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

3
Tables

• Tables are used to store data
• Tables can be temporary 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

4
Tables cont.

• Tables can be organized
• as heap default
• as index table data stored together with
  primary key data
• Tables can be partitioned
• large tables can be split into smaller partitions
• partitions are easier to manage than entire table
• Oracle can perform partition pruning scan only
  some partitions when executing a query

5
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

6
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

7
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

8
Table columns

• BINARY_FLOAT 32 bit floating point number,
  occupies 5 bytes
• BINARY_DOUBLE 64 bit floating point number,
  occupies 9 bytes

9
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

10
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

11
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)

12
Number column

• Value inserted Column specification Stored
  as
• 123.45 NUMBER
  123.45
• 123.45 NUMBER(3) 123
• 123.89 NUMBER(3) 124
• 123.45 NUMBER(4,2) error
• 123.45 NUMBER(4,-2) 100

13
Date, timestamp

• Date columns use client date and time formatting,
  e.g.
• INSERT INTO test VALUES ('2005-05-03',
• '2005-05-03 153005')
• -- 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 153005',
• 'YYYY-MM-DD HH24MISS')
• SELECT TO_CHAR(d1, 'YYYY-MM-DD'),
• TO_CHAR(d2, 'YYYY-MM-DD HH24MISS')

14
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 081627 2006-02-09 081627,000000
• DELETE FROM test
• INSERT INTO test values (SYSTIMESTAMP,
  SYSTIMESTAMP)
• SELECT FROM test
• --------------------------------------------------
  
• 2006-02-09 081735 2006-02-09 081735,076000

15
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)

16
Character case, quotes

• SQL language is case insensitive
• SELECT FROM TABLE
• select from table -- identical statements
• Select From Table
• Character literals are case sensitive
• SELECT FROM USER_TABLES WHERE TABLE_NAME
  'TEST'
• SELECT FROM USER_TABLES WHERE TABLE_NAME
  'test'
• -- different statements
• Character literals are enclosed by single quotes
• Double quotes can be used to quote column and
  table names
• CREATE TABLE "test" (ID NUMBER PRIMARY KEY)

17
Double quotes

• Double quotes, when used
• make the name case sensitive
• make it possible to use reserved word as
  identifier
• CREATE TABLE test (NUMBER NUMBER) -- error
• CREATE TABLE test ("NUMBER" NUMBER) -- ok
• CREATE TABLE test1 ("NUMBER" NUMBER, "Number"
  NUMBER,
• "number" NUMBER) ok
• When not using quotes, Oracle converts the name
  to upper case
• CREATE TABLE test (ID NUMBER)
• SELECT COUNT() FROM USER_TABLES WHERE TABLE_NAME
  'test' --gt 0 rows
• SELECT COUNT() FROM USER_TABLES WHERE TABLE_NAME
  'TEST' --gt 1 row

18
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

19
Indexes cont

• NULL values are ignored by an index (even unique
  index)
• CREATE TABLE test (id NUMBER UNIQUE)
• INSERT INTO test VALUES (1)
• INSERT INTO test VALUES (1) -- error
• INSERT INTO test VALUES (NULL)
• INSERT 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

20
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

21
Index examples
UNIQUE INDEX IND1 ON EMP(ID) INDEX IND2 ON
EMP(SALARY, ID) INDEX IND3 ON EMP(HIRE_DATE,
SALARY, ID) INDEX IND4 ON EMP(HIRE_DATE,
ID) INDEX IND5 ON EMP(ID, HIRE_DATE) SELECT
FROM EMP WHERE ID 5 SELECT FROM EMP WHERE
SALARY gt 200 SELECT FROM EMP WHERE SALARY gt
200 OR HIRE_DATE lt SYSDATE
5 SELECT FROM EMP WHERE
HIRE_DATE lt SYSDATE 5 ORDER
BY ID ASC
22
Index examples
UNIQUE INDEX IND1 ON EMP(ID) UNIQUE INDEX IND3
ON EMP(DEPT_ID, ID) UNIQUE INDEX IND2 ON
DEPT(ID) SELECT D. FROM EMP E, DEPT D
WHERE E.DEPT_ID D.ID AND E.SALARY gt
2000 SELECT D. FROM DEPT D WHERE EXISTS
(SELECT ID FROM EMP E WHERE
E.DEPT_ID D.ID)
23
Index examples
SELECT FROM EMP WHERE SALARY IS NOT
NULL SELECT FROM EMP WHERE SALARY IS
NULL SELECT FROM EMP WHERE SALARY IS NULL OR
SALARY lt 2000
24
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

25
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) gt 10000

26
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

27
Updating a view

• It is possible to create instead of trigger for a
  view which is not updateable
• Instead of trigger is a PL/SQL procedure that can
  execute any action, e.g.
• A view that contains average salary for a
  department is not updateable
• Instead of trigger can execute a code which
  modifies each employee salary when user tries to
  modify average salary on a view

28
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)

29
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

30
Synonyms cont.

• To create synonym, user needs
• CREATE SYNONYM or CREATE ANY SYNONYM privilege
• CREATE PUBLIC SYNONYM
• To drop synonym user needs
• to drop users own private synonym - no special
  privilege
• to drop other users private synonym - DROP ANY
  SYNONYM
• to drop public synonym - DROP PUBLIC SYNONYM