Thursday, April 8, 2010
Query Related to Tablespace
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
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
FROMDBA_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 theDB_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
orCREATE CONTROLFILE
statements, theMAXDATAFILES
parameter specifies an initial size. However, if you attempt to add a new file whose number is greater thanMAXDATAFILES
, but less than or equal toDB_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.