Oracle Database Management

CREATE Database

Creation of an Oracle database can erase an exisiting database. Especially important to database creation is selection of data block size. Data block size cannot be changed after database creation. The database is always read and written in blocks. A larger block size means that for every item read from the database that a larger chunk is read from disk. Thus large individual item storage requirements are suited to larger block sizes, for instance a database with heavy multimedia content.

As shown below, when the CREATE DATABASE command is executed the following items are created.

Database Types

In Oracle8i there are three generally different types of databases.

  1. Online Transaction Processing (OLTP) - many users and many transactions both reading and writing the database. Recovery, availability and concurrency are important.
  2. Data Warehouses / Decision Support Systems (DSS) - generally read-only, fast-access and very available requirements such as the internet.
  3. Combinations of the above two - for instance, an interactive internet site such as online trading and reporting.

Creating a Database Step-by-Step

  1. Select a database name and SID (instance name).
  2. Create an initialisation parameter file.
  3. Execute sqlplus with the /nolog option, connect as internal/oracle AS SYSDBA; and startup nomount;. This starts up the SGA and the background processes.
  4. Create the database using the create database command. Database creation causes the following to occur.
    1. Datafiles creation.
    2. Control files creation.
    3. Redo logs creation.
    4. SYSTEM tablespace and SYSTEM rollback segment creation.
    5. Data dictionary creation.
    6. SYSTEM and SYS users (schemas) creation.
    7. Character set data storage setting.
    8. Mount and open the database. The database is mounted in NOARCHIVELOG and EXCLUSIVE modes.
  5. Run the catalog.sql and catproc.sql scripts to create meta-data views plus dynamic performance views and create PL/SQL supplied packages respectively.
  6. Backup the database NOW !!!
  7. Create a rollback tablespace and create a second rollback segment in the rollback tablespace.
  8. Create a users or data or multiple users, data or applications and tools tablespaces.
  9. Create a temporary tablespace for sorting use.
  10. Bring all the new tablespaces and datafiles online.

... OR use the database creation tool that comes packaged with Oracle8i. I find this tool very useful. Even if you do not create the database exectly right the first time then it can always be altered at a later stage, atleast in development of that database.

If you need to drop a database shutdown the database and remove all physical files.

CREATE DATABASE Command Failure

When a CREATE DATABASE command failes do the following.

  1. Shut down the instance.
  2. Delete any files created by the failed CREATE DATABASE command.
  3. Correct the error in the CREATE DATABASE command.
  4. Restart the instance.

For any datafiles or redo logs already in existence use the REUSE option in the CREATE DATABASE command in order to reuse already existing files.

ALTER Database

CREATE CONTROLFILE

CREATE CONTROLFILE [REUSE] [SET] DATABASE database name log clause datafile clause character set clause

ALTER System

The ALTER SYSTEM command is used to alter the characteristics of the database environment and not to alter database structures. The ALTER SYSTEM command can be used to dynamically tune many of the database initialisation parameters whilst the database is mounted and opened.

The ARCHIVE LOG Clause

The ALTER SYSTEM command can also be used to make changes to database archive logging.

ALTER SYSTEM ARCHIVE LOG CLAUSE;
ARCHIVE LOG clauses:
	THREAD n
	[SEQUENCE n] [TO <location>]
	[CHANGE n] [TO <location>]
	[CURRENT] [TO <location>]
	[GROUP n] [TO <location>]
	[LOGFILE <filename>] [TO <location>]
	[NEXT] [TO <location>]
	[ALL] [TO <location>]
	[START] [TO <location>]
	[STOP]

Database Links

CREATE DATABASE LINK

CREATE [SHARED] [PUBLIC] DATABASE LINK database link
	[ CONNECT TO (CURRENT USER | user name IDENTIFIED BY password [AUTHENTICATED BY user name IDENTIFIED BY password] ]
	[ AUTHENTICATED BY user name IDENTIFIED BY password ]
[ USING connect string ]

DROP DATABASE LINK

DROP [PUBLIC] DATABASE LINK database link