Oracle数据库笔记

Oracle数据库笔记


数据库和实例的区别：
数据库是物理操作系统文件或者磁盘的集合。
实例是一系列Oracle后台进程以及内存区。
数据库可以由多个实例装载，一个实例在其生命周期中只能打开一个数据库。


数据库服务=实例+数据库
实例=SGA+后台进程。
后台进程：PMON（进程监控程序）、SMON（系统监控程序）、CKPT（检查点）、DBWR（数据读写）、LGWR（日志读写）、ARCH（归档日志进程）
查看日志是否位于归档模式：archive log list;
如果日志位于非归档模式，改为归档模式的步骤
1、关闭数据库：shutdown immediate;
2、启动到mount阶段：startup mount;
3、更改日志模式：alter database archivelog;
4、打开数据库：alter database open;


强制启动检查点：alter system checkpoint;




PGA（用户全局区）：主要用于排序以及存放用户连接信息等数据。
PGA排序区大小：alter system set sort_area_size=65536 scope=spfile;


SGA（系统全局区）：数据缓冲区、共享池（库高速缓存和数据字典高速缓存）、java池、流池等.
设置共享池大小：alter system set shared_pool_size=100M;--不能直接设置库高速缓存和数据字典高速缓存的大小
设置数据缓冲区大小：alter system set db_cache_size=500M;
设置日志缓冲区大小：alter system set log_buffer=16000000 scope=spfile;   -- 这里大小不能为M等单位只能为整数， scope不能为both




数据库物理文件：参数文件、数据文件、日志文件、控制文件、跟踪文件、密码文件等
参数文件：SHOW PARAMETER SPFILE
F:\app\tdn084\product\11.2.0\dbhome_1\database\SPFILEORCL.ORA
SPFILE（服务器参数文件）：可以在数据库里修改参数，解决了多份init.ora的问题。
PFILE 即以前的init.ora文件。
F:\app\tdn084\admin\orcl\pfile\init.ora.917201710114.


数据库文件的逻辑组织方式：表空间--段--区--块。
Oracle11g之后采用延迟段的创建方式，即创建时并不立即创建，而是在数据插入时才会创建。


连接和会话：
连接是指用户进程和数据库服务之间的通讯。3种连接方式：基于主机（客户端和服务器在一台机器上）；基于客户机-服务器的方式；基于客户机-应用服务器-数据库服务器的方式。
会话是指一个明确的数据库连接。
一个连接可以由多个会话。




数据库启动三阶段：nomount、mount、open
nomount阶段：打开实例，读取参数文件。  语法：alter database nomount;
mount阶段：读取控制文件。  语法：alter database mount;
open阶段：读取数据文件。  语法：alter database open;
直接打开数据库：startup;
关于数据库启动时的日志文件可在参数 background_dump_dest 定义的目录下找到。


数据库关闭和数据库启动正好相反：先关闭数据文件，然后是控制文件，最后是参数文件。
shutdown immediate;  -- 立即关闭数据库，如果有事务正在执行会回退事务。
shutdown normal;     -- 正常关闭数据，等待会话结束,如果有事务正在执行会一直等到事务执行完毕。
shutdown transactional; -- 正常关闭数据，不等待会话结束,如果有事务正在执行会一直等到事务执行完毕。
shutdown abort;      -- 直接关闭数据，类似于断电。




修改用户密码且解锁/锁定用户
alter user scott identified by scott account unlock/lock;




共享服务器和专用服务器
专用服务器会为每一个连接建一个服务器进程/线程。
共享服务器不会为每一个连接建一个服务器进程/线程。类似于连接池，有专门的调度程序。




SQL语句


SQL语句执行顺序： from--on--join--where--group by --with{cube|rollup}--having--select--distinct--order by
常见的sql函数
lower()/upper() 
initcap()
concat()
substr()
instr()
length()
lpad()/rpad()
trim()/ltrim()/rtrim()
replace()/translate()
round()/floor()/ceil()/trunc()
mod()
to_date()//to_char()/to_number()
months_between()/add_months()/next_day()/last_day()
nvl()/nvl2()/nullif()/coalesce()   --nullif() 适用于除零
case when then else end
descode()
avg()/sum()/min()/max()/count()   -- over()分析函数
rownum  --伪列


分析函数：
row_number() over()/RANK() over()/DENSE_RANK() over()/lag() over()/lead() over()/first_value() over()/last_value() over()


其他语句
start with connect by prior    --递归查询




其他操作语句
insert/update/delete/merge    --数据既有新增又有更新推荐merge
with 语句




数据字典
静态数据字典：类似于dba_tables、all_tables、user_tables
动态数据字典：以V$开头的动态视图，可通过 v$fixed_table 查看所有的动态视图。




数据库网络连接过程
1、客户端发起请求，确定服务器、数据库、监听端口
2、客户端一旦和服务器监听建立连接，会在客户端生成用户进程。服务器监听接收请求，如果服务名称是自己管理的服务名称，在服务器建立服务器进程。如果不是自己管理的服务名称报错。
3、监听创建服务器进程后，会将用户进程与服务器进程建立连接，自己退出与客户端的连接。
4、服务器进程根据客户端提供的用户名和密码到数据字典里进行判断。
5、如果正确，分配PGA，并生成session。否则报错。






监听
查看监听状态：lsnrctl status 
监听启动：lsnrctl start {listener1} -- 默认启动listener监听。 可以指定启动特定的监听
查看监听帮助：lsnrctl help
监听注册就是将数据库作为一个服务注册到监听程序。
静态注册
F:\app\tdn084\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
动态注册
要实现动态注册，数据库的实例至少要处于nomount状态。
数据库服务名：show parameter service_name
动态注册的监听器：show parameter local_listener
动态注册语句：
alter system set local_listener='(address_list=(address=(protocol=tcp)(host=127.0.0.1)(port=1521)))';
alter system register;


直接连接
conn scott/scott@192.168.31.131:1521/orcl




数据库驻留连接池：类似于web中的连接池的概念
查看连接池信息：select * from dba_cpool_info; --INACTIVE 表示禁用
启用连接池：exec dbms_connection_pool.start_pool();
停用连接池：exec dbms_connection_pool.stop_pool();
后台管理进程CMON， tnsnames.ora中 (SERVER = DEDICATED) 须改为 (SERVER = POOLED)


修改连接池配置：exec dbms_connection_pool.alter_param('','maxsize','100');
查看连接池信息：select * from v$cpool_stats;
                select * from v$cpool_conn_info;