跳到主要内容

Oracle

1、启动/停止数据库​

Windows​

sqlplus /nolog
connect / as sysdba
startup

CentOS​

启动数据库

su - oracle
lsnrctl start
sqlplus /nolog
conn /as sysdba
startup

关闭数据库

su - oracle
sqlplus /nolog
conn /as sysdba
shutdown immediate
# exit 退出 sql 命令行
lsnrctl stop

2、中文乱码问题​

在 XShell 中执行 sqlplus / as sysdba 会出现乱码​

现象​

在 XShell 中执行 sqlplus / as sysdba 会出现乱码

问题分析​

操作系统语言和数据库的语言不一致导致的，比如说，操作系统是英文，但是数据库安装的时候选择的是中文。这样在使用远程工具连接的时候就会出现这种问题。

解决方案​

修改操作系统的语言为数据库的语言。（修改数据库的语言可能会导致各种问题，不建议修改数据库的语言。）

步骤：​

查询数据库编码格式​

select * from V$NLS_PARAMETERS;

主要看：NLS_LANGUAGE（语言）、NLS_TERRITORY（地域）、NLS_CHARACTERSET（字符集）三个属性

**将这三个属性按照"语言_地域.字符集"拼接起来。"SIMPLIFIED CHINESE_CHINA.AL32UTF8" 这个很重要，后面的配置都需要这个。 **

修改操作系统中 oracle 用户的环境变量​
vim ~/.bash_profile

添加 export NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8

source ~/.nash_profile
修改 XShell 字符集​

实际效果​

可以发现没有乱码了

sqlplus 无法上下翻历史命令、删除字符等问题。​

现象​

解决方案​

使用 rlwrap 软件

安装方式​

rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum install rlwrap

安装之后配置环境变量

vim ~/.bash_profile

添加 alias sqlplus='rlwrap sqlplus' 添加 alias rman='rlwrap rman'

source ~/.nash_profile

实际效果​

删除字符或者 ↑ ↓ 可以查看历史命令

PLSQL 提示框乱码​

现象​

解决方案​

配置环境变量 NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8

实际效果​

timestamp 字段乱码​

NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SSFF6

3、分页查询​

-- ORACLE 分页查询第 11-20 条数据
SELECT A.*
FROM (SELECT T.*, ROWNUM ROWNO
      FROM TEST T
      WHERE ROWNUM < 20) A
WHERE A.ROWNO > 11;

4、like​

匹配下划线​

在模糊查询中 "" 表示匹配任意单一字符，如果想要匹配下划线"" 需要进行转义

select * from es10_user where userid_ like '%_%' escape '\';

5、ORA 错误​

有关 ORACLE 的错误信息可以直接在百度或谷歌上搜索 ORA-XXXXX site:itpub.net

ORA-00972​

ORA-00972: identifier is too long

12.2 版本之前标识符不允许超过 30 个字符。 从 12.2 版本开始支持 128 字符

ORA-01461​

ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

https://www.cnblogs.com/xiaotiannet/p/3846444.html

这个异常是指，用户向数据库执行插入数据操作时，某条数据的某个字段值过长，如果是varchar2类型的， 当长度超过2000，--4000（最大值）之间的时候，oracle会自动将该字段值转为long型的，然后，插入操作失败。

解决办法是：将此字段的类型改为clob或者blob类型；

ORA-31634​

原因：SELECT * FROM DBA_DATAPUMP_JOBS 中 JOB_NAME 序号超过了 99

解决方案

  • 方案一：删除 OWNER_NAME.JOB_NAME 表，然后 PURGE TABLE OWNER_NAME.JOB_NAME
DROP TABLE OWNER_NAME.JOB_NAME;
PURGE TABLE OWNER_NAME.JOB_NAME;
  • 方案二：使用唯一的 JOB_NAME
expdp db_user/password@orcl directory=dump_back_dir dumpfile=backup.dmp logfile=logfile.log job_name=uniqe_job_name schemas=schema_name

参考文章

https://valehagayev.wordpress.com/2018/01/10/data-pump-job-fails-with-ora-31634-job-already-exists-error/ https://anargodjaev.wordpress.com/2013/12/17/how-to-cleanup-orphaned-datapump-jobs-from-dba_datapump_jobs/

expdp​

DIRECTORY​

创建 directory

create directory EXPDP_FULL as 'D:\\dump_dir';

查询所有的 directory

select * from dba_directories;

授权

grant read,write on directory EXPDP_FULL to user;

删除 directory

drop directory EXPDP_FULL;

查询导出任务

select * from dba_datapump_jobs;

expdp 使用示例​

  1. 全量导出

expdp yunzheng/yunzheng@orclcdb schemas=yunzheng dumpfile=yunzheng.dmp directory=oracle_exp_dir logfile=yunzheng.log

expdp system/system@wangzhy dumpfile=exp_full.dmp directory=EXPDP_FULL full=y logfile=expdp.log
  1. 导出单张表
expdp system/system@orcl  tables=english_words dumpfile=english_words.dmp directory=expdp

用expdp导出数据

1)导出用户 expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir;

2)导出表 expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir;

3)按查询条件导 expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=empquery='where deptno=20';

4)按表空间导 expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmptablespaces=temp,example;

5)导整个数据库 expdp system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;

用impdp导入数据

1)导入用户（从用户scott导入到用户scott） impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott;

2)导入表（从scott用户中把表dept和emp导入到system用户中） impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmptables=scott.dept,scott.emp remap_schema=scott:system;

3)导入表空间 impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example;

4)导入数据库 impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;

5)追加数据 impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=systemtable_exists_action

impdp baidufanyi/baidufanyi@orclcdb directory=expdp_pc dumpfile=expdp_baidufanyi.dmp schemas=tigebaidufanyir;

imp wsi/wsi@localhost/wangzhy file="cheshi.dmp" full=y

导出数据库

-- exp 用户名/密码@localhost:1521/实例名file=路径.dmp exp xzirpt/xzirpt@172.21.150.61:1521/orcl owner=xzirpt file=wsb.dmp log=wsb.log

exp xzirpt/xzirpt@172.21.150.61:1521/orcl file=D:\DB.dmp log=D:\log full=y igno exp xzirpt/xzirpt@172.21.150.61:1521/orcl tables=DIM_XZQHDM,DIM_XZQHN1644 file=D:\exp\DB.dmp log=D:\exp\log exp xzirpt/xzirpt@orcl file=D:\exp\DB.dmp owner=(xzirpt) buffer=80000000 log=D:\exp\log exp xzirpt/xzirpt@172.21.150.61:1521/orcl tables=IRPT_DEPARTMENTS_ZSRL file=D:\exp\exp.dmp log=D:\exp\log

exp wsi/wsi@127.0.0.1:1521/wangzhy owner=wsi file=D:\wsi.dmp log=D:\wsi.log imp system/system@wangzhy file=D:\wsi.dmp fromuser=wsi touser=wsi_country

导入数据库

imp system/system@wangzhy file=D:\exp\wsb.dmp fromuser=xzirpt touser=wsb