oracle备忘录

1.登录linux服务器
执行：su - oracle
执行：sqlplus 用户名/密码
使用dab登录命令，执行：sqlplus / as sysdba
dba授权执行：grant dba to QXT_USER;
（注：QXT_USER是用户名）

2.查看表空间使用情况

SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

SELECT a.tablespace_name,total/(1024 * 1024 * 1024),free/(1024 * 1024 * 1024) FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

3.查询Oracle正在执行的sql语句及执行该语句的用户

SELECT b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value

4.查看正在执行sql的发起者的发放程序

SELECT OSUSER 电脑登录身份,
       PROGRAM 发起请求的程序,
       USERNAME 登录系统的用户名,
       SCHEMANAME,
       B.Cpu_Time 花费cpu的时间,
       STATUS,
       B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
                   AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC;

5.查出oracle当前的被锁对象

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#;

6. kill掉当前的锁对象可以为(注：结合上面的查询)

alter system kill session 'sid, s.serial#';
示例：alter system kill session '144, 8720';

7.查询正在执行的SQL语句

select a.program, a.sid,a.SERIAL#,b.spid, c.sql_text,c.SQL_ID
  from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
   and a.sql_hash_value = c.hash_value
   and a.username is not null order by c.sql_tex

8.linux启动oracle数据库服务
1)以oracle用户登录
#su - oracle
2)使用sysdba角色登录sqlplus，启动数据库实例
$sqlplus / as sysdba
SQL>startup
SQL>alter user qxt_user account unlock;
SQL>Alter system set “_undo_autotune” = false;
SQL>exit
3)打开Oracle监听
$lsnrctl start
4)查看oracle进程
$ps -ef|grep oracle
5)关闭数据库实例
SQL>shutdown immediate
SQL>exit
6)关闭监听
$lsnrctl stop

9.查询所以的表所占空间的大小，排倒叙，单位“兆”

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) M
from dba_segments t
where t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type
order by M desc;

10.查询用户为QXT_USER下所有的触发器，object_name为触发器的名称

select * from all_objects where object_type = 'TRIGGER' and owner='QXT_USER' order by object_name;

11.查询用户为QXT_USER下所有的序列，object_name为序列的名称

select * from all_objects where object_type = 'SEQUENCE' and owner='QXT_USER' order by object_name;

12.查询所有的作业

select * from all_objects where object_type = 'JOB' and owner='QXT_USER' order by object_name;

13.查询当前用户下所有的数据库类型

select table_name,num_rows from user_tables;

14.避免system空间不足的报错，可关闭审计功能，并重启数据库

alter system set audit_trail=none scope=spfile;

查看

show parameter audit_trail;

15.临时表空间
查询语句：select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;
或者：select * from dba_temp_files d;
增加临时表空间执行（dba权限执行）：

    su - oracle
	sqlplus / as sysdba
	alter tablespace temp add tempfile '/user/oracle/oradata/temp02.dbf' size 4G autoextend on next 128M;

说明：/user/oracle/oradata/temp02.dbf为存放路径
修改临时表空间执行（dba权限执行），增大临时文件大小执行：

alter database tempfile '/user/oracle/oradata/temp02.dbf' RESIZE 4096M;

--将临时数据文件设为自动扩展

alter database tempfile '/user/oracle/oradata/temp02.dbf' autoextend on next 10m maxsize 4096M;

16.oracle 等同于mysql的insert ignore into 数据量大时，有问题，慎用

insert /*+IGNORE_ROW_ON_DUPKEY_INDEX(t_car_wz_task_122,T_CAR_WZ_TASK_122_U3)*/ into t_car_wz_task_122(carid,motor,carstyle) select carid,motor,carstyle from t_custdb where codeid in('1001124','10011','44');

17.查看当前的数据库连接数

select count(*) from v$process ;    --当前的数据库连接数

18.oracle 忽略唯一键或主键的写法，有bug, 执行 如果插入200条记录，则不会报错。如果插入的数据量达到1000，则会引发ORA-600错误。

insert /*+IGNORE_ROW_ON_DUPKEY_INDEX(表名01,唯一键的名)*/ into 
 表名01(id,name) 
 select sys_guid(),name from 表名02
 出现问题：ORA-00600: 内部错误代码, 参数: [qerltcInsertSelectRop_bad_state], [], [], [], [], [], [], [], [], [], [], []

19.ORA-28000 账号被锁定的解决办法
检查已经被锁定的用户
SQL> select username,account_status from dba_users;

解锁被锁定的账户(QXT_USER为上面语句中username的值)
SQL> alter user QXT_USER account unlock;

20.CPU高的SQL

select Cpu_Time,sql_text from v$sql order by cpu_time desc;

21.逻辑读多的SQL

select * from (select buffer_gets, sql_text
   from v$sqlarea
   where buffer_gets > 500000
   order by buffer_gets desc) where rownum<=30;

22.执行次数多的SQL

select sql_text,executions from
  (select sql_text,executions from v$sqlarea order by executions desc)
   where rownum<81;

23.读硬盘多的SQL

select disk_reads,sql_text from
   (select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
   where rownum<21;

24.列出使用频率最高的5个查询

select executions,sql_text
   from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
   where exec_rank <=5;

添加链接描述