5、查看表空间是否自动扩展
SQL> select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;

更改自动扩展属性：

alter database datafile
'/home/app/oracle/oradata/oracle8i/sales01.dbf',
'/home/app/oracle/oradata/oracle8i/sales02.dbf'
'/home/app/oracle/oradata/oracle8i/sales01.dbf
autoextend off;

6、表空间的查看与修改

查看用户默认表空间

SQL> select username,default_tablespace from dba_users;

SQL> select username,default_tablespace from user_users;查看某个用户的默认表空间，前提需要connect该用户。

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
where username = 'hr'; 查看用户对应的默认表空间

修改用户默认表空间
SQL> alter user zhanghr default tablespace test;

设置数据库的默认临时表空间:
SQL>Alter database default temporary tablespace temp_tbs_name;

查看用户和默认表空间的关系:
SQL>select username,default_tablespace from dba_users;

查看临时表空间：

SQL> select file_name,file_id,blocks,user_blocks from dba_temp_files；

SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;

查看undo表空间

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

查看undo表空间大小

SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';

通过增加数据文件来改变undo表空间大小

SQL> alter tablespace undotbs1
add datafile '/oracle/oradata/orc6/undo02.dbf' size 10M;

通过resize更改数据文件大小

SQL> alter database datafile '/oracle/oradata/orc6/undo02.dbf' resize 100M;

查看某个表空间的数据文件

SQL> select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB" from dba_data_files where tablespace_name='ORA1TBS';指定表空间名要大写

查看所有表空间大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
group by tablespace_name;