Oracle PLSQL_DBA

col "owner" format a12
col "object" format a20
col "otype" format a12
col "change date" format a20
select substr(owner,1,12) "owner",
substr(object_name,1,20) "object" ,
object_type "otype",
to_char(last_ddl_time,’dd-mon-yyyy hh24:mm:ss’) "change date"
from dba_objects
where status<>’valid’
order by 1,2

1.19重新编译所有的存储过程,函数和程序包

begin
dbms_utility.compile_schema(‘gw’);
end;

1.20在v$sqlarea中找出最占资源的查询

select b.username username,a.disk_reads reads,
a.executions exec,
a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads>100000
order by a.disk_reads desc

1.21在v$sql中找出最占资源的查询

select * from (
select sql_text,rank() over(order by buffer_gets desc) as rank_bufgets,
to_char(100*ratio_to_report(buffer_gets) over(), 999.99) pct_bufgets
from v$sql)
where rank_bufgets<11

二、表空间管理

(1)新建表空间

create tablespace USER_DATA1 datafile
  'd:\oradata\user1_1.ora' size 512M,
  'd:\oradata\user1_2.ora' size 512M,
  'd:\oradata\user1_3.ora' size 512M,
  'd:\oradata\user1_4.ora' size 512M,
  'd:\oradata\user1_5.ora' size 512M,
  'd:\oradata\user1_6.ora' size 512M
  AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
  default storage (initial 128K next 2M pctincrease 0);
   --initial 为空表的最小占用的磁盘空间
   create tablespace ROLLBACK_DATA1 datafile
  'd:\oradata\roll1_1.ora' size 512M ，
  'd:\oradata\roll1_2.ora' size 512M
  AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
  default storage (initial 40M next 5M pctincrease 0);

(2)增加额外的数据文件到表空间中

ALTER TABLESPACE "XYAPP" ADD DATAFILE 'E:\ORACLE\ORADATA\TEST\test.ora' SIZE 5M

(3)修改表空间当前的数据文件

ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\TEST\XYDATA.ORA' RESIZE 52M;

(4)删除表空间及其包含的所有数据对象

drop tablespace data including contents;

(5)该数据物理文件名

alter tablespace tbsdata rename datafile 'path/data_01.dbf' to 'path2/data01.dbf';
alter database open;

(6)回滚字段管理

      (1.) 回滚段查询
       SELECT SEGMENT_NAME,OWNER,
           TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS
           FROM DBA_ROLLBACK_SEGS
     (2.) 回滚段删除
          alter rollback segment 回滚段名 offline;
          drop rollback segment 回滚段名 ;
     (3.) 回滚段建立
         create public rollback segment rb01 tablespace rollback_data1;
         -- 建立新的回滚段，每个都一样大，不同大小的回滚段没有什么意义，系统是随机选择的。
     -- 建多少个，根据并发访问用户的多少，
     -- 如果你们公司每天有 50-100 个人员使用 Oracle 系统开发的管理软件，应该 20 个以上
     (4.) 使回滚字段有效
alter rollback segment rb01 online;

(7)表空间与用户捆绑用例

/** 为避免用户在 S Y S T E M 表空间中创建对象， S Y S T E M 上的任何定额 ( 定额允许在系统中生成对象 ) 都
必须被取消。 **/
alter user xyapp quota 0 on SYSTEM;
CREATE USER ZBGL IDENTIFIED BY ZBGL default tablespace 表空间名 ;
GRANT DBA TO ZBGL;
ALTER USER ZBGL DEFAULT TABLESPACE WXGL_DATA1 TEMPORARY TABLESPACE TEMPORARY_DATA;
alter table acl_query move tablespace xyapp;

(8)查看表空间空闲数据块总和sum(bytes)与最大空闲数据块max(bytes)

select tablespace_name,sum(bytes),max(bytes) from dba_free_space group by tablespace_name;

三、数据导入导出的实例

3.1 数据导出：

1. 将数据库 TEST 完全导出 , 用户名 system 密码 manager 导出到 D:\daochu.dmp 中

exp system/manager@TEST file=d:\daochu.dmp full=y

2. 将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:\daochu.dmp wner=(system,sys)

3. 将数据库中的表inner_notify、notify_staff_relat导出

exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4. 将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

上面是常用的导出，对于压缩，既用WinZip把dmp文件可以很好的压缩。

也可以在上面命令后面加上 compress=y 来实现。

3.2数据的导入

1 将D:\daochu.dmp 中的数据导入 TEST数据库中。

imp system/manager@TEST file=d:\daochu.dmp

imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y

上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。

在后面加上 ignore=y 就可以了。

2 将d:\daochu.dmp中的表table1 导入

imp system/manager@TEST file=d:\daochu.dmp tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注重：

操作者要有足够的权限，权限不够它会提示。

数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

四、查看数据库的SQL

4.11、查看表空间的名称及大小

SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

4.12、查看表空间物理文件的名称及大小

SQL> select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

4.13、查看回滚段名称及大小

SQL> select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

4.14、查看控制文件

SQL> select name from v$controlfile;

4.15、查看日志文件

SQL> select member from v$logfile;

4.16、查看表空间的使用情况

SQL> select sum(bytes)/(1024*1024) as free_space,tablespace_name

from dba_free_space

group by tablespace_name;

SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

4.17、查看数据库库对象

SQL> select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

4.18、查看数据对象(object)内容

SQL> select dbms_metadata.getdll(‘type’,’name’[,’owner’]) from dual;

4.19、查看当前用户连接信息

SQL> select instance_name from v$instance;
用系统管理员，查看当前数据库有几个用户连接：

SQL> select username,sid,serial# from v$session;

如果要停某个连接用

SQL> alter system kill session 'sid,serial#';
如果这命令不行, 找它UNIX 的进程数

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

说明：21是某个连接的sid数
然后用 kill 命令杀此进程号。

8、查看数据库的版本

SQL> Select version FROM Product_component_version

Where SUBSTR(PRODUCT,1,6)='Oracle';

9、查看数据库的创建日期和归档方式

SQL> Select Created, Log_Mode, Log_Mode From V$Database;

五、恢复应用服务器的OWA包 (同名设置)

当你安装新的mod_plsql OWA包时，它们被放在SYS数据库用户模式下，这会为应用服务器（Oracle Application Server）使用OAS PL/SQL cartridge组件带来问题，如果你遇到这些问题并且想继续使用OAS的PL/SQL cartridge组件，就必须重新建立一些同义词，使得这些同义词参照到OWA包。在源数据库上建立同义词的步骤如下：
１．SYS帐户在SQL*Plus中连接到数据库
２．在SQL*Plus中运行下列命令，这将删除所有在Oracle HTTP Server安装进程中建立的OWA公共同义词。
　　drop public synonym OWA_CUSTOM;
　　drop public synonym OWA_GLOBAL;
　　drop public synonym OWA;
　　drop public synonym HTF;
　　drop public synonym HTP;
　　drop public synonym OWA_COOKIE;
　　drop public synonym OWA_IMAGE;
　　drop public synonym OWA_OPT_LOCK;
　　drop public synonym OWA_PATTERN;
　　drop public synonym OWA_SEC;
　　drop public synonym OWA_TEXT;
　　drop public synonym OWA_UTIL;
　　drop public synonym OWA_INIT;
　　drop public synonym OWA_CACHE;
　　drop public synonym WPG_DOCLOAD;
　　
３．连接到OWA包的安装模式oas_public，确保该用户具有CREATE PUBLIC SYNONYM权限，如果没有，则须手动为此用户授此权限。
　　
4．运行下列命令。这将重新建立OWA公共同义词使其参照到Oracle Application Server 的OWA包。
　　create public synonym OWA_CUSTOM for OWA_CUSTOM;
　　create public synonym OWA_GLOBAL for OWA_CUSTOM;
　　create public synonym OWA for OWA;
　　create public synonym HTF for HTF;
　　create public synonym HTP for HTP;
　　create public synonym OWA_COOKIE for OWA_COOKIE;
　　create public synonym OWA_IMAGE for OWA_IMAGE;
　　create public synonym OWA_OPT_LOCK for OWA_OPT_LOCK;
　　create public synonym OWA_PATTERN for OWA_PATTERN;
　　create public synonym OWA_SEC for OWA_SEC;
　　create public synonym OWA_TEXT for OWA_TEXT;
　　create public synonym OWA_UTIL for OWA_UTIL;
　　create public synonym OWA_INIT for OWA_CUSTOM;
　　create public synonym OWA_CACHE for OWA_CACHE;
　　create public synonym WPG_DOCLOAD for WPG_DOCLOAD;

查看同义词的名称
SQL>select * from user_synonyms;

六、MATERIALIZED VIEW

6.1实体化视图概述

Oracle的实体化视图提供了强大的功能，可以用在不同的环境中。在不同的环境中，实体化视图的作用也不相同。数据仓库中的实体化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。在数据仓库中，还经常使用查询重写（ query rewrite）机制，这样不需要修改原有的查询语句，Oracle会自动选择合适的实体化视图进行查询，完全对应用透明。实体化视图和表一样可以直接进行查询。实体化视图可以基于分区表，实体化视图本身也可以分区。除了在数据仓库中使用，实体化视图还用于复制、移动计算等方面。实体化视图有很多方面和索引很相似：使用实体化视图的目的是为了提高查询性能；实体化视图对应用透明，增加和删除实体化视图不会影响应用程序中SQL语句的正确性和有效性；实体化视图需要占用存储空间；当基表发生变化时，实体化视图也应当刷新。

6.2创建实体化视图主要选项说明

创建实体化视图时可以指定多种选项，下面对几种主要的选项进行简单说明：

名称	ON PREBUILD TABLE
描述	将已经存在的表注册为实体化视图。同时还必须提供描述创建该表的查询的 SELECT 子句。可能无法始终保证查询的精度与表的精度匹配。为了克服此问题，应该在规范中包含 WITH REDUCED PRECISION 子句。

名称	Build Clause	创建方式
描述	包括 BUILD IMMEDIATE和BUILD DEFERRED两种
取值	BUILD IMMEDIATE	在创建实体化视图的时候就生成数据
取值	BUILD DEFERRED	在创建时不生成数据，以后根据需要在生成数据
默认	BUILD IMMEDIATE

名称	Refresh	刷新子句
描述	当基表发生了 DML操作后，实体化视图何时采用哪种方式和基表进行同步
语法	[refresh [fast \| complete \| force] [on demand \| commit] [start with date] [next date] [with {primary key \| rowid}] ]
取值	FAST	采用增量刷新，只刷新自上次刷新以后进行的修改
	COMPLETE	对整个实体化视图进行完全的刷新
	FORCE(默认 )	Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用 Fast方式，否则采用Complete的方式，Force选项是默认选项

	ON DEMAND(默认 )	实体化视图在用户需要的时候进行刷新，可以手工通过 DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新
	ON COMMIT	实体化视图在对基表的 DML操作提交的同时进行刷新

	START WITH	第一次刷新时间

	NEXT	刷新时间间隔

	WITH PRIMARY KEY(默认 )	生成主键实体化视图 ,也就是说实体化视图是基于表的主键，而不是ROWID(对应于ROWID子句)。为了生成PRIMARY KEY子句，应该在表上定义主键，否则应该用基于ROWID的实体化视图。主键实体化视图允许识别实体化视图表而不影响实体化视图增量刷新的可用性
	WITH ROWID	只有一个单一的主表，不能包括下面任何一项 : ●Distinct ●聚合函数 ●Group by ●子查询 ●连接 ●SET操作

名称	Query Rewrite	查询重写
描述	包括 ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的实体化视图是否支持查询重写。查询重写是指当对实体化视图的基表进行查询时，Oracle会自动判断能否通过查询实体化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的实体化视图中读取数据
取值	ENABLE QUERY REWRITE	支持查询重写
取值	DISABLE QUERY REWRITE	不支持查询重写
默认	DISABLE QUERY REWRITE

6.3创建实体化视图日志主要选项说明

如果需要进行快速刷新，则需要建立实体化视图日志。实体化视图日志根据不同实体化视图的快速刷新的需要，可以建立为 ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

名称	WITH Clause
描述
取值	OBJECT ID	如果是对象实体化视图 (object materialized view)，则只能采用该方式
	PRIMARY KEY
	ROWID
	SEQUENCE
默认

6.4实体化视图

创建实体化视图时应先创建存储的日志空间

create materialized view log on table1
tablespace ts_data --日志保存在特定的表空间
with rowid;

然后创建实体化视图

create materialized view mv_table1
on prebuild table --将实体化视图建立在一个已经存在的表上
tablespace ts_data --保存表空间
build deferred --延迟刷新不立即刷新
refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新
on demand --按照指定方式刷新
as select * from table1;

删除实体化视图日志

drop materialized view log on table1;

删除实体化视图

drop materialized view mv_table1;

手工强行解除注册。

SQL>exec DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (snapshot_id);

查询物化视图刷新时间

SELECT MM.mview_name,MM.last_refresh_date FROM DBA_MVIEWS MM

不能删除物化视图?

ALTER MATERIALIZED VIEW user_order_mavi
DISABLE QUERY REWRITE
REFRESH ON DEMAND; Error! No table of contents entries found.相关的信息可以看：

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('v_test);
select CAPABILITY_NAME, POSSIBLE,MSGTXT from MV_CAPABILITIES_TABLE;

举例2

1 ）实体化视图所依据的所有表必须注册。如：

create materialized view log on department

with primary key ,

rowid (dept_name)

including new values ;

create materialized view log on newhire

with primary key ,

rowid(dept_id, salary)

including new values ;

2 ）创建实体化视图本身。如：

create materialized view dept_salary_mvw

refresh fast on commit

enable query rewrite

as select d.dept_name , sum(n.salary) as num_salary

from department d , newhire n

where d.dept_id = n.dept_id

group by d.dept_name ;

6.5 视图的刷新

SQL> exec dbms_mview.refresh('mv_gswx');

SQL>select v.MVIEW_NAME,v.REFRESH_METHOD, v.LAST_REFRESH_TYPE from user_mviews v;

6.6 mv的数目与属性查询

SQL>select t.log_owner,t.master,t.log_table,t.current_snapshots,

r.owner,r.name,r.snapshot_site

from dba_snapshot_logs t,dba_registered_snapshots r

where t.snapshot_id= r.snapshot_id(+);

6.7 mv的log查询：MLOG$

Select count(1) from log_table ;

七、Oracle几点精髓

7.1、用户

查看当前用户的缺省表空间

SQL>select username,default_tablespace from user_users;

查看当前用户的角色

SQL>select * from user_role_privs;

查看当前用户的系统权限和表级权限

SQL>select * from user_sys_privs;

SQL>select * from user_tab_privs;

SHP81:

SQL> select * from dba_role_privs where grantee in ('PCOTBG1','OPS$PCOTRPR1') order by grantee

SHPU89:

SQL> select * from dba_role_privs where grantee in ('RCOTRPR1','RCOTBG1') order by GRANTEE;

7.2、表

w 删除表中一个字段:

alter table bill_monthfee_zero set unused column date_type;

w 添加一个字段:

alter table bill_monthfee_zero add date_type number(1);

w 查看用户下所有的表

SQL>select * from user_tables; //显示用户表

select * from user_views;//显示用户视图

select * from tab;显示表和视图

w 查看表的结构

SQL>describe tablename;

w 查看名称包含log字符的表

SQL>select object_name,object_id from user_objects

where instr(object_name,'LOG')>0;

w 查看某表的创建时间

SQL>select object_name,created from user_objects where object_name=upper('&table_name');

w 查看某表的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments

where segment_name=upper('&table_name');

w 查看放在ORACLE的内存区里的表

SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

w SELECT FOR UPDATE

select for update 语句用于锁定行，阻止其他用户在该行上修改数据。当该行被锁定后其他用户可以用 SELECT 语句查询该行的数据，但不能修改或锁定该行。

w 表字段的信息查询sql

SQL>select i.index_name, i.uniqueness, c.column_name, c.COLUMN_POSITION

from dba_indexes i, dba_ind_columns c

where i.index_name = c.index_name

and i.table_name = 'COTS_TAB_ACCT_MVMT'

w 查看某表的约束条件

SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
       from user_constraints where table_name = upper('&table_name');

SQL>select c.constraint_name,c.constraint_type,cc.column_name
       from user_constraints c,user_cons_columns cc
       where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
       and c.owner = cc.owner and c.constraint_name = cc.constraint_name
       order by cc.position;

w 锁定表

LOCK语句常常用于锁定整个表。当表被锁定后，大多数DML语言不能在该表上使用。LOCK语法如下：

LOCK schema table IN lock_mode

其中lock_mode有两个选项：

　　　 share 共享方式; exclusive 唯一方式

例：

LOCK TABLE intentory IN EXCLUSIVE MODE

7.3、索引

（1）95/5规则

如果查询的结果返回的行数少于表中所有行的5%，索引是检索数据的最快方法。

（2）位图索引

如果数据集中不同的行之间只有很小的差异，适合使用位图索引。如：