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;

想了解更多干货，可通过下方扫码关注