Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

tablespace create,add,delete,alter,views

Tablespace : is logical storage unit in Oracle Database.
ii) Tablespace connsit of one or more datafiles (check below)
iii) Information about Tablespace can be obtained from view DBA_TABLESPACES, USER_TABLESPACES, DBA_TEMP_FILES (for Temporary Tablespaces)
iv) Tablespace is further divided in to logical units Segments (Segment is divided in to Extent and Extent in to Block) . To know more about Segment, Extents and Blocks click here
v) Various type of tablespace are BIGFILE, SYSTEM, SYSAUX, UNDO
Datafiles : is physical structure to store oracle data
ii) One or more physical datafile are logically grouped together to make a tablespace
iii) Information about Datafile can be obtained from view DBA_DATA_FILESiv) A Datafile can be associated with only one tablespace
Adding Tablespace in databaseUse CREATE TABLESPACE command to add tablespace in Database like
CREATE TABLESPACE <tablespace_name> DATAFILE <location_of_datafile>
CREATE TABLESPACE my_tablespace DATAFILE ‘/u01/oracle/oradata/data01.dbf';
To create undo tablespace
To create Temporary tablespace
CREATE TEMPORARYTABLESPACE <tablespace_name> TEMPFILE <location_of_datafile> SIZE <size> [AUTOEXTEND ON|OFF] 
Adding Datafile in a TablespaceUse ALTER TABLESPACE to add datafile in tablespace like
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile>
ALTER TABLESPACE my_tablespace ADD DATAFILE ‘/u01/oracle/oradata/data02.dbf';
To add temporary file in TEMP table space
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size>;
Modify DatafileYou can modify datafile using ALTER DATABASE command like
ALTER DATABASE DATAFILE ‘/u01/oracle/oradata/data02.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;
which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required.


Popular posts from this blog

7 Steps to configure BDR replication in postgresql

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?


How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory