Sunday, September 03, 2006
Configuring Listeners
# Start of : D:\Oracle\ora90\network\ADMIN\LISTENER.ORA
# LISTENER.ORA Network Configuration File: d:\oracle\ora90\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SK-LAPTOP)(PORT = 1521))
)
####################################################################
#### portions added for HTTP & HTTPS ####
####################################################################
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SK-LAPTOP)(PORT = 2481))
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = SK-LAPTOP)(PORT = 2482))
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\ora90)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TEST)
(ORACLE_HOME = d:\oracle\ora90)
(SID_NAME = TEST)
)
(SID_DESC =
(GLOBAL_DBNAME = CHIK)
(ORACLE_HOME = d:\oracle\ora90)
(SID_NAME = CHIK)
)
(SID_DESC =
(GLOBAL_DBNAME = SKDB)
(ORACLE_HOME = d:\oracle\ora90)
(SID_NAME = SKDB)
)
)
-------------------------------------------------------------------------------------
# Start of : File:d:\oracle\ora90\NETWORK\ADMIN\tnsnames.ora
-------------------------------------------------------------------------------------
# do i really need all these entries? Is not this file meant for non-default Port (1521)
# configured LISTENERS?
####################################
# TNSNAMES.ORA Configuration File:d:\oracle\ora90\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle Enterprise Manager V2
# Date..........: Sun Sep 03 21:08:11 GMT+05:30 2006
####################################
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SK-LAPTOP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = TEST))
)
SKDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SK-LAPTOP)(PORT = 1521))
)
(CONNECT_DATA = (SID = SKDB)(SERVER = DEDICATED))
)
CHIK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SK-LAPTOP)(PORT = 1521))
)
(CONNECT_DATA = (SID = CHIK)(SERVER = DEDICATED))
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SK-LAPTOP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = MODOSE)(PRESENTATION = http://HRService))
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC0))
)
(CONNECT_DATA = (SID = PLSExtProc)(PRESENTATION = RO))
)
------
# End
------
-------------------------------------------------------------------------------------
# Start of : File:d:\oracle\ora90\network\admin\sqlnet.ora
-------------------------------------------------------------------------------------
# SQLNET.ORA Network Configuration File: d:\oracle\ora90\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
-------------------------------------------------------------------------------------
# End of : File:d:\oracle\ora90\network\admin\sqlnet.ora
-------------------------------------------------------------------------------------
Checking status with LSNRCTL:
------------------------------------
Dos prompt: LSNRCTL
LSNRCTL> status skdb
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.0.1.1.1 - Produc
tion
Start Date 03-SEP-2006 22:49:40
Uptime 0 days 0 hr. 20 min. 26 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File d:\oracle\ora90\network\admin\listener.ora
Listener Log File d:\oracle\ora90\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SK-LAPTOP)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SK-LAPTOP)(PORT=2481))(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=SK-LAPTOP)(PORT=2482))(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=169.254.58.224)(PORT=2482))(PRESENTATION=GIOP)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.254.58.224)(PORT=2481))(PRESENTATION=GIOP)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=169.254.58.224)(PORT=9090))(PRESENTATION=http://admin)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.254.58.224)(PORT=8080))(PRESENTATION=http://admin)(SESSION=RAW))
Services Summary...
Service "CHIK" has 2 instance(s).
Instance "CHIK", status UNKNOWN, has 1 handler(s) for this service...
Instance "chik", status READY, has 3 handler(s) for this service...
Service "MODOSE" has 3 instance(s).
Instance "SKDB", status READY, has 1 handler(s) for this service...
Instance "TEST", status READY, has 1 handler(s) for this service...
Instance "chik", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SKDB" has 2 instance(s).
Instance "SKDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "SKDB", status READY, has 3 handler(s) for this service...
Service "TEST" has 2 instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
Instance "TEST", status READY, has 3 handler(s) for this service...
The command completed successfully
Friday, August 25, 2006
Tables
Tables :
| Regular Tables | : | The default table. Rows can be stored in any matter depending upon the activity in the database. |
| Partitioned Tables
| : | These enable the building of scalable applications. Characteristics:
|
| Index Organized Tables
| : | It is like a heap table with primary key on one or more of its columns. However instead of maintaining two separate storages, an IOT maintains a single B-tree containing the primary key and the rest of the column values. An overflow segment may exist due to PCTTHRESHOLD value being set and the result of longer row lengths requiring the overflow area. Characteristics:
|
| Clustered Tables | : | A cluster is made up of a table or group of table that share the same data blocks, which are grouped together because they share common columns and are often used together. Characteristics:
|
|
Note : Display uses base-64 encoding scheme using following character sets : A-Z,a-z,0-9, and /. That is a total 64 characters. |
Locating a row using rowid
| Segment no.(Data object No.) | Points to tablespace > | Tablespace (since a segment can reside in only one tablespace) |
|
| Relative file no. > | Relative Datafile of the tablespace |
|
| Block no. > | Block No. |
|
| Row ID . > | Row directory entry for the row |
|
| Row > | Beginning of the row |
Restricted ROWID Format in Oracle 7 & earlier : break up of 6 bytes:
|
Notes :
Uses :
|
|
Notes :
|
| CREATE TABLE tablename (column_name, column_datatype ,.....) |
---|---|
| Note : The above is a subset of the CREATE TABLE clause. |
| INITIAL | : |
|
---|---|---|---|
| NEXT | : |
|
| PCTINCREASE | : |
|
| MINEXTENT | : |
|
| MAXEXTENT | : |
|
| PCTFREE | : |
|
| PCTUSED | : | Deprecated from Oracle 9i together with FREELIST, FREELIST GROUPS |
| INTRANS | : |
|
| MAXTRANS | : |
|
| TABLESPACE | : |
|
Changing Storage & Block Utilization parameters:
| Syntax | Example |
| ALTER TABLE table_name | ALTER TABLE hr.employees |
| PCTINCREASE | : | PCTINCREASE will be registered in the data dictionary. It will be used to calculate NEXT, when next extent is allocated. |
| MINEXTENT | : | Can be changed to any value <= to the current no. of extents. Is has no immediate effect but will be used if the table is TRUNCATED. |
| MAXEXTENT | : | Can be changed to any value >= to the current no. of extents. It can also be set to UNLIMITED. |
| Restrictions:
|
|
|
Creating Temporary Tables :
| CREATE GLOBAL TEMPORARY TABLE tablename Note:
|
---|
| ON COMMIT DELETE ROWS | : | Rows only visible within transaction. |
---|---|---|---|
| ON COMMIT PRESERVE ROWS | : | Rows visible during entire session. |
Setting PCTFREE and PCTUSED:
| PCTFREE | : | Average increase in row_size *100 Average row Size | i.e. The free space should be able to accommodate average row size increases |
| Note: Higher PCTFREE is used to accommodate updates within a database block. Higher PCTFREE results in lower block density- fewer rows per block. Use higher value for:
|
| PCTUSED | : | 100 – PCTFREE - Average row size | i.e. Whenever there is space to accommodate a single new row. |
| Note: Set so that the block is returned to the free list only when there is sufficient space to accommodate an average row. If it does not, then Oracle Server looks up the 2nd block on the free list. This linear scan continues until a block with sufficient space is found or the end of list is reached. The value of average row can be found using ANALYZE TABLE command. |
Row Migration & Chaining:
| Row Migration | : | When PCTFREE is low, there may not be sufficient space in the block to accommodate a row growth due to update. In such a situation Oracle server moves the entire row to a new block & leaves a pointer from the original block to the new location. This causes decrease in I/O performance associated with this row due to two data block scans. |
| Chaining | : | Row chaining occurs when a row is too large to fit into any block. Oracle server divides the row into smaller chunks called row pieces. Each row piece is stored in a block along with necessary pointers to retrieve and assemble the entire row. Row chaining can be minimised by choosing a higher block size, or by splitting a table into multiple tables with fewer columns. |
Manually Allocating Extents:
| Syntax | Example |
| ALTER TABLE [schema.] tablename | ALTER TABLE hr.employees |
| Purpose:
Notes:
|
Nonpartitioned Table Reorganization:
| ALTER TABLE hr.employees |
|
| Using this command a non-partitioned table can be moved without using EXPORT or IMPORT utilility. In addition, storage parameters can be changed too. Purpose:
Note: Moving the tables requires rebuilding of indexed otherwise 'index in unusable state' error is thrown. |
Truncating a Table:
| Syntax: | Example: |
| TRUNCATE TABLE [schema.] tablename | TRUNCATE TABLE hr.employees; |
| Characteristics:
|
| Syntax: | Example: |
| DROP TABLE [schema.] tablename | DROP TABLE hr.departments |
| Characteristics:
|
| ALTER TABLE hr.employees |
| Characteristics:
|
UNUSED column option:
| ALTER TABLE hr.employees
ALTER TABLE hr.employees ALTER TABLE hr.employees |
| Characteristics:
Restrictions on dropping a Column. Can't do the followings:
|
Views
| DBA_UNUSED_COL_TABS |
|
| DBA_PARTIAL_DROP_TABS |
|
| DBA_TABLES |
|
| DBA_OBJECTS |
|
Thursday, August 24, 2006
Segments & Blocks
Table
Table Partition : for partitioned table, each partition a segment & storage parameters can be specified to control them independently.
Cluster : contains one of more tables which share same storage characteristics.
Index : one index segment for each index
Index-organised table
Index partition
Undo segment
Temporary segment
LOB segment
Nested table : The nested table is stored in a separate segment
Bootstrap Segment: helps to initialize data dictionary cache when the database is opened by an instance.
Storage parameters:
| 2: | Storage clause specified at segment level except for MINIMUM EXTENT & UNIFORM SIZE tablespace parameters. |
| 1: | If not specified at segment level, default storage clause specified at tablespace level used |
| 0: | If above two not specified than Oracle Server defaults are used Note : Any alteration of staorage clause applies only to the extents not yet allocated |
Database Blocks:
Consists of one of more OS blocks. DB_BLOCK_SIZE is the default block size. Used for SYSTEM tablespace and most other tablespaces. This default block size can't be changed without recreating the database.
DB_CACHE_SIZE specified the size of the default buffer cache for standard block size. Minimum 1 granule. Default 48MB. (1 granule = 4 MB if SGA_MAX_SIZE<128mb granule =" 16MB).">
For each non-default block sizes, sub-buffer caches must be configured within buffer cache in the init.ora file :
| DB_2K_CACHE_SIZE | DB_8K_CACHE_SIZE | DB_32K_CACHE_SIZE |
| DB_4K_CACHE_SIZE | DB_16K_CACHE_SIZE |
|
The default values for these are zero, except for the default DB_CACHE_SIZE which must not be configured using its 'n' size variant.
Creating tablespaces with non-standard block size :
| CREATE TABLESPACE sktab |
|
Restrictions :
Must configure the non-standard sub-chaches (_4K_ in this case) before the above statement can succeed. These can also be configured while the instance is running.
All TEMPORARY tablespaces including default tablespace must be standard block size.
All partitions of a partitioned object must reside in tablespaces of the same block size.
IOT overflow & out-of-line LOB segments can be stored in a tablespace of different block-size.
Block space Utilization paramenters:
Parameters |
| Default values |
| : : : : | 1 for data segment; 2 for index segment. Value range : 1-255. Default value is a function of the data block size. Value range : 1-255. 10% 40%. A block is put on free list when its used space falls below PCTUSED. |
| : | A freelist of a segment is a list of blocks that are candidates for future inserts. A segment by default is created with one free list, but it can be configured to be created with a higher number of free lists by setting the FREELIST parameter. |
Segment space management (Automatic):
| Tracking in-segment free space using bitmaps instead of FREELISTs. This bitmap is stored in a separate set of blocks referred to as bitmapped blocks (BMBs). It describes the status of each block in the segment with respect to its availability for space. Advantages :
Restrictions :
Configuring : CREATE TABLESPACE sktab Notes :
|
Views : See examples
DBA_EXTENTS | DBA_SEGMENTS | DBA_TABLESPACES | DBA_DATA_FILES | DBA_FREE_SPAC |
Saturday, August 19, 2006
Tablespaces
Database -> Tablespace(s) -> Datafile(s)
An Oracle database stores all its data in logical storage units called tablespaces.
Each tablespace consists of one or more datafiles which are physical structures conforming with the OS on which Oracle is running.
SYSTEM Tablespace:
Created with the database and is a required part of the database.
Contains data dictionary, including stored program units & SYSTEM undo segment.
Should not contain user data, even though it is allowed.
Non-SYSTEM Tablespace:
Allows more flexibility in database administration.
Separate undo, temporary, application data, & application index segments.
Separate data by backup requirements
Separate dynamic & static data
Control space allocated to the user's objects.
Purpose:
Creating TABLESPACE:
CREATE TABLESPACE tablespace
| * Only for dictionary managed tablespace
***Only for permanent local tablespace. |
| Examples : CREATE TABLESPACE fin_data CREATE TABLESPACE sktab CREATE TABLESPACE fin_data |
|
---|
tablespace | : | Name of the tablespace to be created | |||||||||||||||||||||
DATAFILE | : | Datafile(s) which make up the tablespace filename [SIZE integer [K|M]] [REUSE] [autoextend_clause]
| |||||||||||||||||||||
MINIMUM EXTENT | : | This ensures that every used extent size in the tablespace is a multiple of this integer in Kilobyte or Megabyte. | |||||||||||||||||||||
LOGGING | : | Any changes to tables, indexes & partition within the tablespace will be written to the redo. This is the default mode. | |||||||||||||||||||||
NOLOGGING | : | Opposite of LOGGING. NOLOGGING effects only some DML & DDL commands ex. direct loads. | |||||||||||||||||||||
DEFAULT | : | Default storage parameters for all objects created in the tablespace. DEFAULT STORAGE (INITIAL integer [K|M] Note :
| |||||||||||||||||||||
OFFLINE | : | Makes the tablespace unavailable immediately after creation. | |||||||||||||||||||||
PERMANENT | : | The tablespace can be used to hold permanent objects. | |||||||||||||||||||||
TEMPORARY | : | The tablespace can be used to hold only temporary objects. Can't specify EXTENT MANAGEMENT LOCAL or BLOCKSIZE clause. | |||||||||||||||||||||
extent_management_clause | : | Specifies how the extents of the tablespace is to be managed. [ENTENT MANAGEMENT [DICTIONARY | LOCAL
| |||||||||||||||||||||
segment_management_clause | : | Relevant only for PERMANENT, LOCAL managed tablespaces. Free lists or bitmaps to track free & used space in the segment.
Eg: [SEGMENT SPACE MANAGEMENT AUTO ] |
Space management in Tablespaces:
Tablespaces allocate space in extents. There are two ways to keep track of free & used space:
Locally managed tablespace:
Free extents managed in the tablespace using bitmap. Locally managed is the default beginning with Oracle 9i.
Each bit corresponds to a block or group of blocks
bit value indicates whether free or used.
For permanent tablespaces other than SYSTEM, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TABLESPACE command
For temporary tablespaces you can specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command
Example:
CREATE TABLESPACE fin_data
DATAFILE 'D:/Oracle/Ora90/findata01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;Advantages:
Avoids recursive space management operations
reduced contention on data dictionary tables
eliminates need to coalesce free space, as adjacent free space gets automatically tracked
the sizes of extents that are locally managed can be determined automatically by the system
changes to extent bitmap don't generate undo information because they don't update data dictionary tables, except in special cases such as tablespace quota information
Note :
- Allow 64K for bitmap while calculating for DATAFILE size and UNIFORM extent size. If DATAFILE SIZE is 10M & UNIFORM EXTENT SIZE is 5M, than the space left after the allocation of 1st extent will not be sufficient to make a 2nd 5M extent and is therefore wasted.
Dictionary managed tablespace:
Free extents managed by data dictionary
Appropriate tables are updated when extents are allocated or deallocated.
Each segment stored in the tablespace can have a different storage clause. This storage is more flexible than locally managed tablespaces but much less efficient.
coalescing required
Example:
CREATE TABLESPACE fin_data
| DATAFILE 'D:/Oracle/Ora90/findata01.dbf' SIZE 100M |
UNDO Tablespace:
Used to store undo segments
Can't contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT MANAGEMENT clauses.
| Syntax | Example |
| CREATE UNDO TABLESPACE tablespace | CREATE UNDO TABLESPACE undo1 |
Temporary Tablespace:
Used for sort operations
Can't contain any permanent objects
Locally managed EXTENTS recommended.
Temporary tablespace provides performance improvement when there are multiple sort operations that are too large to fit into the memory.
| Syntax CREATE TEMPORARY TABLESPACE temp1 |
To optimize performance of a sort in a temporary tablespace set UNIFORM SIZE to a multiple of SORT_AREA_SIZE.
TEMPFILEs are similar to DATAFILES except that:
Tempfiles are always set to NOLOGGING mode
Can't make tempfiles read only
Can't rename a temp file
Can't create a tempfile with ALTER DATABASE command.
Media recovery does not recover tempfiles.
Default Temporary Tablespace:
Specifies a system wide default temporary tablespace
Prevents SYSTEM tablespace from being used to store temporary data.
If no such table is created during CREATE DATABASE, then SYSTEM tablespace will be used as default temporary tablespace. A warning to this effect is placed in alert_sid.log
Once defined, any user not explicitly assigned a temporary tablespace are assigned to default temporary tablespace.
Default temporary tablespace can be changed anytime by using:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;
In this case all users assigned the default temporary tablespace are reassigned to the new default.
Creating default temporary tablespace:
| During database creation: CREATE DATABASE chik ...... >>>>When default Temporary Tablespace is created with the CREATE DATABASE, it is of locally managed type<<<< |
|
After database creation: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1; To find the temporary tablespace for the database (View : DATABASE_PROPERTIES): SELECT * FROM DATABASE_PROPERTIES; |
Restrictions on default temporary tablespace:
Can't be:
Dropped until a new default is made available
Taken offline
Altered to permanent tablespace.
Managing Tablespaces:
Read Only:
Causes a checkpoint on the datafiles of the tablespace.
Data available for read only operations
objects such as tables & indexes can be dropped from the tablespace since these commands only effect the data dictionary. For locally managed tablespaces, the dropped segments are changed to temporary segments to prevent the bitmap being updated.
Features:
The datafiles can reside on read only medias such as CD-ROMs.
Eliminates the need to backup large static portions of the database.
To make a read only tablespace writable, all the datafiles of the tablespace must be online .
Steps to create read only write-once read only (WORM) device:
ALTER TABLESPACE userdata READ ONLY;
Execution of command : This command puts the table in a transitional mode during which no more write operations can occur on the tablespace except for the roll back of the existing transactions which previously modified blocks in the tablespace. After all existing transactions have either been committed or rolled back, the command completes & the tablespace is placed in the read only mode.
Advantages:
ALTER TABLESPACE userdata READ ONLY;
Move the datafiles of the tablespace to the read only device.
ALTER TABLESPACE .... RENAME DATAFILE...
Taking a tablespace offline:
Tablespaces that can't be taken offline:
SYSTEM tablespace
tablespaces with active undo segments
default temporary tablespace
Syntax:
-
ALTER TABLESPACE tablespace
{ONLINE | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE| FOR RECOVERY ] };
NORMAL
:
Default option. Flushes all blocks in all datafiles in the tablespace out of the SGA. Media recovery not needed to bring this tablespace back online.
TEMPORARY
:
Performs a checkpoint on all datafiles even if some files could not be written to. Any offline file may require media recovery.
IMMEDIATE
:
Does not ensure that tablespace files are available and does not perform a checkpoint. Media recovery must before bringing the tablespace back online.
FOR RECOVERY
:
Takes tablespace offline for point-in-time recovery
ALTER TABLESPACE userdata OFFLINE;
ALTER TABLESPACE userdata ONLINE;
make a portion of the database unavailable while allowing normal access to rest of the database.
Perform an offline tablespace backup (although it can also be backed while online & in use)
recover a tablespace or datafile while the database is open
move datafile while the database is open
Oracle does not permit any SQL statements to reference any objects contained in the offline tablespace. Users trying to access such objects receive an error.
The event of tablespace going online or offline is recorded in the data dictionary & control files.
When a tablespace is taken offline, Oracle server takes the all associated datafiles offline.
A tablespace which is offline while database shuts down remains offline & is not checked when database is subsequently mounted & reopened.
Note: Oracle automatically switches a tablespace from online to offline when certain errors are encountered ( like when DBWn fails in several attempts to write to a datafile of the database.
Purpose:
Status of offline Tablespace:
Changing storage settings:
Syntax:
ALTER TABLESPACE tablespace
MINIMUM EXTENT integer [K|M]
| DEFAULT storage_clause];
Examples:
ALTER TABLESPACE itemdata MINIMUM EXTENT 4m;
ALTER TABLESPACE itemdata DEFAULT STORAGE(INITIAL 4M NEXT 4M MAXEXTENTS 500);
Note: Storage settings of locally managed tablespaces can not be altered.
Resizing a tablespace:
Enabling automatic extension of Datafiles:
Syntax:
| autoextend_clause for datafiles: [AUTOEXTEND {ON|OFF[NEXT integer[K|M]] |
| CREATE TABLESPACE tablespace |
| ALTER DATABASE [database] |
Manually resizing a Datafile:
| Syntax | Example |
| ALTER DATABASE [database] | ALTER DATABASE |
Note: This command can be used to increase or decrease the size of the database file. If there are datbase objects stored above the specified size, then the database size is decreased only to the last block of the last objects in the datafile.
Adding Datafiles to a Tablespace:
| Syntax | Example |
| ALTER TABLESPACE tablespace | ALTER TABLESPACE sktab |
Moving Datafiles : Methods:
| Syntax 1: | Example |
| ALTER TABLESPACE tablespace | ALTER TABLESPACE sktab |
Note:
Tablespace must be offline.
Target datafiles must exist.
Source filenames must match the names stored in the control file.
Always provide complete filenames including path names to identify old & new files.
Take Tablespace offline.
Use OS to copy/move files
Execute ALTER TABLESPACE command
Bring Tablespace online.
Delete the old file using OS if necessary.
Steps to perform:
| Syntax 2: | Example |
| ALTER DATABASE [database] | ALTER DATABASE |
Shutdown database
Use OS to copy/move files
Mount database
Execute ALTER DATABASE RENAME command
Open the database.
Steps to perform:
Dropping Tablespaces:
Can't drop a SYSTEM tablespace
Can't drop a tablespace with active segments.
| Syntax : | Example |
| DROP TABLESPACE tablespace | DROP TABLESPACE sktab |
| INCLUDING CONTENTS | : | Drops the segments |
---|---|---|---|
| INCLUDING CONTENTS AND DATAFILES | : | Deletes datafiles |
| CASCADE CONSTRAINTS | : | Drops all referential integrity constraints |
A tablespace which still contains data can't be dropped without the INCLUDING CONTENTS option.
When tablespace is dropped only file points in the control file of the associated database is dropped. The datafiles can then be deleted manually. For auto deletion include the AND DATAFILES clause. If the datafiles are OMF then in that case also they are dropped automatically.
A tablespace in read-only mode can also be dropped with contents.
It is recommended to take the tablespace offline before dropping it to prevent any any transaction from accessing the segments within.
Managing Tablespaces using OMF:
Define DB_CREATE_FILE_DEST in either init.ora or set dynamically with ALTER SYSTEM SET db_create_file_dest= 'path/filename';
| Syntax : | Example |
| CREATE TABLESPACE tablespace [DATAFILE [filename] [SIZE integer[K|M] ] ]; | CREATE TABLESPACE sktab DATAFILES SIZE 50M; ALTER TABLESPACE sktab ADD DATAFILE; |
OMF features:
Datafile clause is not required when using OMF. Datafiles are automatically created and located in DB_CREATE_FILE_DEST. Datafile name are generated automatically by Oracle server as (ora_tbs1_2ixfh90q.dbf)
Default SIZE is 100M
AUTOEXTEND is set to UNLIMITED.
Destination can be changed dynamically using ALTER SYSTEM SET db_create_file_dest= 'path/filename'
When OMF tablespace is dropped, associated datafiles are deleted automatically at the OS level.
Views:
Tablespaces | : | DBA_TABLESPACES V$TABLESPACE | : : | |
Datafiles | : | DBA_DATA_FILES V$DATAFILE | : : | Select tablespace_name, file_name, autoextensible from DBA_DATA_FILES; |
Temp files | : | DBA_TEMP_FILES V$TEMPFILE | : |
|