Contents
- 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 Users
- Managing Users
- Roles
- Explain Roles and Security
- Creating Synonyms
- Creating Public and Private Synonyms
- Creating Packages
- Creating Packages
- Creating Stored Procedures
- Creating Stored Procedures
- Creating Functions
- Creating Functions
- High Water Mark
- Deallocating the High Water Mark
- 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
- Define and create the tables
- Define and create the primary key constraints
- Define and create the foreign key constraints
- Define and create the indexes
Creating Primary and Foreign Key Constraints
Syntax to add a constraint to a 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.
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;
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 online
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}
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:datawrk.dbf' SIZE 50M
DEFAULT STORAGE
(INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 1)
PERMANENT;
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]
Data File Administration
Adding Datafiles to a Tablespace
ALTER TABLESPACE a_tablespace_name
ADD DATAFILE
'C:DATAanother_file.dbf' SIZE 100M;
Checking on Tablespace Status
Select * from V$TABLESPACE;
Changing the Size of a DataFile
ALTER DATABASE DATAFILE
'c:datadatafile1.dbf' RESIZE 200M;
Bringing a DataFile Online
alter database datafile 'c:datadatafile1.dbf' online
Taking a Datafile Offline
alter database datafile 'c:datadatafile1.dbf' offline
Moving Data Files
Alter database rename file
'c:datadatafile1.dbf' to
'c:datadatafile2.dbf';
Checking on Data File Status
Select * from DBA_DATA_FILES;
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
elapsed time measured 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}
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 )
- PRIVILEGE
-
( 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.
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.
alter table {my_table_name} storage (next 30k)
- export the table (export will compress the extents)
- 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)
Grant execute on package_name for to role_name;
Grant execute on package_name for to user_name;
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)
Grant execute on package_name for to role_name;
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)
Grant execute on package_name for to role_name;
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)