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

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就是把这个用户所有的数据(包括在表空间中的数据),全部清除了