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
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.
connect
user/password@host
; or / -> to run SQL @filename -> run the SQL in a file
Oracle 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
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)
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
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
CREATE TABLE {table_name}
(
{field1} VARCHAR2(10),
{field2} NUMBER(10)
)
PCTFREE 30
PCTUSED 60
TABLESPACE (tablespace_name)
STORAGE
(
INITIAL integer
NEXT integer
);
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
)
)
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.
alter index {my_index_idx} rebuild;
* Does not drop the constraints
Stored Procedures
See Also, Defining and using Cursors
(Article Coming Soon)
See Also, Defining and using Functions (Article Coming Soon)
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
Three control files
Three groups of redo log files, with each group having two members
Create a file structure for the data files
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;
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
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
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
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
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
VARIABLE v_jobnum NUMBER;
BEGIN
dbms_job.submit(:v_jobnum,
'my_stored_procedure;', sysdate,'sysdate+1');
END;
dbms_job.run(job_number);
dbms_job.remove(job_number);
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
Managing Datafiles |
|
|
|
Creating Tablespaces Dimensions |
|
|
|
Creating Indexes |
|
|
|
Managing Rollback Segments |
|
|
|
Creating Snapshots |
|
|
|
Creating Tables |
|
|
|
Creating Constraints |
|
|
|
Creating User and Roles |
|
|
|
Creating Synonyms |
|
|
|
Creating Packages, Stored Procedures, and Functions |
|
|
|
High Water Mark |
|
|
|
Pinning Objects into Memory |
|
|
|
Changing the Next Extent Size |
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
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
)
)
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.
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;
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}
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]
ALTER TABLESPACE a_tablespace_name
ADD DATAFILE
'C:\DATA\another_file.dbf' SIZE 100M;
Checking on Tablespace Status
Select * from V$TABLESPACE;
ALTER DATABASE DATAFILE
'c:\data\datafile1.dbf' RESIZE 200M;
alter database datafile 'c:\data\datafile1.dbf' online
alter database datafile 'c:\data\datafile1.dbf' offline
Alter database rename file
'c:\data\datafile1.dbf' to
'c:\data\datafile2.dbf';
Select * from DBA_DATA_FILES;
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}
*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.
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)
)
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
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;
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;
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;
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
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
Cursors
Functions
Procedures
Packages
Triggers
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};
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};
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};
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
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
------------------------à