查询数据源
select * from user_source t where lower(t.text) like '% %';
锁表
select session_id from v$locked_object; --查询被锁的会话ID
SELECT sid, serial#, username, osuser FROM v$session where sid = 9;--查询上面会话的详细信息:
ALTER SYSTEM KILL SESSION '9,99'; --将上面锁定的会话关闭:查询结果:serial#------99
--查询被锁的pkg
SELECT 'alter system kill session ' || '''' || sid || ',' || serial# || '''immediate;', a.*
FROM dba_ddl_locks a, v$session ss
WHERE a.name LIKE '%包名%'
AND a.session_id = ss.sid;
--查询被锁的表
select b.owner TABLEOWNER,
b.object_name TABLENAME,
c.OSUSER LOCKBY,
c.USERNAME LOGINID,
c.sid SID,
c.SERIAL# SERIAL,
'alter system kill session ' || '''' || sid || ',' || serial# || '''immediate;'
from v$locked_object a, dba_objects b, v$session c
where b.object_id = a.object_id
AND a.SESSION_ID = c.sid;
备份
create table table_temp as select * from table;
数据恢复
select * from A as of timestamp sysdate-10/1440;//sysdate-10/1440表示距离现在10分钟之前,1440这个数字表示一天有1440分钟
select * from A as of timestamp to_timestamp('1970-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss');
Insert into tab_temp select * from A as of timestamp sysdate-10/1440;
alert table A rename to A_bak;
alert table tab_temp rename to A;
查询用户下的表
select * from user_tables;
select * from user_tables@SLDEV_DBLINK;
SELECT O.object_name, C.comments
FROM USER_OBJECTS O, USER_TAB_COMMENTS C
WHERE O.OBJECT_TYPE IN ('TABLE', 'VIEW')
AND O.OBJECT_NAME = c.table_name;
####varchar字段转clob
alter table 表名 add 新字段 clob;
update 表名 set 新字段 = 原字段;
alter table 表名 drop column 原字段;
alter table 表名 rename column 新字段 to 原字段;
修改表字段长度
alter table 表名 modify name varchar2(60);
alter table 表名 modify (name varchar(60),name1 varchar2(60));
left join
select * from A left join B on A.id = B.aid and B.aid = 1;
select * from A left join B on A.id = B.aid where B.aid = 1;
- left join on后面不管跟什么条件,都显示出A表中所有的记录,并关联显示B中对应A表中id为1的记录或者B表中id为1的记录。
- where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
- inner join没这个特殊性,则条件放在on中和where中,返回的结果集是相同的,where本身就属于隐式内连接。