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

:




Comments: Post a Comment

Links to this post:

Create a Link



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