导入数据库工具有关的oracle sql操作

alter table TAB_user add (personal varchar2(30) default '用户名' not null);
alter table TAB_user modify (personal number(4) default null);
alter table TAB_user drop column personal;

SELECT DISTINCT df.FILE_NAME FROM DBA_DATA_FILES df, dba_tablespaces dt where df.tablespace_name=dt.tablespace_name and dt.TABLESPACE_NAME ='USERS';

SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;---查看所有表空间对应的数据文件名
select CONSTRAINT_NAME from USER_CONSTRAINTS WHERE TABLE_NAME='TAB_TEST' AND CONSTRAINT_TYPE='U'; ---获取某约束都约束了哪些字段
------------------------------------------------------------------------------------------
select
a.a1 表空间名称,c.c2 类型,c.c3 区管理,b.b2/1024/1024 表空间大小M,(b.b2-a.a2)/1024/1024 已使用M,substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1; -------------获取表空间的相关信息

select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name ----------获取数据库数据文件信息

select
a.tablespace_name 表空间名称,
b.file_name 数据文件名,
a.INITIAL_EXTENT 表空间初始大小,
b.AUTOEXTENSIBLE 是否自动扩展,
b.bytes 数据文件大小bytes,
b.INCREMENT_BY 扩展大小,
b.MAXBYTES 数据文件最大值
from dba_tablespaces a, dba_data_files b
Where a.tablespace_name=b.tablespace_name -----------------获取表空间文件相关信息

select a.tablespace_name ,b.file_name ,a.INITIAL_EXTENT ,
b.AUTOEXTENSIBLE ,b.bytes,b.INCREMENT_BY ,b.MAXBYTES
from dba_tablespaces a, dba_data_files b Where a.tablespace_name = b.tablespace_name
----------------------------------------------------------
SELECT DISTINCT b.file_name ,a.INITIAL_EXTENT , b.AUTOEXTENSIBLE ,b.bytes,b.INCREMENT_BY ,b.MAXBYTES
FROM DBA_DATA_FILES b, dba_tablespaces a where a.tablespace_name=b.tablespace_name and a.TABLESPACE_NAME ='USERS';

select constraint_type from DBA_CONSTRAINTS WHERE CONSTRAINT_NAME='PK_USER'

SELECT constraint_name, constraint_type,R_CONSTRAINT_NAME,DELETE_RULE
FROM DBA_CONSTRAINTS WHERE table_name = 'TABLE_ROLE' ------获取表的约束信息

select cu.constraint_name,cu.TABLE_NAME，cu.column_name,au.constraint_type, au.r_constraint_name, au.delete_rule
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name= 'TABLE_ROLE'
-------------------------------------------------------------------
select t.column_name, t.data_type, t.data_length, t.data_precision, t.data_default, c.comments ,t.NULLABLE,uc.constraint_type
from user_tab_columns t, user_col_comments c ,user_constraints uc
where t.table_name= c.table_name and t.column_name= c.column_name and t.table_name=uc.table_name
and t.table_name= 'TABLE_ROLE'

select * from user_constraints
select * from user_tab_columns
select * from user_col_comments
--------------------------------------------------------------
select cu.constraint_name,cu.TABLE_NAME，cu.column_name,au.constraint_type, au.r_constraint_name, au.delete_rule
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name= 'sys_user'
-----------------------表-------------------------------------------------
select * from user_tab_columns;
select * from user_col_comments;
select * from user_cons_columns;
select * from user_constraints;
select * from dba_tablespaces
create constraints TableName ,constraint_name,constraint_type,column_name,R_CONSTRAINT_NAME,delete_rule

select constraint_name from dba_constraints where owner='sys' and table_name='sys_user'

select 1 from dba_constraints where constraint_name='SYS_C00102';
select constraint_name from user_constraints

select * from user_constraints where table_name='sys_user'
--------------------------------------------------------------
alter table sys_user drop primary key cascade ---删除主键
alter table sys_user drop unique(ID) ----删除colname上的唯一约束

alter table TABLE_ROLE disable constraint PK_ROLE; ----删除约束（）
-------------------------------------------------------------
select 1 from user_tables where table_name = 'SYS_USER'
select * from dba_tables where table_name = 'SYS_USER';

导入数据库工具有关的oracle sql操作

相关推荐