oracle10g中创建用户表空间等操作

/**创建临时表空间**/

 

create temporary tablespace mca_temp tempfile 'D:\DataBase\oracle10g\oradata\table_space\mca_temp.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;

 

 

/**创建表空间**/

create tablespace mca datafile 'D:\DataBase\oracle10g\oradata\table_space\mca_data.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; 

 

 

/**创建表用户和密码并指定表空间**/

create user hnzz7 identified by "11" default tablespace mca temporary tablespace mca_temp; 

 

 

/**给创建的用户授权**/

 

grant connect,resource,dba to hs;

 

 

/**查询系统表空间**/

 

select * from sys.dba_tablespaces; 
select username,default_tablespace from dba_users;

 

 

/**删除用户**/

 

drop user ht cascade; 

 

/**oracle高版本导入低版本数据库语**/

  oracle高版本导入低版本：impdp hkedu/11 dumpfile=htcc.dmp

/**级联查询删除**/

select u1.CONSTRAINT_NAME,
       u1.TABLE_NAME as table_,
       u2.TABLE_NAME as reference_
  from user_constraints u1, user_constraints u2
 where u1.constraint_type = 'R'
   and u1.R_CONSTRAINT_NAME = u2.CONSTRAINT_NAME
   and u2.table_name = 'BASEF_SCHOOL_INFOR';
  

 

 -- 查找项目中存在的学校--
   select * from edu_pro p where p.school_id in(select s.id from basef_school_infor s);
   
   select u1.CONSTRAINT_NAME,
       u1.TABLE_NAME as table_,
       u2.TABLE_NAME as reference_
  from user_constraints u1, user_constraints u2
 where u1.constraint_type = 'R'
   and u1.R_CONSTRAINT_NAME = u2.CONSTRAINT_NAME
   and u2.table_name = 'EDU_PRO';

   select * from edu_pro_stage_info pi where pi.pro_id in(select p.id from edu_pro p where p.school_id in(select s.id from basef_school_infor s));
   delete from edu_pro_stage_info pi where pi.pro_id in(select p.id from edu_pro p where p.school_id in(select s.id from basef_school_infor s));
   delete from edu_pro p where p.school_id in(select s.id from basef_school_infor s);
   delete from basef_school_infor sch;
   //查找学生报名信息中重复字段名称和个数
select max(t.name),count(t.name) from student t where t.name in(select s.name from zxxpw_student s group by s.name having count(*)>1) group by t.name order by count(t.name) desc;