DBAsupport.com : Oracle 9i Central : Listen Software Solutions' "How To" Series : A Collection of DBA How To's

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}

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;



TO THE 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 the LSS "How To" Series Main Page