Tag: Tablespace

-- checking tablespace sizes
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Tablespace|Used MB|Free MB|Total MB|Pct. Free|
----------|-------|-------|--------|---------|
SYSAUX | 606| 34| 640| 5|
UNDOTBS1 | 23| 2| 25| 8|
USERS | 2| 98| 100| 98|
SYSTEM | 352| 8| 360| 2|

--check auto extend for a datafile

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;

TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES |
---------------|---------------------------------------|--------------|-----------|
USERS |/u01/app/oracle/oradata/XE/users.dbf |YES |11811160064|
SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984|
UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984|
SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|

--set auto extend off for a datafile (I think you cannot change it back)

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf'
AUTOEXTEND OFF;

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;

TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES |
---------------|---------------------------------------|--------------|-----------|
USERS |/u01/app/oracle/oradata/XE/users.dbf |NO | 0|
SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984|
UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984|
SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|

--Resizing a datafile

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf'
RESIZE 100M;

--adding a new datafile for a tablespace (with Auto extend) to add more memory
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/XE/users3.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
--adding a new datafile for a tablespace (without Auto extend) to add more memory
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/XE/users2.dbf' SIZE 20M
AUTOEXTEND OFF;