oracle基本操作和查鎖

oracle基本操作
desc all_tables; --查看表結(jié)構(gòu)
select * from all_tables;--查看當(dāng)前數(shù)據(jù)庫所有的表
select table_name from user_tables;查看當(dāng)前登錄的用戶的表:
select * from dba_users;查看有哪些用戶
sqlplus system/oracle as sysdba 登錄dba用戶

--與視圖有關(guān)的表
select * from user_views
select * from dba_views

--涉及到查看表空間和數(shù)據(jù)文件的幾個表
SELECT * FROM dba_free_space --查看表空間剩余空間
select * from dba_temp_files; --查看臨時表空間
select * from dba_tablespaces --查看表空間
select * from dba_data_files --查看數(shù)據(jù)文件位置
SELECT * FROM user_source --查看存儲過程

--與索引有關(guān)的表
select * from user_tables
select * from all_indexes where table_name = 'TEST1';
select* from all_ind_columns where table_name = 'TEST1';
select * from user_ind_columns where index_name='TIME_IDX1';
select * from user_indexes where table_name='TEST1';

查看oracle中被占用的表，分析AWR報告耗時，分析瓶頸時使用
例如：想刪除一張表或者更改表時卡住了，可以用如下語句查看

(1)查看是否有SQL語句在占用這張表
查看誰在占用
select sess.INST_ID, sess.machine,
sess.program, sess.sql_id,
sess.sid, sess.serial#,
sess.PROCESS
lo.oracle_username, lo.os_user_name,
lo.locked_mode,
ao.object_name, ao.object_type,
ao.status,
from gv$locked_object lo, dba_objects ao, gv$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid
and sess.username = 'SCOTT'
--殺掉進(jìn)程 sid,serial#
alter system kill session'10,11562';

(2)查看是否有會話沒有結(jié)束
SQL> select username,sid,serial#,paddr,status from v$session where username='SCOTT';
USERNAME SID SERIAL# PADDR STATUS

SCOTT 1 281 000000008E51C510 KILLED
SCOTT 20 362 000000008E491150 INACTIVE
SCOTT 21 175 000000008E48D050 INACTIVE
SCOTT 28 169 000000008E51C510 KILLED

SQL> select PROGRAM from v$process where addr='000000008E490110';
PROGRAM
oracle@master.example.com

--殺掉進(jìn)程 sid,serial#
alter system kill session '1,281';
alter system kill session '20,362';
alter system kill session '21,175';
alter system kill session '28,169';
SQL> drop user scott cascade;
User dropped.

oracle數(shù)據(jù)庫小知識：
rebuild 和 rebuild online的區(qū)別
alter index rebuild online:實質(zhì)上是掃描表而不是掃描現(xiàn)有的索引塊來實現(xiàn)索引的重建
alter index rebuild:只掃描現(xiàn)有的索引塊來實現(xiàn)索引的重建。
rebuild index online在執(zhí)行期間不會阻塞DML操作，但在開始和結(jié)束階段，需要請求模式為4的TM鎖。因此，如果在rebuild index online開始前或結(jié)束時，有其它長時間的事物在運行，很有可能就造成大量的鎖等待。也就是說在執(zhí)行前仍會產(chǎn)生阻塞, 應(yīng)該避免排他鎖,所以需要晚上停掉應(yīng)用后進(jìn)行操作.
而rebuild index在執(zhí)行期間會阻塞DML操作, 但速度較快.
兩者重建索引時的掃描方式不同，
rebuild用的是“INDEX FAST FULL SCAN”，
rebuild online用的是“TABLE ACCESS FULL”;
即rebuild index是掃描索引塊,而rebuild index online是掃描全表的數(shù)據(jù)塊.

2. 把索引與對應(yīng)的表放在不同的表空間。
   當(dāng)讀取一個表時表與索引是同時進(jìn)行的。如果表與索引和在一個表空間里就會產(chǎn)生資源競爭，放在兩個表空間中可并行執(zhí)行。
3. 如果一個表很大，建立索引的時間很長，建立索引時可以設(shè)置為不產(chǎn)生redo信息。

Oracle默認(rèn)五塊，設(shè)置成5的整數(shù)倍。

--與視圖有關(guān)的表
select from user_views
select from dba_views

--涉及到查看表空間和數(shù)據(jù)文件的幾個表
SELECT FROM dba_free_space; --查看表空間剩余空間
select from dba_temp_files; --查看臨時表空間
select from dba_tablespaces; --查看表空間
select from dba_data_files; --查看數(shù)據(jù)文件位置
SELECT * FROM user_source; --查看存儲過程

--與索引有關(guān)的表
select from user_tables
select from all_indexes where table_name = 'TEST1';
select from all_ind_columns where table_name = 'TEST1';
select from user_ind_columns where index_name='TIME_IDX1';
select * from user_indexes where table_name='TEST1';

1.創(chuàng)建表空間
創(chuàng)建表test_data和索引空間test_idx，數(shù)據(jù)文件放在/oracle/oracle/oradata/orcl/這里，大小1G。
create tablespace test_data datafile '/oracle/oracle/oradata/orcl/test_data01.dbf' size 1024M;
create tablespace test_idx datafile '/oracle/oracle/oradata/orcl/test_idx01.dbf' size 1024M;
select * from datafile
2.創(chuàng)建用戶
創(chuàng)建用戶test1放在表空間test_data中
create user test1 identified by test1 default tablespace test_data;
DROP USER test1 CASCADE --刪除用戶
3.授權(quán)給新用戶
grant connect, resource,create session,CREATE SYNONYM ,create view,select any table TO test1;
revoke xxx on xxxtab to test1； --撤銷權(quán)限
4.登錄新用戶創(chuàng)建表指定表空間
創(chuàng)建一個表test1指定表空間 test_data
為表test1創(chuàng)建一個索引，指定索引空間 test_idx
create table test1 (id number(5), create_date varchar2 (20),charg_date varchar2 (20)) tablespace test_data;

create index time_idx1 on test1 (create_date) tablespace test_idx;
create index time_idx2 on test1 (create_date,charg_date) tablespace test_idx;
create index time_idx3 on test1 (chage_date) tablespace test_idx;

drop index TIME_IDX1; --刪除索引    

alter table test1.test1 rename column charg_date to chage_date;-- 修改字段名
alter table test1.test1 modify (chage_date nvarchar2(20)); -- 修改字段類型
comment on column T_00970001.C_009700010003 is '處罰事由';
comment on column test1.id is '創(chuàng)建id';
comment on column test1.create_date is '創(chuàng)建時間';
comment on column test1.chage_date is '修改時間';
5.創(chuàng)建一個視圖
索引和視圖都占用真實表空間，創(chuàng)建時盡量規(guī)劃
create view v_test1 as select * from test1.test1; --主要是不想每次都輸入前面的test1
commit;
DROP VIEW v_test1; --刪除視圖
6.導(dǎo)入數(shù)據(jù)

begin
for i in 1..80
loop
insert into test1.TEST1 (id,create_date,chage_date) values (i,to_char(SYSDATE-i),to_char(SYSDATE-i,'yyyymmddhh34miss'));
END LOOP;
commit; 

END;

7.測試
幾個可以用全表掃描更改為索引掃描的例子：
包含函數(shù)轉(zhuǎn)換和運算符都是走TABLE ACCESS FULL
1.select from v_test1 where to_char(sysdate,'yyyymmdd') + 7 < to_char(sysdate,'yyyymmdd');
1.select from v_test1 where chage_date < to_char(sysdate,'yyyymmddhh34miss') - 7;
1.select from v_test1 where to_char('chage_date') < '20191003073258';或者
select from v_test1 where create_date < to_date('20191003','yyyy-mm-dd');
2.select from v_test1 where create_date < '03-oct-19';或者
select from v_test1 where create_date < '03-10月-19';
--查1小時內(nèi)的數(shù)據(jù)
1.select from test1 where (sysdate - to_date(chage_date,'yyyymmddhh34miss'))24 <=1;
2.select * from test1 where chage_date >= TO_CHAR((sysdate - 1/24),'yyyymmddhh34miss');

Alter Table test1 Add name varchar2(10); --為表增加一個字段name
update test1 set name='t1' where id ='1';
update test1 set name='t2' where id ='2';
update test1 set name='t3' where id ='3';

COLUMN 可以改變列標(biāo)題
1). 改變?nèi)笔〉牧袠?biāo)題
2). 將列名NAME改為新列名EMPLOYEE NAME并將新列名放在兩行上：
3). 改變列的顯示長度：
4). 設(shè)置列標(biāo)題的對齊方式
5). 不讓一個列顯示在屏幕上
7). 顯示列值時，如果列值為NULL值，用text值代替NULL值
8). 設(shè)置一個列的回繞方式
9). 顯示列的當(dāng)前的顯示屬性值
10). 將所有列的顯示屬性設(shè)為缺省值
具體可以查看https://blog.csdn.net/xiazaixiazai2010/article/details/102622347

COLUMN
col 命令全稱column
varchar2
col name for a20;
number
col id for 9999;

col ID for A60
col CREATE_DATE for A60
col CHAGE_DATE for A60
col NAME for A60

Execution Plan

0 　　SELECT STATEMENT Optimizer=CHOOSE
1　 0 　MERGE JOIN
2　 1 　　SORT (JOIN)
3 　2 　　　NESTED LOOPS
4　 3 　　　　TABLE ACCESS (FULL) OF 'B'
5 　3 　　　　TABLE ACCESS (BY INDEX ROWID) OF 'A'
7 　1 　　SORT (JOIN)
8 　7 　　　TABLE ACCESS (FULL) OF 'C'

8.索引創(chuàng)建思路
多表關(guān)聯(lián)
優(yōu)化的思路是由小到大，即從限制性最強，返回記錄最少的連接開始，基本采用嵌套循環(huán)連接，依次完成其它表的連接，并在訪問每張表時，合理使用索引，特別是復(fù)合索引技術(shù)。

復(fù)合索引
復(fù)合索引比單字段索引效率高多了，但是復(fù)合索引比單字段索引的內(nèi)部原理復(fù)雜，復(fù)合索引有兩個重要原則需要把握：前綴性和可選性。
國內(nèi)很多IT系統(tǒng)開發(fā)人員沒有意識到應(yīng)該優(yōu)先設(shè)計復(fù)合索引，更沒有充分理解復(fù)合索引的前綴性和可選性兩個重要原則。
前綴性:
查詢中只要有復(fù)合索引條件中第一個字段為where后的查詢值，就會使用到該復(fù)合索引
可選性：
字段值多的排在前面，可選性越強,定位的記錄越少,查詢效率越高

9.監(jiān)控索引

查找這些不合理的索引：
1、根據(jù)原理去判斷
這種情況肯定存在很多復(fù)合索引，可根據(jù)前綴性和可選擇性兩大原理，去分析這張表各字段的記錄分布情況，自己做出合并、整合處理。
2、利用oracle索引監(jiān)控特性
更保險的辦法是，利用oracle9i開始提供的索引監(jiān)控特性，在某個典型業(yè)務(wù)周期開始之前，執(zhí)行索引啟用監(jiān)控功能，在典型業(yè)務(wù)周期結(jié)束以后，結(jié)束監(jiān)控，查看v$object_usage視圖，看哪個索引沒有被采用，就刪除。
索引碎片分析和整理
3、頻繁對索引字段進(jìn)行delete和update操作，會讓索引產(chǎn)生大量的碎片，從而極大的影響索引的使用效率，并造成索引i/o的增加。
1、對索引碎片分析，如果索引的碎片空間超過20％，則理解索引碎片非常嚴(yán)重，則重建索引。
2、重建索引可以采用rebuild和coalesce的方法。

摘抄：
收集索引使用的統(tǒng)計信息:
ANALYZE INDEX time_idx1 VALIDATE STRUCTURE;
查看統(tǒng)計信息:
SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100
AS wastage FROM index_stats;
當(dāng)wastage 超過20%時，需要重建索引
ALTER INDEX time_idx1 REBUILD;
整合索引（與rebuild二者選一）:
ALTER INDEX time_idx1 REBUILD;

導(dǎo)出awr報告：