数据字典dict总是属于Oracle用户sys的
用户
select username from dba_users;
改口令
alter user spgroup identified by spgtest;
表空间
select * from dba_data_files;
select * from dba_tablespaces;//表空间
select tablespace_namesum(bytes) sum(blocks)
from dba_free_space group by tablespace_name;//空闲表空间
select * from dba_data_files
where tablespace_name=RBS;//表空间对应的数据文件
select * from dba_segments
where tablespace_name=INDEXS;
数据库对象
select * from dba_objects;
CLUSTERDATABASE LINKFUNCTIONINDEXLIBRARYPACKAGEPACKAGE BODY
PROCEDURESEQUENCESYNONYMTABLETRIGGERTYPEUNDEFINEDVIEW
表
select * from dba_tables;
analyze my_table compute statistics;>dba_tables后列
select extent_idbytes from dba_extents
where segment_name=CUSTOMERS and segment_type=TABLE
order by extent_id;//表使用的extent的信息segment_type=ROLLBACK查看回滚段的空间分配信息
列信息
select distinct table_name
from user_tab_columns
where column_name=SO_TYPE_ID;
索引
select * from dba_indexes;//索引包括主键索引
select * from dba_ind_columns;//索引列
select iindex_nameiuniquenesslumn_name
from user_indexes iuser_ind_columns c
where iindex_name=cindex_name
and itable_name =ACC_NBR;//联接使用
序列
select * from dba_sequences;
视图
select * from dba_views;
select * from all_views;
text 可用于查询视图生成的脚本
聚簇
select * from dba_clusters;
快照
select * from dba_snapshots;
快照分区应存在相应的表空间
同义词
select * from dba_synonyms
where table_owner=SPGROUP;
//if owner is PUBLICthen the synonyms is a public synonym
if owner is one of usersthen the synonyms is a private synonym
数据库链
select * from dba_db_links;
在spbase下建数据库链
create database link dbl_spnew
connect to spnew identified by spnew using jhhx;
insert into acc_nbr@dbl_spnew
select * from acc_nbr where nxx_nbr= and line_nbr=;
触发器
select * from dba_trigers;
存储过程函数从dba_objects查找
其文本select text from user_source where name=BOOK_SP_EXAMPLE;
建立出错select * from user_errors;
oracle总是将存储过程函数等软件放在SYSTEM表空间
约束
（）约束是和表关联的可在create table或alter table table_name add/drop/modify来建立修改删除约束
可以临时禁止约束如
alter table book_example
disable constraint book_example_;
alter table book_example
enable constraint book_example_;
（）主键和外键被称为表约束而not null和unique之类的约束被称为列约束通常将主键和外键作为单独的命名约束放在字段列表下面而列约束可放在列定义的同一行这样更具有可读性
（）列约束可从表定义看出即describe;表约束即主键和外键可从dba_constraints和dba_cons_columns 查
select * from user_constraints
where table_name=BOOK_EXAMPLE;
select ownerCONSTRAINT_NAMETABLE_NAME
from user_constraints
where constraint_type=R
order by table_name;
（）定义约束可以无名（系统自动生成约束名）和自己定义约束名（特别是主键外键）
如create table book_example
(identifier number not null);
create table book_example
(identifier number constranit book_example_ not null);
回滚段
在所有的修改结果存入磁盘前回滚段中保持恢复该事务所需的全部信息必须以数据库发生的事务来相应确定其大小（DML语句才可回滚createdroptruncate等DDL不能回滚）
回滚段数量=并发事务/但不能超过使每个回滚段大小足够处理一个完整的事务
create rollback segment r
tablespace rbs;
create rollback segment rbs_cvt
tablespace rbs
storage(initial M next k);
使回滚段在线
alter rollback segment r online;
用dba_extentsv$rollback_segs监测回滚段的大小和动态增长
回滚段的区间信息
select * from dba_extents
where segment_type=ROLLBACK and segment_name=RB;
回滚段的段信息其中bytes显示目前回滚段的字节数
select * from dba_segments
where segment_type=ROLLBACK and segment_name=RB;
为事物指定回归段
set transaction use rollback segment rbs_cvt
针对bytes可以使用回滚段回缩
alter rollback segment rbs_cvt shrink;
select bytesextentsmax_extents from dba_segments
where segment_type=ROLLBACK and segment_name=RBS_CVT;
回滚段的当前状态信息
select * from dba_rollback_segs
where segment_name=RB;
比多回滚段状态status回滚段所属实例instance_num
查优化值optimal
select nnamesoptsize
from v$rollname nv$rollstat s
where nusn=susn;
回滚段中的数据
set transaction use rollback segment rb;/*回滚段名*/
select nnameswrites
from v$rollname nv$rollstat s
where nusn=susn;
当事务处理完毕再次查询$rollstat比较writes(回滚段条目字节数)差值可确定事务的大小
查询回滚段中的事务
column rr heading RB Segment format a
column us heading Username format a
column os heading Os User format a
column te heading Terminal format a
select rname rrnvl(susernameno transaction) ussosuser ossterminal te
from v$lock lv$session sv$rollname r
where lsid=ssid(+)
and trunc(lid/)=RUSN
and ltype=TX
and llmode=
order by rname;
作业
查询作业信息
select jobbrokennext_dateintervalwhat from user_jobs;
select jobbrokennext_dateintervalwhat from dba_jobs;
查询正在运行的作业
select * from dba_jobs_running;
使用包exec dbms_jobsubmit(:v_numa;sysdatesysdate + (/(**)))加入作业间隔秒钟
exec dbms_jobsubmit(:v_numa;sysdatesysdate + (/(*)))加入作业间隔分钟使用包exec dbms_jobremove()
