常用sql语句
– search all users in oracle db
select * from dba_users;
select * from dba_users where account_status = ‘OPEN’;
— search username and password in oracle DB
select username, password from dba_users;
— 查找某用户的所有表
select count(table_name) from all_tables where owner = ‘CRM’;
select table_name from user_tables;
— 查找用户定义的sequence;
select * from user_sequences;
— 查看用户的对象
select * from user_objects;
select * from user_objects where object_type = ‘PROCEDURE’;
— 查看用户的环境语言，更改HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/NLS_LANG
select userenv(’language’) from dual;
— 授予其他用户某个表的权限
grant select on sean.sr_area to public;
grant select on sean.sr_contacts_t to public;
grant select on sean.sr_contacts to public;
— delete all tables of the current user;
set feedback off;
set pagesize 0;
set heading off;
set verify off;
set linesize 200;
set trimspool on;
spool d:\del_all_tables.sql
select ‘drop table ‘ || table_name || ‘ cascade constraints; ‘
from user_tables;
spool off;
set feedback on;
set pagesize 9999;
set heading on;
set verify on;
– search current user’s information
select * from dba_ustats;
– search all views belong to current user
select * from dba_varrays;
– search all views in oracl DB
select * from dba_views;
– 看一下 SGA 各个组成部分的情况
select * from v$sga;
– UGA主要包含以下部分内存设置
show parameters area_size
– export user’s all tables—————————————————-
— run the following code uder sqlplus
set feedback off;
set pagesize 0;
set heading off;
set verify off;
set linesize 200;
set trimspool on;
spool c:\sr2_bk.bat;
select ‘exp crm/crm20060926@sr3 TABLES=’||table_name||’ FILE=’||table_name||’.dmp TRIGGERS=N’ from user_tables;
spool off;
set feedback on;
set pagesize 9999;
set heading on;
set verify on;
exit
— now, you can find c:\sr2_bk.sql, delete the first line and the last
— line of this file. Run this file
——————————————————————————-
– 创建数据库链接dblink
DROP PUBLIC DATABASE LINK sr3;
CREATE DATABASE LINK sr3110
CONNECT TO durjaya IDENTIFIED BY durjaya
USING ’sr3110′;
SELECT * FROM sr_maintenance@sr3;
drop public database link ei;
create public database link ei
connect to hand2 identified by s123456
using ‘prod1′;
select * from all_db_links;
select * from hotline_head@sr2;
DROP PUBLIC DATABASE LINK sr;
CREATE PUBLIC DATABASE LINK sr
CONNECT TO sean IDENTIFIED BY sean
USING ’sr’;
select * from t_b@sr;
– create a dba user
CREATE USER maggie
IDENTIFIED BY maggie
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp1
QUOTA 1024k ON users;
GRANT connect,resource,dba TO maggie WITH ADMIN OPTION;
– search parts results of one table.
select * from tablea@sr2 where rownum <= 3;
– 将远端sr2中用户crm的全部表导入到本地，并取每个表的头10条记录
set feedback off;
set pagesize 0;
set heading off;
set verify off;
set linesize 200;
set trimspool on;
spool d:\load_from_sr2.sql; — changed for test
SELECT ‘create table ‘ || table_name || ‘ as select * from crm.’ || table_name || ‘@sr2 where rownum <= 3000; ‘
FROM all_tables@sr2
WHERE owner = ‘CRM’;
spool off;
set feedback on;
set pagesize 9999;
set heading on;
set verify on;
/* 针对sr2， 不成功的导入包括：
create table CRM.sys_user_info as select * from crm.CRM.sys_user_info@sr2 where rownum <= 3000
create table MICROSOFTDTPROPERTIES as select * from crm.MICROSOFTDTPROPERTIES@sr2 where rownum <= 3000
create table TOAD_PLAN_TABLE as select * from crm.TOAD_PLAN_TABLE@sr2 where rownum <= 3000
*/
select * from sys_user_info@sr2;
select * from MICROSOFTDTPROPERTIES@sr2;
select * from TOAD_PLAN_TABLE@sr2;
— 得出的结果应该类似：
create table ELEM_TYPE as select * from sean.ELEM_TYPE@s_link where rownum < 3;
create table ELEM_ITEM as select * from sean.ELEM_ITEM@s_link where rownum < 3;
create table TABLEA as select * from sean.TABLEA@s_link where rownum < 3;
create table DWORKSHEET as select * from sean.DWORKSHEET@s_link where rownum < 3;
create table TABLEB as select * from sean.TABLEB@s_link where rownum < 3;
create table TEST12 as select * from sean.TEST12@s_link where rownum < 3;
create table TEST11 as select * from sean.TEST11@s_link where rownum < 3;
select table_name from user_tables;
select * from tablea;
– trigger 示例
——————————————————————————-
– 对修改表的时间、人物进行日志记录。
– 1、 建立试验表
create table employees_copy as select *from hr.employees;
select * from employees_copy;
– 2、 建立日志表
create table employees_log(who varchar2(30),when date);
– 3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。
create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(
Who,when)
Values( user, sysdate);
End;
/
commit;
–4、 测试
update employees_copy set salary= salary*1.1;
select *from employees_log;
–5、 确定是哪个语句起作用？
–即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？
–可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词，作判断：
begin
if inserting then
—–
elsif updating then
—–
elsif deleting then
——
end if;
end;
if updating(’COL1′) or updating(’COL2′) then
——
end if;
– [试验]
– 1、 修改日志表
alter table employees_log add (action varchar2(20));
– 2、 修改触发器，以便记录语句类型。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log(
Who,action,when)
Values( user, l_action,sysdate);
End;
/
– 3、 测试
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
values(12345,’Chen’,'Donny@hotmail’,sysdate,12);
select * from employees_log
– ORA-04098: 触发器 ‘DDL_DENY’ 无效且未通过重新验证 解决方法
– 1、首先查看用户的权限是否正确：
select owner, object_name, object_type, status from dba_objects where object_name = ‘TR_ELEMENT_TYPE’;
ALTER TRIGGER tr_element_type DISABLE;
——————————————————————————-
– 利用触发器修改本表
– 表结构
create table zm_id (zm_id number(2), zm varchar2(8));
–触发器： 如果ZM字段插入或修改的值为“中国” 则ZM-ID的值为1
create or replace trigger tr_cha
before insert or update
on zm_id
for each row
begin
case
when inserting then
if :new.zm=’CHINA’ then
:new.zm_id := 1;
end if;
when updating then
if :new.zm=’American’ then
:new.zm_id := 11;
end if;
- 最新评论