Sunday, September 03, 2006

Configuring Listeners

My Listener files seems quite messed up. Here are the extracts:

# 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:

    • Partitioned using range, hash, composite or list partitioning.

    • Each partition is a segment and can located in a different tablespace.

    • Special command available to manage partitions within a table.

    • Useful for large table which can be queried & manipulated using several processes concurrently.


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:

    • provides fast key-based access to table data for queries involving exact matches and range searches.

    • lower storage requirements as columns not duplicated in separate index.

    • the remaining non-key columns are stored in the index unless the index entry becomes too large, whereby Oracle provides an OVERFLOW clause to handle the problem.


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:

    • Clustering is a mechanism which is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it was stored in a regular table.

    • Clusters normalizes the physical storage of the tables without affecting the logical structure.

    • Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scan on clustered tables are generally slow.

    • Updating one of the columns in the cluster key can cause row migration.

      Cluster key:

    • Clusters have a cluster key which is used to identify the rows.

    • The cluster key can consist of one or more columns.

    • Tables in a cluster have columns corresponding to the cluster key.

    • The cluster key is independent of the primary key. The tables in a cluster can have primary key which can be the cluster key or may be not.

Extended ROWID Format : break up of 10 bytes:




Object No.

Relative File No.

Block No.

Row No.

Display(base 64)

OOOOOO

FFF

BBBBBB

RRR

Display size

6

3

6

3

No. of bits

32

10

22

16

Possible value (estimate!)

4 billion objects

1022 files/tablespace

4 M blocks/datafile

64K rows/block

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:



Block number

Row number

Absolute File number

Display position

1-8

10-13

15-18

Display (base 16)

BBBBBBBB

RRRR

FFFF

Display size

8

4

4

No. of bits




Possible value



1022 files/database

Notes :

    • Display uses base-16 format.

    • Prior to Oracle 7, Oracle required datafile numbers to be unique within the database

    • Oracle 8.0.3 had introduced a 16 byte extended ROWID temporarily. Thereafter the 10bytes format was introduced.

Uses :

    • Still used in non-partitioned indexes on non-partitioned tables where all index entries refer to rows within the same segment.

Row structure:


Row Header

Column length

Column Value

Col. Len.

Col Val

...

...

    • No. of columns in the row

    • Row Lock status

    • Chaining information

Size=1 byte if len<=250

Size=3 byte if len>250







Notes :

    • Columns are generally stored in the order in which they are defined.

    • Any trailing NULL column are not stored. A single byte for column length is required for

    • non-trailing NULL columns.

    • Adjacent rows don't require any space in between them. Each row in a block has an entry in the row directory. The directory slot points to the beginning of the row.

Creating a Table:


CREATE TABLE tablename (column_name, column_datatype ,.....)
STORAGE (INITIAL integer K|M NEXT integer K|M PCTINCREASE integer
MINEXTENT integer MAXEXTENT integer)
TABLESPACE tablespace_name;


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
{[storage_clause]
[INITRANS integer]
[MAXTRANS integer]}

ALTER TABLE hr.employees
PCTFREE 20
PCTUSED 60
STORAGE (NEXT 100K MINEXTENT 5 MAXEXTENT 50);



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:

  • INITIAL value can't be modified.

  • The value of NEXT will be rounded to a value that is a multiple of the block size >= to the value specified.

Guidelines for creating a tables:


    • Place tables in separate tablespace from those holding undo segments, indexes & temporarily segments.

    • Use locally managed tablespaces to avoid fragmentation

    • Use few standard extent sizes to reduce tablespace fragmentation

Creating Temporary Tables :


CREATE GLOBAL TEMPORARY TABLE tablename
[ON COMMIT [DELETE | PRESERVE] ROWS]
(column_name column_datatype ,.....);

Note:

  • The definition of a temporary table is visible to all sessions.

  • Data in a session is private to the session. Each session can only see and modify its own data.

  • DML locks are not acquired on the data of the temporary tables.

  • Indexes, views & triggers can be created on temporary tables. Export & import utilities can be used to export/import definition of temporary tables.


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:

  • Columns that are initially NULL and latter filled up

  • Columns that are likely to increase in size with updates.


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
ALLOCATE EXTENT [([SIZE integer [K|M]]
[DATAFILE 'filename'] )]

ALTER TABLE hr.employees
ALLOCATE EXTENT (SIZE 400K
DATAFILE 'd:/Oracle/ora90/emp1.dbf');


Purpose:

  • To control distribution of extents across files.

  • Before loading data in bulk to avoid dynamic extension of tables.

Notes:

  • If SIZE is omitted NEXT_EXTENT from DBA_TABLES is used.

  • If DATAFILE clause is not used, Oracle will allocate the extent in one of the files in the tablespace containing the table.

  • Specified DATAFILE must belong to the tablespace to which the table belongs. Otherwise an error is thrown.

  • The NEXT_EXTENT value in DBA_TABLES will not be affected by the manual extent allocation. Oracle server will not recalculate the size of the next extent when this command is executed.

Nonpartitioned Table Reorganization:


ALTER TABLE hr.employees
MOVE TABLESPACE sktab;



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:

  • Moving a table from one tablespace to another.

  • Reorganizing the table to eliminate row migration.

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
[{DROP|REUSE} STORAGE];

TRUNCATE TABLE hr.employees;



Characteristics:

  • All rows in the table are deleted.

  • No undo data is generated and the command commits implicitly, TRUNCATE TABLE being a DDL command.

  • Corresponding indexes are truncated too.

  • A table which is reference by a foreign key can't be truncated.

  • The delete trigger don't fire when this command is used.

Dropping a Table:


Syntax:

Example:


DROP TABLE [schema.] tablename
[CASCADE CONSTRAINTS];

DROP TABLE hr.departments
CASCADE CONSTRAINTS
;


Characteristics:

  • When a table is dropped, the extents used by it are released. If they are contiguous they may be coalesced either automatically or manually at a latter stage.

  • CASCADE CONSTRAINTS is necessary if the table is a parent table is a foreign key relationship.

Dropping a Column:


ALTER TABLE hr.employees
DROP COLUMN
comments
CASCADE CONSTRAINTS CHECKPOINT 1000;


Characteristics:

  • Dropping a column cleans unused and potentially space demanding columns without export or import of data and recreate indexes & constraints.

  • It can take significant amount of time as all the data for the column is deleted from the table.

  • Requires a lot of undo space. CHECKPOINTS can be specified to minimize the use of undo space. The the above example, a checkpoint occurs every 1000 rows.

  • The table is marked invalid until the operation continues.

  • If the instance fails during the operation, the table remains invalid on startup, and the operation needs to be completed. To resume interrupted drop operation:

    ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

    The above command will generate an error if the table is in a valid state.

  • Before 8i it was not possible to drop a column.

UNUSED column option:


ALTER TABLE hr.employees
SET UNUSED COLUMN
comments
CASCADE CONSTRAINTS;


ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT
1000;


ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;



Characteristics:

  • Marking a column unused is relatively quick as it does not reclaim unused disk space as the data is not removed. Unused columns act as if they are not part of the table. They don't show up in the DESCRIBE command. A user can add a new column with the same name as an unused column.

  • Can be especially useful when more than one columns needs to be dropped. Dropping each will require all rows of the table to be updated twice. Mark them as unused and then drop in one go.

  • Dropping unused columns can be accomplished during off-peak hours to minimize load on the system.

Restrictions on dropping a Column.

    Can't do the followings:

  • drop a column from an object type table

  • drop a column from nested tables

  • drop all columns in a table

  • drop a partition key column

  • drop a column from tables owned by SYS

  • drop a column from Index-Organized-Tables if the column is a primary key.

  • A LONG or LONG RAW column that is unused but not dropped will prevent an add of a LONG or LONG RAW column to the table.

Views


DBA_UNUSED_COL_TABS



DBA_PARTIAL_DROP_TABS



DBA_TABLES



DBA_OBJECTS



Thursday, August 24, 2006

Segments & Blocks

Types of Segments:
  1. Table

  2. Table Partition : for partitioned table, each partition a segment & storage parameters can be specified to control them independently.

  3. Cluster : contains one of more tables which share same storage characteristics.

  4. Index : one index segment for each index

  5. Index-organised table

  6. Index partition

  7. Undo segment

  8. Temporary segment

  9. LOB segment

  10. Nested table : The nested table is stored in a separate segment

  11. 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:


DB_2K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_32K_CACHE_SIZE


DB_4K_CACHE_SIZE

DB_16K_CACHE_SIZE


Creating tablespaces with non-standard block size :


CREATE TABLESPACE sktab
DATAFILE 'sk01.dbf'
SIZE 10M BLOCKSIZE 4K;

          • View : dba_tablespaces

Block space Utilization paramenters:

    Parameters



Default values
  • INITRANS
  • MAXTRANS
  • PCTFREE
  • PCTUSED
:
:
:
:
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.
  • FREELIST
: 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 :

  • Ease of management : PCTUSED, FREELISTS, FREELIST GROUPS managed automatically.

  • Better space utlization : All objects, especially those with varying row sizes, utilize space more effeciently.

  • better performance for concurrent inserts.

Restrictions :

  • Can't be used for tablespaces with LOBs.

Configuring :

CREATE TABLESPACE sktab
DATAFILE 'D:/Oracle/Ora90/sktab01.dbf'
SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

Notes :

  • Once bitmap segments are specified at tablespace creation, it can't subsequently be altered. Any specification of PCTUSED, FREELISTS, FREELIST GROUPS is ignored.

  • Segments that can be bitmap managed are regular tables, indexes, Index organised tables and LOBs.

Segment space management (Manual) : This is the default method. It was the only method available previous versions of Oracle. Allows configuration of data blocks using PCTUSED, FREELISTS, FREELIST GROUPS.

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)

SYSTEM Tablespace:

Non-SYSTEM Tablespace:

Creating TABLESPACE:


CREATE TABLESPACE tablespace


[DATAFILE clause]
[MINIMUM EXTENT integer [K|M]]
[LOGGING | NOLOGGING]
[DEFAULT storage_clause]*
[ONLINE | OFFLINE]
[PERMANENT | TEMPORARY]
[extent_management_clause]**
[segment_management_clause]***

* Only for dictionary managed tablespace

**LOCAL

DICTIONARY

:

:

AUTOALLOCATE | UNIFORM SIZE

Uses the DEFAULT storage_clause

***Only for permanent local tablespace.


Examples :

CREATE TABLESPACE fin_data
DATAFILE
'D:/Oracle/Ora90/findata01.dbf' size 100M;


CREATE TABLESPACE sktab
DATAFILE 'D:/Oracle/Ora90/sktab01.dbf'
SIZE 10M
EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT
AUTO;


CREATE TABLESPACE fin_data
DATAFILE 'D:/Oracle/Ora90/sktab01.dbf' SIZE 100M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);



tablespace

:

Name of the tablespace to be created

DATAFILE

:

Datafile(s) which make up the tablespace

filename [SIZE integer [K|M]] [REUSE] [autoextend_clause]

filename

:

Name of a datafile in the tablespace

SIZE

:

Size of the file. K : Kilobytes, M: Megabytes

REUSE

:

Reuse an existing file

autoextend_clause

:

Enables or disables autoextention of the datafile.

[AUTOEXTEND {OFF | ON [NEXT integer [K|M]]
[MAXSIZE UNLIMITED | integer [K|M]] } ]


NEXT

:

The size in bytes of the next increment of disk space to be allocated automatically when more extents are required.

MAXSIZE

:

Maximum disk space allowed for automatic extension of a datafile

UNLIMITED

:

The disk space that can be allocated to the datafile or tempfile is not limited.

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]
NEXT integer [K|M] MAXEXTENTS integer)

Note :

  • Only meant for Dictionary managed tablespace. This is the old way of doing things. It is a default setting to be used for objects creations that don't have a storage parameters defined for them.

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
[AUTOALLOCATE | UNIFORM [SIZE integer [K | M]]]]

DICTIONARY

:

Tablespace is managed using dictionary tables.

LOCAL

:

Tablespace is managed locally using bitmaps. In this case can't specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.

AUTOALLOCATE

:

The tablespace is system managed. Can't specify extent size. This is the default setting.

UNIFORM

:

The tablespace is managed using uniform extents of SIZE bytes. Default size is 1M.

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:

Dictionary managed tablespace:


DATAFILE 'D:/Oracle/Ora90/findata01.dbf' SIZE 100M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);

UNDO Tablespace:


Syntax

Example


CREATE UNDO TABLESPACE tablespace
[DATAFILE clause];

CREATE UNDO TABLESPACE undo1
DATAFILE 'D:/Oracle/Ora90/undo01.dbf'
SIZE
50M;

Temporary Tablespace:


Syntax

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE 'D:/Oracle/Ora90/temp01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

Default Temporary Tablespace:


During database creation:

CREATE DATABASE chik ......
....
....
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'D:/Oracle/Ora90/temp.dbf' SIZE 100M
....

>>>>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;

Managing Tablespaces:


autoextend_clause for datafiles:

[AUTOEXTEND {ON|OFF[NEXT integer[K|M]]
[MAXSIZE UNLIMITED|integer[K|M]] } ];



CREATE TABLESPACE tablespace
DATAFILE 'D:/Oracle/Ora90/sk01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;



ALTER DATABASE [database]
DATAFILE filename [, filename] ...
autoextend_clause;


Syntax

Example


ALTER DATABASE [database]
DATAFILE filename [, filename] ...
RESIZE integer[K|M];

ALTER DATABASE
DATAFILE 'D:/Oracle/Ora90/sk02.dbf'
RESIZE 200M;


Syntax

Example


ALTER TABLESPACE tablespace
ADD DATAFILE
filespec [autoextend clause] ;

ALTER TABLESPACE sktab
ADD DATAFILE 'D:/Oracle/Ora90/sk03.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M ;


Syntax 1:

Example


ALTER TABLESPACE tablespace
RENAME DATAFILE filename [, filename] ... TO
filename [, filename] ...
;

ALTER TABLESPACE sktab
RENAME DATAFILE 'D:/Oracle/Ora90/sk03.dbf' TO SK_TAB03 ;


Syntax 2:

Example


ALTER DATABASE [database]
RENAME FILE filename [, filename] ... TO
filename [, filename] ...
;

ALTER DATABASE
RENAME DATAFILE 'D:/Oracle/Ora90/sk03.dbf' TO 'D:/Oracle/Ora90/SK_TAB03';


Syntax :

Example


DROP TABLESPACE tablespace
[INCLUDING CONTENTS [AND DATAFILES]
[CASCADE CONSTRAINTS]] ;

DROP TABLESPACE sktab
INCLUDING CONTENTS AND DATAFILES;



INCLUDING CONTENTS

:

Drops the segments


INCLUDING CONTENTS AND DATAFILES

:

Deletes datafiles


CASCADE CONSTRAINTS

:

Drops all referential integrity constraints


Syntax :

Example


CREATE TABLESPACE tablespace

[DATAFILE [filename] [SIZE integer[K|M] ] ];

CREATE TABLESPACE sktab DATAFILES SIZE 50M;

ALTER TABLESPACE sktab ADD DATAFILE;

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

:




Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google