oracle基本操作和查锁

发布时间：2020-06-24 06:28:24 作者：qq1282886418
来源：网络阅读：265

oracle基本操作
desc all_tables; --查看表结构
select * from all_tables;--查看当前数据库所有的表
select table_name from user_tables;查看当前登录的用户的表:
select * from dba_users;查看有哪些用户
sqlplus system/oracle as sysdba 登录dba用户

--与视图有关的表
select * from user_views
select * from dba_views

--涉及到查看表空间和数据文件的几个表
SELECT * FROM dba_free_space --查看表空间剩余空间
select * from dba_temp_files; --查看临时表空间
select * from dba_tablespaces --查看表空间
select * from dba_data_files --查看数据文件位置
SELECT * FROM user_source --查看存储过程

--与索引有关的表
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'
--杀掉进程 sid,serial#
alter system kill session'10,11562';

(2)查看是否有会话没有结束
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

--杀掉进程 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数据库小知识：
rebuild 和 rebuild online的区别
alter index rebuild online:实质上是扫描表而不是扫描现有的索引块来实现索引的重建
alter index rebuild:只扫描现有的索引块来实现索引的重建。
rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等待。也就是说在执行前仍会产生阻塞, 应该避免排他锁,所以需要晚上停掉应用后进行操作.
而rebuild index在执行期间会阻塞DML操作, 但速度较快.
两者重建索引时的扫描方式不同，
rebuild用的是“INDEX FAST FULL SCAN”，
rebuild online用的是“TABLE ACCESS FULL”;
即rebuild index是扫描索引块,而rebuild index online是扫描全表的数据块.

把索引与对应的表放在不同的表空间。
当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表空间中可并行执行。
如果一个表很大，建立索引的时间很长，建立索引时可以设置为不产生redo信息。

Oracle默认五块，设置成5的整数倍。

--与视图有关的表
select from user_views
select from dba_views

--涉及到查看表空间和数据文件的几个表
SELECT FROM dba_free_space; --查看表空间剩余空间
select from dba_temp_files; --查看临时表空间
select from dba_tablespaces; --查看表空间
select from dba_data_files; --查看数据文件位置
SELECT * FROM user_source; --查看存储过程

--与索引有关的表
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.创建表空间
创建表test_data和索引空间test_idx，数据文件放在/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.创建用户
创建用户test1放在表空间test_data中
create user test1 identified by test1 default tablespace test_data;
DROP USER test1 CASCADE --删除用户
3.授权给新用户
grant connect, resource,create session,CREATE SYNONYM ,create view,select any table TO test1;
revoke xxx on xxxtab to test1； --撤销权限
4.登录新用户创建表指定表空间
创建一个表test1指定表空间 test_data
为表test1创建一个索引，指定索引空间 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 '创建id';
comment on column test1.create_date is '创建时间';
comment on column test1.chage_date is '修改时间';
5.创建一个视图
索引和视图都占用真实表空间，创建时尽量规划
create view v_test1 as select * from test1.test1; --主要是不想每次都输入前面的test1
commit;
DROP VIEW v_test1; --删除视图
6.导入数据

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.测试
几个可以用全表扫描更改为索引扫描的例子：
包含函数转换和运算符都是走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小时内的数据
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 可以改变列标题
1). 改变缺省的列标题
2). 将列名NAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
3). 改变列的显示长度：
4). 设置列标题的对齐方式
5). 不让一个列显示在屏幕上
7). 显示列值时，如果列值为NULL值，用text值代替NULL值
8). 设置一个列的回绕方式
9). 显示列的当前的显示属性值
10). 将所有列的显示属性设为缺省值
具体可以查看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.索引创建思路
多表关联
优化的思路是由小到大，即从限制性最强，返回记录最少的连接开始，基本采用嵌套循环连接，依次完成其它表的连接，并在访问每张表时，合理使用索引，特别是复合索引技术。

复合索引
复合索引比单字段索引效率高多了，但是复合索引比单字段索引的内部原理复杂，复合索引有两个重要原则需要把握：前缀性和可选性。
国内很多IT系统开发人员没有意识到应该优先设计复合索引，更没有充分理解复合索引的前缀性和可选性两个重要原则。
前缀性:
查询中只要有复合索引条件中第一个字段为where后的查询值，就会使用到该复合索引
可选性：
字段值多的排在前面，可选性越强,定位的记录越少,查询效率越高

9.监控索引

查找这些不合理的索引：
1、根据原理去判断
这种情况肯定存在很多复合索引，可根据前缀性和可选择性两大原理，去分析这张表各字段的记录分布情况，自己做出合并、整合处理。
2、利用oracle索引监控特性
更保险的办法是，利用oracle9i开始提供的索引监控特性，在某个典型业务周期开始之前，执行索引启用监控功能，在典型业务周期结束以后，结束监控，查看v$object_usage视图，看哪个索引没有被采用，就删除。
索引碎片分析和整理
3、频繁对索引字段进行delete和update操作，会让索引产生大量的碎片，从而极大的影响索引的使用效率，并造成索引i/o的增加。
1、对索引碎片分析，如果索引的碎片空间超过20％，则理解索引碎片非常严重，则重建索引。
2、重建索引可以采用rebuild和coalesce的方法。

摘抄：
收集索引使用的统计信息:
ANALYZE INDEX time_idx1 VALIDATE STRUCTURE;
查看统计信息:
SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100
AS wastage FROM index_stats;
当wastage 超过20%时，需要重建索引
ALTER INDEX time_idx1 REBUILD;
整合索引（与rebuild二者选一）:
ALTER INDEX time_idx1 REBUILD;

导出awr报告：

oracle基本操作和查锁

相关阅读