Normalization

Following the normalization guidelines usually means splitting tables into two or more tables with fewer columns. Primary and foreign key relationships are designed into the new smaller tables, so they can be reconnected with a join operation. The advantage of splitting the table is to reduce data redundancy.

Here's a brief guide to the standard normalization forms:

First Normal Form
At each row and column intersections, there must be one and only one value. Each column in a row can have only one value, and that value must be atomic.

Second Normal Form
Second normal form requires no non-key column be a fact about a subset of the primary key. Event non-key column must depend entirely on the entire primary key. The primary key must have an element of uniqueness. A primary key may be composed of one or more columns forming a unique key. The primary key does not change one created. The foreign key references an tables primary key. The primary to foreign key represents a one to many relationship.

Third Normal Form
Third Normal Forms Requires no non-key column depend on another non-key column.

Fourth Normal Form
Fourth Normal form forbids independent one-to-may relationships between primary key columns and non-key columns.

Fifth Normal Form
Fifth normal form breaks tables into the smallest possible pieces in order to eliminate all redundancy within a table.

 

               

SQL Plus Reports and Commands

set headsep - identifies a single character to split a title onto two or more lines
ttitle - Top Title
btitle - Bottom Title
column - Heading and format of a column
break on - Spaces between the section
compute sum - Calculate subtotals
set linesize - Set the maximum number of characters per line
set pagesize - Set the number of lines per page
set newpage - Set the number of lines between pages
spool - Move screen I/O to file I/O
set pause - Makes screen display stop between pages
save - Save an sql query to file
host - Sends commands to the host operating system
start - Execute sql from a file
edit - Edit sql stored in a file
define_editor - name the editor

Example:
column {field name} heading 'Enter Column Description'
    format ann | 9990.99 truncate | word_wrapped

Other Commands

list - Outputs the contents of the SQL Buffer
clear buffer - Removes the contents of the SQL Buffer
append - Add contents the end of the SQL Buffer
c\{search pattern}\{replace pattern} - Changes a search
    pattern with a replace pattern for the SQL Buffer.

Running SQL Statements

connect user/password@host

; or / -> to run SQL   @filename -> run the SQL in a file

Oracle Functions

Oracle String Functions

 

initcap(char) - Returns a char with the first letter of each word in uppercase
instr(char1, char2,n,m) - n=position m=occurance returns the numerric position of char2 in char1
length(char) - Length of char
lower(char) - Returns char with all lower case
lpad(char1,n,char2) - Return a char with char1 left pad with char2
ltrim(char) - Remove all leading spaces
rpad(char1,n,char2) - Returns char with char1 right pad with char2
rtrim(char) - Remove all trailing spaces
substr(char,m,n) - Returns substring start at m for n characters
upper(char) - Converts all characters to uppercase
chr(n) - Returns the ascii code equivalent
concat(char1, char2) - Return a char which is the result of chaar1 and char2

 

Oracle Time Functions

 

ADD_MONTHS(d,n) - Add n months to d date
LAST_DAY(d) - Returns last day of the month
MONTHS_BETWEEN(d1,d2) - Difference between d1 and d2
ROUND(d,fmt) - Round to format
TO_CHAR(string,format)
TO_DATE(string,format)

 

Oracle Number Functions

 

ABS(value) - Absolute value
CEIL(value) - Smallest integer larger than equal to value
COS(value) - Cosine value
EXP(value) - e raise to value
FLOOR(value) - Largest value smaller than equal to value
ROUND(value) - Rounding of value to precision
SIGN(value) - 1 if positive -1 if negative
SQRT(value) - Square root of value
TAN(value) - Tangent of value
TRUNC(value) - Value truncated to precision

 

Oracle Group Functions

 

Avg(value) - Average of value for a group of rows Count(value) - Count rows of columns
Max(value) - Maximum of all value for groups of rows
Min(value) - Minimum of all values for groups of rows
Sum(value) - Sum of all values for groups of rows

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Tables

Creating a Table

CREATE TABLE {table_name}
(
    {field1} VARCHAR2(10),
    {field2} NUMBER(10)
)
PCTFREE 30
PCTUSED 60
TABLESPACE (tablespace_name)
STORAGE
(
    INITIAL integer
    NEXT integer
);

Adding a Primary Constraint

ALTER TABLE {A_TABLE} ADD (
    CONSTRAINT {A_TABLE_PK}
    PRIMARY KEY ({A_TABLE_IDX})
    USING INDEX
    TABLESPACE {MY_TABLESPACE}
    PCTFREE 10
    STORAGE
    (
    INITIAL 20K
    NEXT 40K
    )
)

Adding a Foreign Key Constraint

ALTER TABLE (table_name) ADD
    (
    CONSTRAINT (foreign key constraint name)
    FOREIGN KEY
    (field name )
    REFERENCES primary_table_name
    (
    primary_table_primary_index_field
    )


* The foreign key references a unique key in the primary table. The relation is a one-to-many relationship.

Rebuilding Indexes

alter index {my_index_idx} rebuild;

* Does not drop the constraints


Stored Procedures


See Also

See Also, Defining and using Cursors (Article Coming Soon)
See Also, Defining and using Functions (Article Coming Soon)

 

Stored Procedures

CREATE OR REPLACE PROCEDURE {procedure name} 
( {argument} IN {datatype} ) IS v_variable {datatype}; 
/*Variables types:
   
   VARCHAR(X);
   VARCHAR2(X);
   VARCHAR2;
   CHAR(X);
 
   NUMBER(P,S);   
   P-Precision is the number of digits.  
   S-Scale is the number digits to 
   the right of the decimal.
   NUMBER(X);
   NUMBER;
   LONG;          
   Holds up to 32,760 bytes of data
   DOUBLE PRECISION;
   FLOAT;
   INT;
   REAL;
 
   DATE;
   RAW(X) 
   Holds up to 32,760 bytes of data
   LONG RAW; 
   Holds up to 32,760 bytes of data. 
   Note the Database field type LONG RAW 
   holds up to 2 gigabytes of data.
 
   RECORD;
   TABLE;
   VARRAY;
 
   LOB;
   CLOB;
 
   v_variable_c1  VARCHAR2(20);            
   Creates a 20 character variable length field
   v_variable_c2  CHAR(10);                            
   Create a 10 character fixed length field 
   - max size 255 characters
   v_variable_c3  VARCHAR2;                         
   Variable length not to exceed 2000 characters
 
   v_variable_n1  table_name.field_name%TYPE;     
   Determine the variable type by referencing 
   its schema table-field type
   v_variable_n2  NUMBER;
   v_variable_n3  NUMBER := 3;
   v_variable_n4  NUMBER(10);
   v_variable_n5  NUMBER(10,2);
   v_variable_n6  LONG;
   v_variable_n7  FLOAT;
   v_variable_n8  REAL;
 
   TYPE t_my_record IS RECORD
   (
   v_variable1              VARCHAR2(8)
   ,v_variable2 NUMBER(10)
   ,v_variable3 DATE
   );
 
   my_record t_my_record;
 
   TYPE t_my_table is TABLE OF VARCHAR2(10)   
   Similar to an array structure 
   in Visual Basic
 
   INDEX BY BINARY_INTEGER;
 
   my_table t_my_table;
 
BEGIN 
   Insert your code here
 
   v_variable_c1 := 'Hello World';
   v_variable_n2 :=10;
 
   Conditional Logic
 
   IF v_variable_n2 = 1 THEN
                   v_variable_c2 := 'Exact Match';
   ELSIF v_variable_n3 > 2 THEN
                   v_variable_c2 := 'Greater Than Match';
   ELSE
                   v_variable_c3 := 'None of the Above';
   END IF;
 
   my_record.v_variable1:='ABC';
   my_record.v_variable2:=3;
   my_record.v_variable3:=TO_DATE('11-JAN-1999','DD-MON-YYYY');
 
   my_table(1)='A';
   my_table(2)='B';
 
      
   v_variable_n2 value is 10 therefore the 
   first condition fails. v_variable_n3 is initialized
   with the value of 3, therefore, the condition is 
   true and a value of 'Greater Than Match'
   is assigned to v_variable_c2.
   
 
   LOOPS
 
   v_variable_n2:=0;
   LOOP
                   v_variable_n2:=v_variable_n2+1;
                   EXIT WHEN v_variable_n2 > 10;
   END LOOP;
 
   v_variable_n2:=0;
      WHILE v_variable_n2<10 LOOP
                   v_variable_n2:=v_variable_n2+1;
   END LOOP;
 
   FOR v_variable_n2 in 1..10 LOOP
 
   END LOOP;
 
END {procedure name};

----------------------------------------à>>>

     
Creating a Database


Creating a Database

 

Plan Database File Locations

Three control files

Three groups of redo log files, with each group having two members

Create a file structure for the data files

Creating a Database Manually

 
   1. Decide on a unique instance name and database character set
   2. Set the operating system variables
                   (UNIX)
                                  ORACLE_HOME
                                  ORACLE_SID
                                  ORACLE_BASE
                                  ORA_NLS33
                                  PATH
 
                   (NT)
                                  ORADIM80 -NEW -SID u16
                                  -INTPWD password -STARTMODE auto
                                  -PFILE ORACLE_HOME\DATABASE\initu16.ora
 
                                  You must decide the SID, Password, and
                                  Create the parameter 
                                  file (initu16.ora)
 
                                  SET ORACLE_SID=u16
                                  SET LOCAL=2:u16
 
                                  Statement 1 : Make u16 the current SID
                                  Statement 2 : Override the LOCAL environment variable
 
 
   3. Prepare the parameter file
 
                   use init.ora as a parameter file template
 
                   db_name =  Eight characters or fewer that identify the database
 
                   control_files = the location of three control files
 
                   DB_BLOCK_SIZE = Determines the database block size (can not
                   change after the database has been created)
 
 
 
   4. Create a password file
 
   5. Start the instance
 
                   STARTUP NOMOUNT pfile=initu16.ora
 
   6. Create the database
                   
                   MANUALLY FROM Server Manager
 
                                  CREATE DATABASE "U16"
                                                 MAXLOGFILES 6
                                                 MAXLOGMEMBERS 6
                                                 MAXDATAFILES 30
                                                 MAXLOGHISTORY 100
                                                 ARCHIVELOG
 
                                  LOGFILE
                                                 GROUP 1
                                                                'E:\DATA\U16\GROUP1\log1a.rdo' SIZE 200K,
                                                                 'E:\DATA\U16\GROUP1\log1b.rdo' SIZE 200K
                                                 GROUP 2
                                                                'E:\DATA\U16\GROUP2\log2a.rdo' SIZE 200K,
                                                                'E:\DATA\U16\GROUP2\log2b.rdo' SIZE 200K
                                                 GROUP 3
                                                                'E:\DATA\U16\GROUP3\log3a.rdo' SIZE 200K,
                                                                'E:\DATA\U16\GROUP3\log3b.rdo' SIZE 200K
                                                  DATAFILE
                                                                'E:\DATA\U16\DATAFILES\system01.dbf'
                                                 size 30M
 
                                                 CHARACTER SET WE8ISO8859P1;                                                      
                                  
                                  Database
                                                 select name,created,log_mode from v$database;
 
                                  Thread
                                                 select status, instance from v$thread;
 
                                  Datafiles
                                                 select name from v$datafile;
                   
                                  Logfiles
                                                 select member from v$logfile;
 
                                  Controlfiles
                                                 select name from v$controlfile;
 
                                  Verify System Creation
 
                                                 select file_name from dba_data_files
                                                 where tablespace_name='SYSTEM';
 
                                  Look at the database users
 
                                                 select username, created from dba_users;
 
 
   7. Run scripts to generate the data dictionary and
   accomplish postcreation steps.
   
 
                                  Create the data dictionary
                                                 CATALOG.SQL
 
                                  prostcreation objects
                                                 CATPROC.SQL
 
   8. Maintaining Redo Log Files
                   
                   (Determine Archiving Status)
 
                   select log_mode from v$database;
 
                   (Determine is Automatic Archiving is enabled)
                   
                   select archiver from v$instance;
 
                   (if you need to add a new group of redo logs)
 
                   ALTER DATABASE ADD LOGFILE          
                   (                              
                                  'c:\data\log3a.rdo',                    
                                  'e:\data\log3b.rdo' 
                   ) size 200k;
 
                   select * from v$log_file;
 

Creating a Database using Instance Manager

Step 1. Start Instance Manager Press the New Button Input a SID (4 characters) ->abcd Step 2. Enter the DBA Authorization password and authenication Step 3. Press the Advanced Button * The Database Name must be the same as the Database Name in the initabcd.ora file * Enter the max logfile value and the max group member value * Design the location of your datafiles, logfiles, archive files, and control files * Change the logfile location and name to meet your design * Set the location of the Parameter file Defining Parameters in the Parameter File db_name = {myDBName}#database name using to identify the database db_files = 30 #maximum allowable number of database files #control file list (Created by the Instance Manager) control_files = (D:\orant\DATABASE\ctl1{SID}orcl.ora, D:\orant\DATABASE\ctl2{SID}orcl.ora, D:\orant\DATABASE\ctl3{SID}orcl.ora) #database will be compatible with software of this version compatible = 7.3.0.0.0 #db_file_multiblock_read_count= number of database blocks to read with each I/O. #db_file_multiblock_read_count = 8 # INITIAL # db_file_multiblock_read_count = 8 # SMALL db_file_multiblock_read_count = 16 # MEDIUM # db_file_multiblock_read_count = 32 # LARGE # db_block_buffers = number of database blocks cached in memory db_block_buffers tells the oracle kernel the size of the area that stores database read from the disk #db_block_buffers = 200 # INITIAL # db_block_buffers = 200 # SMALL db_block_buffers = 550 # MEDIUM # db_block_buffers = 3200 # LARGE Size in bytes of the shared pool #shared_pool_size = 6500000 # INITIAL # shared_pool_size = 3500000 # SMALL shared_pool_size = 6000000 # MEDIUM # shared_pool_size = 9000000 # LARGE Number of redo blocks for checkpoint threshold log_checkpoint_interval = 10000 Maximum number of user processes #processes = 50 # INITIAL # processes = 50 # SMALL processes = 100 # MEDIUM # processes = 200 # LARGE DML locks - one for each table modified in a transaction #dml_locks = 100 # INITIAL # dml_locks = 100 # SMALL dml_locks = 200 # MEDIUM # dml_locks = 500 # LARGE Redo circular buffer size #log_buffer = 8192 # INITIAL # log_buffer = 8192 # SMALL log_buffer = 32768 # MEDIUM # log_buffer = 163840 # LARGE Number of sequence cache entries #sequence_cache_entries = 10 # INITIAL # sequence_cache_entries = 10 # SMALL sequence_cache_entries = 30 # MEDIUM # sequence_cache_entries = 100 # LARGE #sequence_cache_hash_buckets = 10 # INITIAL # sequence_cache_hash_buckets = 10 # SMALL sequence_cache_hash_buckets = 23 # MEDIUM # sequence_cache_hash_buckets = 89 # LARGE # audit_trail = true # if you want auditing # timed_statistics = true # if you want timed statistics max_dump_file_size = 10240 # limit trace file size to 5 Meg each Start the Archiver Process log_archive_start = true # if you want automatic archiving LOG_ARCHIVE_DEST = E:\{db_name}\ARCHIVE #location of the archive directory # define directories to store trace and alert files background_dump_dest=%RDBMS73%\trace user_dump_dest=%RDBMS73%\trace Size of database block in bytes. Db_block_size can not be changed after database creation db_block_size must be a multiple of 512K db_block_size = 8192 Number of job queue processes to start snapshot_refresh_processes = 1 Password file usage remote_login_passwordfile = shared Enable text searching text_enable = true Step 4. Create your database Once the instance has been started, it is an idle instance. Steps to starting the database From a DOS Prompt set ORACLE_SID=abcd set LOCAL =2:abcd svrmgr23 (NT) connect internal/password startup pfile=initabcd.ora

Creating your development environment

Step 1. Create tablespaces with associated Datafiles Consider the following tables and design the location for storage of the datafile Determine if the tablespace is permenant or temporary - [User] Data tablespace stores data: tables, snapshots, views, functions, procedures, and triggers - [Index] Data tablespace stores index, primary key constraints - [Temporary] Data tablespace stores sort data and is a temporary tablespace type - [Rollback] data tablespace stores rollbaack segments - [Work] data tablespace allows the user to create personal tables (helpful for reports) See DBA Administrative Tasks
Step 2. Create Application role CREATE ROLE APPLICATION_ROLE NOT IDENTIFIED; GRANT ANALYZE ANY, CREATE CLUSTER, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE ROLE, CREATE SESSION, ALTER SESSION, CREATE SEQUENCE, CREATE SNAPSHOT, CREATE SYNONYM, CREATE ANY SYNONYM, DROP ANY SYNONYM, CREATE TABLE, FORCE TRANSACTION, CREATE TRIGGER, CREATE VIEW TO APPLICATION_ROLE; Step 3. Create User role CREATE ROLE USER_ROLE NOT IDENTIFIED; GRANT CREATE DATABASE LINK, CREATE SESSION, ALTER SESSION, CREATE SNAPSHOT, CREATE SYNONYM, CREATE TABLE, CREATE VIEW TO USER_ROLE; Step 4. Create table privilege roles (build the schema - assign object privileges latter) CREATE ROLE APPL_1_ROLE; CREATE ROLE APPL_2_ROLE; Step 5. Create application/programmer users CREATE USER {application_user} IDENTIFIED BY PASSWORD DEFAULT TABLESPACE {data tablespace name} TEMPORARY TABLESPACE {temporary tablespace name} QUOTA UNLIMITED ON {data tablespace name} QUOTA UNLIMITED ON {index tablespace name} QUOTA UNLIMITED ON {temporary tablespace name}; GRANT APPLICATION_ROLE TO {application_user}; GRANT {APPL_1_ROLE} TO {application_user}; Step 6. Create online users CREATE USER {online_user} IDENTIFIED BY PASSWORD DEFAULT TABLESPACE {data tablespace name} TEMPORARY TABLESPACE {temporary tablespace name} QUOTA 5M ON {data tablespace name} QUOTA UNLIMITED ON {index tablespace name} QUOTA 10M ON {temporary tablespace name}; GRANT {APPL_1_ROLE} TO {online_user}; Step 7. Create data schema (tables,indexes, primary and foreign contraints, snapshots, views, function, packages, procedures, and synonyms) 1. Create your tables 2. Create primary and foreign key constraints 3. Create indexes 4. Create Snapshot, Views 5. Create Functions, Packages, and Stored Procedures 6. Create Database Links 7. Create Private and Public Synonyms Step 8. Assign object priviledges to the table privilege roles GRANT SELECT,UPDATE,DELETE,INSERT ON myTable TO {APPL_1_ROLE}; GRANT EXECUTE ON myFunction TO {APPL_1_ROLE};

-------------------------------------------à>

Control File Backups



Control File Backups

Backup a Binary Version of the Control File

   ALTER DATABASE BACKUP CONTROLFILE TO 'd:\orant\control_backup'

Backup a Text Version of the Control File

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

------------------------------------------à

 



Logfiles


All About Logfiles

Resetlogs option

   Immediately perform a complete consistent cold backup.
 
   1) Creates a new incarnation of 
   the database, putting a new SCN in 
   the headers of all database files
   2) Resets the log sequence number to 1
   3) Reformats the online redo log files if they exists.
 

Unintentionally Restoring Online Redo Logs

   When restoring a database, it is possible to accidentally 
   restore the online redo logs.  This forces an incomplete 
   recovery instead of a complete recovery.

Status and Location

   select 
   a.group# 
   ,a.members 
   ,a.status 
   ,a.sequence# 
   ,a.bytes
   ,b.status
   from v$log a, v$logfile b
   where
   a.group#=b.group#

Switching a Logfile

   Alter System switch logfile

Adding Online Redo Log Groups

   Alter Database add logfile
   ('e:\log\log3a.rdo',
    'f:\log\log3b.rdo'
   ) SIZE 1M
 
   The Oracle Server automatic numbers the group
 
   Log File should fill up every 30 to 90 minutes.  
   Therefore, you should adjust the size accordingly.

Adding a Logfile Member

   Alter Database Add Logfile Member
   'e:\log\log4a.rdo' TO GROUP 3
 
   The following statement inserts a 
   log member into an existing Log Group.

Dropping Online Redo Log Groups

   Alter Database Drop logfile Group 3;
 
   The previous statement drops the redo logfile Group identified
   as Group 3.

Dropping Online Redo Log Members

   Alter Database drop logfile member
   'e:\log\log3a.rdo'
 
   The previous statement removes 
   the logfile member 'e:\log\log3a.rdo'
   from GROUP 3.

Clearing a Corrupted Logfile

   Alter Database Clear Logfile
 
   Note: The current redo log of an open thread 
   can never be cleared.
 
   The current log of a close thread can be 
   cleared by switching logs in the closed thread.

Loss of Current Redo Logs

   If database is open but in a Hung State
               * determine the current log group
                           select * from v$log
               * alter database clear unarchived 
               logfile group #nbr
               * perform a full database backup
 
   If the database is closed (Incomplete recovery)
 
               1) select * from v$log ->determine 
               the current log sequence number
               2) recover until cancel  
               3) enter cancel when the archive 
               number equals the log sequence number
               4) alter database open resetlogs;

Archiving Unarchived Redo Log Files

   If you do not have all archived redo logs 
   produced during backup, you cannot recover the
   backup because you do not have all the redo 
   records necessary to make it consistent.
   Archive the current log file ensuring the 
   redo necessary for recovery
 
   ALTER SYSTEM ARCHIVE LOG CURRENT;
 

Archiving all the Non-current Redo Logs

   ALTER SYSTEM ARCHIVE LOG ALL;

---------------------------à>>>

 



Rollback Segments



Rollback Segments

Creating a Rollback Segment

CREATE ROLLBACK SEGMENT {myRBS01}
TABLESPACE {myrollback_tablespace}
STORAGE
(
   INITIAL 100K
   NEXT 100K
   MINEXTENTS 20
   MAXEXTENTS 121
)
 

Bringing a Rollback Segment Online

    alter rollback segment {myRBS01} online;
   
    rollback segments need to be entered into the init{sid}.ora file
 
    ROLLBACK_SEGMENTS=({myRBS01})

-------------------------------------------------------------à>>

 


The Parameter File



Name

Value

Description

background_dump_des

bdump

Detached process dump directory

checkpoint_process

TRUE

Create a separate checkpoint process

control_files

 

control file names list

core_dump_dest

 

core dump destination

cput_count

6

Number of cpus for the instance

db_block_buffers

144000

number of database blocks cached in memory

db_block_size

8192

Size of the database block size

db_domain

WORLD

global database name

db_file_multiblock_read_count

16

Database block to be read each I/O

db_file_simultaneous_writes

4

Maximum simultaneous writes per database file

db_files

1022

Maximum allowable number of database files

db_name

you provide the name

Database Name

db_writers

1

The number of database writers

global_names

FALSE

Enforce that database links have same name as remote database

log_archive_buffer_size

32

size of each buffer in log file blocks

log_archiving_buffers

5

Number of buffers allocate for archiving

log_archive_dest

 

Archival destination

log_archive_duplex_dest

 

duplex the archive files

log_archive_format

%t_s.arch

file format of the archive files

log_archive_start

TRUE

Start archival process on SGA

log_buffer

3145728

Redo circular buffer size

log_checkpoint_interval

31457280

Indicates the number of redo blocks for checkpoint threshold

log_checkout_timeout

0

Maximum time interval in seconds between checkpoints

log_files

255

Maximum allowable number of log files

max_dump_file_size

 

maximum size of the trace file

open_cursors

50

maximum number of cursors per process

optimizer mode

CHOOSE

Optimizer Mode

oracle_trace_enable

FALSE

Oracle TRACE

os_roles

FALSE

Retrieves roles from the operating system

processes

100

Maximum number of user processes

remote_login_passwordfile

NONE

Password file usage

rollback_segments

rbs01,rbs02

Undo segment list

sequence_cache_entries

12

Number of sequence cache entries

shared_pool_reserved_size

0

Size in bytes of reserved area of shared pool

shared_pool_size

262144000

Size in Bytes of shared pool

sort_area_retained_size

5422880

Size of in-memory sort work area

sort_area_size

20971520

Size of in-memory sort area

sort_write_buffer_size

65536

Szie of each sort direct write buffers

sort_write_buffers

4

Number of sort direct write buffers

sql_trace

FALSE

Enable SQL Trace

timed_statistics

TRUE

Maintain internal timing statistics

transactions

126

number of concurrent active transactions

user_dump_dest

 

user process dump directory

-----------------à>>

 

Creating an Oracle Job


Create a Job

     VARIABLE v_jobnum NUMBER;
 
     BEGIN

         dbms_job.submit(:v_jobnum,
         'my_stored_procedure;', sysdate,'sysdate+1');

     END;

Run an Existing Job

     dbms_job.run(job_number);

Remove a Job from the Job Queue

     dbms_job.remove(job_number);

Review Job Status

     select
     job,
     log_user,
     priv_user,
     schema_user,
     next_date,
     next_sec,
     interval,
     failures,
     total_time,
     what
     from dba_jobs;
 
     select * from user_jobs;
 
     select * from dba_jobs_running;

-----------------------à>>

Database Assistant



Step

Instructions

Values

Create Database

   

Next

Custom

Custom allows you to define the parameters, locations of: datafiles, logfiles, archive files. Define tablespace names and parameters.

Next

Replication

Bypass

Next

Database Size

Choose between three sizes for the database: small, medium, or large. Verify the character set is WE8ISO8859P1.

Next

Instance Information

Enter in the name of the database, the System Identification (4 characters), the file location and name of the initialization file, and the internal user password and confirmed password.

Next

Control Files

Enter the name and location for the control files. Specify the maximum number of datafiles, maximum number of logfiles, and maximum number of log members. I prefer three as a number of the maximum log members.

Next

Initial Tablespace Information

Every Oracle Database need as minimum of the following tablespaces: user (a tablespace container for tables,views,snapshots), index (a tablespace container for indexes and primary key constraints), tmp (a tablespace container for sorting records), rb (a tablespace container for the rollback segments), and sys ( the system tablespace). For each tablespace specify: the location of the datafile and size, the initial extent size, the next extent size, the minimum number of extents, the maximum number of extents, or unlimited extents.

Next

Redo Log Information

To activate archiving check the archive log option. Specify the destination of the archive directory.

Next

SGA Information

Select the defaults. Later change the parameters after analyzing system statistics. Leave this section to the pros.

Next

Trace Director Information

Specify the Trace File Directory and the User Process Directory.

Next

Create the Database Now

Press the previous button may reset some of the fields. You will notice which fields are reset to default values as you move forward.

Finish

----------------à>

 

 

A Collection of DBA How To's

Overview

Managing Datafiles

Explain How to Manage Datafiles

 

 

Creating Tablespaces Dimensions

Explain the Structure and Maintenance of Tablespaces

 

 

Creating Indexes

Explain how to create an index

 

 

Managing Rollback Segments

Explain how manage a rollback segment

 

 

Creating Snapshots

Explain how to create an Oracle Snapshot

 

 

Creating Tables

Explain how to create a table

 

 

Creating Constraints

Primary and Foreign Key Constraint

 

 

Creating User and Roles

Managing Users
Explain Roles and Security

 

 

Creating Synonyms

Creating Public and Private Synonyms

 

 

Creating Packages, Stored Procedures, and Functions

Package
Procedure
Function

 

 

High Water Mark

Deallocating the High Water Mark

 

 

Pinning Objects into Memory

DBMS_SHARED_POOL

 

 

Changing the Next Extent Size

Next Extent

 


Creating Tables in Oracle

 
   Syntax to create a table
 
   CREATE TABLE my_table_name(
                   {field1} VARCHAR2(10),
                   {field2} NUMBER(10)
   )
   PCTFREE  30
   PCTUSED  60
   TABLESPACE a_tablespace_name
   STORAGE  
   (
     INITIAL   integer
     NEXT   integer
   );
 
   Processes to setting up the data schema
 
   1) Define and create the tables
   2) Define and create the primary key constraints
   3) Define and create the foreign key constraints
   4) Define and create the indexes
   

Creating Primary and Foreign Key Constraints

   Syntax to add a constraint to an table
 
   Primary Key Constraint
 
   ALTER TABLE my_table_name ADD (
         CONSTRAINT CONSTAINT_NAME_PK
         PRIMARY KEY (FIELD_NAME)
   USING INDEX 
   TABLESPACE a_tablespace_name
   PCTFREE 10
   STORAGE (INITIAL 20K NEXT 20K PCTINCREASE 0)
   )
 
   Foreign Key Constraint
 
   ALTER TABLE my_table_name ADD (
                   CONSTRAINT (foreign key constraint name)
                   FOREIGN KEY (field name )
                   REFERENCES  primary_table_name
   (
                   primary_table_primary_index_field
   )
)

 


Creating Indexes in Oracle

 
   CREATE {UNIQUE} a_index_name ON a_table_name
   (
         field
   )
   PCTFREE  10
   TABLESPACE (table_space_name)
   STORAGE (INITIAL 20K NEXT 20K PCTINCREASE 0);
 
   If UNIQUE is specified the key 
   in the index is force to be unique otherwise
   the index is assumed to be non-unique.

TO THE TOP


Creating a snapshot

 
   Syntax for creating a Snapshot
 
   CREATE SNAPSHOT my_snapshot_name
   PCTFREE 5 PCTUSED 60
   TABLESPACE a_tablespace name
   STORAGE (INITIAL 5M NEXT 2M)
   USING INDEX STORAGE(INITIAL 500K NEXT 25K)
   REFRESH FAST NEXT sysdate + 1 
   as
                   select * from table;
 

TO THE TOP


Managing a rollback segment

   Creating a rollback segment
   Example of a large rollback segment
 
   CREATE ROLLBACK SEGMENT a_rollback_segment_name
   TABLESPACE a_tablespace_name
   STORAGE
   (
   INITIAL 2M
   NEXT 2M
   MINEXTENTS 20
   MAXEXTENTS UNLIMITED
   )
 
   Taking a rollback segment offline
 
   ALTER ROLLBACK SEGMENT a_rollback_segment_name OFFLINE;
 
   Taking a rollback segment offline
 
   ALTER ROLLBACK SEGMENT a_rollback_segment_name ONLINE;
 
   Dropping a rollback segment
   
   DROP ROLLBACK SEGMENT a_rollback_segment_name;
 
   Forcing Transactions to use a specific Rollback Segment
   SET TRANSACTION USE ROLLBACK SEGMENT {segment name}
 

TO THE TOP


Tablespace Administration

Database Structure

   Concepts:
 
   * A tablespace can only belong to one database
   * Tablespaces can be brought online or offline
   * Physical data for the tablespace is stored 
   in a datafile.  Datafiles are composed of Operating
   System blocks.  Therefore, datafiles are binary 
   files stored on a harddrive device.
 
   Control files point to the location of the 
   datafiles.  Backup and duplex your control file.
   See backup and recovery section for techniques 
   control file restoration.
 
   Red Flags:
 
   * tablespaces and datafiles contain your data.  
   Dropping tablespace and datafiles either
   by Oracle or the Operating system can 
   have serious consequences.
 
   Managing Tablespaces
 
   CREATE TABLESPACE a_tablespace_name
   DATAFILE 'c:\data\wrk.dbf' SIZE 50M
   DEFAULT STORAGE
   (INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 1)
   PERMENANT;
 
   FORM:
 
   CREATE TABLESPACE tablespace
   DATAFILE filespec
   [MINIMUM EXTENT integer[K][M]]
   [DEFAULT 
                   INITIAL integer[K][M]
                   NEXT       integer[K][M]
                   MINEXTENTS integer
                   MAXEXTENTS integer|UNLIMITED
                   PCTINCREASE integer
]
   [PERMENANT|TEMPORARY]
   [ONLINE|OFFLINE]

TO THE TOP


Data File Administration

Adding Datafiles to a Tablespace

   ALTER TABLESPACE a_tablespace_name
   ADD DATAFILE
   'C:\DATA\another_file.dbf' SIZE 100M;
 
   Checking on Tablespace Status
 
   Select * from V$TABLESPACE;
 

Changing the Size of a DataFile

 
   ALTER DATABASE DATAFILE
   'c:\data\datafile1.dbf' RESIZE 200M;

Bringing a DataFile Online

   alter database datafile 'c:\data\datafile1.dbf' online

Taking a Datafile Offline

   alter database datafile 'c:\data\datafile1.dbf' offline

Moving Data Files

   Alter database rename file
   'c:\data\datafile1.dbf' to
   'c:\data\datafile2.dbf';

Checking on Data File Status

   Select * from DBA_DATA_FILES;

TO THE TOP


Creating Users and Managing User Security

 
Users and Security
 
Account Locking
   * Accounts can be locked to prevent 
   a user from logging on to the database.
 
Authenication Mechanism
   * Externally -> At the Operating system Level
   * Password -> From the password file
 
Roles Privileges
   * A user can be granted privileges 
   indirectly through the use of roles.
 
 
   Script for Creating a New User 
   (Created by the Security Manager)
 
CREATE USER a_UserId
IDENTIFIED BY a_Password
DEFAULT TABLESPACE a_tablespace_name
PASSWORD EXPIRE;
 
 
CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
{TEMPORARY TABLESPACE tablespace}
{QUOTA {integer [K | M ] | UNLIMITED} ON tablespace
[QUOTA {integer [K | M] | UNLIMITED} ON tablespace ..]
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}]
[PROFILE {profile | DEFAULT}]
 
 
Users who are not assigned an Oracle 
password are designated as IDENTIFIED EXTERNALLY.  
Oracle depends on the operating system 
for authenication of the user.  To use 
external authenication, you must set the
OS_AUTHENT_PREFIX in the database parameter file.
 
Using Oracle Security Manager
User->Create
 
Enter Name, Profile, Authenication(Password) 
and Tablespaces(Default,Temporary)
 
   
Methods to Reset password
 
ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}]
 
 
Methods to Drop a User
DROP USER {User_Name}
 
DROP USER {User_Name} CASCADE
 
The drop user command with CASCADE drops 
the user and all objects owned by the user.
 
Create Profile
 
    CREATE PROFILE {profile_name} LIMIT
   SESSIONS_PER_USER   max_value  
   Number of Concurrent sessions
   CPU_PER_SESSION max_value      
   Total CPU time measured 
   in hundredths of seconds
   CPU_PER_CALL max_value
   CPU time measured 
   in hundredths of seconds
   CONNECT_TIME max_value                    
   elasped time measure in minutes
   IDLE_TIME max_value                             
   periods of inactive time 
   measured in minutes
   LOGICAL_READS_PER_SESSION 
   max_value               Number of data blocks
   LOGICAL_READS_PER_CALL max_value 
   Number of data blocks
   COMPOSITE_LIMIT max_value
   PRIVATE_SGA max_value                      
   Private space in the 
   SGA measured in bytes
 
max_value:= {integer|UNLIMITED|DEFAULT}
 

TO THE TOP


Creating Roles and Granting Privileges to Roles

 
   *Rather than granting the same 
   set of privileges to several user, 
   grant the privileges to a role and 
   assign the role to a user.
 
CREATE ROLE role {NOT IDENTIFIED 
   | IDENTIFIED {BY password | EXTERNALLY}]
 
Example 1
 
CREATE ROLE a_role_name IDENTIFIED BY a_password;
 
Granting Object Privileges
 
GRANT { object_priv [(column_list)]
   [,obj_priv[(column_list)]] ...
   [ALL [PRIVILEGES]}
   ON [schema.]object
   TO {user|role|PUBLIC}
   [,{user|role|PUBLIC}] ...
   [WITH GRANT OPTION]
 
WITH GRANT OPTION enables the grantee to 
grant the object privileges to other users or roles
 
System Privileges
   ANALYZE 
   (Allows the user to analyze 
   any table, cluster, or index)
   AUDIT 
   (Audit any object in the database)
   CLUSTER 
   (
   CREATE CLUSTER, 
   CREATE ANY CLUSTER, 
   ALTER CLUSTER, 
   DROP CLUSTER
   )
   DATABASE 
   (
   ALTER DATABASE
   )
   DATABASE LINK 
   (
   CREATE DATABASE LINK
   )
   INDEX 
   (
   CREATE ANY INDEX, 
   ALTER ANY INDEX, 
   DROP ANY INDEX
   )
   PRIVILEDGE   
   (
   GRANT ANY PRIVILEGE - 
   grant any system privilege
   )
   PROCEDURE        
   (
   CREATE ANY PROCEDURE requires 
   the user also have
   ALTER ANY TABLE, 
   BACKUP ANY TABLE, 
   DROP ANY TABLE, 
   LOCK ANY TABLE,
   COMMENT ANY TABLE, 
   SELECT ANY TABLE, 
   INSERT ANY TABLE,
   UPDATE ANY TABLE, 
   OR GRANT ANY TABLE
   )
 
   PROFILE
   PUBLIC DATABASE LINK
   PUBLIC SYNONYM
   ROLE
   ROLLBACK 
   (
   CREATE ROLLBACK SEGMENT, 
   ALTER ROLLBACK SEGMENT, 
   DROP ROLLBACK SEGMENT
   )
   SESSION 
   (
   CREATE SESSION, 
   ALTER SESSION, 
   RESTRICTED SESSION - allows the user to 
   connect when the database 
   has been startup restrict
   )
   SEQUENCE 
   (
   CREATE SEQUENCE, 
   CREATE ANY SEQUENCE, 
   ALTER ANY SEQUENCE, 
   DROP ANY SEQUENCE, 
   SELECT ANY SEQUENCE
   )
   SNAPSHOT 
   (
   CREATE SNAPSHOT, 
   CREATE ANY SNAPSHOT, 
   DROP ANY SNAPSHOT
   )
   SYNONYM 
   (
   CREATE SYNONYM, 
   CREATE ANY SYNONYM, 
   DROP ANY SYNONYM
   )
   SYSTEM 
   (
   ALTER SYSTEM
   )
   TABLE 
   (
   CREATE TABLE, 
   CREATE ANY TABLE, 
   ALTER ANY TABLE, 
   BACKUP ANY TABLE, 
   DROP ANY TABLE, 
   LOCK ANY TABLE, 
   COMMENT ANY TABLE, 
   SELECT ANY TABLE, 
   INSERT ANY TABLE, 
   UPDATE ANY TABLE, 
   DELETE ANY TABLE
   )
   TABLESPACE 
   (
   CREATE TABLESPACE, 
   ALTER TABLESPACE, 
   MANAGE TABLESPACE, 
   DROP TABLESPACE,
   UNLIMITED TABLESPACE
   )
   TRANSACTION     
   (FORCE TRANSACTION, 
   FORCE ANY TRANSACTION
   )
   TRIGGER 
   (
   CREATE TRIGGER, 
   CREATE ANY TRIGGER, 
   ALTER ANY TRIGGER, 
   DROP ANY TRIGGER
   )
   USER 
   (
   CREATE USER, 
   BECOME USER, 
   ALTER USER, 
   DROP USER
   )
   VIEW 
   (
   CREATE VIEW, 
   CREATE ANY VIEW, 
   DROP ANY VIEW
   )
                                  
   
Object Privileges
   ALTER, DELETE, EXECUTE, INDEX,
   INSERT, REFERENCES,SELECT, UPDATE,CONNECT
Using Security Manager
   
   Choose Role->Create
 
   Enter Role name,  Authenication(Password)
 
changing a password associated with a role
 
ALTER ROLE role {NOT IDENTIFIED | 
IDENTIFIED {BY password | EXTERNALLY}};
 
Assigning Roles to Users
 
   GRANT role [,role] ...
   TO {user|role|PUBLIC}
   [,{user|role|PUBLIC}] ....
   [WITH ADMIN OPTION]
 
with admin option enable the grantee to 
grant the role to other users or roles.
 

TO THE TOP


Changing the Next Extent Size

   The initial extent size can 
   only be set once.  But the next
   extent size can be changed 
   using the following steps.
 
   1. alter table {my_table_name} storage (next 30k)
 
   2. export the table  (export will compress the extents)
 
   3. import the table
 
 
   Storage Clause
 
   STORAGE(
                   INITIAL integer(K or M)
                   NEXT integer(K or M)
                   MINEXTENTS integer
                   MAXEXTENTS integer
                   PCTINCREASE integer
                   FREELISTS integer
                   FREELISTS GROUPS integer
                   OPTIMAL integer (K or M)
   )

Pinning Objects into Memory

   Prevents aging of the object in shared memory
 
   execute dbms_shared_pool.keep('schema.my_package');
 
   Removes a pinned object
   
   execute dbms_shared_pool.unkeep('schema.my_package');
 
   DBA view
   select * from V$DB_OBJECT_CACHE to 
   view objects loaded in the library cache
 

Deallocating the High Water Mark

   alter table {mytable} deallocate unused;

Creating a Package

   Define the Specification
 
 
                   CREATE OR REPLACE PACKAGE myPackage
                   as
 
                   PROCEDURE myProcedure
                   (param1 IN NUMBER, param2 in NUMBER);
                   end myPackage;
 
   Define the Package Body
 
 
 
                   CREATE OR REPLACE PACKAGE BODY myPackage
                   AS
                                  PROCEDURE myProcedure
                                  ( param1 IN NUMBER, param2 in Number)
                                  IS            
                                  BEGIN
                                  END myProcedure;
                   END myPackage;
 
   
   Grant Execute Privileges (Object Level Privileges)
 
   1) Grant execute on package_name for to role_name;
   2) Grant execute on package_name for to user_name;
 

TO THE TOP


Creating a Function

   CREATE OR REPLACE FUNCTION myFunction
   (param1 IN varchar2, param2 IN varchar2) 
   RETURN NUMBER IS
   retparam NUMBER;
   BEGIN
   END myFunction;
 
   Grant Execute Privileges (Object Level Privileges)
 
   1) Grant execute on package_name for to role_name;
   2) Grant execute on package_name for to user_name;
 

Creating a Procedure

   CREATE OR REPLACE PROCEDURE myProcedure
   (param1 IN varchar2, param2 IN varchar2) 
   BEGIN
   END myProcedure;
 
   Grant Execute Privileges 
   (Object Level Privileges)
 
   1) Grant execute on package_name for to role_name;
   2) Grant execute on package_name for to user_name;
 

Creating a Oracle Synonym

   Logon at an application level  
   (You must have Create Public Synonym privileges)
 
   CREATE PUBLIC SYNONYM my_synonym name 
   FOR USER_SCHEMA.
   (FUNCTION, TABLE, SNAPSHOT, PACKAGE, FUNCTION)
 
   Logon as a user and create a private synonym
   CREATE PUBLIC SYNONYM my_synonym name 
   FOR USER_SCHEMA.
   (FUNCTION, TABLE, SNAPSHOT, PACKAGE, FUNCTION)

--------------------à>

 



 

------------------à>



Tablespaces

Tablespaces


Adding a datafile

alter tablespace user_data
add datafile 'e:\{sid}\data\usr2.dbf' size 30M;


Autoextend

alter tablespace user_data autoextend off;

Next and Maxsize are set to zero.

alter tablespace user_data
add datafile 'e:\{sid}\data\usr2.dbf';

alter tablespace user_data autoextend on max_size=40M;

Sets the maximum disk space allowed for automatic extension of the datafile.


Taking the Tablespace Online and Offline

   Online

alter tablespace user_data online;

   Offline

alter tablespace user_data offline;


Hot Backup of a Tablespace

alter tablespace user_data begin backup;

alter tablespace user_data end backup;


This option does not prevent user from performing transactions.


Tablespace Coalese

alter tablespace user_data coalesce;

Coalesing gathers free segments. SMON usual cleans up free space.

--------------à>



Cursors, Functions, Procedures, and More



PL/SQL Commands and Syntax - Contents

Cursors
Functions
Procedures
Packages
Triggers

 

Defining and Using Functions

    CREATE OR REPLACE FUNCTION {function name}
    ( {argument} in {datatype} )
    return {datatype} IS
   
    v_variable {datatype};
   
    BEGIN
   
        /* Insert PL/SQL code here */
   
    return v_variable;
   
    END {function name};

Back to the Top

 

Defining and Using Procedures

See Also, Defining and Using Stored Procedures

    CREATE OR REPLACE PROCEDURE {procedure name}
    ( {argument} IN {datatype} )
    IS
    v_variable {datatype};
    BEGIN
        /*Insert your code here*/
    END {procedure name};

Back to the Top

 

Defining and Using Packages

Two steps to creating a PL/SQL Package

    1) Create the specifications
        Contains the declarative descriptions
        of the function, procedures, and
        gobal variables.
    2) Create the body

Step 1

    CREATE OR REPLACE PACKAGE {package_name} AS
        procedure specification
        function specification
        variable declaration
        cursor declaration
        type definition
    END {package_name};
   

Step 2

    CREATE OR REPLACE PACKAGE BODY {package_name} AS
        /*code in the specifications*/
    END {package_name};

Back to the Top

 

Defining and Using Triggers

    CREATE OR REPLACE TRIGGER {trigger name}
    AFTER|BEFORE INSERT OR DELETE OR UPDATE ON {table name}
   
    DECLARE
        /*You insert your code*/
    BEGIN
    END {trigger name};


    Restrictions on Triggers
        * A trigger may not issue any transaction control
        - COMMIT, ROLLBACK, or SAVEPOINT.
        * Any functions or Procedures called by the
        trigger can not issue any transaction control.
        * No LONG or LONG RAW types can be declared

Back to the Top

 

Defining and Using Cursors

Using Bind Variables:

    v_field1 mytable.field1%TYPE;
    v_field2 mytable.field2%TYPE;

    v_row mytable%ROWTYPE;

    -- DECLARE THE CURSOR

    CURSOR C1 IS
        SELECT * FROM mytable
        where field1 = v_field1
        and field2 = v_field2;

    BEGIN
        -- OPEN A PREVIOUSLY DECLARED CURSOR
        OPEN C1;

        LOOP
            FETCH C1 INTO v_row;

            EXIT WHEN C1%NOTFOUND;
        END LOOP;

        CLOSE C1;
        -- CLOSE THE CURSOR
    END;

Using a While Structure to Fetch Rows

    v_field1 mytable.field1%TYPE;
    v_field2 mytable.field2%TYPE;

    v_row mytable%ROWTYPE;

    -- DECLARE THE CURSOR

    CURSOR C1 IS
        SELECT * FROM mytable
        where field1 = v_field1
        and field2 = v_field2;

    BEGIN
        -- OPEN A PREVIOUSLY DECLARED CURSOR
        OPEN C1;

        FETCH C1 INTO v_row;

        WHILE C1%FOUND LOOP
            FETCH C1 INTO v_row;
        END LOOP;

        CLOSE C1;
        -- CLOSE THE CURSOR
    END;

Using a For Structure to Fetch Rows

    v_field1 mytable.field1%TYPE;
    v_field2 mytable.field2%TYPE;

    v_row mytable%ROWTYPE;

    -- DECLARE THE CURSOR

    CURSOR C1 IS
        SELECT * FROM mytable
        where field1 = v_field1
        and field2 = v_field2;

    BEGIN
        -- OPEN A PREVIOUSLY DECLARED CURSOR
        OPEN C1;

        FOR v_row IN C1
            -- AN IMPLICIT FETCH
            -- IS EXECUTED
            -- AN IMPLICIT CHECK
            FOR C1%NOTFOUND is performed
        END LOOP
        -- AN IMPLICIT CLOSE OF
        -- THE CURSOR IS PERFORMED
        -- CLOSE THE CURSOR
    END;

Where Current Of

    v_field1 mytable.field1%TYPE;
    v_field2 mytable.field2%TYPE;

    v_row mytable%ROWTYPE;

    -- DECLARE THE CURSOR

    CURSOR C1 IS
        SELECT * FROM mytable
        where field1 = v_field1
        and field2 = v_field2
        FOR UPDATE OF field1;

    BEGIN
        -- OPEN A PREVIOUSLY DECLARED CURSOR
        OPEN C1;

        FOR v_row IN C1
            -- Update on the column list
            --in the FOR UPDATE clause
            update mytable
            set field1 = field1 * 1.01;
            where current of C1;
        END LOOP
        -- AN IMPLICIT CLOSE OF THE
        -- CURSOR IS PERFORMED
        -- CLOSE THE CURSOR
    END;

Cursor Variables

    TYPE type_C1 IS REF CURSOR
        RETURN myTable%ROWTYPE;

    v_C1 type_C1;

    v_row mytable%ROWTYPE;

    BEGIN
        OPEN v_C1 FOR
        SELECT * FROM mytable
        where field1 = v_field1
        and field2 = v_field2
        FOR UPDATE OF field1;

        FETCH C1 INTO v_row;

        WHILE C1%FOUND LOOP
            FETCH C1 INTO v_row;
        END LOOP;

        CLOSE C1;

    END

------------------------à

Hosted by www.Geocities.ws

1