OracleDBA之路之Tabespace(五)

扩容表空间大小

查询用户使用的表空间
SQL> show user
USER is "SYS"
SQL> select username,default_tablespace from dba_users where username='XIAOMI';
 
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
XIAOMI                         MYTABLESPACE
 
SQL>

 

修改表空间的大小

创建数据库的时候指定autoextend on next xxM maxsize xxM/UNLIMITED
创建表空间的时候指定autoextend on next xxM maxsize xxM/UNLIMITED
向表空间中添加一个datafile
对表空间中已有不是自动扩容的datafile 修改为autoextend

扩容一个表空间 向表空间中添加一个datafile
SQL> alter tablespace MYTABLESPACE add datafile '/u01/oradata/houzhh/myts02.dbf' size 100M;
 
Tablespace altered.

查看表空间使用情况
SQL>
select a.tablespace_name,a.bytes bytes_userd,b.largest,round(((a.bytes-b.bytes)/a.bytes)*1024,2) percent_used from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;
 
TABLESPACE_NAME                BYTES_USERD    LARGEST PERCENT_USED
------------------------------ ----------- ---------- ------------
SYSTEM                           723517440    8323072      1012.22
SYSAUX                           545259520   29622272       968.37
USERS                              5242880     983040          832
EXAMPLE                          104857600   19726336        806.4
UNDOTBS1                         110100480   63963136       252.34
MYTABLESPACE                     209715200  103809024        10.88
 
6 rows selected.
 
SQL>

select a.tablespace_name,a.bytes bytes_userd,b.largest,round(((a.bytes-b.bytes)/a.bytes)*1024,2) percent_used from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;

查看表空间中数据文件
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
 
FILE_NAME                                TABLESPACE_NAME                AUT
---------------------------------------- ------------------------------ ---
/u01/oradata/houzhh/myts01.dbf           MYTABLESPACE                   YES
/u01/oradata/houzhh/myts02.dbf           MYTABLESPACE                   NO
 
7 rows selected.
可以看到/u01/oradata/houzhh/myts02.dbf  不是自动扩展的
我们修改/u01/oradata/houzhh/myts02.dbf  为自动扩展

SQL> alter database datafile '/u01/oradata/houzhh/myts02.dbf'
  2  autoextend on next 10m maxsize 100M;
 
Database altered.
 
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
 
FILE_NAME                                TABLESPACE_NAME                AUT
---------------------------------------- ------------------------------ ---
/u01/oradata/houzhh/myts01.dbf           MYTABLESPACE                   YES
/u01/oradata/houzhh/myts02.dbf           MYTABLESPACE                   YES
 
SQL>

 

V$tablespace
dba_tablespaces
dba_data_files