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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值