sqlplus环境变量配置
设置 sqlplus 命令提示符 glogin.sql
vim $ORACLE_HOME/sqlplus/admin/glogin.sql
set linesize 120
set pagesize 999
set long 4000
define _editor=vi
set sqlprompt "_user’@’_connect_identifier> "
set sqlp "_user’@’_connect_identifier> "
设置别名(可选配置)：
vim /home/oracle/.bashrc
alias ocm12=‘export ORACLE_SID=ocm12’
alias jm=‘export ORACLE_SID=jm’
alias nocdb=‘export ORACLE_SID=nocdb’
alias dbn=‘cd $ORACLE_HOME/network/admin’
alias dbs=‘cd $ORACLE_HOME/dbs’
alias sql=‘sqlplus / as sysdba’

sqlplus 临时环境变量
/*
使用SQL查看执行计划
REM EXPLAIN PLAN FOR sql语句;
REM SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));
/
REM 开启跟踪
/
设置autotrace
REM set autotrace on
REM set autotrace on explain

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOT OFF 上面的缩写
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS – 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
set autot on
SET AUTOT ON 上面的缩写
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
set serveroutput on --sqlplus DBMS输出显示
*/

Oracle执行计划查看方法
方法1
[explain plan for] plsql按F5
explain plan for select * from dual;
select * from table(dbms_xplan.display());
方法2
[set autotrace on]-sql*plus
set autotrace on
select * from dual;
方法3
[statistics_level=all]
alter session set statistics_level=all;
select * from dual;
select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,null,‘allstats last’));
方法4
[dbms_xplan.display_cursor]
select * from table( dbms_xplan.display_cursor(’&sql_id’) );
方法5
[事件10046 trace]
步骤1：alter session set events ‘10046 trace name context forever,level 12’; --开启追踪
步骤2：执行sql语句；
步骤3：alter session set events ‘10046 trace name context off’; --关闭追踪
步骤4：找到跟踪后产生的文件（开启10046前先用‘ls -lrt’看一下文件，执行结束后再看哪个是多出来的文件即可）
步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

安装oracle数据库程序（略）
创建监听
netca

创建数据库
dbca

emca

1.启动\关闭监视器
在终端下：lsnrctl start \ lsnrctl stop lsnrctl startus（查看运行状态）

2.启动\关闭企业管理器(OEM)
在终端下: emctl start dbconsole \emctl stop dbconsole emctl startus dbconsole

3.启动\关闭数据库实例
在终端或cmd里启动sqlplus / as sysdba（以OS用户身份连接）

切换oracle数据库实例
set ORACLE_SID=数据库实例名 /windos环境
export ORACLE_SID=数据库实例名 /linux或unix环境

sqlplus /nolog --运行sqlplus命令，进入sqlplus环境，nolog参数表示不登录；
SQL> connect / as sysdba --以系统管理员(sysdba)的身份连接数据库；如果需要对数据库

sqlplus sys/yksoft1919@ykchr as sysdba (因为可以直接用这个命令连接数据库)
　　　　　　　　
启动数据库实例：SQL> startup
关闭数据库实例：SQL> shutdown immediate;

startup 启动数据库实例时， 启动控制文件 启动数据文件
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
startup mount 启动数据库实例时， 启动控制文件 不启动数据文件
alter database open 打开数据库
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
startup nomount 启动数据库实例时， 不启动控制文件 不启动数据文件
alter database mount
alter database open

数据库启动的三个台阶nomount，mount，open
目的是明白细化启动数据库的三个步骤
启动数据库到nomount状态的条件如下。如果你是非windows OS 就没有注册表，而有环境变量
服务中的OracleService必须启动
服务的名称和注册表中的oracle_sid相匹配
存在正确的密码文件和参数文件
有足够的内存
参数文件中描述的路径必须存在
数据库产品安装正确

conn sys/oracle as sysdba
shtudown abort;
startup nomount;
select instance_name,status from vinstance; 启动数据库到第一个台阶nomount状态做了如下的工作 1.读参数文件 2.分配内存 3.启动后台进程 4.初始化部分v视图

将数据库带到mount状态
select value from vspparameter where name='control_files'; alter database mount; mount数据库的过程是读参数文件中描述的控制文件，校验控制文件的正确性，将控制文件的内容读入到内存中，mounu是挂接的意思，是操作系统中的概念。一旦mount之后，就是将一个没有意义的实例和一个数据库发生了联系。因为实例是空壳。没有任何数据库和该实例发生关系，我们可以理解为实例是水泵，放到哪个水塘里就会抽取哪里的数据，实例是通用的。mount的意思是将一个通用的水泵放入到一个指定的水塘。mount是读控制文件，控制文件中有数据文件和日志文件的信息。 select instance_name,status from vinstance;

打开数据库
alter database open;
读控制文件中描述的数据文件
验证文件的一致性，如果不一致，使用日志文件将数据库文件恢复到一致的状态。
数据库open后，普通用户才可以访问数据库
用户的表才为可见

只读方式open数据库
startup mount;
alter database open read only;
select open_mode from v$database;
默认的open方式为read write
想改read only为read write必须重新启动数据库
我们现在回想一下数据库启动的三个台阶，我们先读的是参数文件，参数文件可以由我们来编写。读完参数文件后又读了控制文件，控制文件中描述了数据文件和日志文件的信息，如果控制文件丢失了我们可以重新建立，最后是读数据文件。数据文件里才存放了我们的数据。数据库将启动分为三个台阶，目的是我们可以准确的知道哪里有问题，迅速排除。有点象老拖木钎，大头的在后面。由最开始的一个1K的参数文件，最后到几个 T 的大型数据库。当我们只打startup 而不加任何参数的时候，默认是到open，等于startup open；

我们从屏幕显示的结果可以清楚的看出，有三个台阶。
还有一个命令是startup force强制启动数据库，等于强制停止数据库再启动数据库。

force mount就等于是强制关闭数据库，然后将数据库启动到mount状态。

还有一个命令restricted为受限模式，当startup restricted以受限模式打开数据库后，只有具有restricted session 权限或DBA角色的用户可以访问数据库，当执行数据库结构维护或导出导入数据时为了禁止普通用户访问数据库可以使用受限模式启动数据库。

startup nomount

Shutdown immediate；
这种方式可以较快且安全地关闭数据库，是DBA经常采用的一种关闭数据库的方式，此时ORACLE会做一些操作，中断当前事务，回滚未提交的事务，强制断开所有用户连接，执行检查点把脏数据写到数据文件中。虽然参数IMMEDIATE有立即关闭数据库的含义，但是它只是相对的概念，如果当前事务很多，且业务量很大，则中断事务以及回滚数据、断开连接的用户都需要时间 。
Shutdown transactional
使用TRANSACTIONAL参数时，数据库当前的连接继续执行，但不允许新的连接，一旦当前的所有事务执行完毕，则关闭数据库。
显然这种方式，通常情况下，在生产数据库系统中，这方式也不会快速关闭数据库，因为如果当前的某些事务一直执行，或许会用几天时间关闭数据库。
Shutdown abort
这是一种很不安全地关闭数据库的方法，最好不要使用使用该方式关闭数据库。SHUTDOWN ABORT关闭数据库时，ORACLE会断开当前的所有用户连接，拒绝新的连接，断开当前的所有执行事务，立即关闭数据库。使用这种方式关闭数据库，当数据库重启时需要进行数据库恢复，因为它不会对未完成事务回滚，也不会执行检查点操作。
Shutdown normal
这种方式是SHUTDOWN数据库的默认方式，如果用户输入SHUTDOWN，则默认采用NORMAL参数，这种方式关闭数据库时，不允许新的数据库连接，只有当前的所有连接都退出时才会关闭数据库，这是一种安全地关闭数据库的方式，但是如果有大量用户连接，则需要较长时间关闭数据库。

查看实例状态
select instance_name, status from vinstance； 查看数据库名称 select name from vdatabase;

查看控制文件
SELECT name FROM vcontrolfile; 查看数据文件 select name from vdatafile;
查看日志文件
SELECT * FROM v$logfile;
查看数据文件表空间情况
SELECT tablespace_name,file_id,bytes/1024/1024,file_name FROM dba_data_files ORDER BY file_id;

archive log list 查看数据库日志处于什么模式
把非归档模式改为归档模式的手工操作步骤如下：

SQL> connect /as sysdba;–需要以SYSDBA的身份进行归档操作

SQL> shutdown immediate;–关闭数据库

SQL> startup mount;–以mount方式装载数据库

SQL> alter database archivelog;–手工归档，取消归档用noarchivelog

SQL> alter database open;–以open方式完整装载数据库

SQL> archive log list;–显示归档模式

SQL> archive log start;–设置自动归档

表空间操作
创建表空间
CREATE TABLESPACE cms2013 DATAFILE
‘/u01/app/oracle/oradata/don/cms2013.dbf’ SIZE 32M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

em企业管理器

CREATE SMALLFILE TABLESPACE “ykspace” DATAFILE ‘/u01/app/oracle/oradata/orcl/ykspace.dbf’ SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

删除表空间
删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
–删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
–删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
–删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
–如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

drop tablespace jma including contents and datafiles CASCADE CONSTRAINTS;

drop tablespace jma including contents;

drop tablespace jma including datafiles;

用户操作
创建用户（有分号的在sqlplus需要一条一条执行）
CREATE USER cms2013 IDENTIFIED BY password
DEFAULT TABLESPACE CMS2013
TEMPORARY TABLESPACE TEMP
PROFILE “DEFAULT”
QUOTA UNLIMITED ON CMS2013;
GRANT “CONNECT” TO cms2013 WITH ADMIN OPTION;
GRANT “DBA” TO cms2013 WITH ADMIN OPTION;
GRANT “RESOURCE” TO cms2013 WITH ADMIN OPTION;
ALTER USER cms2013 DEFAULT ROLE “CONNECT”, “DBA”, “RESOURCE”;
修改用户
ALTER

将口令有效期默认值180天修改成“无限制”

SQL>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
修改用户名密码
alter user system identified by password;

删除用户
删除用户yksoft和对应的数据
drop user yksoft cascade;
commit;

查看当前用户所有的表

查看当前用户所有的表的命令如下

SELECT * FROM cat WHERE table_name not like ‘BIN%’;

或者：

SELECT * FROM tab;

或者：

SELECT table_name FROM user_tables;

查看当前用户默认表空间的命令如下

SELECT username,default_tablespace FROM user_users;

数据泵导入导出

创建oracle数据库后

1.命令行打开sqlplus
sqlplus /nolog
conn / as sysdba
2、创建逻辑目录，该命令不会在操作系统创建真正的目录（最好手工先建好），最好以administrator等管理员创建。
create directory dir as ‘d:\databak’; //dir名称可以随便命名 需要手工创建d:\databak

3、查看管理理员目录（同时查看操作系统中是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
select * from dba_directories;
4、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。
grant read,write on directory dir to yksoft;

按用户导出
expdp yksoft/yksoft919@ykchr schemas=yksoft dumpfile=yksoft_data.dmp DIRECTORY=dir logfile=eyksoft_data_log.log

按用户导入
impdp yksoft/yksoft919@ykchr schemas=yksoft dumpfile=yksoft_data.dmp DIRECTORY=dir logfile=iyksoft_data_log.log

备注：如果原来有数据需加参数table_exists_action=replace
impdp yksoft/yksoft919@ykchr schemas=yksoft dumpfile=yksoft_data.dmp DIRECTORY=dir table_exists_action=replace logfile=iyksoft_data_log.log
不同用户不同表空间导入
impdp b/b directory=dir dumpfile=data.dmp remap_tablespace=a:b remap_schema=a:b logfile=data.log
详情看下面

导入a用户所有数据到b，并且转换表空间a为b：
SQL> conn sys / as sysdba
SQL> create directory dir as ‘/home/oracle/’;
SQL> grant read,write on directory dir to system;
impdp b/b directory=dir dumpfile=data.dmp remap_tablespace=a:b remap_schema=a:b logfile=data.log

解释：
remap_schema=a:b 将数据的schema从a 转换为b
remap_tablespace=a:b 将数据的tablespace 从a 转换为b

注意：如果oracle是10g的，要加参数 EXCLUDE=TABLE_STATISTICS 选项将 table_statistics 对象过滤。 否则会出现数据泵导入中table_statistics长时间等待、用impdp 导入，检查 table_statistics 时等待了N长时间

总结：执行impdp时无需创建b用户，在导入时会自动创建并改名用户a为b（拥有a的所有权限等），自动设置默认表空间为转换后的表空间b。如果有多个表空间需要转换，则使用多个remap_tablespace=源：目标字段。
此种方法只限于支持oracle10g以上版本。

字符编码

设置字符集
locale -a

C:\Users\jimdon>chcp
活动代码页: 936

os 字符集

软件 字符集

oracle 字符集

US7ASCII
zhs16cgb231280
AL32UTF8 数据库字符集
AF16UTF16 国家字符集
ZHS16GBK 超集 严格超集（即包含又编码一致）
utf8

export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK linux
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 linux
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 windows
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

常用中文字符集

set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

常用unicode字符集

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

设置默认实例
export oracle_sid=sidname linux

set oracle_sid=sidname windows

编码–>字符 字符–>编码

客户端 OS ORACLE数据库
编码 编码/转码
NLS_LANG

字符集出现问题的判断流程
存储的是错误的字符编码
存储的是正确的字符编码

Oracle的字符集命名遵循以下命名规则:

即: <语言><比特位数><编码>
比如: ZHS16GBK表示采用GBK编码格式、16位（两个字节）简体中文字符集

NLS_LANG=_.
Language:显示oracle消息,校验，日期命名
Territory：指定默认日期、数字、货币等格式
Client character set：指定客户端将使用的字符集
例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII
AMERICAN是语言，AMERICA是地区，US7ASCII是客户端字符集

exp和imp的导出和导入

exp yksoft/yksoft1919@ykchr owner=yksoft file=d:\ykchr_data.dmp buffer=409600 log=ykchr_data_log.log

imp yksoft/yksoft1919@ykchr fromuser=yksoft touser=yksoft ignore=y file=d:\ykchr_data.dmp buffer=409600 log=ykchr_data_log.log

1G(11.2.0.1.0)中有个新特性，当表无数据时，不分配segment，以节省空间
　　解决方法：
　　1、insert一行，再rollback就产生segment了。
　　该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。
　　2、设置deferred_segment_creation 参数

SQL>show parameter deferred_segment_creation

NAME TYPE VALUE

deferred_segment_creation boolean TRUE
SQL> alter system set deferred_segment_creation=false;

系统已更改。

SQL> show parameter deferred_segment_creation

NAME TYPE VALUE

deferred_segment_creation boolean FALSE

11g(11.2.0.2.0)这个bug好像就修复了，建议用expdb 命令导出

racle错误代码：ORA-28002。

受影响版本：Oracle11g以上版本。

导致密码消失的原因：Oracle 11g中默认的DEFAULT概要文件中口令有效期PASSWORD_LIFE_TIME默认值为180天。

当以客户端登陆Oracle提示ORA-28002，则基本可以确定登陆帐号已过有效期，使用具有DBA权限的帐号重置该帐号密码即可。

解决方法：

以下步骤以具有DBA权限用户操作

1.查看口令失效用户的profile文件

SQL>SELECT username,profile FROM dba_users;

EM：服务器>用户，查看口令失效的用户对应的概要文件，这里假设为DEFAULT，下同。

2.查看对应的概要文件的口令有效期设置

SQL>SELECT * FROM dba_profiles WHERE profile=‘DEFAULT’ AND resource_name=‘PASSWORD_LIFE_TIME’;

EM：服务器>概要文件>选择刚刚查到的概要文件DEFAULT>查看，查看口令下面的有效期值。

3.将口令有效期默认值180天修改成“无限制”

SQL>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

EM：服务器>概要文件>选择刚刚查到的概要文件DEFAULT>编辑>口令，在有效期输入或选择你需要的值，保存。

该参数修改实时生效。

出于数据库安全性考虑，不建议将PASSWORD_LIFE_TIME值设置成UNLIMITED，即建议客户能够定期修改数据库用户口令。

在修改PASSWORD_LIFE_TIME值之前已经失效的用户，还是需要重新修改一次密码才能使用。

SQL>ALTER USER test IDENTIFIED BY password

select owner,segment_type,segment_name,buffer_pool
from dba_segments
where buffer_pool != ‘DEFAULT’;

**********************Oracle数据库补丁版本查看

$ORACLE_HOME/OPatch/opatch lsinventory$ORACLE_HOME/OPatch/opatch lspatches
11g
select * from dba_registry_history;
12c
select PATCH_ID, PATCH_UID,VERSION,ACTION, STATUS,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;