oracle命令

最新推荐文章于 2022-09-23 09:10:23 发布

原创最新推荐文章于 2022-09-23 09:10:23 发布 · 297 阅读

CC 4.0 BY-SA版权

本文详细介绍如何在Oracle数据库中创建表空间、用户，并授予各种权限的过程，包括表、索引、表空间等操作权限，以及如何进行数据导入导出。

本地库

1、创建表空间
CREATE TABLESPACE BJZZB_DN_SPACES

DATAFILE
'D:\NEWZZB\BJZZB_DN_SPACES'
SIZE 20M REUSE AUTOEXTEND ON
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

2、创建用户

-- 创建用户
CREATE USER cssdn
IDENTIFIED BY zzb192171

DEFAULT TABLESPACE BJZZB_DN_SPACES
;

-- Directory
GRANT SELECT ANY DICTIONARY TO cssdn
;

-- PROCEDURE
GRANT EXECUTE ANY PROCEDURE TO cssdn
;
GRANT DEBUG ANY PROCEDURE TO cssdn
;

-- Sequence
GRANT SELECT ANY SEQUENCE TO cssdn
;

-- TABLE
GRANT SELECT ANY TABLE TO cssdn
;
GRANT INSERT ANY TABLE TO cssdn
;
GRANT DELETE ANY TABLE TO cssdn
;
GRANT UPDATE ANY TABLE TO cssdn
;
GRANT CREATE ANY TABLE TO cssdn
;
GRANT ALTER ANY TABLE TO cssdn
;
GRANT DROP ANY TABLE TO cssdn
;

-- INDEX
GRANT CREATE ANY INDEX TO cssdn
;
GRANT ALTER ANY INDEX TO cssdn
;
GRANT DROP ANY INDEX TO cssdn
;

-- TABLESPACE
GRANT UNLIMITED TABLESPACE TO cssdn
;

-- SESSION
GRANT DEBUG CONNECT SESSION TO cssdn
;
GRANT ALTER SESSION TO cssdn
;
GRANT RESOURCE, CONNECT TO cssdn
;

-- EXP, IMP
GRANT EXP_FULL_DATABASE TO cssdn
;
GRANT IMP_FULL_DATABASE TO cssdn
;

--为了执行包含DDL的动态SQL，直接给用户授权
GRANT ALL PRIVILEGE TO cssdn
;
ALTER USER cssdn
DEFAULT ROLE ALL;

select * from dba_directories;

impdp cssdn/111111 dumpfile=cssdn_20170921.DMP directory=DATA_PUMP_DIR remap_schema= cssdn:cssdn2 remap_tablespace= BJZZB_DN_SPACES:CSSDN_DATA03 table_exists_action=replace

expdp cssdn/123 dumpfile=******.dmp directory=***** logfile=*****.log version=11.2.0.1.0

impdp cssdn/111111 dumpfile=daochu.DMP directory=DATA_PUMP_DIR remap_tablespace= BJZZB_DN_SPACES:CSSDNTMP_DATA

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--导入
impdp cssdn/123456 dumpfile=daochu.DMP directory=DATA_PUMP_DIR table_exists_action=replace

--导出
expdp cssdn/密码 dumpfile=daochu.dmp directory=DATA_PUMP_DIR logfile=daochu.log

导入：

impdp cssdn/111111 (新用户名和密码) dumpfile=DAOCHU1.DMP directory=DATA_PUMP_DIR remap_schema= cssdjpro:cssdn(老用户名:新用户名) remap_tablespace= DJ_PRO:BJZZB_DN_SPACES (老表空间：新表空间) table_exists_action=replace

expdp cssdjpro/123456@orcl(用户名/密码 @服务) dumpfile=daochu1.dmp directory=DATA_PUMP_DIR logfile=daochu1.log version=11.1.0.6.0（要导入数据库的版本号）

/////////////////////////////////////////////////////////////////////////////////////////////////////////

imp cssdn/密码@cssdj fromUser=cssdn toUser=cssdn ignore=y file=20170920.dmp log=999.log

select * from d_serverid_zcfw

select * from g_param

连接前需要配置本地的监听和服务
listener.ora 文件添加

LISTENER_YCORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.43

)(PORT = 1521))
    )
  )

tnsnames.ora 文件添加 YCORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.43

)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

远程无监听：

需要在服务器端，配置为IP地址