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
--------------------------------------------------------------------------------
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
------------------------------ ----------
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;
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
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
------------------------------ ----------
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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
TABLESPACE_NAME MB
------------------------------ ----------
/oracle/oradata/orcl/zxy01.dbf
ZXY 100
#看到了吗,zxy的表空间空闲又回到原来的接近100m了
SQL> select tablespace_name,bytes/1024/1024 mb from dba_free_space;
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
------------------------------ ----------
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博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-665991/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-665991/