Asking for the datafiles, and tablespaces in the current database

Things that an ORACLE DBA should know

The database dictionary is your best friend!!!

What is inside of the database dictionary?

SQL> column comments format a35 wrap

SQL> select * from dictionary;

TABLE_NAME COMMENTS

------------------------------ ------------------------------

ALL_CATALOG	All tables, views, synonyms, and sequences accessible to the user
ALL_CLUSTERS	Description of clusters accessible to the user
ALL_CLUSTER_HASH_EXPRESSIONS	Hash functions for all accessible clusters
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_COL_GRANTS_MADE	Grants on columns for which the user is owner or grantor
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_DB_LINKS	Database links accessible to the user
…	…

276 rows selected.

NOTE:

There are very large number of files (276) in the dictionary. To get a description of each control file type at the SQL prompt a DESC command, for instance

sql> desc user_tables

How to create objects in ORACLE

Objects in an ORACLE database are created in the following order

create database
create tablespace(s)
create rollback segment (s)
create user(s)
create table(s)
create index
create other objects such as: sequence, cluster, procedure, function, package, trigger, link, profile, synonym, schema, snapshot, view, etc.

CREATE DATABASE Command

Used to create a database, making it available for general use, with the following options:

to establish a maximum number of instances, data files, redo log files groups, or redo log file members
to specify names and sizes of data files and redo log files
to choose a mode of use for the redo log

Warning: This command prepares a database for initial use and erases any data currently in the specified files. Only use this command when you understand its ramifications.

You must have the OSDBA role enabled.

Example

1- The following statement creates a database and fully specifies each argument:

CREATE DATABASE newtest

CONTROLFILE REUSE

LOGFILE

GROUP 1 (’diskb:log1.log’, ’diskc:log1.log’) SIZE 50K,

GROUP 2 (’diskb:log2.log’, ’diskc:log2.log’) SIZE 50K

MAXLOGFILES 5

MAXLOGHISTORY 100

DATAFILE ’diska:dbone.dat’ SIZE 2M

MAXDATAFILES 10

MAXINSTANCES 2

ARCHIVELOG

EXCLUSIVE

CHARACTER SET US7ASCII

DATAFILE ’disk1:df1.dbf’ AUTOEXTEND ON

’disk2:df2.dbf’ AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CONTROLFILE REUSE reuses existing control files identified by the initialization parameter CONTROL_FILES

LOGFILE specifies one or more files to be used as redo log files.

MAXLOGFILES specifies the maximum number of redo log file groups that can ever be created for the database.

MAXLOGMEMBERS specifies the maximum number of members, or copies, for a redo log file group.

MAXLOGHISTORY specifies the maximum number of archived redo log files for automatic media recovery of Oracle7 with the Parallel Server option.

MAXINSTANCES specifies the maximum number of instances that can simultaneously have this database mounted and open.

ARCHIVELOG establishes archivelog mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused.

NOARCHIVELOG establishes noarchivelog mode for redo log files groups. In this mode, the contents of a redo log file group need not be archived before the group can be reused.

CHARACTER SET specifies the character set the database uses to store data.

DATAFILE specifies one or more files to be used as data files. These files all become part of the SYSTEM tablespace.

AUTOEXTEND enables or disables the automatic extension of a datafile.

Asking for the datafiles, and tablespaces in the current database

SQL> select file_name, tablespace_name, bytes from dba_data_files;

FILE_NAME TABLESPACE_NAME BYTES

------------------------------ ------------------------------ ---------

D:\ORA95\DATABASE\usr1orcl.ora USER_DATA 3145728

D:\ORA95\DATABASE\rbs1orcl.ora ROLLBACK_DATA 5242880

D:\ORA95\DATABASE\tmp1orcl.ora TEMPORARY_DATA 2097152

D:\ORA95\DATABASE\sys1orcl.ora SYSTEM 10485760

How to create a Tablespace

CREATE TABLESPACE MyTabspace_2

DATAFILE 'd:\MYDB\MyData2.dat' SIZE 10M

DEFAULT STORAGE (

INITIAL 10K

NEXT 50K

MINEXTENTS 1

MAXEXTENTS 999

PCTINCREASE 10)

ONLINE;

How to eliminate a tablespace

DROP TABLESPACE Mytabspace2

INCLUDING CONTENTS

CASCADE CONSTRAINTS;

Create Rollback Segment

Is used to create a rollback segment. A rollback segment is an object that Oracle7 uses to store data necessary to reverse, or undo, changes made by transactions.

Example

The following statement creates a rollback segment (rbs_2) with default storage values in the system tablespace:

CREATE ROLLBACK SEGMENT rbs_2

TABLESPACE system;

Create USER

To create a database user, or an account through which you can log in to the database, and establish the means by which Oracle7 permits access by the user. You can optionally assign the following properties to the user:

default tablespace
temporary tablespace
quotas for allocating space in tablespaces
profile containing resource limits

Example

You can create the user BETTY by issuing the following statement:

CREATE USER betty

IDENTIFIED BY boop

DEFAULT TABLESPACE cases_ts

QUOTA 10M ON cases_ts

QUOTA 5M ON temp_ts

QUOTA 5M ON system

PROFILE engineer;

Grant Connect, Resource to betty;

The user BETTY has the following characteristics:

the password BOOP
default tablespace CASES_TS, with a quota of 10 megabytes
temporary tablespace TEMP_TS, with a quota of 5 megabytes
access to the tablespace SYSTEM, with a quota of 5 megabytes
limits on database resources defined by the profile ENGINEER
Grant gives connection and data manipulation rights (an alternative to profiles)

Example: Creating a Database

-- BUILD_DB.SQL script file

-- Creating the Personal Oracle 7.1 starting database

startup nomount pfile=D:\ORA95\DATABASE\initorcl.ora

-- Create database

create database oracle

controlfile reuse

logfile 'D:\ORA95\DATABASE\log1orcl.ora' size 200K reuse,

'D:\ORA95\DATABASE\log2orcl.ora' size 200K reuse

datafile 'D:\ORA95\DATABASE\sys1orcl.ora' size 20M reuse

autoextend on next 10M

maxsize 200M

character set WE8ISO8859P1;

create rollback segment rb_temp

storage (initial 50K

next 100K

optimal 150K maxextents %sstarterrb_maxe_size%);

-- Create additioanal tablespaces ...

-- USER_DATA: Create user sets this as the default tablespace

-- TEMPORARY_DATA: Create user sets this as the temporary tablespace

-- ROLLBACK_DATA: For rollback segments

create tablespace user_data

datafile 'D:\ORA95\DATABASE\usr1orcl.ora'

size 3M reuse

autoextend on next 5M maxsize 150M;

create tablespace rollback_data

datafile 'D:\ORA95\DATABASE\rbs1orcl.ora'

size 5M reuse

autoextend on next 5M maxsize 150M;

create tablespace temporary_data

datafile 'D:\ORA95\DATABASE\tmp1orcl.ora' size 2M reuse

autoextend on next 5M maxsize 150M;

alter rollback segment rb_temp online;

-- Change the SYSTEM users' password, default tablespace and

-- temporary tablespace.

alter user system temporary tablespace temporary_data;

alter user system default tablespace user_data;

-- Create 16 rollback segments. Allows 16 concurrent users with open

-- transactions updating the database. This should be enough.

create public rollback segment rb1

storage(initial 50K next 50K)

tablespace rollback_data;

create public rollback segment rb2

storage(initial 50K next 50K)

tablespace rollback_data;

...

...

create public rollback segment rb16

storage(initial 50K next 50K)

tablespace rollback_data;