Oracle经常使用的语句，你可能有时候需要用到

写在前面：

• 以下都是由于工作中可能需要用到的语句，经过各种搜罗整理出来的一部分觉得比较有用的Oracle的一些语句，如果查询报错请切换至具有DBA权限的用户执行。
• 这些资料都是别人共享给我们学习使用，那现在我也整理下共享出来，来贯彻我们的行业精神。
• [我是一名Java Web菜鸟程序员，如有错误欢迎指出，轻点喷…]
• 陆续更新中… …
• 不知道大家有没有同感，我觉得做java Web就是从前端到后台再到部署运行，什么都要会。纯前端的框架不考虑，起码要会Html，css，js，jquery，javaee ,spring ioc,aop,mvc ssh|ssm，oracle|mysql , tomcat，nginx,linux，cache，可能还会用到mq，还有最近比较火的spring-cloud/boot 等等等等一些乱七八糟的工具和知识点，那么这么多，这么乱就要取学会整理，靠死记是记不住那么多东西的。整理的好了，以后直接cv大法拿来用，岂不快哉！

Oracle用户表空间创建

--例如我要创建一个用户为 bidb的用户 密码为 bidb_123
create user bidb identified by bidb_123;

--授予bidb用户各种权限
grant create session,create table,create view,create sequence,unlimited tablespace to bidb; 
grant create any procedure to bidb; 
grant execute any procedure to bidb;

--创建bidb表空间
create tablespace bidb datafile '/u01/oracle/app/oradata/bidb/bidb_data01.dbf' size 1M autoextend on maxsize 30G extent management local;  
--扩展表空间
alter tablespace bidb add datafile '/u01/oracle/app/oradata/bidb/bidb_data02.dbf' size 1M autoextend on maxsize 30G;

--切换归档文件
alter system switch logfile;

--查看数据库文件
select * from dba_data_files;
--查看表空间文件大小
select file_name,bytes/1024/1024 M from dba_data_files where tablespace_name='bidb';

--设置用户bidb的表空间为bidb
alter user bidb default tablespace bidb;

--删除用户
drop user bidb cascade;

--删除表空间
drop tablespace bidb including contents and datafiles cascade constraint;

oracle创建dblink连接

--首先创建dblink一定要有创建dblink的权限
--给用户bidb授权
grant create database link to bidb;

--创建一个能够连接test用户的dblink，dblink的名称这里定义为testlink
--test_pwd: test用户的密码    注意配置好IP，端口号
create database link testlink
　　connect to test identified by test_pwd
　　using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';
  
--创建好之后就可以使用dblink了，例如：
select * from t_test_user@testlink;

--如果要创建公共dblink的话,加上 /* public */ 
create  /* public */  database link ....
--查看 dblink
select * from dba_db_links;
--删除 dblink
drop public database link dblinkname;
drop /* public */ database link dblinkname;  

修改Oracle连接数

--查看Oracle数据库默认允许重复连接多少次，默认是10次
select * from dba_profiles WHERE resource_name = 'FAILED_LOGIN_ATTEMPTS' AND PROFILE = 'DEFAULT';

--修改Oracle数据库默认连接数为100个用户
alter profile default limit FAILED_LOGIN_ATTEMPTS 100;

--设置无限制
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED; 

oracle用户解锁语句

--给用户解锁
ALTER USER username ACCOUNT UNLOCK;

oracle查询表空间使用情况

select 
b.file_id 文件ID号, 
b.tablespace_name 表空间名, 
b.bytes/1024/1024||'M'字节数, 
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用, 
sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间, 
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 占用百分比 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.bytes 
order by b.file_id;

查看Oracle当前连接的情况

 select s.username 用户名,
   s.osuser 操作系统用户,
   s.program 使用程序,
   s.MACHINE 服务器,
   s.sid,s.serial#,p.spid,
   'alter system kill session '||''''||trim(s.sid)||','||trim(s.serial#)||''';' Kill语句
   from v$session s,v$process p where s.paddr = p.addr 
   --and s.username ='';  --过滤用户

查询Oracle正在执行的sql语句情况

SELECT b.sid,  
       b.username 用户名,
       b.serial#,
       spid,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 服务器,
       'alter system kill session ' || '''' || b.sid || ',' || b.serial# || '''' || ';' as kill语句
FROM v$process a, v$session b, v$sqlarea c 
WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value 
--and b.username='';  --过滤用户

查询oracle当前被锁对象

SELECT l.session_id sid,  
       s.serial#,  
       l.locked_mode 锁模式,  
       l.oracle_username 用户名,  
       s.machine 服务器,
       o.object_name 被锁对象名,
       s.logon_time 登录数据库时间,
       'alter system kill session ' || '''' || sid || ',' || s.serial# || '''' || ';' Kill语句
FROM v$locked_object l, all_objects o, v$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid; 

Kill不掉的锁处理（ora-00031:session marked for kill）

如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：

select spid, osuser, s.program 
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24; （24是上面的sid）

在OS上杀死这个进程（线程）：
1)在unix/linux上，用root身份执行命令:

#kill -9 12345    （即上面查询出的spid）

2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：

orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345

PLSQL

查询数据中文乱码解决办法

1.执行下面语句

select (select value from nls_database_parameters where parameter = 'NLS_LANGUAGE') ||'_'||
(select value from nls_database_parameters where parameter = 'NLS_TERRITORY') ||'.'||
(select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET') as NLS_LANG FROM DUAL;

2.设置环境变量

设置环境变量NLS_LANG 为1中得到的NLS_LANG值，重启PLSQL解决

PLSQL解决&符号插入问题

使用命令行执行 set define off
然后继续执行需要执行的语句即可

还有一种解决方案即用 使用拼接符拼接即可 例如
insert into table values (‘name’, ‘code’ , ‘code’||’&’||‘name’);
【ps：因为要经常整理升级的sql执行起来太麻烦，所以整理sql产生&符号的时候统一采用拼接的方法很方便】