SQL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ZXY';#查看表空间对应数据文件的分配大小(并非实际使用的大小)

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME MB
------------------------------ ----------
/oracle/oradata/orcl/zxy01.dbf
ZXY 100

SQL> desc dba_free_space;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME                                    VARCHAR2(30)
FILE_ID                                            NUMBER
BLOCK_ID                                           NUMBER
BYTES                                              NUMBER
BLOCKS                                             NUMBER
RELATIVE_FNO                                       NUMBER

#查看表空间的还有多少空闲空间

SQL> select tablespace_name,bytes/1024/1024 mb from dba_free_space;

TABLESPACE_NAME                        MB
------------------------------ ----------
SYSTEM                                .25
SYSTEM                              .9375
UNDOTBS1                            1.625
UNDOTBS1                          16.9375
SYSAUX                             8.3125
USERS                              4.5625
TEST                            2033.5625
ZXY                               99.9375

8 rows selected.

SQL> create user zxy identified by system default tablespace zxy account unlock;

User created.

SQL> grant resource,connect,dba to zxy;

Grant succeeded.

SQL> conn zxy/system
Connected.
SQL> select * from tab;

no rows selected

SQL> select * from user_objects;

no rows selected

SQL> create table t_obj as select * from dba_objects;

Table created.

SQL> insert into t_obj select * from dba_objects;#为了测试空间大小占用,插入批量数据,运行多次吧

50354 rows created.

SQL> r
1* insert into t_obj select * from dba_objects

50354 rows created.

SQL> /

50354 rows created.

SQL> /

50354 rows created.

SQL> /

50354 rows created.

SQL> /

50354 rows created.

SQL> commit;

Commit complete.

#这下有数据了吧,空闲空间只有60m,原来有接近100m的哟

SQL> select tablespace_name,bytes/1024/1024 mb from dba_free_space;

TABLESPACE_NAME                        MB
------------------------------ ----------
SYSTEM                                .25
SYSTEM                              .9375
UNDOTBS1                             .125
UNDOTBS1                          16.9375
SYSAUX                             8.3125
USERS                              4.5625
TEST                            2033.5625
ZXY                               60.9375

8 rows selected.

SQL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ZXY';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME MB
------------------------------ ----------
/oracle/oradata/orcl/zxy01.dbf
ZXY 100

SQL> conn /as sysdba
Connected.
SQL> drop user zxy cascade;#删除一个用户,加cascade,作用在下面,别急

User dropped.

SQL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ZXY';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME MB
------------------------------ ----------
/oracle/oradata/orcl/zxy01.dbf
ZXY 100

#看到了吗,zxy的表空间空闲又回到原来的接近100m了
SQL> select tablespace_name,bytes/1024/1024 mb from dba_free_space;

TABLESPACE_NAME                        MB
------------------------------ ----------
SYSTEM                                .25
SYSTEM                              .9375
UNDOTBS1                             .125
UNDOTBS1                          16.9375
SYSAUX                             8.3125
USERS                              4.5625
TEST                            2033.5625
ZXY                               99.9375

8 rows selected.

小结:

drop user zxy cascade就是把这个用户所有的数据(包括在表空间中的数据),全部清除了

ITPub博客

drop user cascade_快速回收表空间大小

最新文章