oracle，使用sqlplus，导入/删除库

1.运行cmd，输入【sqlplus / as sysdba】，进入sqlplus；

2.创建tablespace 【

CREATE TABLESPACE XXX
DATAFILE 'D:\app\Administrator\oradata\orcl\XXX.ORA' SIZE 100M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED 
LOGGING
ONLINE
BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/

】

3.创建用户【

CREATE USER XXX
IDENTIFIED BY XXX
DEFAULT TABLESPACE XXX
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
/

】

4.赋予用户权限【

GRANT AQ_ADMINISTRATOR_ROLE TO XXX WITH ADMIN OPTION
/
GRANT AQ_USER_ROLE TO XXX WITH ADMIN OPTION
/
GRANT AUTHENTICATEDUSER TO XXX WITH ADMIN OPTION
/
GRANT CONNECT TO XXX WITH ADMIN OPTION
/
GRANT CSW_USR_ROLE TO XXX WITH ADMIN OPTION
/
GRANT CTXAPP TO XXX WITH ADMIN OPTION
/
GRANT CWM_USER TO XXX WITH ADMIN OPTION
/
GRANT DATAPUMP_EXP_FULL_DATABASE TO XXX WITH ADMIN OPTION
/
GRANT DATAPUMP_IMP_FULL_DATABASE TO XXX WITH ADMIN OPTION
/
GRANT DBA TO XXX WITH ADMIN OPTION
/
GRANT DELETE_CATALOG_ROLE TO XXX WITH ADMIN OPTION
/
GRANT EJBCLIENT TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE_CATALOG_ROLE TO XXX WITH ADMIN OPTION
/
GRANT EXP_FULL_DATABASE TO XXX WITH ADMIN OPTION
/
GRANT GATHER_SYSTEM_STATISTICS TO XXX WITH ADMIN OPTION
/
GRANT HS_ADMIN_ROLE TO XXX WITH ADMIN OPTION
/
GRANT IMP_FULL_DATABASE TO XXX WITH ADMIN OPTION
/
GRANT JAVADEBUGPRIV TO XXX WITH ADMIN OPTION
/
GRANT JAVAIDPRIV TO XXX WITH ADMIN OPTION
/
GRANT JAVASYSPRIV TO XXX WITH ADMIN OPTION
/
GRANT JAVAUSERPRIV TO XXX WITH ADMIN OPTION
/
GRANT JAVA_ADMIN TO XXX WITH ADMIN OPTION
/
GRANT JAVA_DEPLOY TO XXX WITH ADMIN OPTION
/
GRANT JMXSERVER TO XXX WITH ADMIN OPTION
/
GRANT LOGSTDBY_ADMINISTRATOR TO XXX WITH ADMIN OPTION
/
GRANT MGMT_USER TO XXX WITH ADMIN OPTION
/
GRANT OEM_ADVISOR TO XXX WITH ADMIN OPTION
/
GRANT OEM_MONITOR TO XXX WITH ADMIN OPTION
/
GRANT OLAP_DBA TO XXX WITH ADMIN OPTION
/
GRANT OLAP_USER TO XXX WITH ADMIN OPTION
/
GRANT OLAP_XS_ADMIN TO XXX WITH ADMIN OPTION
/
GRANT ORDADMIN TO XXX WITH ADMIN OPTION
/
GRANT OWB$CLIENT TO XXX WITH ADMIN OPTION
/
GRANT OWB_DESIGNCENTER_VIEW TO XXX WITH ADMIN OPTION
/
GRANT OWB_USER TO XXX WITH ADMIN OPTION
/
GRANT RECOVERY_CATALOG_OWNER TO XXX WITH ADMIN OPTION
/
GRANT RESOURCE TO XXX WITH ADMIN OPTION
/
GRANT SCHEDULER_ADMIN TO XXX WITH ADMIN OPTION
/
GRANT SELECT_CATALOG_ROLE TO XXX WITH ADMIN OPTION
/
GRANT SPATIAL_CSW_ADMIN TO XXX WITH ADMIN OPTION
/
GRANT SPATIAL_WFS_ADMIN TO XXX WITH ADMIN OPTION
/
GRANT WFS_USR_ROLE TO XXX WITH ADMIN OPTION
/
GRANT WM_ADMIN_ROLE TO XXX WITH ADMIN OPTION
/
GRANT XDBADMIN TO XXX WITH ADMIN OPTION
/
GRANT XDB_SET_INVOKER TO XXX WITH ADMIN OPTION
/
GRANT XDB_WEBSERVICES TO XXX WITH ADMIN OPTION
/
GRANT XDB_WEBSERVICES_OVER_HTTP TO XXX WITH ADMIN OPTION
/
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO XXX WITH ADMIN OPTION
/
GRANT ADMINISTER ANY SQL TUNING SET TO XXX WITH ADMIN OPTION
/
GRANT ADMINISTER DATABASE TRIGGER TO XXX WITH ADMIN OPTION
/
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO XXX WITH ADMIN OPTION
/
GRANT ADMINISTER SQL TUNING SET TO XXX WITH ADMIN OPTION
/
GRANT ADVISOR TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY ASSEMBLY TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY CLUSTER TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY CUBE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY EDITION TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY INDEX TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY INDEXTYPE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY LIBRARY TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY MATERIALIZED VIEW TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY MINING MODEL TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY OPERATOR TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY OUTLINE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY PROCEDURE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY ROLE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY SEQUENCE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY SQL PROFILE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY TRIGGER TO XXX WITH ADMIN OPTION
/
GRANT ALTER ANY TYPE TO XXX WITH ADMIN OPTION
/
GRANT ALTER DATABASE TO XXX WITH ADMIN OPTION
/
GRANT ALTER PROFILE TO XXX WITH ADMIN OPTION
/
GRANT ALTER RESOURCE COST TO XXX WITH ADMIN OPTION
/
GRANT ALTER ROLLBACK SEGMENT TO XXX WITH ADMIN OPTION
/
GRANT ALTER SESSION TO XXX WITH ADMIN OPTION
/
GRANT ALTER SYSTEM TO XXX WITH ADMIN OPTION
/
GRANT ALTER TABLESPACE TO XXX WITH ADMIN OPTION
/
GRANT ALTER USER TO XXX WITH ADMIN OPTION
/
GRANT ANALYZE ANY TO XXX WITH ADMIN OPTION
/
GRANT ANALYZE ANY DICTIONARY TO XXX WITH ADMIN OPTION
/
GRANT AUDIT ANY TO XXX WITH ADMIN OPTION
/
GRANT AUDIT SYSTEM TO XXX WITH ADMIN OPTION
/
GRANT BACKUP ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT BECOME USER TO XXX WITH ADMIN OPTION
/
GRANT CHANGE NOTIFICATION TO XXX WITH ADMIN OPTION
/
GRANT COMMENT ANY MINING MODEL TO XXX WITH ADMIN OPTION
/
GRANT COMMENT ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY ASSEMBLY TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY CLUSTER TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY CONTEXT TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY CUBE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY CUBE BUILD PROCESS TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY DIRECTORY TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY EDITION TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY INDEX TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY INDEXTYPE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY JOB TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY LIBRARY TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY MATERIALIZED VIEW TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY MEASURE FOLDER TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY MINING MODEL TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY OPERATOR TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY OUTLINE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY PROCEDURE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY SEQUENCE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY SQL PROFILE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY SYNONYM TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY TRIGGER TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY TYPE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ANY VIEW TO XXX WITH ADMIN OPTION
/
GRANT CREATE ASSEMBLY TO XXX WITH ADMIN OPTION
/
GRANT CREATE CLUSTER TO XXX WITH ADMIN OPTION
/
GRANT CREATE CUBE TO XXX WITH ADMIN OPTION
/
GRANT CREATE CUBE BUILD PROCESS TO XXX WITH ADMIN OPTION
/
GRANT CREATE CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT CREATE DATABASE LINK TO XXX WITH ADMIN OPTION
/
GRANT CREATE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT CREATE EXTERNAL JOB TO XXX WITH ADMIN OPTION
/
GRANT CREATE INDEXTYPE TO XXX WITH ADMIN OPTION
/
GRANT CREATE JOB TO XXX WITH ADMIN OPTION
/
GRANT CREATE LIBRARY TO XXX WITH ADMIN OPTION
/
GRANT CREATE MATERIALIZED VIEW TO XXX WITH ADMIN OPTION
/
GRANT CREATE MEASURE FOLDER TO XXX WITH ADMIN OPTION
/
GRANT CREATE MINING MODEL TO XXX WITH ADMIN OPTION
/
GRANT CREATE OPERATOR TO XXX WITH ADMIN OPTION
/
GRANT CREATE PROCEDURE TO XXX WITH ADMIN OPTION
/
GRANT CREATE PROFILE TO XXX WITH ADMIN OPTION
/
GRANT CREATE PUBLIC DATABASE LINK TO XXX WITH ADMIN OPTION
/
GRANT CREATE PUBLIC SYNONYM TO XXX WITH ADMIN OPTION
/
GRANT CREATE ROLE TO XXX WITH ADMIN OPTION
/
GRANT CREATE ROLLBACK SEGMENT TO XXX WITH ADMIN OPTION
/
GRANT CREATE SEQUENCE TO XXX WITH ADMIN OPTION
/
GRANT CREATE SESSION TO XXX WITH ADMIN OPTION
/
GRANT CREATE SYNONYM TO XXX WITH ADMIN OPTION
/
GRANT CREATE TABLE TO XXX WITH ADMIN OPTION
/
GRANT CREATE TABLESPACE TO XXX WITH ADMIN OPTION
/
GRANT CREATE TRIGGER TO XXX WITH ADMIN OPTION
/
GRANT CREATE TYPE TO XXX WITH ADMIN OPTION
/
GRANT CREATE USER TO XXX WITH ADMIN OPTION
/
GRANT CREATE VIEW TO XXX WITH ADMIN OPTION
/
GRANT DEBUG ANY PROCEDURE TO XXX WITH ADMIN OPTION
/
GRANT DEBUG CONNECT SESSION TO XXX WITH ADMIN OPTION
/
GRANT DELETE ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT DELETE ANY MEASURE FOLDER TO XXX WITH ADMIN OPTION
/
GRANT DELETE ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY ASSEMBLY TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY CLUSTER TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY CONTEXT TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY CUBE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY CUBE BUILD PROCESS TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY DIRECTORY TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY EDITION TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY INDEX TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY INDEXTYPE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY LIBRARY TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY MATERIALIZED VIEW TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY MEASURE FOLDER TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY MINING MODEL TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY OPERATOR TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY OUTLINE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY PROCEDURE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY ROLE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY SEQUENCE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY SQL PROFILE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY SYNONYM TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY TRIGGER TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY TYPE TO XXX WITH ADMIN OPTION
/
GRANT DROP ANY VIEW TO XXX WITH ADMIN OPTION
/
GRANT DROP PROFILE TO XXX WITH ADMIN OPTION
/
GRANT DROP PUBLIC DATABASE LINK TO XXX WITH ADMIN OPTION
/
GRANT DROP PUBLIC SYNONYM TO XXX WITH ADMIN OPTION
/
GRANT DROP ROLLBACK SEGMENT TO XXX WITH ADMIN OPTION
/
GRANT DROP TABLESPACE TO XXX WITH ADMIN OPTION
/
GRANT DROP USER TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY ASSEMBLY TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY CLASS TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY INDEXTYPE TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY LIBRARY TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY OPERATOR TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY PROCEDURE TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY PROGRAM TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ANY TYPE TO XXX WITH ADMIN OPTION
/
GRANT EXECUTE ASSEMBLY TO XXX WITH ADMIN OPTION
/
GRANT EXEMPT ACCESS POLICY TO XXX WITH ADMIN OPTION
/
GRANT EXEMPT IDENTITY POLICY TO XXX WITH ADMIN OPTION
/
GRANT EXPORT FULL DATABASE TO XXX WITH ADMIN OPTION
/
GRANT FLASHBACK ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT FLASHBACK ARCHIVE ADMINISTER TO XXX WITH ADMIN OPTION
/
GRANT FORCE ANY TRANSACTION TO XXX WITH ADMIN OPTION
/
GRANT FORCE TRANSACTION TO XXX WITH ADMIN OPTION
/
GRANT GLOBAL QUERY REWRITE TO XXX WITH ADMIN OPTION
/
GRANT GRANT ANY OBJECT PRIVILEGE TO XXX WITH ADMIN OPTION
/
GRANT GRANT ANY PRIVILEGE TO XXX WITH ADMIN OPTION
/
GRANT GRANT ANY ROLE TO XXX WITH ADMIN OPTION
/
GRANT IMPORT FULL DATABASE TO XXX WITH ADMIN OPTION
/
GRANT INSERT ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT INSERT ANY MEASURE FOLDER TO XXX WITH ADMIN OPTION
/
GRANT INSERT ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT LOCK ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT MANAGE SCHEDULER TO XXX WITH ADMIN OPTION
/
GRANT MANAGE TABLESPACE TO XXX WITH ADMIN OPTION
/
GRANT MERGE ANY VIEW TO XXX WITH ADMIN OPTION
/
GRANT ON COMMIT REFRESH TO XXX WITH ADMIN OPTION
/
GRANT QUERY REWRITE TO XXX WITH ADMIN OPTION
/
GRANT RESTRICTED SESSION TO XXX WITH ADMIN OPTION
/
GRANT RESUMABLE TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY CUBE TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY DICTIONARY TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY MINING MODEL TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY SEQUENCE TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT SELECT ANY TRANSACTION TO XXX WITH ADMIN OPTION
/
GRANT UNDER ANY TABLE TO XXX WITH ADMIN OPTION
/
GRANT UNDER ANY TYPE TO XXX WITH ADMIN OPTION
/
GRANT UNDER ANY VIEW TO XXX WITH ADMIN OPTION
/
GRANT UNLIMITED TABLESPACE TO XXX WITH ADMIN OPTION
/
GRANT UPDATE ANY CUBE TO XXX WITH ADMIN OPTION
/
GRANT UPDATE ANY CUBE BUILD PROCESS TO XXX WITH ADMIN OPTION
/
GRANT UPDATE ANY CUBE DIMENSION TO XXX WITH ADMIN OPTION
/
GRANT UPDATE ANY TABLE TO XXX WITH ADMIN OPTION
/

】

--------------------------------------------------------------------------------------------------

导入及导出DMP文件：

1.导出【

EXP SC2_GEFRAME/SC2_GEFRAME BUFFER=64000 FILE=E:\dump\SC2_GEFRAME.DMP FULL=Y

】

2.导入【

impdp XXX/XXX@orcl directory=DUMPDIR dumpfile=XXX.DMP version=11.2.0.4.0 full=y

】

 

---------------------------------------------------------------------------------------------------------

删除用户记表空间：

--删除用户
drop user XXX cascade;

--删除表空间
drop tablespace XXX including contents and datafiles cascade constraint;

 

---------------------------------------------------------------------------------------------------------------

若在删除用户时，提示：ORA-01940 无法删除当前已连接用户

1.查看用户的连接状态
select username,sid,serial# from v$session;

2.找到要删除用户的sid和serial并杀死
alter system kill session'$sid,$serial';

3.删除用户
drop user xxx cascade;