oracle-pl/sql -常用sql （持续更新中）

锁表问题

--dba权限下执行
select b.username,b.sid,b.serial#,b.logon_time 
from v$locked_object a,v$session b 
where a.session_id = b.sid order by b.logon_time; 

SELECT sid, serial#, username, osuser FROM v$session order by username;
--停止锁住的表
alter system kill session 'sid值,serial#值';   

如果出现报错：

此时查看v$session视图，会话还存在，只是状态改为 killed，并未被真正kill掉。

---

使用：

SELECT l.session_id sid,
s.serial#,
l.locked_mode 锁模式,
l.oracle_username 登录用户,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id
AND l.session_id = s.sid ORDER BY sid, s.serial#;

从操作系统层杀掉该进程

select s.sid, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal
from v$session s, v$process p
 where s.paddr = p.addr
 and s.sid = 42       //42为sid的值

查出spid为104011
登录到操作系统Oracle用户，查找对应的进程

# su - oracle
$ ps -ef |grep 104011  //104011为spid的值

在Linux上，用root身份执行命令：

kill -9 104011 //104011为spid的值

如果是本机上windows:

orakill system 104011

参考博客：夜光小兔纸

获取某用户下所有的表名/视图、所属用户、表/视图备注

--获取某用户下所有的表名/视图、所属用户、表/视图备注  (普通用户权限即可)
select   *   from   all_tab_comments   where owner=upper('用户名') ;

获取某用户 某表下的:字段名、字段注释、字段类型

-- 获取某用户 某表下的:字段名、字段注释、字段类型  (普通用户权限即可)
select a.column_name,a.comments,b.data_type from
(select   column_name,comments   from   all_col_comments   where   table_name=upper('表名')  AND owner=upper('用户名') ) a,
(select column_name,data_type||'('||DATA_LENGTH||')' data_type  From all_tab_columns
where table_name=upper('与上述表名一致') AND owner=upper('与上述用户一致') )b 
where a.column_name=b.column_name;

将当前用户下 某表下所有表字段名合并成一列

-- 将当前用户下 某表下所有表字段名合并成一列(主要用于在写动态SQL上)
SELECT LISTAGG(column_name,',') WITHIN GROUP(ORDER BY column_name) AS columns
FROM (select column_name from user_tab_columns where Table_Name=upper('表名'));

随机获取表中的一条或0条数据

--随机获取表中的一条或0条数据
SELECT * FROM 表名 SAMPLE (1) WHERE ROWNUM = 1

获取当前用户下某表下的所有字段

-- 获取当前用户下某表下的所有字段
select column_name from user_tab_columns where Table_Name=upper('表名') order by COLUMN_NAME;

数据备份

备份表

导出：exp 用户名/用户密码@数据库服务器地址/orcl file=dmp地址
导入：imp 用户名/用户密码@数据库服务器地址/orcl file=dmp地址 full=y

导出用户下的部分表,多个表之间使用逗号隔开:
exp 用户名/用户密码@数据库IP/orcl file=dmp地址.dmp tables=(表名); 
导入也要一致：
imp 用户名/用户密码@数据库IP/orcl file=dmp地址.dmp tables=(表名); 

只能在服务器端使用

-- 导出命令
expdp 用户名/密码@数据库IP:1521/orcl file=导出的目标文件名称.dmp tables=要导出的表名(多个表时用逗号隔开)

-- 导入命令
impdp 用户名/密码@数据库IP:1521/orcl file=导入的目标文件名称.dmp tables=要导入的表名(多个表时用逗号隔开)

备份用户

-- 导出指定用户1数据
exp system/manager@ip地址/orcl owner=用户名1 file=文件地址\文件名.dmp
-- 将用户1的数据导入进用户2
imp system/managerb@ip地址/orcl  file=文件地址\文件名.dmp fromuser=用户名1 touser=用户名2

ORACLE查询一张表的字段信息并且与原表字段顺序保存一致

使用场景 ： 当我们需要动态的去查询一些数据并且将数据按表原有的字段进行排序的时候

现在要将 TAX_70 的字段按照 TAX_70 表字段顺序插入到 TEST_FIELDS中；

我们可以看到查询出来的数据是与TAX_70 字段顺序一致的，现在将其插入进 TEST_FIELDS中；


这个时候可能发生不一致，所以一开始我们就可以带上序号

INSERT INTO TEST_FIELDS(FIELD,ORDER_NUM)
select a.COLUMN_NAME,a.column_id  
from USER_TAB_COLUMNS a,all_col_comments b   
where a.table_name=upper('TAX_70') 
and a.table_name=b.TABLE_NAME 
and a.column_name=b.COLUMN_NAME 
and b.OWNER=upper('TRANS') 
order by column_id; 

---

恢复误删的数据

delete from TEST where id=3;
commit;

select * from v$sql where sql_text like '%误删表数据的表名%' ;--得到时间

select * from 误删表数据的表名 as of timestamp to_timestamp('误删表数据时的时间','yyyy-mm-dd hh24:mi:ss')
where (删除表数据时所给的条件);

insert into 误删表数据的表名 (select * from 误删表数据的表名 as of timestamp to_timestamp('误删表数据的时间','yyyy-mm-dd hh24:mi:ss') where 误删表数据的条件);

恢复误删数据（闪回技术）

drop table op_log;

select object_name,original_name,operation from recyclebin
 where original_name='OP_LOG';

flashback table "BIN$5vfYWDcxdXrgU0T4Eqz6mw==$0" to before drop;

如果此时已有该表名称了，那么你必须进行重命名

flashback table "BIN$5vfYWDcxdXrgU0T4Eqz6mw==$0" to before drop rename to OP_LOG_FLUSHBACK;

---

修改数据库用户用户名

以windows 为例
(linux 比window多两步，使用ssh工具以root用户连接服务器，然后使用 su - oracle 切换到oracle用户 )

1. 使用sqlplus 连接数据库： sqlplus /nolog
2. 以管理员身份登录 conn sys/sys as sysdba
   
3. 查询所需要修改的用户名称：select user#,name from user$;
   
4. 例如我们现在将要修改 TEST 为 TEST_BACK；update user$ set name='TEST_BACK' where user#=87;
5. 提交：commit;
   
6. 不要忘记修改密码 alter user TEST_BACK IDENTIFIED BY test_back;
7. 提交 ： commit;
   
8. 测试 conn TEST_BACK/test_back
   
   修改成功

删除用户

--删除指定的用户
drop user username cascade

如果出现 ：ORA-01940无法删除当前已连接用户

--1.首先将索要删除的用户锁定（这句必须执行）：
     alter user username account lock;

--2.查看当前用户占用资源：
     select saddr,sid,serial#,paddr,username,status from v$session where username = 'username ';

--3. 执行以下杀死进程的sql（下面的两个参数是status  为 INACTIVE 时的记录）：
　　alter system kill session 'sid,serial#';

--4. 执行删除用户操作
　　drop user username cascade;

注释的动态复制添加

/**
需求场景：
存在两张字段都很多的表，例如200个以上，其中一张表注释已经完全写好
而另一张表由于业务需求是新添加的，两者字段名重复率在%70以上，那么
我们是否可以通过程序让字段一致，注释也一致，剩下不一致的就自能自己写了
这样我们能够大大的提高效率
*/
--如果两张表不属于同一个用户,那么该程序在需要被添加的注释用户下执行
declare
V_NUM NUMBER;
V_SQL CLOB;
--定义游标
  cursor gz_member_cursor is 
  -- 获取已经写好注释的表字段以及注释
  select   column_name,comments from all_col_comments   where   table_name=upper('已有注释的表名')  AND owner=upper('已有注释的表所在用户'); 
begin
  for c in gz_member_cursor loop
    --判断需要被添加注释的表中是否存在该字段
    select COUNT(1) INTO V_NUM from all_col_comments   where   table_name=upper('未写注释的表名')  AND owner=upper('未写注释的表所在用户') AND trim(COLUMN_NAME)=trim(c.column_name); 
    IF V_NUM >0 THEN
      V_SQL:='comment on  column 未写注释的表所在用户.未写注释的表名.'||trim(c.column_name)||'  is '''||trim(c.comments)||'''';
      execute immediate v_sql;
      end IF;
  end loop; 
  close gz_member_cursor ;
end;

使用dblink 连接

1. 登录system用户，给需要创建dblink 的用户赋予权限
   grant create public database link,drop public database link to 用户名;
2. 登录赋予创建dblink 权限的用户，创建dblink
   创建dblink: create public database link dblink的连接名 connect to 用户名 identified by "用户密码" USING '待连接的数据库地址:1521/orcl';
   删除dblink链接:drop public database link 用户名;
   使用dblink连接查询：SELECT * from 被连接数据库中的表名@dblink的连接名;