Oracle数据库常用操作

---导出
	exp wang/123456@127.0.0.1:1521/orcl file=d:\activityTable.dmp owner=(wang)
	--导出表和数据
	exp SCMS_HI/SCMS_HI@127.0.0.1:1521/orcl  tables=(SCMS,SCM_PROVIDER) rows=y file=d:\kaizhi.dmp
	expdp SCMS_HI/SCMS_HI@127.0.0.1:1521/orcl directory=scms dumpfile=kaizhi.dmp TABLES=SCM_PROVIDER
	--schemas=scott 
	--TABLES=emp,dept 
	--query='WHERE deptno=20'    --带条件
	--TABLESPACES=temp,example  --按表空间
	--FULL=y       --整个库
	--content=all/metadata_only/data_only --所有/只导表元数据/表行
---导入
	imp HNSCMS_D/HNSCMS_D@127.0.0.1:1521/orcl file=d:\kaizhi.dmp remap_tablespace=SCMS ignore=y  full=y 
	imp scms/scms@127.0.0.1:1521/orcl file=D:\5\scms.dmp\sccreate or replace directory bao as 'D:\5\scms.dmp';
	Grant read,write on directory bao to scms;ms.dmp ignore=y  full=y
	
	
	impdp HNSCMS_D/HNSCMS_D@127.0.0.1:1521/orcl DIRECTORY=scms DUMPFILE=kaizhi.dmp remap_tablespace=USERS:SCMS
	


3.
--创建人员指定表空间
create user HNSCMS_D identified by HNSCMS_D default tablespace userspace;
--赋权
grant create session to HNSCMS_D;
grant create table to  HNSCMS_D;
grant create tablespace to  HNSCMS_D;
grant create view to  HNSCMS_D;
grant dba to HNSCMS_D ;
alter user  HNSCMS_D default role DBA;
--删除人员
drop user HNSCMS_D cascade;

--获取所有表空间
select distinct TABLESPACE_NAME from tabs;
--更改用户密码
ALTER USER 用户名 IDENTIFIED BY 新密码;

--创建表空间
create tablespace SCMS   
datafile 'E:/Oracle/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' 
size 8192M 
autoextend on next 1M maxsize 8192M;
--删除表空间
drop tablespace DEMOSPACE including contents and datafiles
--修改表空间大小
alter database datafile 'E:/Oracle/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' resize 18240m;
--自动增长，表空间不足时增加200MB，最大扩展5000MB
alter database datafile 'E:/Oracle/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' autoextend on next 200m maxsize 5000m;
--扩展无限大空间
alter database DATAFILE 'E:/Oracle/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf'  autoextend on maxsize unlimited;  


--查询表空间大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 
--添加列唯一
alter table s_user add constraint login_unique unique(LOGINNAME);

--分配到指定表空间
alter table table_name move tablespace SCMS; 
--全部表移动表空间
select 'alter table '||table_name ||' move tablespace SCMS' from user_tables t;
alter table table_name move tablespace SCMS; --分配到指定表空间
--移动索引
select 'alter index '||INDEX_NAME ||' rebuild tablespace SCMS' from user_indexes;
--查询所有用户
select *from dba_users t order by t.created desc;
--查询用户所对应的表空间
select username,default_tablespace from dba_users  where username='WANG';
--查询表空间及对应的文件位置
select t1.NAME,t2.NAME,t2.* from v$tablespace t1 ,v$datafile t2 where t1.TS# = t2.TS#