oracle 日志文件位置

逃离我推掉我的手 2023-02-16 06:50 108阅读 0赞

select * from V$diag_Info;

alert  为运行日志的目录。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdfamlhbl9kb25n_size_16_color_FFFFFF_t_70][]

以下为 sql记录。

--先查锁
    select * from v$lock where lmode > 0 and type in ('TM','TX');
    --查用户名
    select * from v$session where username = 'PROD_JCJ'
    -- 查编号方便下一步 kill
    select sid,serial#,username from v$session where sid = '13313';
    
    --kill 锁死的会话
    alter system kill session '15489,47476';
    alter system kill session '16215,2972';
    alter system kill session '2545,51671';
    alter system kill session '609,63963';
    alter system kill session '6415,45951';
    alter system kill session '2306,64117';
    
    
    --查看被锁的表       
    select * from v$locked_object a, dba_objects b where b.object_id = a.object_id     
    
    
    --查看表锁的原因
    select l.session_id sid,
           s.serial#,
           l.locked_mode,
           l.oracle_username,
           s.user#,
           l.os_user_name,
           s.machine,
           s.terminal,
           a.sql_text,
           a.action from v$sqlarea a, v$session s, v$locked_object l
           where l.session_id = s.sid and s.prev_sql_addr = a.address
           order by sid,s.serial#;
    	   
    	   
    --查看被锁的表       
    select * from v$locked_object a, dba_objects b where b.object_id = a.object_id   ;    
    
    --查看哪个用户哪个进程造成死锁
    select  * from v$locked_object a, v$session b where a.session_id = b.sid;
    
    --查看连接的进程
    select sid,serial#,username,osuser from v$session;
    
    --查询锁定的表
    select object_name,machine,s.sid,s.serial#,s.username,s.logon_time
    from v$locked_object l, dba_objects o,v$session s
    where l.object_id = o.object_id
    and l.session_id = s.sid
    
    --查询低效的SQL
    SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
       ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio,
       ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run,
       SQL_TEXT
    FROM   V$SQLAREA
    WHERE  EXECUTIONS>0
    AND     BUFFER_GETS > 0 
    AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
    ORDER BY 4 DESC
    
    --查看oracle版本
    select * from v$version
    
    --查看表的大小
    select  t.segment_name, round(bytes /1024/1024, 2) VALUE  from user_segments t WHERE T.SEGMENT_NAME LIKE 'T_JCJ%' order by  VALUE DESC ;
    
    -- 查看表空间挂载的数据文件
    SELECT FILE_NAME ,TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'DG110PROD';
    
    -- 查看表空间大小及使用情况
    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 
    
    --创建表空间
    create tablespace t_text_space datafile'+DBDATA' size 2G autoextend on next 1G maxsize unlimited extent management local;
    
    create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 2G autoextend on next 1G maxsize unlimited extent management local;
    
    --表空间重置大小
    ALTER DATABASE DATAFILE '/u02/oradata/MYCDB/MYPDB1/undotbs01.dbf' RESIZE 100M;
    
    --表空间自扩容大小调整
    ALTER DATABASE DATAFILE '/u02/oradata/MYCDB/MYPDB1/undotbs01.dbf' AUTOEXTEND ON NEXT 50M;
    
    --移除表空间
    drop tablespace t_text_space including contents and datafiles;
    
    --表空间加载数据文件
    alter tablespace TS_DATA_PROD_JCJ_CZRZ add datafile '/oradata/DGZHDG/5F15BBF77DE05BAEE053634BAE444953/datafile/space02.dbf' size 20G 
    
    alter tablespace TS_DATA_PROD_JCJ_CZRZ add datafile '+DBDATA' size 20G
     
    --移动表空间
    alter table prod_jcj.t_jcj_czrz_12 MOVE TABLESPACE TS_DATA_PROD_JCJ_CZRZ_12
    
    --查看表空间大小
    select t.tablespace_name, round(sum(bytes / (1024*1024)) ,0) t_zise from dba_tablespaces t, dba_data_files d where 
    t.tablespace_name = d.tablespace_name group by t.tablespace_name;
    
    
    
    --查看各用户的各种资源占用，可以运行下面的SQL
    select se.SID, ses.username, ses.osuser, n.NAME, se.VALUE
      from v$statname n, v$sesstat se, v$session ses
      where n.statistic# = se.statistic# and
            se.sid = ses.sid and
            ses.username is not null and
            n.name in ('CPU used by this session',
                       'db block gets',
                       'consistent gets',
                       'physical reads',
                       'free buffer requested',
                       'table scans (long tables)',
                       'table scan rows gotten',
                       'sorts (memory)',
                       'sorts (disk)',
                       'sorts (rows)', 
                       'session uga memory max' ,
                       'session pga memory max')
      order by sid, n.statistic#;
      
      
     --要想看占用资源的SQL top10之类的数据，有下面的SQL哦：
    
    --从V$SQLAREA中查询最占用资源的查询
    select b.username username,a.disk_reads reads,
        a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
        a.sql_text Statement
    from  v$sqlarea a,dba_users b
    where a.parsing_user_id=b.user_id
     and a.disk_reads > 100000
    order by a.disk_reads desc;
    
    
    
    --从V$SQLAREA中查询最占用资源的查询
    select b.username username,a.buffer_gets reads,
        a.executions exec,a.buffer_gets/decode(a.executions,0,1,a.executions) rds_exec_ratio,
        a.sql_text Statement
    from  v$sqlarea a,dba_users b
    where a.parsing_user_id=b.user_id
     and a.buffer_gets > 100000
    order by a.buffer_gets desc;
    
    
    
    --列出使用频率最高的5个查询：
    select sql_text,executions
    from (select sql_text,executions,
       rank() over
        (order by executions desc) exec_rank
       from v$sql)
    where exec_rank <=5;
    
    
    --消耗磁盘读取最多的sql top5：
    select disk_reads,sql_text
    from (select sql_text,disk_reads,
       dense_rank() over
         (order by disk_reads desc) disk_reads_rank
       from v$sql)
    where disk_reads_rank <=5;
    
    
    
    --找出需要大量缓冲读取（逻辑读）操作的查询：
    select buffer_gets,sql_text
    from (select sql_text,buffer_gets,
       dense_rank() over
         (order by buffer_gets desc) buffer_gets_rank
       from v$sql)
    where buffer_gets_rank<=5;
    
    
    
    
    
    --查询日志文件位置
    select * from V$diag_Info
    
    
    
    --创建索引， 压缩索引，指定表空间， 初始事务大小
    alter index t_jcj_jjdb_gxdwdm rebuild compress initrans 50 maxtrans 255 storage(INITIAL 1M next 10M)   tablespace t_jcj_jjdb_tabspace online nologging;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdfamlhbl9kb25n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200609103720889.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Rhbmdfamlhbl9kb25n,size_16,color_FFFFFF,t_70