Listen Software’s How To: A Collection of DBA How To’s

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

  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

Back to top


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

Back to top


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.

Back to 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;

Back to 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 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}

Back to 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: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]

Back to top


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;

Back to 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		 
   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}

Back to 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
)
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.

Back to 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)
)

Back to top


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

Back to top


Deallocating the High Water Mark

alter table {mytable} deallocate unused;

Back to top


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;

Back to 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;

Back to top


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;

Back to top


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)

Back to top

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles