oracle 扩容步骤,Oracle表空间扩容

Oracle表空间扩容

1创建表空间

SQL> create tablespace my_01 logging datafile

'/oracle/app/oradata/mytablespace/my_01.dbf' size 128M;

表空间已创建。

在表空间上创建表

SQL> create table t1 (id int) tablespace my_01;

表已创建。

插入数据

SQL> insert into t1 values(10);

已创建1行。

2表空间扩容

方法一：改变数据文件的大小

SQL> alter database datafile '/oracle/app/oradata/mytablespace/my_01.dbf'

resize 256M;

数据库已更改。

验证：

SQL> select bytes/1024/1024, tablespace_name from

dba_data_files where tablespace_name='MY_01';

BYTES/1024/1024 TABLESPACE_NAME

--------------- ---------------

256 MY_01

SQL> select table_name from dba_tables where

tablespace_name='MY_01';

TABLE_NAME

------------------------------

T1

SQL> select * from t1;

ID

----------

10

方法二：添加数据文件

SQL> alter tablespace my_01 add datafile

'/oracle/app/oradata/mytablespace/my_02.dbf' size 128M;

表空间已更改。

验证：

SQL> select sum(bytes)/1024/1024, tablespace_name from

dba_data_files where tablespace_name='MY_01' group by tablespace_name;

SUM(BYTES)/1024/1024 TABLESPACE_NAME

-------------------- ---------------

384 MY_01

SQL> select table_name from dba_tables where

tablespace_name='MY_01';

TABLE_NAME

------------------------------

T1

SQL> select * from t1;

ID

----------

10

3查看表空间大小和使用率

select

a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1,

5) as "FREE%", substr((total - free)/total * 100, 1, 5) as

"USED%" from(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files

group by tablespace_name) a,

(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group

by tablespace_name) b

where a.tablespace_name = b.tablespace_name

order by a.tablespace_name;

TABLESPACE_NAMETOTALFREEUSED FREE%USED%

---------------

---------- ---------- ---------- ---------- ----------

MY_01384381.93752.0625 99.46.5371

QUR_DT01128126.93751.0625 99.16.8300

QUR_DT02128126.93751.0625 99.16.8300

QUR_DT03128126.93751.0625 99.16.8300

QUR_DT04128126.93751.0625 99.16.8300

QUR_IDX01128126.93751.0625 99.16.8300

QUR_IDX02128126.68751.3125 98.971.025

QUR_IDX03128126.93751.0625 99.16.8300

QUR_IDX04128126.93751.0625 99.16.8300

SYSAUX50031.5625468.4375 6.31293.68

SYSTEM6808.625671.375 1.26898.73

UNDOTBS17510.37564.625 13.8386.16

USERS53.68751.3125 73.7526.25