Sunday, April 30, 2006

Oracle 9i - Index of notes

Credits & Disclaimer : The following are my Oracle 9i SQL notes prepared during the course of my preparation for Oracle DBA 9i Exam 1Z0-007 and mostly referenced from various Oracle publications. These are meant for my personal use.

In past I have prepared notes on paper many times but seldom have i been able to use them. As layers of dust settle on them, it gets kind of messy to touch them :) This blog, an online notebook, seems like an ideal solution to me, to be referenced on the move without need to carry bulky books.

Part I :
  1. Data Query
  2. Logical Operators (AND, OR, NOT etc) & Single row Functions
  3. DML & Locks
  4. DDL & Constraints
  5. Views & Top-N Analysis
  6. Sequences
  7. Database Link (note : scoll to bottom of privilege notes)
Part II:
  1. Creating Database manually / Restoring backed-up database
  2. Password authentication setup : Operating System authentication / password file authentication
  3. Control Files
  4. Redo Log Files
  5. Segments & Data Blocks - INITRANS-MAXTRANS / PCTFREE-PCTUSED-FREELIST
  6. Tablespaces
  7. Managing User (Create-Alter-Drop)
  8. Profiles
  9. Privileges (Grant-Revoke) & Database Link
  10. Roles(Create-Alter-Set-Drop)
  11. Auditing(Audit-Noaudit)
  12. Password Management script utlpwdmg.sql
  13. Views : Storage relationships (Segments & Extents)
  14. Views : Users-Quota-Profile-Resource cost-Resource Limit-Privileges

Tuesday, April 18, 2006

Oracle Sequences

Features :

Note : A sequence should be named after its intended use for clarity. However it can be used anywhere irrespective of its name.

Syntax:

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];


ALTER SEQUENCE sequence_name

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];


DROP SEQUENCE sequence_name;

INCREMENT BY : default sequence increment is by 1.

START WITH : default sequence starts with 1.

NOMAXVALUE : 10^27 for ascending sequence, -1 for descending sequence.

NOMINVALUE : 1 for ascending sequence, 10^26 for descending sequence.

CACHE : how many values Oracle preallocates & keeps in memory. default is 20.

USER_SEQUENCES data dictionary:

SELECT sequence_name, min_value, max_value, increment_by, last_number

from USER_SEQUENCES;

Note : The last_number displays the next available sequence number unless there are cached values.

Pseudocolumns NEXTVAL & CURRVAL:

Rules for using NEXTVAL & CURRVAL

Can be used in:

Can't be used in:

Note : CACHE is populated the 1st time the sequence is referred. NEXTVAL requests pull the values from the cache until the last value is used up, after which the next NEXTVAL request pulls another cache of sequences into the memory.

Gaps in Sequences:

Altering Sequences:

Usage Example:

CREATE SEQUENCE departments_seq2

START WITH 20

INCREMENT BY 20

MAXVALUE 999

NOCACHE

NOCYCLE;

ALTER SEQUENCE departments_seq2

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

INSERT INTO departments (department_id, departments_name, location_id)

VALUES (departments_seq.NEXTVAL, 'Navigation', 9152);

SELECT departments_seq.CURRVAL FROM DUAL;


Saturday, April 15, 2006

Oracle - Views & Top-N Analysis

View: A view is a logical table based on a table or another view. It is like a window through which data from tables can be viewed or changed. It is stored as a SELECT statement in the data dictionary. It's definition can be viewed in the USER-VIEWS data dictionary table.

Advantages of Views & its purpose :

Views options :

SYNTAX for creating a VIEW:

CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW viewname [(column alias[, column alias]...)]

AS subquery

[WITH CHECK OPTION] [CONSTRAINT constraint_name]

[WITH READ ONLY] [CONSTRAINT constraint_name];

Controlling column names by including aliases after the Create statement & prior to the subquery:

CREATE OR REPLACE VIEW empvw80 (id_number, name, sal, department_id)

AS

SELECT employee_id id_number, first_name||' '||last_name name , salary sal ,

department_id department_id

FROM employees

WHERE department_id=80;

Controlling column names by including aliases within the subquery:

CREATE OR REPLACE VIEW empvw80

AS

SELECT employee_id id_number, first_name||' '||last_name name , salary sal ,

department_id department_id

FROM employees

WHERE department_id=80;

Note : Using CREATE OR REPLACE a view can be created even when it already exists, thus replacing an existing one. This means that the view can be altered without dropping, re-creating and re-granting object privileges.

Creating complex views:

CREATE OR REPLACE VIEW dept_sum_vu (name, minimum, maximum,average)

AS

SELECT d.department_name, MIN(e.salary),Max(e.salary), AVG(e.salary)

FROM employees e, departments d

WHERE e.department_id=d.department_id

GROUP BY d.department_name;

Query a view just like any table :

SELECT * FROM empvw80;

Steps undertaken to executing a view based query:

DML operations on a VIEW:

Can't remove or modify or add a row if a view contains the following:

Can't modify or add a row if

Can't add a row if

Data can be added into a view unless it contains any of the above listed items or there are NOT NULL columns without default values in the base table that are not selected by the view. All required values must be present in the view.

WITH CHECK OPTION :

Can be used for the following:

  1. referential integrity checks

  2. enforce constraints at database level

  3. can also be used to protect data integrity but its use is very limited.

WITH READ ONLY : Denying DML operations.

SYNTAX for dropping a VIEW: DROP VIEW view-name;

Inline Views :

SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id) b
WHERE a.department_id=b.department_id
AND a.salary

SYNTAX for TOP-N Analysis: Used to display only the n top most or the n bottom-most records from a table based on a condition. These queries use a consistent nested query structure with following elements :

SELECT [column list...], ROWNUM

FROM (SELECT [column list...]

FROM tablename

ORDER BY Top-N-column)

WHERE ROWNUM<=N;

eg:

SELECT ROWNUM as RANK, last_name,salary
FROM (SELECT last_name,salary from employees ORDER BY salary DESC)
WHERE ROWNUM<=3;

SET LONG 600;

SELECT * FROM user_views;


Sunday, April 09, 2006

Oracle : DDL & constraints

NAME OF TABLE/COLUMS : Must begin with a letter.
SIZE : 1-30 characters
Allowed characters in a name : A-Z,a-z,0-9,_,$,#

Note : Name must not be duplicate another object owned by the owner. And it must not be an Oracle reserved word. Names are not case sensetive.

DATA TYPES

VARCHAR2(max_size) : Variable length Character data. (Min size:1; Max size:4000)

CHAR([size]) : Fixed length Character data, Oracle pads the values with blanks upto the declared length of the column. (default/Min size:1; Max size:2000)

NUMBER([n,m]) : n: total number of digits(1 to 38, default 38), m: number of decimal points(upto 38).

DATE : between Jan 1,4712 BC to Dec 31, 9999 AD. DATE columns are 7 bytes in length.

LONG : Variable length Character data, upto 2 gigabyte.

CLOB : Character data upto 4 gigabyte.
RAW(MAX_size) : Raw binary data. Max size is 2000.
LONG RAW : Raw binary data of variable length upto 2 gigabyte.
BLOB : Binary data upto 4 gigabyte.
BFILE : Binary data stored in external file; upto 4 gigabyte.
ROWID : A 64 base number system representing the unique address of a row in its table. These could be physical
ROWIDs or logical ROWIDs.

Single bytes or multibyte UNICODE character based data types :

NVARCHAR2(MAX_size), NCHAR ([size]), NCLOB : same as above but stores UNICODE data.

DDL COMMANDS

Summary of available DDL commands :


CREATE TABLE tablename
ALTER TABLE tablename

DROP TABLE tablename
TRUNCATE tablename
RENAME ... TO ...
COMMENT ON TABLE tablename IS '....' ;

Explainations :

CREATE TABLE [schema.]table
(column_name datatype [DEFAULT expression] [[CONSTRAINT constraint_name] constraint type]
[,column_name ....]
[,[CONSTRAINT constraint_name] constraint type (column,column...)]
[,CONSTRAINT ....] );

DEFAULT can be literal values, expressions, SQL functions. These can't be another column name or pseudocolumn.

CREATE TABLE tablename [(column [DEFAULT expression] [ [CONSTRAINT constraint_name] constraint type], [...])]
AS
SELECT [*|column list] FROM tablename2;

ALTER TABLE table_name
ADD (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

Note : The new columns is initially null for all the rows.

ALTER TABLE table_name
MODIFY (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

ALTER TABLE table_name
DROP (column, column,....) [CASCADE CONSTRAINTS];

ALTER TABLE table_name
DROP COLUMN column_name [CASCADE CONSTRAINTS];

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

ALTER TABLE table_name
SET UNUSED (column_name, column_name,.....);

ALTER TABLE tablename
DROP UNUSED COLUMNS;

alter table my_emp2
drop Primary Key
drop Unique (Lname)
modify (id number(6))
add (id2 number(8,4));

DROP TABLE tablename;

RENAME tablename1 TO tablename2;

TRUNCATE TABLE tablename;

Note : TRUNCATE is faster than DELETE TABLE for following reasons:

COMMENT ON TABLE tablename IS 'this is test table';

COMMENT ON COLUMN tablename.column IS ' testing column comment';

ALTER TABLE tablename
ADD [CONSTRAINT constraint_name] constraint type (column);

ALTER TABLE tablename
MODIFY column [CONSTRAINT constraint_name] NOT NULL;

ALTER TABLE tablename
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;

NOTE : DISABLE/ENABLE can be used in both CREATE TABLE/ALTER TABLE statements. CASCADE option causes any depended constraints also to be dropped or disabled.

DISABLEing a Primary Key or Unique Key constraint removes the unique index.

ENABLING creates the UNIQUE KEY or PRIMARY KEY indexes.If you enable a constraint, then all data in the table must fit the constraint. Enabling a primary key constraint disabled using CASCADE doesnot enable any foreign keys that are dependent upon the primary key.

Steps to take in case existing data violates a disabled Constraint:

CONSTRAINTS

Syntax :

Column Constraint Level :
column [CONSTRAINT constraint_name] REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Table Constraint Level :
column,... [CONSTRAINT constraint_name] FOREIGN KEY (column,...) REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Note : check constraints declared at table level can't reference other columns. All constraints can be defined at Table Level except NOT NULL. Composite unique/primary key/Foreign key contraints are created at table level declaration.

NOT NULL : can only be defined at column level. A NOT NULL constraint can only be defined if the table is empty or if the column has values for all the rows.

UNIQUE : Ensures uniqueness of column or column combination. Unless NOT NULL is also defined of the column, unique constraint allows any number of null values as NULL are not considered equal to anything. If the Unique constraint comprises more than one column eg. Unique(col1,c2) that group of column is called a Composite Unique Key. Can't have identical values in the non-null columns of a partially null UNIQUE key constraint.

PRIMARY KEY : Ensures uniqueness of column or column combination. No columns which are part of a composite primary key can contain a NULL. Only one Primary key is allowed per table.

Oracle enforces Unique & Primary key constraints by implicitly creating UNIQUE indexes on these columns/column combinations.

FOREIGN KEY : Referential integrity constraint. Designates a column or column combination as foreign key and establishes a relationship between a primary key or unique key in the same table or different table. A foreign key must match an existing value in the parent table or be NULL. Foreign keys are based on data values and are purely logical pointers.The rows in the parent table can't be deleted if it is referenced in the child table, unless ON DELETE CASCADE or ON DELETE SET NULL is specified. This default behaviour is called the restrict rule which disallows update or deletion of referenced data.

Note: A FOREIGN KEY can't be created on the child table until the parent table is created and the PRIMARY KEY is defined on that parent table.

CHECK constraints : Defines a condition that each row must satisfy.

The CHECK condition may be the same as SQL conditions except following:

CASCADE CONSTRAINTS : Used along with DROP COLUMN clause. It drops all referencial integrity constraints that refer to the primary & unique keys defined on the dropped columns. It also drops all multicolumn constraints defined on the dropped columns.

eg.
CREATE TABLE test1 (
column1 primary key,columns...,
CONSTRAINT constraint_name1 FOREIGN KEY (column1) references test1,
CONSTRAINT constraint_name2 CHECK (column1>0, column3>0);

Alter Table drop column1 ; -> error as column1 is a parent key
ALTER TABLE DROP column3 ; -> error because column3 is referenced by multi-column constraint_name2

Solution :
ALTER TABLE DROP (column1) CASCADE CONSTRAINTS; -> drops constraints primary key, foreign key & constraint_name2

ALTER TABLE DROP (column1,column2,column3); -> this is valid as all the columns referenced by the constraints defined on the dropped columns are also dropped.

DateTime Data Types

TIMESTAMP [(fractional_second_precision)][WITH TIME ZONE | WITH LOCAL TIME ZONE]

WITH TIME ZONE : timestamp + time zone displacement between local time & UTC(GMT). Can't be used for index/primary keys/unique keys.

WITH LOCAL TIME ZONE : Data stored in database is normalised to the database time zone.
Oracles returns the data in the user's local session time zone.

INTERVAL YEAR [(year precisiom)] TO MONTH

example:
INTERVAL '123-2' YEAR(3) TO MONTH : +123-02 (month value can be 0-11)
INTERVAL '123' YEAR(3) : +123-00
INTERVAL '300' MONTH(3) : +025-00

INVERVAL DAY[day precision] TO SECOND[fractional_second_precision]

example:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3) : +04 05:12:10.222
INTERVAL '7' DAY : +07 00:00:00
INTERVAL '180' DAY(3) : +180 00:00:00
INTERVAL '4 5:12' DAY TO MINUTE : +04 05:12:00
INTERVAL '4 5' DAY TO HOUR : +04 05:00:00
INTERVAL '5:12:10.1234567' HOUR TO SECOND(7) : +00 05:12:10.1234567

Data Dictionary Views :
All data dictionary tables are owned by SYS user.

Data dictionary views :

Prefixes:

Default data dictionary views available in Oracle 9i :

Note : C: check P: Primary key, R : Referential Integrity, U : Unique key. Not NULL is a check constraint

A SQL statement to get a useful table constraints view :

SELECT ucc.table_name, ucc.column_name, uc.constraint_type, ucc.constraint_name,

uc.r_constraint_name,uc.delete_rule,uc.status,uc.search_condition, uc.index_name
FROM user_constraints uc
JOIN user_cons_columns ucc
ON (ucc.constraint_name=uc.constraint_name)
ORDER BY ucc.table_name;


Saturday, April 08, 2006

Oracle - DML & Locks

INSERT INTO tablename [(column list ,....,....)]
VALUES
(,....,....)

Note : If column list is omitted then the value list must contain a value for each column in the table in the same order.

INSERT INTO tablename (column list....)
SELECT columns, ..., ...
FROM table2
WHERE condition;
Note 1: Using Sub-query in place of VALUE clause.
Note 2: Match the table1 column list to the column list of table2.

INSERT INTO tablename
SELECT * FROM table1;
Note 1: copy all rows into an existing table.

INSERT INTO tablename
(SELECT columns,...,...
FROM table2
WHERE clause [WITH CHECK OPTION]
)

VALUES (values....);

Note 1: Using Sub-query in place of tablename.
Note 2: Any rules on the columns of the base table must be followed.
Note 3: WITH CHECK OPTION limits the scope of the SQL command to the data-set fitting the Subquery. If a subquery is used in place of tablename in INSERT/UPDATE/DELETE statements specifying WITH CHECK OPTION, no changes that would produce rows not included in the sub-query will be permitted.

UPDATE tablename
SET column= value
[ , ..=...]

[WHERE condition];

Note 1: value : can be a value or a sub-query for the column.
Note 2: Specific rows are updated with WHERE clause. Without the WHERE condition all the rows are modified.

DELETE [FROM] tablename
[WHERE condition];
Note 1: Specific rows are deleted with WHERE clause. Without the WHERE condition all the rows are deleted.

MERGE INTO tablename
USING (table/view/subquery)
ON (join condition)
WHEN MATCHED THEN
[ UPDATE SET
col1=value, col2=value
]
WHEN NOT MATCHED THEN
[ INSERT [(column list)]
VALUES (values...,...,...)]
;

INTO : target table being updated or inserted upon
USING : source of data (table/view/subquery)
ON : condition of merge

TRANSACTION CONTROL
COMMIT
SAVEPOINT name
ROLLBACK [TO SAVEPOINT name]

Note : DDL/DCL statements automatically commits thereby implicitly committing current transaction.

Purpose:
COMMIT : Ends current transaction by making all pending data changes permanent & releasing space in the undo segment.

ROLLBACK : Ends current transaction by discarding all pending data changes. The old values are restored back from the undo segment.

SAVEPOINT : Since these are logical, there is no way to list the created savepoints.

SET AUTOCOMMIT [ON|OFF]: If SET then each DML statement is committed as soon as it is executed.

Status before commit:
Status after Commit:
Statement level rollback:
Read Consistency:
LOCKS

Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google