导入导出创建表空间创建用户删除表空间

select * from all_users where username not in 
('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES,MDDATA','MDSYS,MGMT_VIEW',
'OLAPSYS,ORACLE_OCM','ORDDATA,ORDPLUGINS','ORDSYS,OUTLN','OWBSYS,OWBSYS_AUDIT','SCOTT,SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL')
go

(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC)

exp "'system/solution@gssjzx as sysdba'" owner=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) file=E:\silu\gssjzx20160623.dmp log=E:\silu\gssjzx20160623.log

exp system/!QAZ2wsx@orcl owner=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) file=d:\hengyang.dmp log=d:\hengyang.log
exp system/!QAZ2wsx@orcl owner=BUSINESS file=d:\hengyang.dmp log=d:\hengyang.log
exp tmain/ths-passw0rd owner=TSERVICE file=d:\TSERVICE.dmp
exp "'system/solution@gsxkz as sysdba'" owner=jl file=E:\silu\gszkx20160623.dmp log=E:\silu\gsxkz20160623.log

imp "'system/!QAZ2wsx@orcl as sysdba'" fromuser=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) touser=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) DESTROY=y file=C:\Users\gongjj\Desktop\甘肃数据备份\gssjzx20160623.dmp log=C:\Users\gongjj\Desktop\甘肃数据备份\log.log

imp system/!QAZ2wsx@orcl fromuser=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) touser=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) DESTROY=y file=C:\gssjzx20160623.dmp log=C:\log.log
imp system/Aa123456@orcl fromuser=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) touser=(BUSINESS,EQA,GISSYS,HJTJ12,ICCARD,KETTLE,KETTLE6,PWSB,TAUTOMONITOR,TCODE,TCONSTRUCT,TDANGER,TDIRECTORY,TEMISSIONFEES,TEMISSIONLICENCE,TEMISSIONSHEET,TEMSSLISTBASE,TENV,THEAVYMETAL,THIDDENDANGER,THSMAP,TINDICATOR,TLETTERVISIT,TMAIN,TMONITOR,TMOTORVEHICLE,TPLATFORM,TPROBLEM,TPUNISH,TQUERY,TRADIA,TSEARCH,TSERVICE,TSOILD,TSSIS,TSTAT,TSUBJECT,TSUPERMONITOR,TSUPERVISE,TTOTAL,YWHJYJ,YWHYFS,YWJDC) DESTROY=y file=C:\hysjzx\hengyang.dmp log=C:\hysjzx\hengyang.log
imp system/Aa123456@orcl fromuser=BUSINESS touser=BUSINESS DESTROY=y file=C:\hysjzx\hengyang.dmp log=C:\hysjzx\hengyang.log

imp "'system/!QAZ2wsx@orcl as sysdba'" full=y DESTROY=y file=C:\Users\gongjj\Desktop\甘肃数据备份\gsxkz20160623.dmp log=C:\Users\gongjj\Desktop\甘肃数据备份\log.log

imp "'system/solution@gssjzx as sysdba'" fromuser=jl touser=jl DESTROY=y file=d:\gszkx20160623.dmp log=d:\log.log

imp "'system/!QAZ2wsx@gssjzx as sysdba'" full=y DESTROY=y file=C:\gsxkz20160623.dmp log=C:\log.log
imp "'TSERVICE/TSERVICE as sysdba'" full=y DESTROY=y file=D:\TSERVICE.dmp

(THS,TTOTAL,TSUPERVISE,TSUPERMONITOR,TSUBJECT,TSTAT,TSSIS,TSOILD,TSMSYS,TSEARCH,TSERVICE,TRADIA,TQUERY,TPUNISH,TPROBLEM,TPLATFORM,TMONITOR,TMAIN,TLETTERVISIT,TINDICATOR,THIDDENDANGER,THEAVYMETAL,TENV,TEMSSLISTBASE,TEMISSIONSHEET,TEMISSIONLICENCE,TEMISSIONFEES,TDIRECTORY,TDANGER,TCONSTRUCT,TCODE,TAUTOMONITOR,HJTJ12,KETTLE6,SCOTT,OWBSYS_AUDIT,TMOTORVEHICLE,OWBSYS,FLOWS_FILES,MGMT_VIEW,MDDATA,MDSYS,SI_INFORMTN_SCHEMA,ORDPLUGINS,ORDDATA,ORDSYS,OLAPSYS,ORACLE_OCM,OUTLN)

--表空间查询及修改
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name
go
select SEGMENT_TYPE,owner,sum(bytes)/1024/1024 from  dba_segments  where tablespace_name='SHSPACE' group by segment_type,owner
go
alter database datafile 'D:\app\Administrator\oradata\exchange\SHSPACE.DBF' resize 5120m
go
--表空间创建
create temporary tablespace jl tempfile 'D:\app\Administrator\gsxkz\jl.dbf' size 100m autoextend on next 100m maxsize 2048m extent management local
go
create tablespace ts_jl datafile 'D:\app\Administrator\gsxkz\ts_jl.dbf' size 100m autoextend on  next 100m maxsize 2048m extent management local segment space management auto
go
--查看表空间及设置表空间大小不限制
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files
go
alter database DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\EXCHANGE\SHSPACE.DBF'  autoextend on maxsize unlimited
go
--查看表空间的分配情况
select tablespace_name, sum(bytes) / 1024 / 1024 /1024 from dba_data_files  group by tablespace_name
go

create user jl identified by jl
go
alter user jl default tablespace ts_jl
go
alter user jl temporary tablespace jl
go
GRANT CONNECT, RESOURCE,DBA TO jl
go
GRANT CREATE VIEW TO jl
go
GRANT CREATE ANY PROCEDURE TO jl
go
GRANT DEBUG ANY PROCEDURE TO jl
go                                              
GRANT DEBUG CONNECT SESSION TO jl
go
GRANT UNLIMITED TABLESPACE TO jl
go

--drop user jl cascade
--go
--DROP TABLESPACE ts_jl INCLUDING CONTENTS AND DATAFILES
--go

create user SJZX_JA identified by silupassw0rd
go
alter user SJZX_JA default tablespace SHSPACE
go
GRANT CONNECT TO SJZX_JA
go
GRANT UNLIMITED TABLESPACE TO SJZX_JA
go

exp qirui/qirui@orcl owner=qirui file=d:\hengyang.dmp

exp system/orcl@49 owner=GEOMAP47 file=F:\GEOMAP.dmp log=F:\GEOMAP.log

exp system/orcl@49 owner=qirui file=F:\plateform.dmp log=F:\plateform.log