oracle 领航_数据字典

数据库中所有模式对象的信息
表，视图，簇，索引
多少空间
列
约束完整性
Oracle


//-----------------------
静态数据字典

动态数据字典
反映数据库实例运行的信息..

//------------------------
静态数据字典
dba_*
 dba_tables,
 dba_segments
 dba_indexes
all_*
 all_tagles
 all_segments
 all_indexes
user_*
 user_tables,
 user_segments
 user_indexes..
//--------------------------
desc dba_users;//整个数据库所有用户(全局)
desc all_users;//当前用户可以看到用户信息??? 
desc user_users;//当前用户下相关用户信息



//-------------------------------------------------
由于sys 有dba 权限看到是一样的
实验一:创建新用户查询不同的数据字典...



create user tom identified by tom;
grant create session,resource,select_catalog_role to tom;

sqlplus tom/tom@abc

select username,created from dba_users;//23行
select username,created from all_users;//23行同样
select username,created from user_users;//1行

DBA_*意为DBA拥有的或可以访问的所有的对象。
ALL_*意为某一用户拥有的或可以访问的所有的对象。
USER_*意为某一用户所拥有的所有的对象。


不是一一对应的
select * from dba_data_files;
但是没有all_data_files;user_data_files;
//---------------------------------------------
实例二;
select * from dba_tables;//1104 行
select * from all_tables;//34   行
select * from user_tables;//0行 还没有创建表..

//-------------------------------------------
动态数据字典
v$--本地(当前实例)动态视图
 v$instance;
 v$log;
 v$lock;
gv$*(RAC架构下所有实例)
  gv$instance;
  gv$log;

//---------------------------------
数据字典中有多少个视图
select * from dict;//1738行
如果你想知道哪些有表相关的视图 
SELECT table_name from dict where table_name like ‘%TAB%‘;
  
//-------------------------------
oracle 数据字典的基表
是保存数据的真正表
数据字典视图的数据来自于基表
oracle不对基表做支持和解释
select * from v$fixed_table 
where name like ‘X$%‘ and rownum<10;
  
select * from x$bh;///sys查询块头
//-----------------------------------
如何知道哪个视图使用的哪些基表
看执行记划就可以
set autotrace trace exp;
SET AUTOTRACE OFF //关闭执行计划
select * from v$lock;
--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   143 |     1 (100)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |   143 |     1 (100)| 00:00:01 |
|*  2 |   HASH JOIN              |                 |     1 |   108 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FULL      | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |
|   4 |    VIEW                  | GV$_LOCK        |    10 |   760 |     0   (0)| 00:00:01 |
|   5 |     UNION-ALL            |                 |       |       |            |          |
|*  6 |      FILTER              |                 |       |       |            |          |
|   7 |       VIEW               | GV$_LOCK1       |     2 |   152 |     0   (0)| 00:00:01 |
|   8 |        UNION-ALL         |                 |       |       |            |          |
|*  9 |         FIXED TABLE FULL | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
|* 10 |         FIXED TABLE FULL | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |
|* 11 |      FIXED TABLE FULL    | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |
|* 12 |      FIXED TABLE FULL    | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 13 |      FIXED TABLE FULL    | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 14 |      FIXED TABLE FULL    | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL    | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |
|* 16 |      FIXED TABLE FULL    | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |
|* 17 |      FIXED TABLE FULL    | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |
|* 18 |      FIXED TABLE FULL    | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |
|* 19 |   FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) |     1 |    35 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
user_tables 当前用户可以使用的表
table_name       //t1
tablespace_name  //users
num_rows;        //
read_only
compression 是否压缩的.
segment_created 什么时候创建的

//---------------------------------
查询分区表的信息
如果是分区表是没什么表空间的信息..
user_tab_partitions
select * from user_tab_partitions;

//----------------------------------------
案例 ：
创建分区表
drop table t_par purge;//从回收站清空表
create table t_part(id int,name varchar(20))
partition by range(id)
(partition p1 values less than(5),
 partition p2 values less than(10),
 partition p3 values less than(maxvalue));

insert into t_part values(1,‘tom1‘);
insert into t_part values(4,‘tom4‘);
insert into t_part values(9,‘tom9‘);
insert into t_part values(20,‘tom20‘);


SQL> select * from t_part;
select * from t_part partition(p1);
select * from t_part partition(p2);
select * from t_part partition(p3);


        ID NAME
---------- --------------------
         1 tom1
         4 tom4
         9 tom9
        20 tom20
        
        
select * from user_tables;
select * from user_tab_partitions;

TABLE_NAME                     COM PARTITION_NAME
------------------------------ --- ------------------------------
T_PART                         NO  P1
T_PART                         NO  P2
T_PART                         NO  P3

SQL> select * from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             USERS
T_PART

//分区表没有表空间的信息
//----------------------------------------------
实验2:
将不同的分区添加到不同的表空间中

select * from v$tablespace;
         9 TOM                            YES NO  YES
        10 TOM2                           YES NO  YES

       TS# NAME                           INC BIG FLA E
---------- ------------------------------ --- --- --- -
        11 JERRY                          YES NO  YES
alter table t_part move partition p2 tablespace tom;
alter table t_part move partition p1 tablespace tom2;

//--------------------------------------------------
查看用户表上创建索引信息
SELECT * FROM user_indexes;
CREATE INDEX t1_id on t1(id);

SQL> SELECT * FROM user_indexes;
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
T1_ID                          NORMAL


//---------------------------------------
创建分区索引
create index idx_t_part on t_part(id) local tablespace tom2;
select * from user_ind_partitions;


//###################################
当前对象所有用户...
select * from user_segments
当前用户所有对象大小 
select sum(t.bytes) from user_segments t;
SQL> select segment_name,(sum(t.bytes)/1024) as kb from user_segments t group by segment_name;
SEGMENT_NAME                                                                              KB
--------------------------------------------------------------------------------- ----------
T1_ID                                                                                     64
T1                                                                                        64
IDX_T_PART                                                                               192
T_PART                                                                                   192

//################################################3
数据库表空间，数据文件，多大
col file_name for a100;
select * from dba_data_files;
select * from dba_temp_files;


//$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$44
动态视图
select * from v$instance;
数据库三个状态
startup nomount; //启动实例
select status from v$instance;
SQL> select status from v$instance;
STATUS
------------------------
STARTED
 alter database mount;
 SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
数据库已更改。
SQL> select status from v$instance;
STATUS
------------
OPEN
//########################################3
select name,created from v$database;
查看数据库是否是归档
archive log list;

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            D:\dev\oracledata\test\archive
最早的联机日志序列     163
下一个存档日志序列   167
当前日志序列           167


//--------------------------------------
查看在线日志
DESC V$LOG;

desc v$logfile;//日志文件物理位置
select * from v$logfile;

//------------------------------
select * from v$session;
select machine from v$session;//从哪台机器上发起会话

/--------------------------
实验开一个窗口定位问题
sqlplus tom/tom@abc;
select count(*) from t1;
select distinct sid from v$mystat;//10

另一个窗口
select sql_id from v$session where sid = 10;
5bc0v4my7dvr5

select cpu_time,elapsed_time,sql_text from v$sql
where sql_id=‘5bc0v4my7dvr5‘;

  CPU_TIME ELAPSED_TIME SQL_TEXT
-----------------------------------------
  0      34080(毫秒)         select count(*) from t1

//--------------------------------
v$session_wait; 等待
select event,seconds_in_wait from 
v$session_wait
where sid = 10;

SQL*Net message from client          637[秒]

等待客户端发的指令..


//------------------------------------
锁;
主键表..

sqlplus tom/tom@abc
drop table t1;
create table t1(id int primary key);
insert into t1 values(1);
commit;
select  distinct sid from v$mystat;//251
update t1 set id = 2 where id = 1;



sqlplus tom/tom@abc
select * from t1;
update t1 set id = 3 where id = 1;
阻塞



select sid,type,lmode,request,block from v$lock
where type in(‘TM‘,‘TX‘);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
        10 TX          0          6          0
        10 TM          3          0          0
       251 TM          3          0          0
       251 TX          6          0          1


SELECT session_id,object_id,
       locked_mode from 
       v$locked_object
       where session_id in(251,10);


SESSION_ID  OBJECT_ID LOCKED_MODE
---------- ---------- -----------
        10      16259           3
       251      16259           3

10 251 二个session 争一个对象 

//---------------------------------
二人争什么对象呢?
select object_name from dba_objects where object_id=16259;

OBJECT_NAME
----------------
T1

争t1表

//-------------------------------------------------