Showing posts with label Managing Database. Show all posts
Showing posts with label Managing Database. Show all posts

Thursday, April 8, 2010

Query Related to Tablespace

How to retrieve tablespace default storage Parameters?

SELECT TABLESPACE_NAME "TABLESPACE",
INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;

TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
RBS 1048576 1048576 2 40 0
SYSTEM 106496 106496 1 99 1
TEMP 106496 106496 1 99 0
TESTTBS 57344 16384 2 10 1
USERS 57344 57344 1 99 1

How to retrieve information tablesapce and associated datafile?

SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;

FILE_NAME BLOCKS TABLESPACE_NAME
------------ ---------- -------------------
/U02/ORACLE/IDDB3/RBS01.DBF 1536 RBS
/U02/ORACLE/IDDB3/SYSTEM01.DBF 6586 SYSTEM
/U02/ORACLE/IDDB3/TEMP01.DBF 6400 TEMP
/U02/ORACLE/IDDB3/TESTTBS01.DBF 6400 TESTTBS
/U02/ORACLE/IDDB3/USERS01.DBF 384 USERS

How to retrive Statistics for Free Space (Extents) of Each Tablespace?

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL
---------- ------- ------ ------- ------- ------- ------
RBS 2 1 955 955 955 955
SYSTEM 1 1 119 119 119 119
TEMP 4 1 6399 6399 6399 6399
TESTTBS 5 5 6364 3 1278 6390
USERS 3 1 363 363 363 363

PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.

Managing Tablespace

A tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.

Type of tablespace?

System Tablespace

* Created with the database
* Required in all database
* Contain the data dictionary

Non System Tablespace:

* Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user’s objects
* Enable more flexibility in database administration

How to Create Tablespace?

CREATE TABLESPACE "tablespace name"

DATAFILE clause SIZE ……. REUSE

MENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)

BLOCKSIZE

LOGGING | NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging : tablespace do not have all changes written to redo)

ONLINE | OFFLINE (OFFLINE: tablespace unavailable immediately after creation)

PERMANENT | TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)

EXTENT MANAGEMENT clause

Example:

CREATE TABLESPACE "USER1"

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSE

BLOCKSIZE 8192

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL



How to manage space in Tablespace?

Tablespace allocate space in extent.

Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don’t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.

Extent Management [Local | Dictionary]

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.

To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).

If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.

If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace

When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.

How to Create a Locally Managed Tablespace?

The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

How to Create a Dictionary Managed Tablespace?

The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (

INITIAL 64K

NEXT 64K

MINEXTENTS 2

MAXEXTENTS 121

PCTINCREASE 0);



What is Segment Space Management Options?

Two choices for segment-space management, one is manual (the default) and another auto.

Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.

Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management

Example:

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSE

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

SEGMENT SPACE MANAGEMENT AUTO

PERMANENT

ONLINE;

How to Convert between LMT and DMT Tablespace?

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');

PL/SQL procedure successfully completed.

Monday, July 20, 2009

Important Query related to Tablesapce

How to retrieve tablespace default storage Parameters?

SELECT TABLESPACE_NAME "TABLESPACE",
INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;

TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
RBS 1048576 1048576 2 40 0
SYSTEM 106496 106496 1 99 1
TEMP 106496 106496 1 99 0
TESTTBS 57344 16384 2 10 1
USERS 57344 57344 1 99 1



How to retrieve information tablesapce and associated datafile?







SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;

FILE_NAME BLOCKS TABLESPACE_NAME
------------ ---------- -------------------
/U02/ORACLE/IDDB3/RBS01.DBF 1536 RBS
/U02/ORACLE/IDDB3/SYSTEM01.DBF 6586 SYSTEM
/U02/ORACLE/IDDB3/TEMP01.DBF 6400 TEMP
/U02/ORACLE/IDDB3/TESTTBS01.DBF 6400 TESTTBS
/U02/ORACLE/IDDB3/USERS01.DBF 384 USERS



How to retrive Statistics for Free Space (Extents) of Each Tablespace?







SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL
---------- ------- ------ ------- ------- ------- ------
RBS 2 1 955 955 955 955
SYSTEM 1 1 119 119 119 119
TEMP 4 1 6399 6399 6399 6399
TESTTBS 5 5 6364 3 1278 6390
USERS 3 1 363 363 363 363



PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.

Wednesday, July 15, 2009

Managing Tablespace

A tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.

Type of tablespace?

System Tablespace

  • Created with the database
  • Required in all database
  • Contain the data dictionary

Non System Tablespace:

  • Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user’s objects
  • Enable more flexibility in database administration

How to Create Tablespace?

CREATE TABLESPACE "tablespace name"

DATAFILE clause SIZE ……. REUSE

MENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)

BLOCKSIZE

LOGGING | NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging : tablespace do not have all changes written to redo)

ONLINE | OFFLINE (OFFLINE: tablespace unavailable immediately after creation)

PERMANENT | TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)

EXTENT MANAGEMENT clause

Example:

CREATE TABLESPACE "USER1"

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSE

BLOCKSIZE 8192

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL

How to manage space in Tablespace?

Tablespace allocate space in extent.

Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don’t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.

Extent Management [Local | Dictionary]

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.

To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).

If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.

If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace

When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.

How to Create a Locally Managed Tablespace?

The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

How to Create a Dictionary Managed Tablespace?

The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (

INITIAL 64K

NEXT 64K

MINEXTENTS 2

MAXEXTENTS 121

PCTINCREASE 0);

What is Segment Space Management Options?

Two choices for segment-space management, one is manual (the default) and another auto.

Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.

Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management

Example:

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSE

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

SEGMENT SPACE MANAGEMENT AUTO

PERMANENT

ONLINE;

How to Convert between LMT and DMT Tablespace?

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');

PL/SQL procedure successfully completed.

Tuesday, July 14, 2009

Managing UNDO TABLESPACE

Before commit, Oracle Database keeps records of actions of transaction because Oracle needs this information to rollback or Undo the Changes.

What is the main Init.ora Parameters for Automatic Undo Management?

UNDO_MANAGEMENT:

The default value for this parameter is MANUAL. If you want to set the database in an automated mode, set this value to AUTO. (UNDO_MANAGEMENT = AUTO)

UNDO_TABLESPACE:

UNDO_TABLESPACE defines the tablespaces that are to be used as Undo Tablespaces. If no value is specified, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online (UNDO_TABLESPACE = <Tablespace_Name>)

UNDO_RETENTION:

The default value for this parameter is 900 Secs. This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past.

UNDO_SUPRESS_ERRORS:

Default values is FALSE. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.

How to Creating UNDO Tablespaces?

UNDO tablespaces can be created during the database creation time or can be added to an existing database using the create UNDO Tablespace command

Scripts at the time of Database creation:  

CREATE DATABASE <DB_NAME>

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

DATAFILE '<DISK>:\Directory\<FILE_NAME>.DBF' SIZE 204800K REUSE

AUTOEXTEND ON NEXT 20480K MAXSIZE 32767M

UNDO TABLESPACE "<UNDO_TABLESPACE_NAME>"

DATAFILE '<DISK>:\DIRECTORY\<FILE_NAME>.DBF’ SIZE 1178624K REUSE

AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M

CHARACTER SET WE8MSWIN1252

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 (<DISK>:\DIRECTORY\<FILE_NAME>.LOG') SIZE 5024K,

GROUP 2 ('<DISK>:\DIRECTORY\<FILE_NAME>.LOG') SIZE 5024K,

GROUP 3 (<DISK>:\DIRECTORY\<FILE_NAME>.LOG') SIZE 5024K;

Scripts after creating Database:

CREATE UNDO TABLESPACE "<UNDO_TABLESPACE_NAME"

DATAFILE '<DISK>:\DIRECTORY\<FILE_NAME>.DBF' SIZE 1178624K REUSE

AUTOEXTEND ON;

How to Dropping an Undo Tablespace?

You cannot drop Active undo tablespace. Means, undo tablespace can only be dropped if it is not currently used by any instance. Use the DROP TABLESPACE statement to drop an undo tablespace and all contents of the undo tablespace are removed.

Example:

DROP TABLESPACE <UNDO_TABLESPACE_NAME> including contents;

How to Switching Undo Tablespaces?

We can switch form one undo tablespace to another undo tablespace. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

Step 1: Create another UNDO TABLESPACE

CREATE UNDO TABLESPACE "<ANOTHER_UNDO_TABLESPACE>"

DATAFILE '<DISK>:\Directory\<FILE_NAME>.DBF' SIZE 1178624K REUSE

AUTOEXTEND ON;

Step 2: Switches to a new undo tablespace:

alter system set UNDO_TABLESPACE=<UNDO_TABLESPACE>;

Step 3: Drop old UNDO TABLESPACE

drop tablespace <UNDO_TABLESPACE> including contents;

IMPORTANT:

The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.

The switch operation does not wait for transactions in the old undo tablespace to commit. If there is any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.

An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).

If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This can be used, for example, to unassign an undo tablespace in the event that you want to revert to manual undo management mode.

The following example unassigns the current undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = '';

How to Monitoring Undo Space?

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for undo space consumption, transaction concurrency, and length of queries in the instance.

The following example shows the results of a query on the V$UNDOSTAT view.

SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,

MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT;

Sunday, July 12, 2009

Managing Temporary Tablespace

First we will discus about use of temporary tablespace. We use it to manage space for database sort operation. For example: if we join two large tables it require space for sort operation because oracle cannot do shorting in memory. This sort operation will be done in temperory tablespace.

We must assign a temporary tablespace to each user in the database; if we don’t assign temperory tablespace to user in the database oracle allocate sort space in the SYSTEM tablespace by default.

Important:

  • That a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.
  • When we create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.
  • TEMPFILEs are not recorded in the database's control file.
  • We cannot remove datafiles from a tablespace until you drop the entire tablespace but we can remove a TEMPFILE from a database:

SQL> ALTER DATABASE TEMPFILE ''<disk>:\<directory>\<Tablespace Name>.dbf' DROP INCLUDING DATAFILES;

  • Except for adding a tempfile, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).

How does create Temporary Tablespaces?

CREATE TEMPORARY TABLESPACE temp

TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf' size 20M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.

How can define Default Temporary Tablespaces?

We can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Important:

  • The default “Default Temporary Tablespace” is SYSTEM.
  • Each database can be assigned one and only one Default Temporary Tablespace.
  • Temporary Tablespace is automatically assigned to users.

Restriction:

The following restrictions apply to default temporary tablespaces:

  • The Default Temporary Tablespace must be of type TEMPORARY
  • The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
  • The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

How to see the default temporary tablespace for a database?

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

How to Monitoring Temporary Tablespaces and Sorting?

Use following query to view temp file information:

Select * from dba_temp_files; or Select * from v$tempfile;

Use following query for monitor temporary segment

Select * from v$sort_segments or Select * from v$sort_usage

Use following query for free space in tablespace :

select TABLESPACE_NAME,BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

How to Dropping / Recreating Temporary Tablespace? (Method)

This should be performed during off ours with no user logged on performing work.

If you are working with a temporary tablespace that is NOT the default temporary tablespace for the database, this process is very simple. Simply drop and recreate the temporary tablespace:

Step:1 Drop the Tablespace

DROP TABLESPACE temp;

Tablespace dropped.

Step: 2 Create new temporary tablespace.

CREATE TEMPORARY TABLESPACE TEMP

TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf' SIZE 500M REUSE

AUTOEXTEND ON NEXT 100M MAXSIZE unlimited

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

How to Dropping / Recreating Default Temporary Tablespace? (Method)

You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

DROP TABLESPACE temp;

drop tablespace temp

*

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

Step: 1 Create another temperory tablespace.

CREATE TEMPORARY TABLESPACE temp2

TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf'SIZE 5M REUSE

AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Step: 2 Make default tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

Step: 3 Drop old defaule tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Most Importent:

You do not need to assign temporary tablespace while creating a database user. The Temporary Tablespace is automatically assigned. The name of the temporary tablespace is determined by the DEFAULT_TEMP_TABLESPACE column from the data dictionary view DATABASE_PROPERTIES_VIEW.

Example:

Step:1 Create database user

create user test identified by test default TABLESPACE users;

User created.

Step: 2 View information

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM

DBA_USERS WHERE USERNAME='TEST';

USERNAME     DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

TEST USERS TEMP

NOTE: Temporary Tablespace TEMP is automatically assigned to the user TEST.

Certain Restrictions?

  • The default temporary tablespace can not be DROP.

  • The default temporary tablespace cab not be taken offline

Thursday, July 9, 2009

Managing Redo Log Files

Redo logs consists of two or more pre allocated files that store all changes made to the database. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.

Main points to consider before creating redo log files?

  • Members of the same group should be stores in separate disk so that no single disk failure can cause LGWR and database instance to fail.
  • Set the archive destination to separate disk other than redo log members to avoid contention between LGWR and Arch.
  • With mirrored groups of online redo logs , all members of the same group must be the same size.

What are the parameters related to Redo log files?

Parameters related to redo log files are

  • MAXLOGFILES
  • MAXLOGMEMEBERS

MAXLOGFILES and MAXLOGMEMEBERS parameters are defined while creation of database. You can increase these parameters by recreating the control file.

How do you create online Redo log group?

Alter database add logfile group <group Number> (‘<DISK>:\Directory\<LOG_FILE_NAME>.log’,’ (‘<DISK>:\Directory\<LOG_FILE_NAME>.log’) size 500K;

How to check the status of added redo log group?

Select * from v$log;

Interpretation:

Here you will observe that status is UNUSED means that this redo log file is not being used by oracle as yet. ARC is the archived column in v$log , it is by default YES when you create a redo log file. It will returns to NO if the system is not in archive log mode and this file is used by oracle. Sequence# 0 also indicate that it is not being used as yet.

How to create online redo log member ?

alter database add logfile member '<DISK>:\Directory\<LOG_FILE_NAME>.log’,'<DISK>:\Directory\<LOG_FILE_NAME>.log’' to group <GROUP NUMBER>;

How to rename and relocate online redo log members ?

Important: Take the backup before renaming and relocating.

Step:1 Shutdown the database .

Step:2 Startup the database as startup mount.

Step:3 Copy the desired redo log files to new location . You can change the name of redo log file in the new location.

Step:4 Alter database rename file ‘<DISK>:\Directory\<LOG_FILE_NAME>.log’ to ‘<new path><DISK>:\Directory\<LOG_FILE_NAME>.log’,

Step:5 Alter database open;

Step: 6 Shutdown the database normal and take the backup.

How to drop online redo log group?

Important:

  • You must have at- least two online groups.
  • You can not drop a active online redo log group. If it active switch it by alter system switch logfile before dropping.
  • Also make sure that online redo log group is archived ( if archiving is enabled).

Syntax:

If you want to drop log group:

Alter database drop logfile group <GROUP_NUMBER>;

If you want to drop a logfile member:

Alter database drop logfile member ’ <DISK>:\Directory\<LOG_FILE_NAME>.log’;

How to Viewing Online Redo Log Information?

SELECT * FROM V$LOG;

GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 10605 1048576 1 YES ACTIVE 11515628 16-APR-00

2 1 10606 1048576 1 NO CURRENT 11517595 16-APR-00

3 1 10603 1048576 1 YES INACTIVE 11511666 16-APR-00

4 1 10604 1048576 1 YES INACTIVE 11513647 16-APR-00

SELECT * FROM V$LOGFILE;

GROUP# STATUS MEMBER

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

1 D:\ORANT\ORADATA\IDDB2\REDO04.LOG

2 D:\ORANT\ORADATA\IDDB2\REDO03.LOG

3 D:\ORANT\ORADATA\IDDB2\REDO02.LOG

4 D:\ORANT\ORADATA\IDDB2\REDO01.LOG

If STATUS is blank for a member, then the file is in use.

Tuesday, July 7, 2009

Managing Control Files

A control file is a small binary file that records the physical structure of the database with database name, Names and locations of associated datafiles, online redo log files, timestamp of the database creation, current log sequence number and Checkpoint information.

Note:

  • Without the control file, the database cannot be mounted.

  • You should create two or more copies of the control file during database creation.

Role of Control File:

When Database instance mount, Oracle recognized all listed file in Control file and open it. Oracle writes and maintains all listed control files during database operation.

Important:

  • If you do not specify files for CONTROL_FILES before database creation, and you are not using the Oracle Managed Files feature, Oracle creates a control file in <DISK>:\ORACLE_HOME\DTATBASE\ location and uses a default filename. The default name is operating system specific.

  • Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down.

  • Oracle writes to all filenames listed for the initialization parameter CONTROL_FILES in the database's initialization parameter file.

  • The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle database server during database operation.

  • If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

How to Create Control file at the time od database creation:

The initial control files of an Oracle database are created when you issue the CREATE DATABASE statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation.

How to Create Additional Copies, Renaming, and Relocating Control Files

Step:1 Shut down the database.

Step:2 Copy an existing control file to a different location, using operating system commands.

Step:3 Edit the CONTROL_FILES parameter in the database's initialization parameter file to add the new control file's name, or to change the existing control filename.

Step:4 Restart the database.

When you Create New Control Files?

  • All control files for the database have been permanently damaged and you do not have a control file backup.

  • You want to change one of the permanent database parameter settings originally specified in the CREATE DATABASE statement. These settings include the database's name and the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

Steps for Creating New Control Files

Step:1 Make a list of all datafiles and online redo log files of the database.

SELECT MEMBER FROM V$LOGFILE;

SELECT NAME FROM V$DATAFILE;

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

Step:2 Shut down the database.

Step:3 Back up all datafiles and online redo log files of the database.

Step:4 Start up a new instance, but do not mount or open the database:

STARTUP NOMOUNT

Step:5 Create a new control file for the database using the CREATE CONTROLFILE statement.

Example:

CREATE CONTROLFILE REUSE DATABASE "<DB_NAME" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 '<DISK>:\Directory\REDO01.LOG' SIZE 5024K,

GROUP 2 '<DISK>:\Directory\REDO02.LOG' SIZE 5024K,

GROUP 3 '<DISK>:\Directory\REDO03.LOG' SIZE 5024K

# STANDBY LOGFILE

DATAFILE

'<DISK>:\Directory\SYSTEM.DBF',

'<DISK>:\Directory\UNDOTBS.DBF'

CHARACTER SET WE8MSWIN1252

;

Step:6 Open the database using one of the following methods:

  • If you specify NORESETLOGS when creation the control file, use following commands: ALTER DATABASE OPEN;

  • If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

TIPS:

When creating a new control file, select the RESETLOGS option if you have lost any online redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs . You must also specify the RESETLOGS option if you have renamed the database. Otherwise, select the NORESETLOGS option.

Backing Up Control Files

Method 1:

Back up the control file to a binary file (duplicate of existing control file) using the following statement:

ALTER DATABASE BACKUP CONTROLFILE TO '<DISK>:\Directory\control.bkp';

Method 2:

Produce SQL statements that can later be used to re-create your control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

How to retrieve information related to Control File:

V$DATABASE

Displays database information from the control file

V$CONTROLFILE

Lists the names of control files

V$CONTROLFILE_RECORD_SECTION

Displays information about control file record sections

Monday, July 6, 2009

How to drop a datafile from a tablespace

Important : Oracle does not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user.

Reasons why you want to remove a datafile from a tablespace:

  • You may have mistakenly added a file to a tablespace.
  • You may have made the file much larger than intended and now want to remove it.
  • You may be involved in a recovery scenario and the database won't start because a datafile is missing.

Important : Once the DBA creates a datafile for a tablespace, the datafile cannot be removed. If you want to do any critical operation like dropping datafiles, ensure you have a full backup of the database.

Step: 1 Determining how many datafiles make up a tablespace

To determine how many and which datafiles make up a tablespace, you can use the following query:

SELECT file_name, tablespace_name FROM dba_data_files WHERE tablespace_name ='<name of tablespace>';

Case 1

If you have only one datafile in the tablespace and you want to remove it. You can simply drop the entire tablespace using the following:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

The above command will remove the tablespace, the datafile, and the tablespace's contents from the data dictionary.

Important : Oracle will not drop the physical datafile after the DROP TABLESPACE command. This action needs to be performed at the operating system.

Case 2

If you have more than one datafile in the tablespace, and you wnat to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

Case 3

If you have more than one datafile in the tablespace and you want to remove only one or two ( not all) datafile in the tablesapce or you want to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace.

Step: 1 Gather information on the current datafiles within the tablespace by running the following query in SQL*Plus:

SELECT file_name, tablespace_name FROM dba_data_files WHERE tablespace_name ='<name of tablespace>';

Step: 2 You now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:

SELECT owner, segment_name, segment_type FROM dba_segments WHERE tablespace_name='<name of tablespace>'

Step : 3 Now, export all the objects that you wish to keep.

Step : 4 Once the export is done, issue the

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS.

Step : 5 Delete the datafiles belonging to this tablespace using the operating system.

Step : 6 Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace.

Case : 4

If you do not want to follow any of these procedures, there are other things that can be done besides dropping the tablespace.

  • If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
  • If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.

Important : The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.

Important : If you are running in archivelog mode, you can also use: ALTER DATABASE DATAFILE <datafile name> OFFLINE; instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

Wednesday, July 1, 2009

Managing Data Files

What is data File?

Data files are physical files of the OS that store the data of all logical structures in the database. Data file must be created for each tablespace.

How to determine the number of dataf iles?

At least one datafile is required for the SYSTEM tablespace. We can create separate datafile for other teblespace. When we create DATABASE , MAXDATAFILES may be or not specify in create database statement clause. Oracle assassin db_files default value to 200. We can also specify the number of datafiles in init file.

When we start the oracle instance , the DB_FILES initialization parameter reserve for datafile information and the maximum number of datafile in SGA. We can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance.

Important:

  • If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit. Example : if init parameter db_files set to 2 then you can not add more then 2 in your database.
  • If the value of DB_FILES is too high, memory is unnecessarily consumed.
  • When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Note:

If you add new datafiles to a tablespace and do not fully specify the filenames, the database creates the datafiles in the default database directory . Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.

How to add datafile in execting tablespace?

alter tablespace <Tablespace_Name> add datafile ‘/............../......./file01.dbf’ size 10m autoextend on;  

How to resize the datafile?

alter database datafile '/............../......./file01.dbf' resize 100M;

How to bring datafile online and offline?

alter database datafile '/............../......./file01.dbf' online;

alter database datafile '/............../......./file01.dbf' offline;

How to renaming the datafile in a single tablesapce?

Step:1 Take the tablespace that contains the datafiles offline. The database must be open.

alter tablespace <Tablespace_Name> offline normal;

Step:2 Rename the datafiles using the operating system.

Step:3 Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

alter tablespace <Tablespace_Name> rename datafile '/...../..../..../user.dbf' to '/..../..../.../users1.dbf';

Step 4: Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to relocate datafile in a single tablesapce?

Step:1 Use following query to know the specifiec file name or size.

select file_name,bytes from dba_data_files where tablespace_name='<tablespace_name>';

Step:2 Take the tablespace containing the datafiles offline:

alter tablespace <Tablespace_Name> offline normal;

Step:3 Copy the datafiles to their new locations and rename them using the operating system.

Step:4 Rename the datafiles within the database.

ALTER TABLESPACE <Tablespace_Name> RENAME DATAFILE

'/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf'

TO '/u03/oracle/rbdb1/users01.dbf','/u04/oracle/rbdb1/users02.dbf';

Step:5 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to Renaming and Relocating Datafiles in Multiple Tablespaces?

Step:1 Ensure that the database is mounted but closed.

Step:2 Copy the datafiles to be renamed to their new locations and new names, using the operating system.

Step:3 Use ALTER DATABASE to rename the file pointers in the database control file.

ALTER DATABASE

RENAME FILE

'/u02/oracle/rbdb1/sort01.dbf',

'/u02/oracle/rbdb1/user3.dbf'

TO '/u02/oracle/rbdb1/temp01.dbf',

'/u02/oracle/rbdb1/users03.dbf;

Step:4 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.