Monday, June 19, 2006

ALL ABOUT TABLESPACES

ALL ABOUT TABLESPACES

Here is all that you might need to know about when working with tablespaces. I've just given the syntax taking only some requirements into account. Change them according to your requirement.

DISCLAIMER: All the views experessed here are my own. I'm not responsible for any problem that you might get into by following the below syntax. Use them at your own risk. But, this is pretty much i use and they worked fine for me until now.

NOTE:

1. Never, Ever, Ever delete a datafile. You can only resize the datafile to five oracle blocks. You can drop the whole tablespace though !! If you delete the datafile, then you’ve to recover it. The control file remembers that the datafile is still there eventhough you drop it at the database level. Also, remember that there is no RECYCLE BIN in unix.

2. You can’t drop the default temp and undo tablespaces. You need to create a new temp/undotbs, make it default temp/undotbs and then drop the old temp/undo tbs.

3. Make sure you fill up the temp datafile once you add them to the temp tablespaces, ‘cos it does not really occupy the whole OS space until it is used completely. If you are on solaris, use mkfile command to do that.


---------------------------------------------------------------------------------
---------------------------------------------------------------------------------

1. To get the script of an already existing tablespace, use the following. (Reverse engineering the tablespace creation script)

select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;

For Example, to get the script of the SYSTEM tablespace,

select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;


2. To create a tablespace: (with extent management local, uniform size 128K extent sizes)

CREATE TABLESPACE "tablespace_name" DATAFILE
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M,
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;

3. To alter/add a datafile :

alter database datafile '/db/xxxx/xxx/datafilename.dbf' resize 2048M;

To add a datafile to an existing tablespace:

alter tablespace tablespace_name add datafile ‘datafile_name’ size xxxxM;

For example, to add a datafile of size 512M to the SYSTEM tablespace,

Alter tablespace system add datafile ‘/db/dbxxx/oracle/xxxxxxx/datafile_name.dbf’ size 512M;

4. To drop a tablespace:

drop tablespace tablespace_name including contents and datafiles;

5. To get the tablespace info of a database:

Note that the values are rounded to the nearest integer.

select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;

6. To check the default temporary tablespace of the database:

select property_name, property_value from database_properties;

select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

7. To change the default temporary tablespace of the whole database:

alter database default temporary tablespace temp_tablespace_name;

8. To change the default temporary tablespace of a particular user :

alter user username default temporary tablespace temp_tablespace_name;


9. To create a temp tablespace:

On solaris:

host /usr/sbin/mkfile 1024m /db/db004/oracle/TOR_M704/dsmtemp.df01.dbf

create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' reuse
extent management local uniform size 128K;

If the OS is not solaris, then

create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' size 1024M
extent management local uniform size 128K;

NOTE : When you create the tempfile without using mkfile, the file is created but the entire space is not occupied at the OS level. Lets say that there is 1GB in that mount point where you have created the datafile of size 512M. It still shows that there is 1GB of space in that mountpoint eventhough this tempfile is created. This space is occupied only when that space is used. You can use the following script to recursively sort until the space is filled.

Select * from dba_source a, dba_source b, dba_source c order by 1;

This script basically keeps on sorting and is never ending. So, it fills up the temp space continiously until the whole tempfile is full. Once the whole tempfile is full, you can see that the space in that mountpoint (where there was 1GB of space ) is now only 512MB, ‘cos 512MB is used for this tempfile.


10. To add a tempfile to the existing temp tablespace:

alter tablespace temp add tempfile ‘/db/dbxxx/xxx/temp02.dbf’ size 1024M;

11. To resize the tempfile

alter database tempfile ‘/db/dbxxx/xxx/temp02.dbf’ resize 512M;

12. To drop and recreate a temp tablespace (lets say temp1):

a) first create another temp tablespace ..lets say temp2
b) then make this the default tablespace for the database, also for the users who had default temp tablespace as temp1
c) then drop the tablespace temp1
Note: The database won’t let you drop the default temporary tablespace of the database anyway.

13. To drop and recreate the undotablespace:

Follow the same steps as in step 12.

14. To drop a tempfile:

In 10g:

alter database tempfile '/db/dbxxx/xxxx/xxxx/temp01.dbf' drop including datafiles;

In 9i:

Alter database tempfile ‘/db/dbxxx/xxx/xxxx/temp01.dbf’ offline drop;

NOTE : Be careful in deleting the temp files. Refer to the documentation before doin’ that and remember that there is no RECYCLE BIN for unix. So, be extra careful when you delete something in unix.

15. To get the space usage of the datafiles:

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

Author : Kam Muhamad Sirajdin (Acknowledgement)


-- Will keep on adding stuff as i remember.....so keep checking.....

12 comments:

miguel ojeda said...

hi i am miguel from Peru , could yoy tell me how to convert files in oracle to dbf files?

please i need it for a job

my mail is ojedmig@yahoo.com

Sandy said...

For point 9 Creation of TEMP table space:
Temporary tablespaces are created using temp files instead of datafiles. Temp files are allocated slightly differently than datafiles. Although datafiles are completely allocated and initialized at creation time, temp files are not always guaranteed to allocate the disk space specified. This means that on some Unix systems a temp file will not actually allocate disk space until a sorting operation requires it. Although this delayed allocation approach allows rapid file creation, it can cause problems down the road if you have not reserved the space that may be needed at runtime.



Workaround for Deferred Temp File Disk Space Allocations
A workaround for allocating temp file space at runtime is to preallocate it, just like you do with a datafile. In fact, you first allocate it as a datafile and then drop the tablespace, leaving the file. Finally, you create your temp tablespace reusing the old datafile as a temp file.

-- first create it as a permanent tablespace
-- to force the disk space to be allocated
CREATE TABLESPACE temp
DATAFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G;

-- dropping the tablespace does not remove
-- the file from the file system
DROP TABLESPACE temp;

-- the keyword REUSE is needed to use the existing
-- file created in the previous steps
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G REUSE

Sandy said...
This comment has been removed by a blog administrator.
vish said...

Thanks sandy. Thats a good way of doin' it. Another way of doin' it is using mkfile in unix, which allocates the space (as opposed to touch command). the syntax is

mkfile 1024M /oracle/10g/oradata/xxx.dbf
create temporary tablespace temp tempfile '/oracle/10g/oradata/xxx.dbf' reuse;

Anonymous said...

Hi vish .. I'm Ahmed .. This is one of the best posts and blogs I have ever come across .. I will keep reading the rest posts .. From your Tablespace Experience - vish - do I need to increase the default temporary tablespace if it is full or it will not affect performance? .. and how? increase space or add a datafile?

**********************
CREATE TEMPORARY TABLESPACE "TEMPORARY" TEMPFILE
'X:\X\ORADATA\X\TEMPORARY1.DBF' SIZE 0.000000004294967296 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
**********************
Best regards.

Anonymous said...

Hi,
Please assist me for my query.
I have to increase the size of the tablespace in APPLICATION server and with Unix commands only.

I can see the sql command to increase the size of the tablespace but no information related for the unix os.-Oracle 10g DB.

Thanks & Regards,
Jaya

Anonymous said...

Thanks a lot, a very good source of useful info !!

Deepak said...

The tablespace & monitoring scripts are very helpful, I really appreciate it. Can i get some more excellent scripts.

deepak_454@rediffmail.com

srikanth said...

CREATE TABLESPACE "EXAMPLE"
LOGGING DATAFILE 'xxxxx/.../example01.dbf' SIZE 50M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Why we have to use the REUSE option here.

Anonymous said...

Guy
You have got to see this. Obama playing on XBox. Funniest video ever. http://bit.ly/bllhx1

Anonymous said...

What interesting idea..

ARUN said...

hi, i am arun from delhi(INDIA) i want to know why we r use reuse option.. plz help me...thnaks