В этой статье я буду публиковать полезные скрипты и запросы для СУБД Oracle.

1. Как узнать где находится alert лог:

show parameter dump_dest;

2. Свободное место в БД:

select * from dba_tablespace_usage_metrics order by used_percent desc;

select tsu.tablespace_name, ceil(tsu.used_mb) "size MB", decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB", decode(floor(tsu.used_mb- tsf.free_mb), NULL,0,floor(tsu.used_mb- tsf.free_mb)) "used MB", decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100, 100 - ceil(tsf.free_mb/tsu.used_mb*100)) || ' %' "% used"from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name ) tsu, (select tablespace_name, sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name) tsf where tsu.tablespace_name = tsf.tablespace_name (+)order by (decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100, 100 - ceil(tsf.free_mb/tsu.used_mb*100))) desc

3. Длинные запросы в системе:

SELECT SID,SERIAL#,SQL_FULLTEXT,LAST_CALL_ET,sql_text,event,p1
FROM V$SESSION T1, V$SQL T2
WHERE T1.STATUS = 'ACTIVE' AND T1.SQL_ADDRESS = T2.ADDRESS AND LAST_CALL_ET > 900
order by last_call_et desc

4. Проверить статус партишионингового индекса:

SELECT i.table_name,  i.index_name, ip.partition_name,
       i.status AS index_status, ip.status AS partition_status
  FROM user_indexes i
       JOIN user_ind_partitions ip
         ON (i.index_name = ip.index_name )
 ORDER BY i.table_name, i.index_name, ip.partition_name;

5. Размер всех индексов схемы:

select segment_name, sum(bytes/1024/1024) as MB from dba_segments
where owner='HISTORY_DB'
and segment_type='INDEX'
or segment_type='INDEX PARTITION'
and owner='HISTORY_DB'
group by segment_name
order by MB desc

6. Перекомпилировать невалидные объекты:

@?/rdbms/admin/utlrp.sql;
This script must be run under sys as sysdba user

7. Переместить таблицы и индексы в другой тейблспейс:

ALTER TABLE TEST MOVE TABLESPACE SLMDATA;
ALTER INDEX TEST_IND_1 REBUILD TABLESPACE SLMDATA;

8. Изменить лимит процессов и сессий:

select * from v$resource_limit
ALTER system SET processes = 1000 scope=spfile;
ALTER system SET sessions = 1000 scope=spfile;
select * from v$session

9. Статистика:

select table_name,partition_Name,last_analyzed
from all_tab_partitions order by table_name,partition_Name desc

Создание статистики для схемы (например, для количества строк в таблицах):
exec DBMS_STATS.GATHER_SCHEMA_STATS('PMMCONF_DB',DBMS_STATS.AUTO_SAMPLE_SIZE);

Создание статистики для таблицы:
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

Статистика для таблицы вместе с индексами
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES',cascade=>TRUE);
This script must be run under sys as sysdba user.

10. Свободное место в UNDO:

select (free / total) * 100
  from (select (select sum(bytes)
                      from dba_undo_extents
                     where status = 'EXPIRED') +
                 (select sum(bytes)
                   from dba_free_space
                  where tablespace_name like '%UNDO%') free ,
                  (select sum(bytes) from dba_data_files
                  where tablespace_name like '%UNDO%') total
                  from dual
        );

11. Размер таблиц в схеме

select segment_name, sum(bytes/1024/1024) as MB from dba_segments
where owner='PMMCOUNTER_DB'
and segment_type='TABLE'
or segment_type='TABLE PARTITION'
and owner='PMMCOUNTER_DB'
group by segment_name
order by MB desc

set serveroutput on
declare
    v_cnt number;
begin
    dbms_output.enable(1000000);
     for v_rec in (select table_name from all_tables a where owner='XXX') loop
      execute immediate 'select count(*) from xxx.' || v_rec.table_name into v_cnt;
      dbms_output.put_line('Table XXX.' || v_rec.table_name || ' has ' || v_cnt || ' row(s).');
    end loop;
end;
/