Oracle¶

@2019-02-13 新版功能: 创建

连接数据库¶

$ su - oracle
$ rlwrap sqlplus / as sysdba  # rlwrap 启用 readline 支持
SQL> connect dbname
输入密码：xxxxxxxx

关闭数据库¶

$ su - oracle
$ lsnrctl stop   # 关闭服务器端监听。
$ sqlplus / as sysdba
SQL> shutdown immediate;  #立即强制关闭
SQL> quit

全量导出与导入命令¶

expdp \' / as sysdba\' dumpfile=full.dmp full=y directory=BACKUP # 导出
impdp \' / as sysdba\' dumpfile=full.dmp full=y directory=BACKUP # 导入

导入命令参数：

TABLE_EXISTS_ACTION=[SKIP|APPEND|TRUNCATE|REPLACE]

SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

APPEND loads rows from the source and leaves existing rows unchanged.

TRUNCATE deletes existing rows and then loads rows from the source.

REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

其他常用语句¶

查看数据库的用户
- select username from dba_users;
修改用户密码
- alter user system identified by 123456;
查看数据库名
- select name from v$database;
查看实例名
- select instance_name from v$instance;
查看 Oracle 中创建的目录
- select * from dba_directories;
查询数据库当前进程的连接数
- select count(*) from v$process;
查看数据库当前会话的连接数
- select count(*) from v$session;
查看数据库的并发连接数
- select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况
- select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数
- select value from v$parameter where name = 'processes';
- show parameter processes;
- show parameter sessions;
修改数据库允许的最大连接数
- alter system set processes = 300 scope = spfile;
- create pfile from spfile; # 需要重启数据库才能实现连接数的修改

修复 spfile 问题

参数不匹配时数据库启动会报错，例如：

ORA-00821: Specified value of sga_target 500M is too small, needs to be at least 760M
解决办法：
SQL> create pfile='/home/oracle/init.ora' from spfile;

File created.

SQL> host vi /home/oracle/init.ora
# 修正导致问题的参数
SQL> create spfile from pfile='/home/oracle/init.ora';

File created.

SQL> startup

其他参考： Oracle init params 说明