oracle数据库知识笔记

1 . 分区表：

64位处理器的出现，是Partition分区技术出现的技术前提。在32位系统的时候最大的寻址空间只有2**32=4294967296=4GB这么大的寻址空间，不需要考虑考虑分区的问题。
范围分区，散列分区，列表分区和复合分区 四种。
用什么方式分区，取决于需求和具体的业务场景。本人用得最多的是按照时间范围进行分区。

在应用中，要注意Oracle的查询计划与分区之间的关联。即让查询语句的where条件能够先选定分区，然后再判断其它的条件。
比如一个表按时间范围进行了分区，每一个月一个分区，那么查询时就最好能够加上时间条件：
select …… from TableName where (CreatedDateTime Between xxxx and yyyy ) and ……
这样，oracle的查询计划会先锁定在一定的分区范围，然后再考虑其它条件的过滤。如果不能让查询先进行分区过滤，则分区就失去了意义。

还有就是规模，以前作过测试，一个分区的数据量如果少于2000W，分区基本上看不出什么优势。所以，没必要动不动就进行分区。

分区的自动维护。比如是按一个字段的时间值进行范围分区，则应该写一个过程，定期检查是否需要创建新的分区。

分区表小实例：

-- 用Oracle 11.2.0.3 版本的 按天间隔分区 + 网点子分区加以解决
-- Oracle 11.2.0.1 版本并行查询有问题，经常造成查询结果不正确。所以建议用Oracle 11.2.0.3 版本

-- 类似建表示例如下：
 CREATE TABLE BIEE.DW_ADS_IMP_DAY_WQ
(
DATE_TIME GENERATED ALWAYS AS (TO_DATE(TO_CHAR("DATE_ID"),'YYYYMMDD')) VIRTUAL VISIBLE,
DATE_ID NUMBER(8),
SITE_ID number(1),
CHANNEL_ID number(7),
SUB_CHANNEL_ID number(7),
PROVINCE_ID NUMBER(6),
CITY_ID  number(6),
TYPE_ID number(2),
SLOT_ID number(4),
CREATIVE_ID number,
IMP number,
CLICK number,
IMPOVER number
)
PARTITION BY RANGE (date_time) INTERVAL(NUMTODSINTERVAL(1,'day'))
STORE IN (adv_m01_tbs,adv_m02_tbs,adv_m03_tbs,adv_m04_tbs,adv_m05_tbs,adv_m06_tbs,adv_m07_tbs,adv_m08_tbs,adv_m09_tbs,adv_m10_tbs,adv_m11_tbs,adv_m12_tbs) 
SUBPARTITION BY LIST (SLOT_ID)
  SUBPARTITION TEMPLATE
  ( 
  SUBPARTITION p_01 VALUES(-14,1,16,31,46,61,76,91,106,121,136,151,166,181,196,211,226,241,256,271,286,301,316,331,346,361,376,391,406,421,436,451,466,481,496,511,526,541,556,571,586,601,616,631,646,661,676,691,706,721,736,751,766,781,796,811,826,841,856,871,886,901,916,931,946,961,976,991,1006),
  SUBPARTITION p_02 VALUES(-13,2,17,32,47,62,77,92,107,122,137,152,167,182,197,212,227,242,257,272,287,302,317,332,347,362,377,392,407,422,437,452,467,482,497,512,527,542,557,572,587,602,617,632,647,662,677,692,707,722,737,752,767,782,797,812,827,842,857,872,887,902,917,932,947,962,977,992,1007),
  SUBPARTITION p_03 VALUES(-12,3,18,33,48,63,78,93,108,123,138,153,168,183,198,213,228,243,258,273,288,303,318,333,348,363,378,393,408,423,438,453,468,483,498,513,528,543,558,573,588,603,618,633,648,663,678,693,708,723,738,753,768,783,798,813,828,843,858,873,888,903,918,933,948,963,978,993,1008),
  SUBPARTITION p_04 VALUES(-11,4,19,34,49,64,79,94,109,124,139,154,169,184,199,214,229,244,259,274,289,304,319,334,349,364,379,394,409,424,439,454,469,484,499,514,529,544,559,574,589,604,619,634,649,664,679,694,709,724,739,754,769,784,799,814,829,844,859,874,889,904,919,934,949,964,979,994,1009),
  SUBPARTITION p_05 VALUES(-10,5,20,35,50,65,80,95,110,125,140,155,170,185,200,215,230,245,260,275,290,305,320,335,350,365,380,395,410,425,440,455,470,485,500,515,530,545,560,575,590,605,620,635,650,665,680,695,710,725,740,755,770,785,800,815,830,845,860,875,890,905,920,935,950,965,980,995,1010),
  SUBPARTITION p_06 VALUES(-9,6,21,36,51,66,81,96,111,126,141,156,171,186,201,216,231,246,261,276,291,306,321,336,351,366,381,396,411,426,441,456,471,486,501,516,531,546,561,576,591,606,621,636,651,666,681,696,711,726,741,756,771,786,801,816,831,846,861,876,891,906,921,936,951,966,981,996,1011),
  SUBPARTITION p_07 VALUES(-8,7,22,37,52,67,82,97,112,127,142,157,172,187,202,217,232,247,262,277,292,307,322,337,352,367,382,397,412,427,442,457,472,487,502,517,532,547,562,577,592,607,622,637,652,667,682,697,712,727,742,757,772,787,802,817,832,847,862,877,892,907,922,937,952,967,982,997,1012),
  SUBPARTITION p_08 VALUES(-7,8,23,38,53,68,83,98,113,128,143,158,173,188,203,218,233,248,263,278,293,308,323,338,353,368,383,398,413,428,443,458,473,488,503,518,533,548,563,578,593,608,623,638,653,668,683,698,713,728,743,758,773,788,803,818,833,848,863,878,893,908,923,938,953,968,983,998,1013),
  SUBPARTITION p_09 VALUES(-6,9,24,39,54,69,84,99,114,129,144,159,174,189,204,219,234,249,264,279,294,309,324,339,354,369,384,399,414,429,444,459,474,489,504,519,534,549,564,579,594,609,624,639,654,669,684,699,714,729,744,759,774,789,804,819,834,849,864,879,894,909,924,939,954,969,984,999,1014),
  SUBPARTITION p_10 VALUES(-5,10,25,40,55,70,85,100,115,130,145,160,175,190,205,220,235,250,265,280,295,310,325,340,355,370,385,400,415,430,445,460,475,490,505,520,535,550,565,580,595,610,625,640,655,670,685,700,715,730,745,760,775,790,805,820,835,850,865,880,895,910,925,940,955,970,985,1000,1015),
  SUBPARTITION p_11 VALUES(-4,11,26,41,56,71,86,101,116,131,146,161,176,191,206,221,236,251,266,281,296,311,326,341,356,371,386,401,416,431,446,461,476,491,506,521,536,551,566,581,596,611,626,641,656,671,686,701,716,731,746,761,776,791,806,821,836,851,866,881,896,911,926,941,956,971,986,1001,1016),
  SUBPARTITION p_12 VALUES(-3,12,27,42,57,72,87,102,117,132,147,162,177,192,207,222,237,252,267,282,297,312,327,342,357,372,387,402,417,432,447,462,477,492,507,522,537,552,567,582,597,612,627,642,657,672,687,702,717,732,747,762,777,792,807,822,837,852,867,882,897,912,927,942,957,972,987,1002,1017),
  SUBPARTITION p_13 VALUES(-2,13,28,43,58,73,88,103,118,133,148,163,178,193,208,223,238,253,268,283,298,313,328,343,358,373,388,403,418,433,448,463,478,493,508,523,538,553,568,583,598,613,628,643,658,673,688,703,718,733,748,763,778,793,808,823,838,853,868,883,898,913,928,943,958,973,988,1003,1018),
  SUBPARTITION p_14 VALUES(-1,14,29,44,59,74,89,104,119,134,149,164,179,194,209,224,239,254,269,284,299,314,329,344,359,374,389,404,419,434,449,464,479,494,509,524,539,554,569,584,599,614,629,644,659,674,689,704,719,734,749,764,779,794,809,824,839,854,869,884,899,914,929,944,959,974,989,1004,1019),
  SUBPARTITION p_15 VALUES(0,15,30,45,60,75,90,105,120,135,150,165,180,195,210,225,240,255,270,285,300,315,330,345,360,375,390,405,420,435,450,465,480,495,510,525,540,555,570,585,600,615,630,645,660,675,690,705,720,735,750,765,780,795,810,825,840,855,870,885,900,915,930,945,960,975,990,1005,1020)
  )
(PARTITION P20120101_LS VALUES LESS THAN (TO_DATE('20120101','YYYYMMDD')) TABLESPACE adv_m10_tbs)
compress;

-- 上面date_time是虚拟字段，按这个虚拟字段分区，且按slot_id(广告位)list(当然也可以范围等)子分区。

-- Oracle 11g 的间隔分区非常好用，建议多　熟悉一下！

2. job任务

–建立一个存储过程
CREATE OR REPLACE PROCEDURE p_jobtest IS
v_hh VARCHAR2(2);
BEGIN
v_hh := to_char(SYSDATE, ‘hh24’);
IF v_hh >= ‘08’ AND v_hh <= ‘22’ THEN
–你的sql语句
NULL;
END IF;
END;
/
–提交一个JOB
DECLARE
v_jobno NUMBER;
BEGIN
dbms_job.submit(v_jobno,
‘p_jobtest;’,
trunc(SYSDATE, ‘mi’) + 1 / 1440,
‘trunc(SYSDATE, ”mi”) + 5 / 1440’);
END;
/

3.早期笔记

1 . 数据库表空间监控：dba_free_sspace
2 . 数据库进程监控：v$session，可以看到挂死的进程，然后‘alter system kill session ‘SID,serial#’’杀死。SID(1…7)且username列为空属于数据库后台进程。
3 . 控制文件备份：对数据库做了操作都会记录到控制文件，通过’alter database backup controlfile to ‘/tmp/control_back.ctl’ ’
4 . 数据库文件状态监控：dba_data_files。
select file_name,status from dba_data_files.如果status列不是available，则此文件损坏，需要恢复。
5 . 数据库定时任务监控：dba_jobs.
select job,log_user,last_date,failures from dba_jobs;
如果failures >0,说明任务失败。
6 . 创建数据字典

@?/rdbms/admin/catalog.sql; //创建数据动态字典
@?/rdbms/admin/catproc.sql; //required for or used with pl/sql
@?/sqlplus/admin/pupbld.sql; //required for sql*plus

7 . when redobuffer lgwr write redolog：

  *when transaction commit;
  *every three seconds;
  *when redologbuffer becomes one_three;
  *when there is more than a megabyte of change records in the   redolog buffer(redo buffer没有足够的空间....).
  *before the dbwn write modify blocks in the database buffer cache to the datafiles.
 ```

 8 . v$log
 ```
    status
    *inactive 已归档模式，可以正常删除
    *current 当前使用，不能删除
    *active  未归档，不能删除

9 . http://blog.csdn.net/tianlesoftware/article/details/5622268
10 .ASM相关资料 http://www.xifenfei.com/2016/07/asm-list.html
索引相关资料：http://www.cnblogs.com/langtianya/p/6652987.html
11 . x$bh buffercache状态:

   STATUS-Status of the buffer:
•   free - Not currently in use
•   xcur - Exclusive
•   scur - Shared current
•   cr  - Consistent read
•   read - Being read from disk
•   mrec - In media recovery mode
•   irec - In instance recovery mode

12 . 判断当前buffer状态：

SQL> select 
o.object_name,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state , 
count(*) blocks from x$bh b,dba_objects o 
where b.obj=o.data_object_id and o.object_name='T2'  
group by o.object_name ,state;

13 . 查询当前对象占用buffer 块：

SQL> select o.object_name,dbarfil,dbablk from x$bh a,dba_objects o where a.obj=o.data_object_id and o.object_name='T2';

由于存在cr块，所以占用buffer=blocks*2

14 . 查询当前块状态：

SQL> select class,flag,state,lru_flag from x$bh where dbarfil =1 and dbablk=86649;

15 . 查询当前所有对象占用buffercache状态：

select 
o.object_name,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state , 
count(*) blocks from x$bh b,dba_objects o 
where b.obj=o.data_object_id 
group by object_name,state   

16 . 查询当前块访问次数超过10次：

select obj,dbarfil,dbablk,tch from x$bh where tch > 10 order by 4

17 . 寻找热快对应的表对象：

SQL> select object_name,dbarfil,dbablk from x$bh a,dba_objects b where a.obj=b.object_id and dbarfil=2
and dbablk=34171;

18 .查询name列的rowid:

SQL> select dbms_rowid.rowid_block_number(rowid),name from t2;

19 .查看当前会话pga使用：

select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

20 .查看当前所有会话pga使用量：

select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem from v$process where program like '%jhdb%';

21 .查看单个进程分配最大容量：

select ksppinm "Name",ksppstvl/1024/1024||'M' "Value",ksppdesc "Desc" from x$ksppi x,x$ksppcv y where x.indx=y.indx and ksppinm='_pga_max_size';

22 .查看pga各部件大小：

select * from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers','Granule Size');

23 .查看表是否有行连接、行迁移：

select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len from user_tables where tablespace_name='ECIMSAIO_DATA'

SQL> select cpdrt,cplrba_seq||','||cplrba_bno||','||cplrba_bof "low rba" , cpodr_seq||','||cpodr_bno||'
,'||cpodr_bof "on disk rba" , cpods,cpodt,cphbt from x$kcccp;

–cpdrt 检查点队列中的脏数据块
–cpods 是on disk rba的scn
–cpodt 是on disk rba的时间戳
–cphbt 是心跳

在同一个session中，DDL自动提交未提交的DML操作。

24 .
事务槽：xid(事务ID),uba(undo block address)
每个数据块头都有1~255个事务槽，
25 .
undo段事务表：
每个undo段的第一个数据块有一个事务表（有47行，意味着一个段有47个事务使用），每个事务有一个XID;
26 .
事务流程：
（1）每个事务开始的时候会在undo segment header block 中的事务表写入一条记录，并为这个事务分配一个回滚块，把这个回滚块的地址（uba）一并写入事务表中，
（2）在要修改的数据块头的事务槽写入事务xid指向回滚段, uba地址指向undo block。
数据块中数据行头有个标记指向事务槽（相当于每个数据行头有一个事务锁，行级锁），这样可以明确的知道这个事务修改了哪几个数据行，如果这个数据块被另外一个事务修改，另外事务申请事务槽并读取数据行，发现这个数据行头部有别的事务槽信息，通过事务槽知道这个事务是否提交，因为事务信息寄存在回滚段头数据块事务表中又存在于数据块头事务槽中，直接在数据块事务槽查询 即可，无需到回滚段属无标查询，这样设计的好处是防止回滚段事务表过于繁忙，，但是这里又出现另外一个问题，两边都有事务信息，信息同步，事务更新都需要额外的操作，导致commit提交过慢，所以oracle提出快速提交功能，当发现提交事务过多，oracle只会更新回滚段头中的事务表信息，这样提交速度就很快了，所以回滚段头中的事务表信息最能体现事务状态；

（3）把要修改的数据前镜像存到undo block（回滚块）中，oracle在查询一个数据块中的数据行，发现这个数据行头有一个锁标记，通过查询数据块数据槽得这个数据行被别的事务访问未提交，这个时候会去回滚段头读取事务表确认这个事务是否提交，如果回滚段头事务表中标明这个事务未提交，那么oracle就不能直接访问这个数据行，通过uba找到回滚块读取这个数据行前镜像；
如果回滚段事务表中标明已提交，oracle就直接读取并把数据块头中的事务槽置为已提交状态。如果查询数据块的事务槽发现已提交，oracle就直接去读这个数据行并把这个数据行头的事务锁清除掉，所以这个select 也会产生少量日志，。
当执行回滚的时候，首先在回滚段找到xid，通过xid找到数据块，通过数据块中记录的uba找到回滚块，然后回滚最新的回滚块。
27 .
事务表信息查询：

select xid, xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;
xid:事务ID，包括块号、行号、覆盖次数信息，通过事务ID可以找到事务表
xidusn:回滚段编号（xid undo segment number）
xidslot：事务槽位号
xidsqn：覆盖次数
ubafil：回滚块文件编号
ubablk:回滚块
alter system dump datafile 5 block 4308;//转存储数据块信息
itl:                            //事务槽编号
xid:  0x0003.008.00002a0e       //事务ID
uba: 0x00c018df.0bc9.0f         //回滚块地址  
flg: C---                       //是否提交
lkc:  0                         //锁信息
scn: 0x0000.00e3a603            //时间点

查询表块事务槽（最小事务槽可以修改，最大事务槽不支持修改）：
select ini_trans,max_trans from dba_tables where table_name=’T2’;
对一个数据块进行操作，必须要先获得这个数据块的事务槽，然后分配相应的回滚块，大量的事务槽分配占用块pctfree空间，如果pctfree空间耗尽，别的事务就必须等待事务提交，造成事务槽争用，可以通过增加pctfree来缓解，oracle为了避免事务槽争用，针对插入操作oracle将负载均衡到多个数据块中，但是对于update，delete操作不适用，为了避免这种情况oracle使用了多种提交方式。

查询表占用块号，文件号

select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),id from t2;

查看系统undo segment信息：

select * from v$rollname;

查看系统段信息：

select segment_name,blocks,extents from dba_segments;

查看系统区信息：

select segment_name,tablespace_name,extent_id,file_id,block_id,blocks from dba_extents;

查看undo segment中extent状态：

select extent_id,bytes,status from dba_undo_extents  

free:空闲
active:活动，事务没有提交，extent区被占用
inactive：非活动，事务已提交但是extent区被占用
expired:过期的，extent时间超过undo retetion保留时间

undo 作用：
读一致性，构造cr块
回滚（rollback）
实例恢复(前滚，回滚)

select tablespace_name,retention from dba_tablespaces;

select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat;

–查询当前会话进程ID.

select spid from v$process where addr in (
select paddr from v$session where sid=(select sid from v$mystat where rownum=1)
)

alter system dump undo header ”;//转存储undo segment header信息到trc文件（位于dump目录下，文件名为当前进程$INSTANCE_NAME_PID.trc）
alter system dump datafile 5 block 4308;//转存储数据块信息

IMU及Redo Private Strands技术：
为了避免传统方式管理undo tablespace，oracle使用 imu机制管理，
针对每一个事物，oracel在shard pool中申请一个imu buffer，存放回滚数据。imu buffer写满了之后会把数据写到buffer cache中，这个步骤叫imu flush； 在rac，stream环境中 imu机制会被禁用。

一般事务在pga中产生日志，写到redolog buffer中，然后提交事务写到redolog中，通过checkpoint 检查点写到disk中；但是对于imu修改产生的日志，在redo private strands中产生
select * from v$sysstat where name like ‘%IMU%’

读一致性：
通过 scn来比较判断数据块是否被更新过，通过使用undo块来构造cr块来保持读一致性;如果 undo块空间不够，无法构造CR块，“ORA-01555 :snape too old”

字符集：数据库存储字符对应的编码，
操作系统字符集 linux系统使用locale查看 windows系统使用chcp查看
当一个表存储char,varchar2,clob,long会使用数据库字符集存储；当使用nchar,nvarchar2,nclob会使用国家字符集存储,国家字符集是对数据库字符集的一个补充。
字符集命名： ,例如：ZHS16GBK（只显示中文、英文），AL32UTF8（数据库字符集）,AF16UTF16(国家字符集)

NLS_LANG=_.
Language:显示oracle消息，校验，日期命名
territory:指定默认日期，数字，货币等格式
client character set:指定客户端使用的字符集
例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII
select * from nls_database_parameters//查看目前字符集
select * from v$nls_vaild_values//查看支持字符集

Oracle客户端字符集（NLS_LANG）应该跟客户端OS字符集一致；所有的字符集转换都在oracle端进行转换,ORACLE都是以自己的字符集为标准进行存储;Oracle通过客户端的NLS_LANG参数来判断客户端字符集。
例如：客户端OS为 WEISO88，客户端NLS_LANG=UTF8,DBServer数据库字符集 NLS_CHARACTERSET=UTF8，
在存储数据的时候，客户端通过自己的编码集WEISO88转码后传输到数据库，数据库通过客户端的参数NLS_LANG=UTF8得知客户端的编码集为UTF8,数据库服务端编码集为NLS_CHARACTERSET=UTF8，Oracle认为客户端和服务端字符集一致就不需要转换，所以直接存储，然而实际上客户端跟服务端编码集不一致，这样会出错。
客户端在读取数据的时候，Oracle服务器通过NLS_LANG=UTF8得知客户端字符集跟自己的一致，所以不需要转换直接返还给客户端显示，但是客户端实际的字符集编码为WEISO88，客户端不会做字符集转换所以就直接显示。

1、客户端
2、DBServer服务端：
sqlplus/sqlDevper OracleDBServer(nls_database_parameters：NLS_CHARACTERSET)
client OS windows(chcp) Server OS linux/unix(locale：LANG)

3、客户端字符集参数
Oracle client（NLS_LANG）

select id,name,dump(name,1016) from t2;

dump(name,1016)//以16进制把name列dump出来

Lock(锁)：因为锁所以才有并发，因为锁限制了并发。
类型：
latch（保护chain链上的buffer/chunk）
Lock (排它锁(x)，共享锁（s）)
1、行级锁：
Lock最小粒度单位-行级锁
SqlServer最小粒度单位-page锁
2、事务锁：
有了行级锁就会产生事务锁（TX）
3、表级锁：TM
（1）、行级排它锁（row exclusive）RX锁
当我们进行DML操作，会自动在被更新的表上添加RX锁，另外一个用户要删除这张表，oracle确保这个表上的所有事务均提交；RX锁之间兼容，与X互斥
可以使用lock对表加RX锁
（2）、行级共享锁(Row Shared,RS)
使用select * from table for update;RS互斥
（3）、共享锁（Shared ,S）
使用lock table in share mode添加S锁
（4）、排他锁(Exclusive ,X)
通过lock table in exclusive mode 添加X锁
（5）、共享行级排它锁(Shared Row Exclusive,SRX)
通过lock table in share row exclusive mode添加SRX锁；
lock table in [row share][row exclusive][share][share row exclusive][exclusive] mode;
锁兼容性：

//锁对象

select lmode,sid,type,id1,id2 ,request,block from v$lock where sid=(select sid from v$mystat where rownum=1) ;//

lmode:持有锁类型
request:请求
block:说明是否阻止了其他用户获取锁，>0 代表是，=0代表否。

//产生表级锁表对象

select lmode,sid,type,id1,id2,object_name from v$lock,user_objects  where sid=(select sid from v$mystat where rownum=1) and type ='TM' and id1=object_id;

//查看锁分配的undo block，覆盖次数

select lmode,sid,type,id1,id2,trunc(id1/power(2,16)) as undo_usn#,bitand(id1,to_number('ffff','xxxx'))+0 as slot#  from v$lock where sid=(select sid from v$mystat where rownum=1) and type ='TX'  ;

trunc(id1/power(2,16)) : 回滚段ID
bitand(id1,to_number(‘ffff’,’xxxx’))+0:事务槽
id2:覆盖次数

v$enqueue_lock:
该视图中只显示那些申请锁定，但是无法获得锁定的session信息。
其中的记录按照您申请锁定的时间先后顺序排列，先申请锁定的seeions排在前面，排在前面的session将先获得锁定。

v$locked_object
记录当前已经被锁定的对象的信息;
XIDUSN 表示当前事务使用回滚段编号
XIDSLOT 说明该事务在回滚段头部的事务表中对应的记录编号
XIDSQN 序列号
object_id 通过查询dba_objects查找被锁的对象。

dba_ddl_locks，列出数据库和所有未完成的DDL锁对象
NAME：
TYPE:Cursor,Table/Procedure/Type,Body,Trigger,Index,Cluster,Java Source,Java Resource,Java Data
MODE_HELD:None,null,share,exclusive
mode_requested:None,null,share,exclusive
可以查出SID，然后杀死。

v$session
SID： session编号
SERIAL# 序列号

SID,SERIAL#是v$session主键 ，可以唯一标识一个会话。
一个 DML语句首先会产生一个事务，通过查询v$transaction可以得到事务ID，回滚段号，覆盖次数，事务槽，块号，文件号等信息；

select xid, xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;


XID                  XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL
---------------- ---------- ---------- ---------- ---------- ----------
070015009A2B0000          7         21      11162       1015          3
09000000102B0000          9          0      11024        779          3

一个dml也产生一个锁信息，通过查询v$lock 可以得到锁信息和被锁对象信息

select lmode,sid,type,id1,id2 ,request,block from v$lock where sid='136'
SQL> select sid,lmode,type,id1,id2,request,block from v$lock where sid in ('136','199');


  SID      LMODE TYPE              ID1        ID2    REQUEST      BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
       136          3 TM               85406           0          0          0
       136          6 TX             458773      11162           0          1
       199          6 TX             589824      11024          0          0
       199          3 TM              85406          0          0          0
       199          0 TX              458773      11162          6          0

会话136,199持有表级锁对象一致；199事务锁请求事务X锁，136事务阻止了别的事务申请锁资源，说明136阻止了199事务申请锁资源，因为两个事务访问事务锁资源一样（458773 ，11162）。

SQL> select sid,type,request from v$enqueue_lock where sid in('136','199');

       SID TYPE          REQUEST
---------- ---------- ----------
       199 TX                  6

199事务申请事务X锁。

select a.sid as blocker_sid,a.serial#,a.username as blocker_name,b.type,b.lmode,
b.ctime as time_held,c.sid as waiter_sid,
c.request,c.ctime from 
v$lock b,v$enqueue_lock c,v$session a where a.sid=b.sid and b.id1=c.id1(+) and b.id2=c.id2 and c.type(+)='TX' and  b.type='TX' and b.block=1 ;

BLOCKER_SID    SERIAL# BLOCKER_NA TYPE        LMODE  TIME_HELD WAITER_SID    REQUEST      CTIME
----------- ---------- ---------- ------ ---------- ---------- ---------- ---------- ----------
        136        860 SYS        TX              6      10383        199          6       4222

136锁定了199。
一个dml修改多个表，至少一个TX锁，多个TM锁；
数据库并发锁和事务由dml_locks和transactions决定，查询库当前锁和事务数量和阈值v$resource_limit
12.
主键会创建索引，外键不会。
唯一约束也会产生索引。
select * from dba_data_files;//查看数据文件是否是自动扩展
select * from user_ind_columns uic;//查看索引列
drop user ECIMS_AIO CASCADE;//删除用户下到额所有对象,
select tb.owner,tb.table_name,tb.tablespace_name,tb.num_rows from dba_tables tb where tb.owner=’ECIMS_AIO’ order by 4;
select * from dba_tables where owner=’ECIMS_AIO’;
select * from dba_ind_columns where index_owner=’ECIMS_AIO’;
13 .
权限：
//分页排序
select table_name,
blocks*8192/1024/1024 mb ,
row_number() over( order by blocks*8192/1024/1024 desc )
from dba_tables where owner =’ECIMS_AIO’ order by mb desc

select * from role_sys_privs;//查询用户拥有的权限
给用户授权:
grant dba to lxg;–授予DBA权限
grant unlimited tablespace to lxg;–授予不限制的表空间
grant select any table to lxg;–授予查询任何表
grant select any dictionary to lxg;–授予 查询 任何字典
grant connect,resource,dba to user;
select * from dba_sys_privs where GRANTEE=’DEVMAIN’
select * from role_sys_privs WHERE ROLE=’DEVMAIN’
select * from role_sys_privs WHERE ROLE=’JHDBPRJ’
select * from user_role_privs
select * from user_sys_privs

select * from role_sys_privs WHERE ROLE LIKE ‘CONNECT’;
select * from role_sys_privs WHERE ROLE LIKE ‘RESOURCE’;

CONNECT角色： –是授予最终用户的典型权利，最基本的
CREATE SESSION –建立会话
RESOURCE角色： –是授予开发人员的
CREATE CLUSTER –建立聚簇
CREATE PROCEDURE –建立过程
CREATE SEQUENCE –建立序列
CREATE TABLE –建表
CREATE TRIGGER –建立触发器
CREATE TYPE –建立类型
CREATE OPERATOR –创建操作者
CREATE INDEXTYPE –创建索引类型

–另外开发人员还需要
CREATE any JOB –创建任务
CREATE any SYNONYM –创建同义词
CREATE any TABLESPACE –创建表空间
CREATE any VIEW –创建视图
CREATE any USER –创建用户
alter any trigger
alter any table
alter any sequence

create role jhdbprj;
grant resource, CREATE VIEW to jhdbprj;

DBA角色： –是授予数据库维护人员的

OLAP_DBA
SCHEDULER_ADMIN
DELETE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
WM_ADMIN_ROLE
EXP_FULL_DATABASE
SELECT_CATALOG_ROLE
JAVA_DEPLOY
GATHER_SYSTEM_STATISTICS
JAVA_ADMIN
XDBADMIN
IMP_FULL_DATABASE
XDBWEBSERVICES

普通索引迁移
alter index gbos.INDEX_T_B_FAULT_LIST_STATUS rebuild tablespace INDEXTBS;
分区索引迁移
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P61 tablespace INDEXTBS;
select * from dba_indexes
select obj.object_type from dba_objects obj where obj.owner=’ECIMS_AIO’ group by obj.object_type
select * from user_ind_columns uic where uic.table_name=’STANDA_SCHEDULE_NODE’
select * from v$session
select * from dba_users
select * from dba_tablespaces
select * from dba_data_files;

select * from dba_tables where owner=’ECIMS_AIO’ and table_name=’PROJ’;
select tbs.NAME,df.NAME from v$tablespace tbs , v$datafile df where tbs.TS#=df.TS#;
select * from v$datafile;

select * from v$tablespace;–ECIMSAIO_INDEX_DATA
select * from dba_tables where owner=’ECIMS_AIO’;
select * from dba_ind_columns where index_owner=’ECIMS_AIO’;
alter table ECIMS_AIO.PROJ
add constraint PROJ_SP_ID_FK foreign key (SP_ID)
references ECIMS_AIO.STANDARD_PROJ (SP_ID)
tablespace ECIMSAIO_INDEX_DATA;

create table app(id int,name varchar2(10));
create table acs(id int,name varchar2(10),p_id int);

alter table app add constraint app_p primary key(id);
alter table acs add constraint acs_fk foreign key (p_id) references app(id) ;

insert into app values(2,’b’)

SELECT * FROM TABLE(SPLIT(DRAW_LIST.Area_Id,’,’))

select * from app
select * from table(split(app.name,”));

alter table ECIMS_AIO.ACC_CALENDAR
add constraint PK_ACCCALENDAR_NO primary key (ACCCALENDAR_NO)
using index
tablespace ECIMSAIO_INDEX_DATA;

alter table ECIMS_AIO.PROJ
add constraint PROJ_SP_ID_FK foreign key (SP_ID)
references ECIMS_AIO.STANDARD_PROJ (SP_ID);

select ‘drop tablespace ’ ||name||’ including contents and datafiles; ’ from v$tablespace;

select tb.owner,tb.table_name,tb.tablespace_name,tb.num_rows from dba_tables tb where tb.owner=’ECIMS_AIO_DEMO’ order by 4;

14 . 数据库锁解决方法：
用dba用户执行以下语句，查出锁会话
select session_id,serial#,username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object)
Alter system kill session ‘session_id,serial#’;

查看被锁的sql语句：
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

查看锁会话的线程
select pro.spid from v$session ses,v$process pro where ses.sid in（select session_id from v$locked_object） and ses.paddr=pro.addr;
Kill -9 spid

要看是什么锁了，是ddl锁你找不到，dml锁才能用 v$locked_object找到
可以试试：
dba_dml_locks;
dba_ddl_locks;

查看锁的进程：
SELECT p.spid,p.USERNAME as proname, s.USERNAME,s.STATUS,s.OSUSER,s.MACHINE, s.program,s.sid,s.PADDR,a.OBJECT
FROM v$process p, v$session s,V$ACCESS a
WHERE p.addr=s.paddr
and a.SID=s.SID
and a.TYPE = ‘PROCEDURE’
and s.OSUSER=’Administrator’

通过wget下载oracle软件：
wget -c –http-user=username –http-password=pwd –output-document=patchname.zip “Download_URL” -o logname.log &