常用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;
end case;
end tr_cha;
/
insert into zm_id values(2,’CINA’);
insert into zm_id(zm) values(’CHINA’);
select * from zm_id;
delete from zm_id;
——————————————————————————-
——————————————————————————-
– trigger用例
——————————————————————————-
– 表element_type的触发器
CREATE TABLE ELEMENT_TYPE (
ELEMENT_TYPE_ID NUMBER not null,
ELEMENT_TYPE_CODE VARCHAR2(6),
ELEMENT_TYPE_NAME VARCHAR2(20),
ELEMENT_TYPE_DESC VARCHAR2(100),
PARENT_TYPE_CODE NUMBER,
ELEMENT_GROUP_ID NUMBER,
CREATED_BY VARCHAR2(20),
CREATION_DATE DATE,
LAST_UPDATE_BY VARCHAR2(20),
LAST_UPDATE_DATE DATE,
START_ACTIVE_DATE DATE,
END_ACTIVE_DATE DATE,
ACTIVE_STATUS VARCHAR2(1),
CONSTRAINT PK_ELEMENT_TYPE PRIMARY KEY (ELEMENT_TYPE_ID)
);
——————————————————————————-
— 1.向表element_type插入一条记录之后，自动将当前系统时间插入到此记录的创建
– 时间(creation_date), 最后更新时间(last_update_date)
— 2.更新时，在更新之后，将当前系统时间插入到更新记录的最后更新时间
– (last_update_date)
— 3.插入或更新时，如果有效状态变为有效(0)，将当前系统时间插入到对应记录的
– 生效时间(start_active_date)；如果变为无效(1)，则将当前系统时间插入到
– 对应记录的失效时间(end_active_date)
CREATE OR REPLACE TRIGGER tr_element_type
BEFORE INSERT OR UPDATE
ON element_type
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
:new.creation_date := sysdate;
:new.last_update_date := sysdate;
IF :NEW.active_status = ‘1′ THEN — 1 means active
:new.start_active_date := SYSDATE;
ELSIF :NEW.active_status = ‘0′ THEN — 0 means in-active
:new.END_active_date := SYSDATE;
END IF;
WHEN UPDATING THEN
:new.last_update_date := sysdate;
IF :NEW.active_status = ‘1′ THEN — 1 means active
:new.start_active_date := SYSDATE;
ELSIF :NEW.active_status = ‘0′ THEN — 0 means in-active
:new.END_active_date := SYSDATE;
END IF;
END CASE;
END; –end tr_element_type
/
————————————————————-
CREATE OR REPLACE TRIGGER tr_element_type
AFTER INSERT OR DELETE
ON element_type
FOR EACH ROW
BEGIN
raise_application_error(-20001, ‘asdfsdfsadfsa’);
END; –end tr_element_type
— 测试
select * from user_triggers;
drop trigger tr_element_type;
SELECT * FROM element_type;
delete from element_type;
INSERT INTO element_type(element_type_id,start_active_date)
VALUES (element_type_sequence.NEXTVAL,SYSDATE);
INSERT INTO ELEMENT_TYPE
VALUES(element_type_sequence.NEXTVAL, ‘ct0001′,’客户类型’,'列出客户类型，如：普通客户…’,
0, 0,’Administrator’,NULL,’Administrator’,NULL,NULL,NULL,’0′);
INSERT INTO ELEMENT_TYPE
VALUES(element_type_sequence.NEXTVAL, ‘ct0001′,’客户类型’,'列出客户类型，如：普通客户…’,
0, 0,’Administrator’,NULL,’Administrator’,NULL,NULL,NULL,’1′);
update element_type set active_status = ‘0′ where element_type_code = ‘ct0001′;
——————————————————————————-
select table_name from user_tables;
– 更改用户密码
alter user hr identified by hr;
——————————————————————————-
–将用户的表转移到其它的表空间
–(1)按用户或其它方式exp导出所有的数据
EXP OWNER=user1 FILE=user1db.DMP
EXP OWNER=crm FILE=sr2_crm.DMP
–(2)如果在原实例更换表空间，先删除该用户的所有表。
select ‘drop table ‘||user_tables||’;’ from user_tables; 生成删除表的语句 也可用enterprise manager删除所有表
–(3)利用imp创建表结构脚本
IMP FULL=Y FILE=user1db.DMP INDEXFILE=user1.SQL
–此步骤不导入数据，但将user1db.DMP中user1用户的表结构、索引等信息生成为user1.SQL。
–去掉user1.SQL脚本程序中所有的注释和引号，然后将脚本中所有的表空间修改为指定转移的表空间。
–(4)调用执行user1.SQL脚本程序。在新指定的表空间创建所有的表和索引等。
–(5)导入数据。
IMP FULL=Y FILE=user1db.DMP IGNORE=Y
IMP FULL=Y INDEXES=N FILE=user1db.DMP IGNORE=Y 若出现索引导入问题，可用INDEXES=N不导入索引。
imp market/market@orawww160_55 FROMUSER=MARKETSYS INDEXES=N FILE=EXPDAT.DMP IGNORE=Y LOG=Imp1.LOG tables=(doctrack,newsinfo)
select ‘drop table ‘|| TABLE_NAME ||’;’ from user_tables;
——————————————————————————-
– 组内排序
select * from (select row_number() over(order by cust_no ) rn,t.* from t_customerinfo t)
where rn>3 and rn<=5
select * from ( select c导出
ust_name,rn from (select row_number() over(order by cust_no ) rn,t.* from t_customerinfo_1 t)
where rn>(select count(*) from t_customerinfo_1)/2-2 and rn<=(select count(*) from t_customerinfo_1))
where rownum<=5；
create table t_customerinfo_1 as select * from t_customerinfo
— 针对oracle8.1.7.0.0, 用户scott, 表emp， 进行测试
select * from emp;
select rownum from emp;
select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;
select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;
select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp order by deptno;
select deptno,ename,sal,lag(ename,2,’example’) over(partition by deptno order by ename) from emp order by deptno;
———————————————————————————
– 插入日期
–你可以先在你的sql plus中看看你的日期格式是什么样子的
select sysdate from dual;
–然后你插入的时候可以按照你的系统日期格式插入，不需要转换，要不然你就自己指定格式转换插入
select sysdate from dual;
SYSDATE
———
26-AUG-04
create table tmp(rq date);
insert into tmp(rq) values(’26-AUG-04′);
insert into tmp(rq) values(to_date(’2004-08-26′,’yyyy-mm-dd’));
———————————————————————————
select table_name from user_tables;
drop table t_a;
drop table t_b;
drop table t_c;
drop table t_d;
drop table t_e;
DROP SEQUENCE t_a_sequence;
CREATE SEQUENCE sr_call_back_sequence
INCREMENT BY 1 — 每次加几个
START WITH 100001 — 从1开始计数
NOMAXVALUE — 不设置最大值
NOCYCLE — 一直累加，不循环
NOCACHE;
create table t_a(id number,
code varchar2(20),
name varchar2(20),
c_id number,
d_num number
);
create table t_b(code varchar2(20),
name varchar2(20),
name2 varchar2(20),
name3 varchar2(20)
);
create table t_c(c_id number,
name2 varchar2(20)
);
create table t_d(d_num number,
name3 varchar2(20)
);
create table t_e(e_num number,
name varchar2(20)
);
insert into t_b values(’tb_code1′, ‘name1′, ‘aaa’, ‘xxx’);
insert into t_b values(’tb_code2′, ‘name2′, ‘bbb’, ‘yyy’);
insert into t_b values(’tb_code3′, ‘name3′, ‘ccc’, ‘zzz’);
insert into t_b values(’tb_code4′, ‘name4′, ‘ddd’, ‘www’);
insert into t_c values(11, ‘bbb’);
insert into t_c values(22, ‘ccc’);
insert into t_d values(888, ‘zzz’);
insert into t_d values(999, ‘www’);
insert into t_e values(101, ‘name1′);
insert into t_e values(102, ‘name3′);
insert into t_a(id,
code,
name,
c_id,
d_num
)
select t_a_sequence.nextval,b.code,b.name,c.c_id,d.d_num
from t_b b,t_c c,(select b1.code code,d1.d_num d_num from t_b b1,t_d d1 where b1.name3=d1.name3) d
where b.code=d.code(+) and b.name2=c.name2(+);
commit;
select t_a_sequence.nextval, b.code, c.c_id
from t_b b, t_c c
where b.name2 = c.name2(+);
select * from t_a;
delete from t_a;
commit;
select table_name from user_tables;
select * from t_a;
– 两表(多表)关联update — 被修改值由另一个表运算而来
select * from t_a;
select * from t_b;
select * from t_c;
select * from t_d;
select * from t_e;
update t_a a
set d_num = (select e.e_num from t_e e where e.name = a.name)
where exists (select 1 from t_e e where e.name = a.name);
SELECT * FROM t_a;
SELECT * FROM t_e;
/*
update ( select a.d_num d_num, e.e_num e_num
from t_a a, t_e e
where a.name = e.name
)
set d_num = e_num;
*/
UPDATE t_a a
SET a.d_num =(select e.e_num
from t_e e where e.name=a.name)
where a.rowid=(select a.rowid FROM t_e e
where e.name=a.name
)
select * from t_a;
select t_e.e_num from t_e, t_a where t_e.name = t_a.name;
rollback;
———————————————————————————————–
– oracle 查看索引
select * from user_ind_columns@sr2 ;
select * from user_ind_columns@sr2 where table_name = ‘HOTLINE_HEAD’;
select * from user_ind_columns;
———————————————————————————————–
– oracle 查看主键
select * from user_part_tables;
———————————————————————————————–
– 查看语句执行所用的时间 sql语句
set timing on
select count(*) from t;
SELECT sql_text “SQL”, executions “运行次数”, buffer_gets / decode(executions, 0, 1, executions) / 4000 “响应时间”
FROM v$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;
select object_name, owner object_owner, status, object_type, created, last_ddl_time
from sys.all_objects o
where object_type = ‘PROCEDURE’
and object_name not like ‘BIN$%’
order by decode(owner, user, 0, 1), owner, object_name;
select s.synonym_name object_name, o.object_type
from sys.all_synonyms s, sys.all_objects o
where s.owner in (’PUBLIC’, user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type
in (’TABLE’, ‘VIEW’, ‘PACKAGE’,'TYPE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SEQUENCE’)
SELECT object_name, object_type
FROM sys.all_objects
WHERE object_name NOT LIKE ‘BIN$%’
AND owner = ”;
SELECT text FROM all_source WHERE owner = ”;
———————————————————————————————–
– 绑定变量测试
select table_name from user_tables;
select * from t;
drop table t cascade constraint;
create table t(x int);
select * from t for update nowait;
create or replace procedure proc1
as
begin
for i in 1 .. 10000 loop
execute immediate
‘insert into t values(:x)’ using i;
end loop;
end;
/
create or replace procedure proc2
as
begin
for i in 1 .. 10000 loop
execute immediate
‘insert into t values (’||i||’)';
end loop;
end;
/
———————————————————————————————–
– 使用merge (oracle9i 以上才支持)
create table inventory (part_no integer,part_count integer);
insert into inventory values(1,5);
insert into inventory values(3,6);
create table shipment (part_no integer,part_count integer);
insert into shipment values(1,2);
insert into shipment values(2,2);
MERGE INTO inventory
USING shipment
ON (inventory.part_no = shipment.part_no)
WHEN MATCHED THEN
UPDATE SET part_count = part_count + shipment.part_count
WHEN NOT MATCHED THEN
INSERT VALUES (shipment.part_no,shipment.part_count);
commit;
select * from inventory;
———————————————————————————————–
– 解决ORA-00054 系统忙， 进程
–用select sid,serial#,status,username from v$session查找状态为
– INACTIVE的session,用
–alter system kill session ‘14,3534′ 查杀。
网页吧·中国站长第一门户
