分享几种Oracle的实用操作

触发器实现记录对某张表做删除修改操作的用户信息

数据库触发器是一个与表关联的存储下来的可以自动执行的PL/SQL程序。每当一个特定的数据操作语句在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列。比如我们可以使用触发器实现让用户在周末无法对数据库表进行修改。

下面的例子是建立一个信息表，用来存储对某张表做了删除操作的用户的相关信息。

#触发器监控表的修改和删除操作

#1 创建接收删除用户的表信息

SQL> create tabletab_mon_midify as (select sid,username,program,machine,'000.000.000.000' ipadd,sysdate moditime from v$session where0=1);

该表分别记录了，删除的用户、机器名、ip及删除时间等

#2 创建触发器

SQL>create or replacetrigger tab_mon_mod_tr

before delete on Md_Accountsubject

for each row

begin

insert into tab_mon_midify

selectsid,username,program,machine,sys_context('userenv','ip_address'),sysdate

from v$session where audsid =userenv('sessionid');

end;

#3 做完以上操作，一旦修改，记录会自动插入监控表里面

触发器实现禁止对某张表做修改

# 创建触发器,指定字段变化时，弹出报错不允许修改

create or replace triggerForbidUpdate_COM_LINE_B

before update on HSS_TASK_ITEM_COM_LINE_B

for each row

begin

if

updating('STATUS_CODE') or updating('LAST_UPDATE_DATE') or updating('BATCH_NUM')

then

return;

else

Raise_application_error(-20527, 'thetrigger is added by HAND, Exception: In order to ensure the bill rowsinformation is correct, HSS_TASK_ITEM_COM_LINE_B can not be updated!!! ');

end if;

end;

--error_number是范围在-20000到-20999之间的负整数

----删除触发器

　drop triggerForbidUpdate_COM_LINE_B;

当对表HSS_TASK_ITEM_COM_LINE_B里面除了STATUS_COD，LAST_UPDATE_DATE，BATCH_NUM这三个字段以外的字段进行修改时，弹出报错-20527, 'thetrigger is added by HAND, Exception: In order to ensure the bill rowsinformation is correct, HSS_TASK_ITEM_COM_LINE_B can not be updated!!!，并会让当前的修改操作失效。

ORACLE导入与导出

导出与导入分别都有三种方式：

--完全模式导出

将整个数据库内容导出，但是操作时需要有特殊权限。

命令格式：

exp 用户名/密码@实例名buffer=32000 导出路径 full=y;

例如：

exp dna/dna@orcl buffer=1024 file=d:/97back1121.dmp full=y;

--用户模式导出

将指定用户的所有对象进行导出：

exp dna/dna@orcl buffer=32000file=d:/test.dmp owner=dna

--表模式导出

将用户的所有表数据进行导出：

exp dna/dna@orcl buffer=32000file=d:/dna.dmp owner=dna tables=(dna)

--导入

imp dna/dna@orcl file= d:/dna.dmp log= d:/oracle/dna.log full=y ignore=y;

注意，在出时最好使用dba权限，不然可能会出错。

在导入时，要注意表空间是否够用，否则也有可能导致失败。

ORACLE查看锁表情况

执行以下语句，可以查看当前数据库里的锁表情况，而且还有相应的解锁语句在里面。

SELECTdob.object_name table_name,

lo.locked_mode,

lo.session_id,

vss.serial#,

vps.spid,

vss.action,

vss.osuser,

vss.process,

vps.spid db_pid,

'alter system kill session ' || '''' ||lo.session_id || ',' || vss.serial# || ''';' kill_command

FROM v$locked_object lo, dba_objects dob,v$session vss, v$process vps

WHERE lo.object_id = dob.object_id

AND lo.session_id = vss.sid

AND vss.paddr = vps.addr

ORDER BY 2, 3, dob.object_name;

新建用户，赋权，设置表空间

sqlplus/nolog

conn/as sysdba

--创建表空间

createtablespace dna_htqc datafile 'D:\oracle\dna_uat\dna_htqc.ora' size 500M;

--新建用户并设置默认表空间

createuser dna_htqc identified by "dna_htqc" default tablespace dna_htqc;

--赋权

grant create session to dna_htqc;

grant create table to dna_htqc;

grant create tablespace to dna_htqc;

grant create view to dna_htqc;

grant resource to dna_htqc;

--修改表空间自动增长属性：

alter database datafile'D:\ORACLE\HX_UAT\HX_UAT.ORA' autoextend on;

--修改表空间大小：

alterdatabase datafile'D:\ORACLE\AISINOFSSC.ORA' resize 1500m;

--修改用户的默认表空间

alteruser dna_uat default tablespace dna_uat;

--查看用户表空间使用情况，以及是否有自动扩展能力

SELECTT.TABLESPACE_NAME,D.FILE_NAME,

D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS

FROMDBA_TABLESPACES T,DBA_DATA_FILES D

WHERET.TABLESPACE_NAME =D.TABLESPACE_NAME

ORDER BY TABLESPACE_NAME,FILE_NAME;

--查看用户

selectusername,default_tablespace from user_users;

--某个用户在哪个表空间下：

selecttablespace_name,file_name,autoextensible from dba_data_files;

--该用户下所有的表：

select * from user_tables;

数据库连接相关

--当前连接数

selectcount(*) from v$process where program='Oracle.EXE(SHAD)';

selectcount(*) from v$process;

--最大连接数

selectvalue from v$parameter where name ='processes';

--修改最大连接数

altersystem set processes = 300 scope = spfile;

--最大连接

showparameter processes;

--并发连接数

selectcount(*) from v$session where status='ACTIVE';

--当前的session连接数

selectcount(*) from v$session;

--查看当前有哪些用户正在使用数据

SELECTosuser, a.username,cpu_time/executions/1000000||'s',b.sql_text,machine

fromv$session a, v$sqlarea b

wherea.sql_address =b.address order by cpu_time/executionsdesc;