Database Space Management

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;

Unix command

$du -sh

$df -k

Oracle database space management

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

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.

Welcome to Oracle Database Administrator Home

24x7 oracle database support and solutions