oralce学习

查看SGA/PGA开辟多大空间,查看日志缓冲区分配大小

$sqlplus "/as sysdba"
SQL>show parameter sga 
...
SQL>show parameter pga
SQL>show parameter log_buffer

oracle 10g中：一般情况下，oracle设置为sga自动管理，共享池喝数据缓存区的大小分配由之前的SGA_MAX_SIZE和SGA_TARGET决定，若需要手工分配，设置SGA_TARGET=0，SHADE_POOL_SIZE和DB_CACHE_SIZE设置为非0即可。

修改sga大小(scpoe=spfile方式),scope=xxx可以不写，默认both

SQL>alter system set sga_target=2000M scope=spfile;
SQL>alter system set sga_target=2000M scope=memory;
SQL>alter system set sga_target=2000M scope=both;

注：修改log_buffer大小，必须重启才会生效，且 scope=memory或者 scope=both会报错，只能是 scope=spfile;

oracle 11g中：自动化更彻底，只需设置MEMORY_TARGET参数，连PGA都不需要设置，MEMORY_TARGET参数指定的内存会自动分配内存给SGA与PGA

ipcs -m查看共享内存的命令:

$ipcs -m

查看oracle进程：
进入Oracle用户

#su oracle
$ps -ef |grep oracle

查看实例的进程

$ps -ef |grep instance_name

查看实例名：

SQL>show parameter instance_name

查看归档进程：

$ps -ef |grep arc

查看数据库归档是否开启

SQL>archive log list;

更改数据库归档模式，需要重启数据库，先将数据库至于mount状态，再更改，再open数据库：

SQL>shutdown immediate
SQL>startup mount;
SQL>alter database archivelog;(SQL>alter databese noarchivelog)
SQL>alter database open;
SQL>archive log list;

数据库的开启与关闭：
开启分三个步骤：
nomount与参数文件(pfile/spfile)相关 oracle 9i起 引进了spfile
mount 与 控制文件相关
open 与 数据文件/日志文件相关

SQL>startup nomount
SQL>alter database mount;
SQL>alter database open;
SQL>shutdown immediate;

各种文件所在位置：

SQL>show parameter spfile;
SQL>show parameter control
SQL>select file_name from dba_data_files;
SQL>select group#,member from v$logfile;
SQL>show parameter recovery

oracle监听
如果想在远程A机器上通过网络访问本地B机器上的数据库，B机器上的数据库必须开启监听，远程的A机器只需安装数据库客户端，然后通过读取A机器上数据库客户端配置的TNSNAMES.ORA的配置文件，即可连接并访问B机器的数据库。详细可参考oracle官方文档的concept说明。
lsnrctl status 命令是查看监听状态的民工，其中Listener Parameter File 和 Listener Log File定位了监听文件LIstener.ora以及对应的日志；
lsnrctl stop命令是关闭监听的命令
lsnrctl start 命令是开启监听的命令

$lsnrctl status 
$lsnrctl stop
$lsnrctl start

清空共享池

alter system flush shared_pool;

观察数据库体系结构中的逻辑结构
块BLOCK–>区EXTENT–>段SEGMENT–>表空间TABLESPACE–>数据库DATABASE

逻辑结构之BLOCK

SQW>show paramenter db_block_size
--也可以通过观察表空间视图dba_tablespaces的block_size值获取 8192=8k
select block_size from dba_tablespaces where tablespace+name='SYSTEM';

逻辑结构之TACBLESPACES

sqlplus "/ as sysdba"
--删除表空间  including contents and datafiles 表示删除表空间的数据和对应数据文件
drop tablespace TBS_CLC including contents and datafiles;

--普通数据表空间
SQL> create tablespace TBS_CLC
blocksize 16K --指定最小块为16k 最小快默认为8k的
datafile 'D:\ORADATA\ORA10\DATAFILE\TBS_CLC_01.DBF' size 100M
autoextend on --开启自动扩展
next 64k --每次都以64k扩展 等同于 uniform 64k的功能
maxsize 5G
extent management local--10g以上可以取消
segment space management auto;--10g以上可以取消
表空间已创建
select file_name, tablespace_name, autoextentsible, bytes from dba_data_files where tablespace_name='TBS_CLC';

--临时表空间(语法有些特别，有temporary及tempfile的关键字)
CREATE TEMPORARY TABLESPACE TEMP_CLC TEMPFILE 'D:\ORADATA\ORA10\DATAFILE\TEMP_CLC.DBF' SIZE 100M;
表空间已创建
select file_name, bytes, autoextentsible from dba_temp_files where tablespace_name='TEMP_CLC';

--回滚表空间（语法有些特别，有undo的关键字）
CREATE UNDO TABLESPACE UNDOTBS_CLC DATAFILE '' SIZE 100M;
表空间已创建
select file_name, tablespace_naem, autoextentsible, bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS_CLC';

--系统表空间
select file_name, tablespace_name, autoextentsible, bytes/1024/1024 from dba_data_files where tablespace_name like 'SYS%';--sysaux作为辅助系统表空间
--系统表空间和用户表空间都属于永久保留内容的表空间
select tablespace_name, contents from dba_tablespaces where table_space_name in ('TBS_CLC','UNDOTBS_CLC','SYSTEM','SYSAUX');

逻辑结构之user

--sysdba用户登录，假设clc用户存在，先删除
sqlplus "/as sysdba"
drop user clc cascade;
--建用户，并将先前建的表空间tbs_clc和临时表空间temp_clc作为clc用户的默认使用空间。
create user clc
identified by clc000000
default tablespace tbs_clc
temporary tablespace temp_clc;
--授权，暂且将最大权限给clc用户
grant dba to clc;
--可以登录用户了
connect clc/clc000000

逻辑结构之EXTENT
oracle的最小单位是BLOCK，最小扩展单位是EXTENT

--构造t(如果没有知名表空间，就是用户clc的默认表空间)
sqlplus clc/clc000000
drop table t purge;
create table t (id int) tablespace tbs_clc;
--查询数据字典获取extent相关信息
select segment_name, extent_id,tablespace_name, byts/1024/1024, blocks from suer_extents where segment_name = 'T';
--插入数据后继续观察，发现

查看表空间剩余空间，原始表空间=剩余表空间=已使用表空间

--原始表空间-剩余表空间=已使用表空间
select sum(byts)/1024/1024 from dba_free_space where tablespace_name='TBS_CLC';--查看剩余多少  
select sum(byts)/1024/1024 from dba_data_files where tablespace_name='TBS_CLC';--原始表空间

表空间扩展：
1、增加数据文件：

SQL>alter tablespace TBS_CLC add datafile 'D:\ORADATA\ORA10\DATAFILE\TBS_CLC_02.DBF' size 100M;

2、修改为表空间自动扩展方式

SQL>alter database datafile 'D:\ORADATA\ORA10\DATAFILE\TBS_CLC_02.DBF' autoextend on;

回滚表空间可以新建多个，并且自由切换，但是数据库当前使用的回滚表空间却只能有一个(RAC数据库会有多个)

--查看数据库当前在用回滚段
SQL>show parameter undo
--查看数据库有几个回滚端
select tablespace_name, status from dba_tablespaces where contents ='UNDO';
--切换回滚表空间
SQL>alter system set undo_tablespace=undotbs2 scope=both;

临时表空间可以建多个，也可以同时被使用。

分析数据库产生多少日志：

select a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and a.name='redo size';

该脚本是利用V$statname和v$mystat两个动态性能视图来跟踪当前session操作产生的日志量，使用方法很简单：首次先执行该脚本，查看日志大小，随即执行你的更新语句，再执行该脚本返回的日志大小，两者相减就是你此次更新语句产生的日志大小。增删改都会产生日志，删除产生的undo量最多，redo也最多

sqlplus "/as sysdba"
--其中该视频需要先sqlplus /as sysdba 登录授权如下后方可执行
SQL>grant all on v_$mystat to clc;
SQL>grant all on V_$statname to clc;
CONNECT CLC/CLC000000
create or replace view v_redo_size as select a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and a.name='redo size'; 

分区表可以带条件truncate：

alter table t truncate partition '分区名';

全局临时表分为两种：一种是基于会话的全局临时表(commit preserve rows)，一种是基于事务的全局临时表(on commit delete rows)
全局临时表DML操作(增删改)都会产生日志，且会话临时日志量大于事务临时，但是都远小于普通临时产生的日志量

create  global temporary table temp_test on commit preserve rows as select * from dba_projects where 1=2;
create  global temporary table temp_test on commit delete rows as select * from dba_projects where 1=2;

全局事务临时表，在commit或者session链接退出后，临时表记录自动删除,且都不会产生日志(140可以忽略不计)；
全局会话临时表commit之后，记录还在。

不同会话独立。

分区表（排名按使用频率）
范围分区range；
列表分区list；
组合分区；
HASH分区；

1.范围分区(最常见是是按照时间分区)

SQL>drop table range_part_tab purge;
SQL>create table rang_part_tab (id number,deal_date date, area_code number, contents varchar2(4000))
partition by rang(deal_dsate)
(partition p1 values less than (to_date('2018-02-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2018-03-01','yyyy-mm-dd')),
partition p12 values less than (to_date('2019-01-01','yyyy-mm-dd')),
partition p_max values less than (maxvalue)
);
insert into range_part_tab (id,deal_date,area_code,contents)
select rownum,to_date( to_char(sysdata-365,'j')+trunc(dbms_random.value(0,365)),'j'), ceil(dbms_random.value(590,599)),--ceil向上取整,floor向下取整
rpad('*,400,'*') from dual connect by rownum<=10000;
SQL>commit;

value less than 是范围分区的特定语法，用于指明具体的范围，比如partition p2 values less than (to_date(‘2018-03-01’,‘yyyy-mm-dd’)),表示小于3月份的记录。
partition p_max values less than (maxvalue)表示超出范围的记录全部落在这个区中
分区表的分区可分别指定在不同的表空间里，如果不写即为都在同一默认表空间里。

2.列表分区

SQL>drop table list_part_tab purge;
SQL>create table list_part_tab (id number,deal_date date, area_code number, contents varchar2(4000))
partition by list(area_code)
(partition p_591 values (591),
partition p_592 values (592),
partition p_599 values (599),
partition p_other values (default)
);

插入语法同范围分区
列表分区仅需values即可确定范围，可以写为多个如 partition p_union values(591,592,594).
partition p_other values(default) 表示不在591-599范围的记录全部落在这个默认分区中。
表空间可以指定也可以不指定。

3.散列分区(HASH)

SQL>drop table hash_part_tab purge;
SQL>create table hash_part_tab(id number,deal_date date, area_code number, contents varchar2(4000))
partition by hash(deal_date)
partitions 12
;

插入语法同范围分区
散列分区与之前两种分区的明显差别在于，没有指定分区名，而仅仅指定了分区个数 如partitions 12.
散列分区的分区个数设置为偶数个。
可以指定散列分区的分区表空间，比如增加下以小段，store in (ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8,ts9,ts10,ts11,ts12)表示分别再12个不同的表空间里，不屑即在同一默认表空间。
表空间可以指定也可以不指定。

4.1组合分区-范围列表(range-list)

SQL>drop table range_list_part_tab purge;
SQL>create table range_list_part_tab(id number,deal_date date, area_code number, contents varchar2(4000))
partition by range(deal_date)
subpartition by list(area_code)
subpartition template
(subpatition p_591 values (591),
subpatition p_599 values (599)
subpatition p_other values (default)
)
(partition p1 values less than (to_date('2018-02-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2018-03-01','yyyy-mm-dd')),
partition p12 values less than (to_date('2019-01-01','yyyy-mm-dd')),
partition p_max values less than (maxvalue)
)
;

插入语法同范围分区
组合分区是由主分区和从分区组成的，如范围-列表分区，就表示主分区是范围分区，从分区是列表分区，从分区关键字为subpartition。
为了避免在每个分区中都写相同的从分区，可以考虑用模板凡是，如subpartition template关键字。
设计子分区模块，都要有subpartition关键字。
表空间可以指定也可以不指定。

分区消除：消除了除指定分区外的其他分区，使之查询的代价cost小、逻辑读小；分区越多，cost越大。

delete无法释放空间，truncate可以释放空间却不能带条件，只能全表清除数据，分区表可以truncate某一个分区而不影响其他分区：

`alter table range_part_tab truncate partition p9;`

分区交换即备份转移，瞬间完成：

alter table range_part_tab exchange partition p8 with newtable;
--注意是交换，再执行一次，则又交换回来了。

分区分割：

SQL>alter table range_part_tab split partition p_max at (to_date('2019-02-01','yyyy-mm-dd')) into (partition p201902,partition p_max);
SQL>alter table range_part_tab split partition p_max at (to_date('2019-03-01','yyyy-mm-dd')) into (partition p201903,partition p_max);

三个关键字：split\as\into
at部分说明了具体范围，小于某个指定的值
into部分说明分区被分割成两个分区，表示p_max被分割成partition p201902 和 partition p_max两部分，其中括号里的p_max可以改为新的名字，也可以保留原来的名字。

有分割就有合并：

SQL>alter table range_part_tab merge partition p201902, p_max into partition p_max;
SQL>alter table range_part_tab merge partition p201903, p_max into partition p_max;

两个关键字：merge、into
merge后买你跟着的是需要合并的两个分区名
into部分 为合并后的分区名，可以是新的，也可以沿用已存在的

分区的增删：
如果追加的分区界限比p_max还低，是不被允许的。在最后一个分区是less than(maxvalue)的情况下，是不能追加分区的，只能split分割。或者可以先删除p_max，再追加。

SQL>alter table range_part_tab drop partition p_max;
SQL>alter table range_part_tab add partition p201902 values less than (to_date('2019-02-01','yyyy-mm-dd'));
SQL>alter table range_part_tab add partition p201903 values less than (to_date('2019-03-01','yyyy-mm-dd'));

分区索引：
全局索引：即普通索引，与普通的建索引方式一样
局部索引：需要增加local关键字，相当于每个分区都分别建一个索引
全局索引好比一个大索引，局部索引好比多个小索引

--range_part_tab表的deal_date列建全局索引
SQL>create index idx_part_tab_date on range_part_tab(deal_date);
--对area_code列建局部索引
SQL>create index idx_part_tab_area on range_part_tab(area_code) local;
select segment_name,partition_name,segment_type,bytes/1024/1024,tablespace_name from user_segments where segment_name in ('IDX_PART_TAB_DATE');

索引状态查看
status为N/A表示是局部索引
表USER_IND_PARTITIONS进一步分析索引的状态

SQL>select index_name,status from user_indexs where index_name in ('IDX_PART_TAB_DATE','IDX_PART_TAB_AREA');
SQL>select index_name ,partition_name,status from user_ind_partitions where index_name in ('IDX_PART_TAB_DATE','IDX_PART_TAB_AREA');
--索引重建
SQL>alter index IDX_PART_TAB_DATE rebulid;
--oracle自己执行索引重建：update global indexes
--分区转移、合并、分割、增删，都可以用这个关键字
SQL>alter table range_part_tab truncate parttion p2 update global indexes;

假设索引高度为3，有100万条记录：
查询返回一条记录 ，需要3到4个IO；
查询返回100w条记录，需要100w乘3到4个IO，就是三四百万个IO，不如全表扫描；
如果表字段很多（上百个），占用的BLOCK就多，查询速度就慢下来了。

select count() from t ; --id 列建有索引
要使用到索引：
1.加不为空条件：select count() from t where id is not null;
2.给id列设为不允许为空：alter table t modify id not null;
3.给id列建立主键：alter table t add constraint pk1_id primary key (id);
若t表只有一个id字段，索引因为还有rowid，效率反而不如全表扫描。
select min(id),max(id) from t 用不到索引，一次执行只能从一个方向读索引数据，oracle无法用index full scan(min/max)这个算法同时在最左边和最右边读取；
select (select min(id) from t ) min,(select max(id) from t) max from dual;

distinct 会产生排序，建立索引可取消排序；
index fast full scan(可让逻辑读减少，但无法消除排序)；
index full scan (可消除排序，但逻辑读比索引快速全扫描多)；
union去除重复数据 union all未做筛选 （某些业务场景下，两个表不会重复，所以不用union 改为union all）
union all 需要排序
索引无法消除union all排序(index fast full scan)；–这是两个不同的结果集的筛选，各自的索引无法奏效

创建主从表

--删除表
drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;
--创建主表
create table t_p (id number, namevarchar2(20));
--创建主键
alter table t_p add constraint t_p_id_pk primary key (id);
--创建子表
create table t_c(id number,fid number, name varchar2(30));
--创建外键
alter table t_c add constraint fk_t_c foreing key(fid) references t_p(id);
--创建子表索引
create index ind_t_c_fid on t_c(fid);

主表子表级联删除：

先删除外键，然后根据on delete cascade关键字重建外键
alter table t_c drop constraint FK_T_C;
alter table t_c add constraint FK_T_C foreign key(fid) references t_p(id) on delete cascade;

改造主键：
若一个表某字段符合主键条件没有重复记录，但却只有一个普通索引，要改为主键该如何操作？
直接建主键即可。

drop table t cascade constraints purge;
create table t (id number, name varchar2(20));
create index idx_t_id on t(id);
--直接创建主键即可
alter table t add constraint t_id_pk primary key (id);

在等值查询情况下，组合索引的列无论哪一列在前，性能都一样；
组合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才最高效。

索引过多对DML语句的影响：
1对insert语句负面影响最大，有百害无一利，只要有索引，插入就越慢，越多越慢。
2对delete语句来说，有好有坏，在海量数据库定位删除少数记录时，这个条件列时索引列显然是必要的，但是过多列有索引还是会影响明显，因为其他列的索引也要因此被更新。在经常需要删除大量记录的时候，危害加剧。
3对update语句的负面影响最小，快速定位少量记录并更新的场景和delete类似，但是具体修改某列时却有差别，不会触及其他索引列的维护。