数据库导出

空表处理

ORACLE 11G中有个新特性，为节省空间，当表无数据时不会分配segment，导致空表无法导出表结构。
使用以下语句生成并执行ALTER语句。

  1. SELECT 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' AS SQL_TEXT
  2. FROM USER_TABLES WHERE TEMPORARY='N' AND ( SEGMENT_CREATED='NO' OR NUM_ROWS=0 OR NUM_ROWS IS NULL );

exp导出

exp qdbctest/password@192.168.8.100:1521/orcl owner=qdbctest file=F:\qdbctest.dmp

如果需要转换编码格式

先查看目标库的语言设置，根据目标库的语言决定导出的语言
SELECT USERENV(‘LANGUAGE’) FROM DUAL;
如查询结果为：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

windows导出

set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
echo %NLS_LANG%
exp qdbctest/password@192.168.8.100:1521/orcl owner=qdbctest file=F:\qdbctest.dmp

linux导出

export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
echo $NLS_LANG
exp qdbctest/password@192.168.8.100:1521/orcl owner=qdbctest file=/home/oracle/backup/qdbctest.exp.dmp

数据库导入

创建表空间、用户、授予权限
F:> sqlplus / as sysdba
SQL> create bigfile tablespace QDBCTBS datafile ‘E:\oracle\oradata\orcl\QDBCTBS.dbf’ size 10m reuse autoextend on next 10m maxsize unlimited;
SQL> create user qdbcdev identified by password default tablespace QDBCTBS;
SQL> grant dba to qdbcdev;
SQL> grant connect, resource to qdbcdev;
SQL> exit;
F:> imp qdbcdev/password@192.168.8.200:1521/orcl file=F:\qdbctest.exp.dmp tablespaces=QDBCTBS fromuser=qdbctest touser=qdbcdev ignore=y
删除用户
SQL> drop user qdbcdev cascade;
修改密码
SQL> alter user qdbcdev identified by password;
删除表空间
SQL> drop tablespace QDBCTBS including contents and datafiles;

表解锁

  1. select object_name, machine, s.sid, s.serial#
  2. from v$locked_object l, dba_objects o, v$session s
  3. where l.object_id = o.object_id and l.session_id=s.sid;

alter system kill session ‘32,115’; ( 其中32,115是查询结果的sid和serial# )

新装数据库的配置

新装数据库，如果不改配置，本机可以访问，远程访问不了。

修改listener.ora

使用命令lsnrctl status查看监听状态：

  1. F:\> lsnrctl status
  2. Alias                     LISTENER
  3. Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
  4. Listener Parameter File   F:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
  5. Listener Log File         F:\oracle\diag\tnslsnr\zhhlnv\listener\alert\log.xml
  6. Listening Endpoints Summary...
  7.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  8.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

根据上面提示的文件位置，打开listener.ora文件

  1. LISTENER =
  2.   (DESCRIPTION_LIST =
  3.     (DESCRIPTION =
  4.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  6.     )
  7.   )

增加一个本机IP的监听项

  1. LISTENER =
  2.   (DESCRIPTION_LIST =
  3.     (DESCRIPTION =
  4.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  6.     )
  7.     (DESCRIPTION =
  8.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  9.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.200)(PORT = 1521))
  10.     )
  11.   )

重启Oracle服务

我的电脑—右键—管理—服务
重启OracleOraDb11gTNSListener服务/OracleServiceORCL服务

防火墙放开1521端口

控制面板—系统和安全—WindowsDefender防火墙—高级设置
入站规则—右键新建规则—端口—1521

这样远程就可以访问了。

数据泵导出

数据泵导出首先需要知道逻辑目录，导出命令中指定导出到哪个逻辑目录，导出的文件就在逻辑目录对应的系统路径下
sqlplus / as sysdba
查询逻辑目录
SQL> COL DIRECTORY_PATH FOR A60;
SQL> SET LINESIZE 160;
SQL> SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM DBA_DIRECTORIES;

  1. DIRECTORY_NAME                 DIRECTORY_PATH
  2. ------------------------------ ------------------------------------------------------------
  3. DIRECTORY                      /home/oracle/app/oracle/oradata/orcl/
  4. DUMP_DIR                       /home/oracle/dump_dir

也可以创建目录
SQL> CREATE DIRECTORY DUMP_DIR AS ‘/home/oracle/dump_dir’;
授权
SQL> GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO qdbcdev;
执行导出 (version批定目标版本，可以不要)
expdp qdbcdev/qdbcdev@orcl schemas=qdbcdev directory=DUMP_DIR dumpfile=qdbcdev.expdp.dmp logfile=qdbcdev.expdp.log version=11.1.0.0.0;
oracle 12c以上的连接地址为：qdbcdev/qdbcdev@127.0.0.1:1521/LHRPDB1
导出的dump文件和日志文件在逻辑目录中

查询表空间，导入时需要替换
sqlplus qdbcdev
SQL> SELECT DISTINCT TABLESPACE_NAME FROM USER_TABLES;

数据泵导入

sqlplus / as sysdba
创建表空间
SQL> create bigfile tablespace qdbctbs datafile ‘/home/oracle/app/oracle/oradata/orcl/qdbctbs.dbf’ size 10m reuse autoextend on next 10m maxsize unlimited;
创建临时表空间
SQL> create temporary tablespace qdbctemp tempfile ‘/home/oracle/app/oracle/oradata/orcl/qdbctemp.dbf’ size 10m reuse autoextend on next 10m maxsize unlimited;
执行导入
impdp qdbcdev/qdbcdev@orcl directory=DUMP_DIR dumpfile=qdbcdev.expdp.dmp remap_schema=qdbcdev:qdbcdev remap_tablespace=platform:qdbctbs,users:qdbctbs,qdbctemp:qdbctemp logfile=qdbcdev.impdp.log transform=oid:n exclude=USER;

删除连接

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’QDBCDEV’;
SID SERIAL#
————— —————
35 36237
65 28326
ALTER SYSTEM KILL SESSION ‘35,36237’;
ALTER SYSTEM KILL SESSION ‘65,28326’;

导出表注释和列注释

  1. SELECT 'COMMENT ON TABLE '||TABLE_NAME||' IS '''||
  2.         REPLACE(REPLACE(COMMENTS,CHR(10),' '),CHR(13),'')||''';' COMMENTS
  3.     FROM DBA_TAB_COMMENTS 
  4.     WHERE OWNER='PLATFORM' AND COMMENTS IS NOT NULL AND INSTR(COMMENTS,'?') = 0
  5. UNION
  6. SELECT 'COMMENT ON COLUMN '||TABLE_NAME||'.'||COLUMN_NAME||' IS '''||
  7.         REPLACE(REPLACE(COMMENTS,CHR(10),' '),CHR(13),'')||''';' COMMENTS
  8.     FROM DBA_COL_COMMENTS 
  9.     WHERE OWNER='PLATFORM' AND COMMENTS IS NOT NULL AND INSTR(COMMENTS,'?') = 0;

只导流程表的注释

  1. SELECT 'COMMENT ON TABLE '||TABLE_NAME||' IS '''||
  2.         REPLACE(REPLACE(COMMENTS,CHR(10),' '),CHR(13),'')||''';' COMMENTS
  3.     FROM DBA_TAB_COMMENTS 
  4.     WHERE OWNER='PLATFORM' AND COMMENTS IS NOT NULL AND INSTR(COMMENTS,'?') = 0
  5.   AND TABLE_NAME LIKE 'ACT#___#_%' ESCAPE '#'
  6. UNION
  7. SELECT 'COMMENT ON COLUMN '||TABLE_NAME||'.'||COLUMN_NAME||' IS '''||
  8.         REPLACE(REPLACE(COMMENTS,CHR(10),' '),CHR(13),'')||''';' COMMENTS
  9.     FROM DBA_COL_COMMENTS 
  10.     WHERE OWNER='PLATFORM' AND COMMENTS IS NOT NULL AND INSTR(COMMENTS,'?') = 0
  11.   AND TABLE_NAME LIKE 'ACT#___#_%' ESCAPE '#';


表空间

命令行查询表空间
SQL> COL FILE_NAME FOR A60;
SQL> SET LINESIZE 160;
SQL> SELECT TABLESPACE_NAME,FILE_NAME,ROUND(BYTES/1024/1024/1024) GB,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
SQL> SELECT TABLESPACE_NAME,FILE_NAME,ROUND(BYTES/1024/1024/1024) GB,AUTOEXTENSIBLE FROM DBA_TEMP_FILES;
临时表空间增加文件 (数据表空间将TEMPFILE换成DATAFILE)
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata19c/DCLHR/TEMP2.dbf’ size 10m reuse autoextend on next 10m maxsize unlimited;
修改文件大小
SQL> ALTER DATABASE TEMPFILE ‘/oracle/oradata19c/DCLHR/TEMP1.dbf’ resize 100m;
设置自动扩展
SQL> ALTER DATABASE TEMPFILE ‘/oracle/oradata19c/DCLHR/TEMP1.dbf’ autoextend on;

oracle19c

show con_name # 查看当前连接
select name,open_mode from v$pdbs; # 查看PDBS
alter session set container=LHRPDB1; # 切换容器
导入时的地址与老版本不同
impdp qdbcdev/qdbcdev@orcl
impdp qdbcdev/qdbcdev@127.0.0.1:1521/LHRPDB1