#dbms admin

Schema and User

Schema is the whole sum of objects that a user has created.

A user needs to be added when there was new work process is added in a company.

1. Default tablespace needs to be created CREATE TABLESPACE name DATAFILE ‘file name’ SIZE # [ M | G ] CREATE TABLESPACE NAME DATAFILE ‘file name’ SIZE # [ M | G ]

2. Temporary Tablespace needs to be assigned CREATE TEMPORARY TABLESPACE name TEMPFI…

Oracle Memory Management

Oracle memory areas are divided two, SGA(Shared Global Area) and PGA(Process Global Area). In SGA, Shared Pool, DBCache, Redo buffer and etc are process and in PGA, each processes have it’s own work space.

Until 8i, everything had to be managed manually. The problem was that to apply adjusted memory space, a database had to restart. (Static SGA) However from 9i, automatic…

Oracle Data Structure

Hierarchy

Database Tablespace Segment Extend Oracle Block O/S Block

Oracle Block is consist of O/S Block. O/S block and Oracle block may or may not be the same size. If DBA decide to set up Oracle Block as 8KB and O/S Block is 2KB, 4 O/S Blocks are one Oracle Data Block. However, most of modern O/S have 8KB Block system. If they are not the same size it can cause Block Split.…

Undo Tablespace and Temporary Tablespace

About Undo Tablespace and Temporary Tablespace

Undo Tablespace is a virtual space on the PGA. When a transaction happens change vector is transferred to redo log buffer then the original data is sent to Undo Tablespace in order to enable a user to undo the undesired process. Undo Tablespace is also used to achieve Consistent Read. While a user is executing DML, the data is locked until the suer…

About the Tablespace

Tablespace is the conceptual space in the memory. Tablespace is used to accomodate the data which needs to be processed. Data that is not currently being used is stored in data files. A tablespace can have more than one tablespace if it’s needed. There are two kinds of tablespaces roughly speaking. One that is created by Oracle and the other one is create by a DBA for the…

About Redo Log Management

Redo Log is a log that contains history of transactions in a database. Redo log is one of the file that is used for backup and recovery. Therefore it is important to manage it correctly and keep the file multiplexed.

When a transaction happens, the first thing happens in a instance is check Database Cache Buffer to see if the target row’s data is in the buffer because…