set serveroutput on
BEGIN
for i in (select file_name,autoextensible from dba_data_files where autoextensible = ‘YES’) loop
dbms_output.put_line(‘File name is: ‘||i.file_name||’ and autoextensible is: ‘||i.autoextensible);
execute immediate ‘alter database datafile ”’||i.file_name||”’ autoextend off’;
end loop;
END;
/
the script below will help you to see how big auto allocation is set plus all the report for the current used and free space.
[spocoradb1.aeso.ca]/dbadmin/workspace2/alima/sql>cat db_tablespaces_report.sql
set linesize 200
set trimspool on
set pagesize 100
set feedback off
column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a30 heading “Tablespace Name”
column Kbytes_alloc format 999,999,999,999 heading “Alloc|KBytes”
column currently_used format 999,999,999,999 heading “Used|KBytes”
column kbytes_free format 999,999,999,999 heading “Free|KBytes”
column Kbytes_max format 999,999,999,999 heading “MaxPoss|Kbytes”
column pct_free format 999.99 heading “%Free”
column max_kbytes_free format 999,999,999,999 heading “MaxPoss|Free|KBytes”
column max_pct_used format 999.99 heading “%Max|Used”
column host_name format a30
spool $vfile_email
select TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) TODAY FROM dual
/
SELECT host_name, instance_name FROM v$instance
/
break on report
compute sum of Kbytes_alloc on report
compute sum of kbytes_free on report
compute sum of currently_used on report
compute sum of Kbytes_max on report
select decode(extent_management,’LOCAL’,'*’,”) ||
decode(segment_space_management,’AUTO’,'a ‘,’m ‘) ||
b.tablespace_name name,
Kbytes_alloc,
kbytes_max,
(Kbytes_alloc – NVL(kbytes_free, 0)) currently_used,
NVL(kbytes_free, 0) kbytes_free,
(NVL(kbytes_free, 0))*100/Kbytes_alloc pct_free,
(Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) max_kbytes_free,
(Kbytes_alloc – NVL(kbytes_free, 0))*100/kbytes_max max_pct_used
FROM
( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(decode(AUTOEXTENSIBLE, ‘NO’, bytes,
decode(sign(maxbytes – bytes), 1, maxbytes, -1, bytes, 0,maxbytes)))/1024 Kbytes_max,tablespace_name
from sys.dba_data_files
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name
AND c.tablespace_name = b.tablespace_name
AND b.tablespace_name NOT IN (select distinct TABLESPACE_NAME from
DBA_UNDO_EXTENTS)
— 5% free and less than 2GB
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max < 5
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) < (2*1024*1024)
ORDER BY (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max, b.tablespace_name
/
spool off
– Set default tablespace for all users, so when users are created without specifying default tablespace it will be user’s default tablespace
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;
Database altered.
– To find the default tablespace name for the database is
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;
PROPERTY_VALUE
——————
ABC
Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace
SQL> drop tablespace abc including contents and datafiles;
drop tablespace abc including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
So changing the default tablespace for the database will change default tablespace for users who have that tablespace as default tablespace. The following demonstrates what happens when default tablespace is changed for the database.
– set default tablespace to be users
SQL> ALTER DATABASE DEFAULT TABLESPACE users;
Database altered.
– create user with tablespace users as default
SQL> create user test1 identified by test1 default tablespace users;
User created.
– change default tablespace for the database
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;
Database altered.
– it changes default tablespace for user TEST1
SQL> select default_tablespace from dba_users where username = ‘TEST1′;
DEFAULT_TABLESPACE
——————————
ABC
By default if default tablespace is not specified the default tablespace is set to SYSTEM, so you can specify the default tablespace when creating the database (For example: CREATE DATABASE … DEFAULT TABLESPACE abc…)