oracle数据库操作指南

sql语句类

1.1、查询所有实例

select * from gv$instance;

1.2、查询DBA用户

SELECT username,PROFILE FROM dba_users;

1.3、修改密码不过期

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

1.4、查询v$sqlarea中执行的sql语句

 select sql_text,
                sql_fulltext,
                parsing_schema_name,
                module,
                last_load_time
           from v$sqlarea v
          where v.LAST_LOAD_TIME <
                to_date('2020/08/18 15:59:56', 'yyyy-mm-dd hh24:mi:ss')
            and v.LAST_LOAD_TIME >
                to_date('2020/08/18 15:59:53', 'yyyy-mm-dd hh24:mi:ss')
          order by LAST_LOAD_TIME;
          
          
  select sql_text,
                sql_fulltext,
                parsing_schema_name,
                module,
                last_load_time
           from v$sqlarea v
          where  v.LAST_LOAD_TIME >
                to_date('2021/10/14 13:55:53', 'yyyy-mm-dd hh24:mi:ss')
          order by LAST_LOAD_TIME;
/*查询一段时间内数据库执行的sql语句*/
select sql_text, sql_fulltext, parsing_schema_name, module, last_load_time
  from v$sqlarea v
 where v.LAST_LOAD_TIME <=
       to_date('2020/08/23 11:15:04', 'yyyy-mm-dd hh24:mi:ss')
   and v.LAST_LOAD_TIME >=
       to_date('2020/08/23 11:15:04', 'yyyy-mm-dd hh24:mi:ss')
 order by LAST_LOAD_TIME;

sql_text中存储1000个字符多出的被截断

1.5、v$parameter显示当前对会话有效的初始化参数的信息

SELECT name,type,value,isdefault,isses_modifiable,issys_modifiable FROM v$parameter;

1.6、oracle数据库中时间函数

select to_date(to_char(to_date('2019-01-01 13:13:13','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss') - 1 / 6 + 1 / (24 * 60) aa,trunc(to_date('2019-01-01 13:13:13', 'yyyy-mm-dd hh24:mi:ss')) - 1 / 6 + 1 / (24 * 60) bb from dual;
select trunc(sysdate, 'month') from dual;
select add_months(trunc(sysdate, 'month'), 1) - 1 from dual;

1.7、查询表的大小

select a.segment_name,
       a.segment_type,
       a.bytes,
       a.bytes / 1024 / 1024 byte_m, 
       b.created
  from dba_segments a
 inner join all_objects b
    on b.object_type = 'TABLE'
   and a.owner = b.owner
   and a.segment_name = b.object_name
 where a.owner = 'NC65'
   and a.segment_type = 'TABLE'  and a.bytes>50000000
 order by a.bytes desc;

--select count(*) from SRP.SRP_TR_CHECK_COUNT 

1.8、查询表空间

SELECT tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
FROM   dba_data_files
ORDER BY tablespace_name;

1.9、归档日志

一天的总大小和总数量

select logtime,
      count(*),
      round(sum(blocks * block_size) / 1024 / 1024 / 1024) GBsize
from (select 
        trunc(first_time, 'dd') as logtime,
        a.BLOCKS,
        a.BLOCK_SIZE
      from v$archived_log a
      where a.DEST_ID = 1
      and a.FIRST_TIME > trunc(sysdate - 14)
      )
      group by logtime
      order by logtime desc;

查询数据库归档日志目录和使用率

select name,
       space_used,
       space_limit,
       space_used / space_limit,
       number_of_files
  from v$recovery_file_dest;

1.10、查看每小时dbtime的大小

SELECT to_char(b.begin_interval_time, 'yyyymmddhh24mi') begin_snapshot_time,
       c.DB_TIME
  FROM (SELECT a.snap_id,
               to_char(TRUNC((DB_TIME - lag(DB_TIME, 1, DB_TIME)
                              over(PARTITION BY stat_name,
                                   instance_number ORDER BY snap_id)) /
                             1000000 / 60,
                             2)) DB_TIME
          FROM (SELECT a.snap_id,
                       a.dbid,
                       a.instance_number,
                       a.stat_name,
                       SUM(a.value) DB_TIME
                  FROM DBA_HIST_SYS_TIME_MODEL a
                 WHERE a.stat_name = 'DB time'
                   AND a.instance_number = 2 ---这个地方要检查实例1和实例2
                 GROUP BY a.snap_id, a.dbid, a.instance_number, a.stat_name) a) c,
       dba_hist_snapshot b
 WHERE b.instance_number = 2 ---这个地方要检查实例1和实例2
   AND c.snap_id = b.snap_id
 ORDER BY 1, 2;

1.11、查询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

查看正在执行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

1.12、查询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

查看死锁的进程

 SELECT S.USERNAME,
        DECODE(L.TYPE,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
        O.OWNER,
        O.OBJECT_NAME,
        O.OBJECT_TYPE,
        S.SID,
        S.SERIAL#,
        S.TERMINAL,
        S.MACHINE,
        S.PROGRAM,
        S.OSUSER
  FROM V$SESSION   S,
       V$LOCK      L,
       DBA_OBJECTS O
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL
and username = 'IMES_QPES'
and L.TYPE = 'TX'
order by s.MACHINE, s.sid;

查询被锁的 sql语句和sid用户等信息

SELECT sq.sql_text,
       sq.SQL_FULLTEXT,
       S.SID,
       S.SERIAL#,
       S.TERMINAL,
       S.MACHINE,
       S.PROGRAM,
       S.OSUSER,
       O.OWNER,
       S.USERNAME,
       DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE
  FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O, v$sql sq
 WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID(+)
   AND S.USERNAME IS NOT NULL
   and sq.hash_value = s.sql_hash_value
      --and username= 'IMES_QPES' 
   and L.TYPE = 'TX'
 order by s.MACHINE, s.sid;

1.13、用户相关操作

修改密码

 alter user nc65_sw identified by nc65_sw;

连接池

SELECT CONNECTION_POOL,STATUS,MINSIZE,MAXSIZE FROM DBA_CPOOL_INFO;
select value from v$parameter where name ='sessions';

锁和解锁用户

--alter user nc65_sw account unlock; --lock
alter user nc65 account unlock;

查询DBA参数

select * from dba_profiles

修改 密码尝试30次后锁定一般是10次

alter profile default limit FAILED_LOGIN_ATTEMPTS 30;