CREATING A TABLESPACE

CREATE TABLESPACE emdb DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\MJBDB\emdb01.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON;

 
ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\TESTDB\USERS01.DBF' RESIZE 20M;

ALTER TABLESPACE USERS ADD DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\MJBDB\USERS02.DBF' SIZE 20M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\MJBDB\USERS01.DBF' AUTOEXTEND ON NEXT 1280K MAXSIZE 200M;

ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\MJBDB\USERS01.DBF' RESIZE 50M;

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

 SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.

 SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

 SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Tablespace Report

rem -----------------------------------------------------------------------
rem Filename: tsspace.sql
rem Purpose: Show Used/free space in Meg by tablespace name
rem -----------------------------------------------------------------------

tti "Space Usage for Database in Meg"
SELECT Total.name "Tablespace Name",nvl(Free_space, 0) Free_space,nvl(total_space-Free_space, 0) Used_space,total_space
FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/
tti off

 Other Tablespace space monitoring SQL's

 

select * from DBA_TABLESPACE_USAGE_METRICS;

select tablespace_name, round(sum(bytes)/(1024*1024),2) freeinMB from DBA_FREE_SPACE group by tablespace_name order by freeinMB ;

select * from DBA_HIST_TABLESPACE_STAT;

select * from DBA_HIST_TABLESPACE;

select sum(gb)
from(
select tablespace_name,sum(bytes/(1024*1024*1024)) GB from dba_segments where owner='DBAREFP' group by tablespace_name order by tablespace_name,gb desc
);

select sum(gb)
from(
select tablespace_name,sum(bytes/(1024*1024*1024)) GB from dba_segments where owner='DBAREFX' group by tablespace_name order by tablespace_name,gb desc
);

SELECT tablespace_name, sum(bytes/(1024*1024*1024)) GB
FROM dba_data_files where tablespace_name in (select tablespace_name from dba_segments where owner='DBAREFX')
group by tablespace_name ORDER BY 1,2 desc;

SELECT tablespace_name,file_name, ROUND(bytes/(1024*1024*1024)) GB
FROM dba_data_files where tablespace_name in (select tablespace_name from dba_segments where owner='DBAREFX')
ORDER BY 1,3 desc;

select * from dba_tablespaces;

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;