Orcale 常用命令集合 （更新中）



导入数据：
imp system/searching@EWM_RF full=y file= D:\LPPZ\20150427expdp_test_rf.dmp ignore=y

泵：
--创建逻辑目录
create directory lppz_dpdata as 'D:\LPPZ\dpdata';
create directory les_dpdata as 'D:\LES\dpdata';
create directory idoc_dpdata as 'D:\LES\dpdata';

--查看管理理员目录
select * from dba_directories;
--给scott用户赋予在指定目录的操作权限
grant read,write on directory lppz_dpdata to test_rf;
grant read,write on directory les_dpdata to LES_test;
grant read,write on directory les_dpdata to EDEDB;
grant read,write on directory les_dpdata to IDOC_TEST;
--导入
impdp system/searching DIRECTORY=lppz_dpdata DUMPFILE=20150427expdp_test_rf.dmp FULL=y;

impdp system/searching DIRECTORY=lppz_dpdata DUMPFILE=expdp_lppz_test_rf20150427.dmp FULL=y;
impdp system/searching DIRECTORY=lppz_dpdata DUMPFILE=expdp_lppz_test_rf20150428.dmp FULL=y;
impdp system/searching DIRECTORY=les_dpdata DUMPFILE=expdp_les20150504.dmp FULL=y;
impdp system/searching DIRECTORY=les_dpdata DUMPFILE=expdp_EDEDB20150508.dmp FULL=y;

TEST_RF（用户）
DMDATA（表空间）
HLDATA（表空间）

grant read,write on directory lppz_dpdata to TEST_RF;

--导出
expdp schemas=TEST_RF dumpfile=expdp_lppz_test_rf20150428.dmp DIRECTORY=lppz_dpdata
expdp  TEST_RF/searching@EWM_RF schemas=TEST_RF dumpfile=expdp_lppz_test_rf20150427.dmp DIRECTORY=lppz_dpdata

expdp schemas=LES_TEST dumpfile=expdp_les20150504.dmp DIRECTORY=les_dpdata

expdp schemas=EDEDB dumpfile=expdp_EDEDB20150508.dmp DIRECTORY=les_dpdata

expdp schemas=IDOC_TEST dumpfile=expdp_EDEDB20150511.dmp DIRECTORY=les_dpdata

--创建表空间
create tablespace EWM_RF datafile 'D:\EWM_RF.dbf' size 500M;
create tablespace HLDATA datafile 'D:\HLADATA.dbf' size 500M;
-dp数据库
create tablespace DMDATA datafile 'D:\LPPZ\DMDATA.dbf' size 500M;
create tablespace LPPZDATA datafile 'D:\LPPZ\LPPZDATA.dbf' size 500M;

create tablespace LESDATA datafile 'D:\LES\LESDATA.dbf' size 500M;

create tablespace IDOCDATA datafile 'D:\LES\IDOCDATA.dbf' size 500M;

--修改表空间
alter tablespace  HLDATA rename to LPPZDATA;
alter tablespace  LPPZDATA rename to LESDATA;
alter tablespace  LESDATA rename to LPPZDATA;
--删除表
drop table TEST_RF.TRMPICKAREA_BIN;

--删除表空间
DROP TABLESPACE EWM_RF INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE HLDATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE DMDATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE HLDATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE LPPZDATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE IDOCDATA INCLUDING CONTENTS AND DATAFILES;

--视图权限
grant create any view to LES_TEST;
grant create any Table to LES_TEST;

--创建用户
CREATE USER "EWM_RF" PROFILE "DEFAULT" IDENTIFIED BY "EWM_RF" DEFAULT TABLESPACE EWM_RF;
CREATE USER "EWM_RF_TEST" PROFILE "DEFAULT" IDENTIFIED BY "EWM_RF" DEFAULT TABLESPACE EWM_RF;

-dp数据库
CREATE USER "TEST_RF" PROFILE "DEFAULT" IDENTIFIED BY "EWM_RF" DEFAULT TABLESPACE LPPZDATA;
CREATE USER "LES_TEST" PROFILE "DEFAULT" IDENTIFIED BY "LES" DEFAULT TABLESPACE LESDATA;
CREATE USER "EDEDB" PROFILE "DEFAULT" IDENTIFIED BY "LES" DEFAULT TABLESPACE IDOCDATA;
CREATE USER "IDOC_TEST" PROFILE "DEFAULT" IDENTIFIED BY "LES" DEFAULT TABLESPACE IDOCDATA;

'更改用户名
sqlplus "/ as sysdba"

select user#, name from sys.user$ where name = 'TEST_RF';
update sys.user$ set name = 'LES_TEST' where user# = 111;
alter system checkpoint;　
alter system flush shared_pool;
alter user LES_TEST identified by LES_TEST;
commit;

select user#, name from sys.user$ where name = 'LES_TEST';
update sys.user$ set name = 'TEST_RF' where user# = 86;
alter system checkpoint;　
alter system flush shared_pool;
alter user TEST_RF identified by TEST_RF;
commit;

select user#, name from sys.user$ where name = 'EDEDB';
update sys.user$ set name = 'IDOC_TEST' where user# = 92;
alter system checkpoint;　
alter system flush shared_pool;
alter user IDOC_TEST identified by IDOC_TEST;
commit;

CREATE USER "HLHOME" PROFILE "DEFAULT" IDENTIFIED BY "EWM_RF" DEFAULT TABLESPACE EWM_RF;
CREATE USER "HLHOME_TEST" PROFILE "DEFAULT" IDENTIFIED BY "EWM_RF" DEFAULT TABLESPACE EWM_RF;

--修改用户密码
alter user EWM_RF identified by searching ;

-dp数据库
alter user TEST_RF identified by searching ;
alter user LES_TEST identified by searching ;
alter user migrations identified by searching ;
alter user EDEDB identified by searching ;
alter user IDOC_TEST identified by searching ;

--用户授权连接
grant connect,resource to EWM_RF; 
grant connect,resource to EDEDB; 

-dp数据库
grant connect,resource to TEST_RF;
grant connect,resource to LES_TEST;
grant connect,resource to IDOC_TEST;
 
--授权表空间
grant unlimited tablespace to EWM_RF;
-dp数据库
grant unlimited tablespace to TEST_RF;
grant unlimited tablespace to LES_TEST;
grant unlimited tablespace to IDOC_TEST;

--授权序列
GRANT CREATE ANY sequence TO EWM_RF;
-dp数据库
GRANT CREATE ANY sequence TO TEST_RF;
GRANT CREATE ANY sequence TO LES_TEST;
GRANT CREATE ANY sequence TO IDOC_TEST;

--获取sysdba权限
GRANT EXECUTE ANY PROCEDURE TO EWM_RF WITH ADMIN OPTION;

--登陆系统
sqlplus

--删除用户
DROP USER EWM_RF CASCADE;
DROP USER TEST_RF CASCADE;
DROP USER EDEDB CASCADE;
DROP USER IDOC_TEST CASCADE;

--获取用户Session
select username,sid,serial# from v$session;
--结束Session
alter system kill session '34,4409';

--查看序列是否存在
select coreDevPage_Demo_DEMOID_SEQ.NEXTVAL from dual;

-- Create sequence 创建序列
 create sequence coreBackgroundTask_BT_ID_SEQ
minvalue  1
maxvalue  999999999999999999999999999
start  with 1
increment  by  1
nocache;

create sequence coreDevPage_Demo_DEMOID_SEQ
minvalue  1
maxvalue  999999999999999999999999999
start  with 1
increment  by  1
nocache;