Oracle用户、表、表空间操作

Oracle用户、表、表空间操作

删除Oracle用户及表空间

–以sysdba用户登录，查找需要删除的用户

conn / as sysdba

–查找用户

select * from dba_users;
select username from dba_users;
select username from dba_users where username=‘JACK’;
select username from all_users where username=‘JACK’;

–查看所有表空间总大小、已使用大小、剩余大小

select a.tablespace_name,
total “Total(M)”,
free “Free(M)”,
total - free “Used(M)”,
round(((total - free) / total) * 100, 2) “Used(%)”
from (select tablespace_name, sum(bytes) / 1024 / 1024 total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

–查找表空间存储文件的路径

select * from dba_data_files;

–查看所有表占用的表空间的大小

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) “占用空间(M)”
from dba_segments t
where t.segment_type=‘TABLE’
group by OWNER, t.segment_name, t.segment_type;

–查看表所属的用户

select owner,table_name from dba_tables where table_name=‘TEST_TBL01’;

–查看用户所在的表空间（查看当前用户的缺省表空间）

conn JACK
select username,default_tablespace from user_users;

–查看表所属的表空间（查看所有表）

select table_name,tablespace_name from user_tables;

–查看表所属的表空间（查看指定表）

select table_name,tablespace_name from user_tables where table_name=upper(’&table_name’);

–删除用户

–注意：指定 cascade 会删除用户下的所有对象（包括表、视图、主键、外键、索引等；但不会删除存储过程、函数、包）。如果不指定则仅仅只删除用户，一般建议指定
conn /as sysdba
drop user myusername cascade;

–删除表空间

drop tablespace mytablespace including contents and datafiles cascade constraint;
例如：删除用户JACK及表空间USER_DATA：
–删除用户JACK，级联删除
drop user JACK cascade;
–删除表空间，及对应的表空间文件也删除掉。注意：如果多个用户使用相同的表空间，删除用户时不要删除表空间
drop tablespace USER_DATA including contents and datafiles cascade constraint;

–Tablespace表空间删除

一、普通表空间删除：

Oracle 11g删除表空间语法描述：
DROP TABLESPACE tablespace_name [ including contents [ and datafiles ] [ CASCADE CONSTRAINT 搜索] ];
无选项 – 当表空间为空才能删除；
including contents — 删除表空间及对象；
including contents and datafiles — 删除表空间、对象及数据文件；
including contents CASCADE CONSTRAINT — 删除关联；
including contents and datafiles cascade constraint – 含前两项。

生成脚本：
select ‘drop tablespace ’ || tablespace_name ||
’ including contents and datafiles cascade constraint;’
from dba_data_files
where tablespace_name not in
(‘SYSTEM’, ‘SYSAUX’, ‘USERS’, ‘EXAMPLE’, ‘UNDOTBS2’, ‘UNDOTBS1’);

二、分区表空间删除：

select ‘alter table ’ || owner || ‘.’ || segment_name || ’ drop partition ’ ||
partition_name || ’ ;’
from dba_segments
where segment_name in (select distinct segment_name
from dba_segments
where tablespace_name = ‘p1’
and segment_type like ‘%PART%’)
and tablespace_name <> ‘p1’;

得出：
alter table CP.IDX_CP_HANDLE_BATCH_NO drop partition SYS_P200 ;
alter table CP.IDX_CP_HANDLE_REQUEST_ID drop partition SYS_P200 ;
alter table CP.IDX_CP_PAYMENT_REQUEST_ID drop partition SYS_P201 ;
alter table CP.IDX_CP_PAYMENT_TRAN_NO drop partition SYS_P201 ;
alter table CP.IDX_CP_REQUEST_ID drop partition SYS_P199 ;
alter table CP.IDX_CP_REQUEST_TRAN_NO drop partition SYS_P199 ;
alter table CP.TBL_CP_HANDLE drop partition SYS_P200 ;
alter table CP.TBL_CP_PAYMENT drop partition SYS_P201 ;
alter table CP.TBL_CP_REQUEST drop partition SYS_P199 ;

三、异常处理：

报错有下面几种：
一. ORA-23515
— ORA-23515: materialized views and/or their indices exist in the tablespace
drop tablespace crm_data including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

意思是：该表空间 CRM_DATA含有物化视图，或者含有物化视图的索引
解决办法：
– 首先删掉该表空间下的的物化视图
select ‘drop materialized view ’ || owner || ‘.’ || segment_name || ’ ;’
from dba_segments
where segment_name in (select mview_name from dba_mviews)
and tablespace_name = ‘CRM_DATA’

– 然后删除该表空间下的其他表空间下物化视图在本表空间下创建的索引
select *
from dba_segments
where tablespace_name = ‘CRM_DATA’
and segment_name in
(select index_name
from dba_indexes
where table_name in (select mview_name from dba_mviews));
二. ORA-02429
—ORA-02429: cannot drop index used for enforcement of unique/primary key
drop tablespace crm_idx including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
ORA-02429的意思是： 让你删除该表空间下面的 primary key 和 unique key
处理办法：
select ‘alter table ’ || owner || ‘.’ || table_name || ’ drop constraint ’ ||
constraint_name || ’ ;’
from dba_constraints
where constraint_type in (‘U’, ‘P’)
and (index_owner, index_name) in
(select owner, segment_name
from dba_segments
where tablespace_name = ‘CRM_IDX’);

三. ORA-14404
–ORA-14404: partitioned table contains partitions in a different tablespace
drop tablespace crm_arc_data including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
意思是: 本表空间下面有这么样一个或一些分区表的分区： this partition OR partitions的table所包含的全部 partitions不在一个表空间下面：
处理办法：
select ‘alter table ’ || owner || ‘.’ || segment_name || ’ drop partition ’ ||
partition_name || ’ ;’
from dba_segments
where segment_name in (select distinct segment_name
from dba_segments
where tablespace_name = ‘CRM_ARC_DATA’
and segment_type like ‘%PART%’)
and tablespace_name <> ‘CRM_ARC_DATA’;
杀手锏： 直接drop 这个分区表（如果允许的话）

四. ORA-02449
— ORA-02449: unique/primary keys in table referenced by foreign keys
drop tablespace crm_data including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
意思是： 这个要删除的表空间 里面含有这么样的一些主键： 其他表空间的表在这些主键上建有外键
处理办法： 去掉这些垃圾外键
select ‘alter table ’ || owner || ‘.’ || table_name || ’ drop constraint ’ ||
constraint_name || ’ ;’
from dba_constraints
where constraint_type = ‘R’
and table_name in (select segment_name
from dba_segments
where tablespace_name = ‘CRM_DATA’
and segment_type like ‘%TABLE%’);
如果还是不行的话，就用这个语句来删表空间吧：
drop tablespace crm_data including contents cascade constraints