Tablespace:
A database is divided into one or more logical storage units called tablespaces.
All tablespaces, including the SYSTEM tablespace, can be locally managed.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7116328455352
Types: 1.System tablespace 2. Non System tablespace
System
SysAUX
Undo
Temporary
— A different block size object cannot move between the tablespace.
— All the data file name should be created as unique. Since, it might have some issues in cloning a database.
TEMPFILEs are not fully initialized (sparse). When you 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. This implies that one can just recreate them whenever you restore the database.
Query to get tablesapce space:
sql> select * from v$tablespace;
sql> select * from dba_tablespaces;
Difference between V$ view and DBA
| V$ | DBAs |
| Dynamic view | Data dictionary view |
| It can be queried in mount and open | It can only queried in open |
| It will not have ‘s’ at the end of the command | It will have ‘s’ at the end of the command |
Create new TableSpace
sql> create tablespace <ts ame> datafile ‘\opt\oracle.dbf’ size 100m;
Query to get a data file info
sql> select * from v$datafile;
sql> select * from dba_data_files;
Query to get a table space growths
Total – DBA_data_files;
sql> select tablespace_name, sum(bytes/1204/1024 from dba_data_files group by tablesspace_name;
Used – DBA_segment;
Free – DBA_free_space;
Expand the Tablespace size:
There are two options are available, either you can add one more data file or expand the existing data file. The Max size of each data file is 32G.
sql> alter tablespace <Ts name> add datafile ‘opt/oracle/_3.dbf’ size 100m;
sql> alter database datafile ‘opt/oracle/_3.dbf’ resize 200m;
Table space mode: 1.Read only 2.Read Write
sql> alter tablespace <Ts name> read only;
sql> alter tablespace <Ts name> read write;
sql> alter tablespace <Ts name> offline;
sql> alter tablespace <Ts name> online;
Data file Offline:
This is mainly used to recover the data file from corruption.
To take the data file offline, should first take the tablespace offline and then data file.
sql> alter tablespace <Ts name> offline;
sql> alter database datafile ‘opt/oracle/.dbf’ offline; — In Archive / no Archive mode
sql> alter database datafile ‘opt/oracle/.dbf’ offline drop; — No archive mode
‘offline Drop’ will not drop the files the data files physically, it will remove the details from control file.
Rename the tablespace:
sql> alter tablespace <ts name> rename to ‘new name’;
Data file rename and move: It’s a physical one needs down time
sql> alter tablespace <ts name> offline;
$ cd /opt/oracle/
$ ls
$ cp tools.dbf tools_new_01.dbf
sql> alter tablespace < ts name > rename datafile ‘old full location’ to ‘new full location’;
Example:
ALTER TABLESPACE users
RENAME DATAFILE ‘/u02/oracle/rbdb1/users01.dbf’,’/u02/oracle/rbdb1/users02.dbf’
TO ‘/u03/oracle/rbdb1/users01.dbf’,’/u04/oracle/rbdb1/users02.dbf’;
Auto extent:
By default it will be off. You can enable it, oracle will increase this by 1 Mb, 1MB so on.
sql> alter database datafile ‘opt/oracle/.dbf’ autoextend on;
Ex
ALTER TABLESPACE users
ADD DATAFILE ‘/u02/oracle/rbdb1/users03.dbf’ SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;
Create tablespace with auto extend on
sql> create tablespace <ts name> ‘opt/oracle/.dbf’ size 100m autoextend on;
Drop tablespace:
sql> Drop tablespace <ts name> including contents
“including contents” It only remove the content not the physical file.
sql> Drop tablespace <ts name> including contents and datafiles;
Optional –“ including contents and datafiles “, will remove the files physically. It’s always good to use with include command.
SQL> set linesize 3000
SQL> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,MAX_SIZE,PCT_INCREASE,MIN_EXTLEN,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from USER_TABLESPACES;
System TableSpace:
System:
It’s a source of the database. (Data dictionary view and dynamic view, default, SPs, Functions is stored here.)
We can create a LMT and can migrate the DMT to LMT. If the system is in LMT.
SysAUX: (10g Onwards)
It’s an auxiliary tablespace to the SYSTEM tablespace.
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail.
AWR and the OEM repository task are processed here.
Other components: Oracle streams, ultra search & oracle text all are processed here.
Temporary:
It contains temporary data that persists only for the duration of the session.
No permanent data. All the sorting processed here.
No backup needed, we can rename & relocate and drop the data files.
A default temporary cannot be taken off and drop. Sorting will not run and it will send an error. By default 1 temporary tablespace will be created. We can create many and can assign one per user.
In case if it is full, you can shrink the tablespace or data file.
Create temporary tablespace
Create temporary tablespace temp tempfile ‘/opt/xx.tmp’ size 100m;
Find the tempfile
Select * from v$tempfile;
select * from dba_temp_files;
Find the default temp tablespace
Select * from database_Properties where property_name like ‘DEFAULT’;
SELECT * FROM database_properties WHERE property_name like ‘%TABLESPACE’;
Change the default temp tablespace
Alter database default temporary tablespace temp;
Growth checking of temp tablespace
Select tablespace_name, total_extents, used_extents,free_extents from v$sort_segment;
Best practice, we need to add space, when it reaches 50% threshold.
Add a temp file
Alter tablespace temp add tempfile ‘/opt/xx02.tmp’ size 100m;
Resize the temp file
Alter database tempfile ‘/opt/yy.tmp’ resize 200m;
Drop the temp file
Drop tablespace tmp1 including contents and datafiles;
Undo Tablespace (9i Onwards)
Old records are stored here, until before commit. Flashback features use the Automatic Undo Management system to obtain metadata and historical data for transactions.
Undo will be used in following area. If no undo tablespaces available Oracle uses system tablespace.
Transaction rollback – The rollback records are taken from undo to rollback.
Transaction recovery – It’s a SMON process (Redo process)
Read consistency – The undo will give a read consistency reader do not block writers vice versa.
In some cases, the old records may not be available, if the undo is full it gets overwrite and will send an error : ORA – 01555 snapshot too old.
Parameters
Undo_management = auto/manual – Default AUTO
Undo-tablespace = undotbs1 – Default name
Undo_Retention = 900 – Seconds
Retention Guarantee = GUARANTEE, NOGUARANTEE, or NOT APPLY – Default NOGUARANTEE
Oracel by default use Auto and you can change if needed.
We can create many undo tablespace. It cannot be assign to specific users.
Create Undo tablespace:
Create undo tablespace name_undo datafile ‘/opt/o1.dbf’ size 100m;
To view the Undo tablespace: – A normal V$ and DBAs table
To find Undo default tablespace info:
Show parameter undo;
To Change default to some other
Alter system set undo_tablespace= undotbs;
Growth Check: We can get this from normal tablespace code.
To drop Undo tablespace:
We cannot drop the default undo. Before drop change the default and do not drop default one
Drop tablespace undotbs1 including contents and data files;
Big Tablespace (10g onwards)
It mostly used for big database – more than 5 TB.
Max datafiles limit 64000
It only supports – 8k & 32K blocks.
For 8K – you can use 32 TB of tablespace and 32k – 128 TB of space.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three Exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace – Only managed manual.
To find the big tablespace:
Select bigfile from v$tablespace;
Select bigfile from dba_tablespaces;
Create big tablespace:
Create bigfile tablespace <name_bigfile> datafile ‘/opt/big.tbs’ size 100g;
To use the big tablespace, we need to change the normal tablespace into bigfile.
Alter database set default bigfile tablespace;
Alter database default tablespace <name_bigfile>;
If we do not mention in the tablespace creation, Oracle wil automatically use default one i.e either normal tablesapce or bigfile, which is default.
To Change back to normal tablespace:
Alter database set default smallfile tablespace;
Alter database default tablespace <Anytablespace>;
How to resize the tablespace:
Alter tablespace <bigtbs> resize 200g;
The command only for big tablespace
To Drop big tablespace:
Drop tablespace bigtbs including contents and datafiles;
Types of tablespace
Locally managed tablespace – LMT and dictionary managed tablespace – DMT.
By default from 9i onwards all are LMT.
Differences:
| LMT | DMT |
| It can have the extent free space in the bitmap within tablespace and manage local | It has to go system tablespace to get extents free info |
| All the extents are Auto allocated /uniform | non – uniform |
| User friendly | No |
| Less fragmentation | More fragmentation |
| By default, all tablespaces are LMT |
Create LMT:
Create tablespace <tools> datafile ‘/opt/01.dbf’ size 100m extent management local uniform size 100k;
Create DMT:
CREATE TABLESPACE tbs_DMT_01 DATAFILE ‘/opt/oracle/oradata/test1/tbs_DMT_01.dbf’ SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
You cannot create a DMT from 9i onwards.
Multiple temporary tablespace (10g onwards)
To use this we can create a group and add more than one temporary tablespace.
To add temporay tablespace:
You can create a new one or add the existing one.
Create temporary tablespace <temp> tempfile’/opt/01.tmp’ size 100m tablespace group temp_gs;
Alter tablespace <temp1> tablespace group temp_gs;
Find a tablespace in a group:
Select * from dba_tablespace_groups;
Assign a group to a default temporary tablespace:
Alter database default temporary tablespace temp_gs;
Remove the temporary tablespace from the group:
Alter tablespace temp1 tablespace group ‘ ‘;
A single quotes will remove the tablespace.
To remove the group, you can remove the other tablespaces, but make sure you do not remove the default temporary tablespace. If it’s used change that to some other as default.
