ORACLE. Запросы актуальные

РАБОТА С ДИРЕКТОРИЯМИ

-- Посмотрим, какие директории у нас есть
SELECT * FROM dba_directories;

-- Создадим директорию в linux
CREATE DIRECTORY LOG_DIR as '/mnt/hd2tb/oracle_ee/playground/logs';

-- Создадим директорию в Windows

CREATE DIRECTORY MY_BACKUP_DIR as 'H:\db_storage';
CREATE DIRECTORY LOG_DIR as 'H:\db_storage\logs';

-- Удаляем директорию(ссылку в Oracle)
DROP DIRECTORY LOG_DIR;

-- Добавляем доступ пользователю
GRANT READ, WRITE ON DIRECTORY cube_dir TO cube;

-- Забираем право записи в директорию у пользователя
REVOKE WRITE ON DIRECTORY cube_dir FROM cube;

-- Разрешим пользователю создавать и удалять директории
GRANT CREATE ANY DIRECTORY TO SCOTT;
GRANT DROP ANY DIRECTORY TO SCOTT;
GRANT CREATE MATERIALIZED VIEW TO SCOTT;

---

РАБОТА С ПОЛЬЗОВАТЕЛЯМИ/СХЕМАМИ

-- Выводим список пользователей
SELECT * FROM dba_users;

-- Выводим список пользователей

  SELECT USERNAME,

  CREATED

FROM dba_users

   WHERE ACCOUNT_STATUS = 'OPEN'

    AND (USERNAME like 'F_%' or USERNAME like 'P_%' or USERNAME like 'Z_%' 

        or USERNAME like '%GZ%' or USERNAME like '%PLAN%' or USERNAME like '%FIN%'

    or USERNAME like 'GZ%' or username like 'PL%' or USERNAME like 'FIN%' or USERNAME like 'MZ%')

ORDER BY username;

-- Создадим пользователя
CREATE user SCOTT IDENTIFIED BY PASSWORD1;

-- Поменяем пароль
alter user SCOTT IDENTIFIED by "PASSWORD2";

-- Удалим пользователя/схему
drop user SCOTT cascade;

-- Разблокируем пользователя/схему

alter user SCOTT account unlock;

-- Сбор статистики

execute dbms_stats.gather_schema_stats (ownname =>'SCOTT',cascade => TRUE);

-- Проверить когда собиралась статистика

select table_name, stale_stats, last_analyzed 

from dba_tab_statistics 

where owner = 'SCOTT' 

order by last_analyzed desc, table_name ASC;

-- Поиск невалидных объектов

select owner, object_type, 'begin dbms_utility.compile_schema('''||owner||''',TRUE); end ;' AS "CommandToRecompil", count(1) AS "Скрипт для исправления"

from all_objects

where status = 'INVALID'

GROUP BY owner, object_type

ORDER BY owner;

--Компиляция по схеме

begin dbms_utility.compile_schema('SCOTT',TRUE);

end;

-- Компиляция невалидных объектов

BEGIN

  FOR l_crs IN (SELECT owner, object_name, object_type FROM all_objects WHERE STATUS = 'INVALID' AND owner IN ('SCOTT'))

  LOOP

    BEGIN

IF l_crs.object_type in ('TRIGGER', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE BODY')

      THEN

        EXECUTE IMMEDIATE 'ALTER ' || l_crs.object_type || ' "' || l_crs.owner || '"."' || l_crs.object_name || '" COMPILE';

      END IF;

    EXCEPTION

      WHEN OTHERS THEN NULL;

        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);

        RETURN;

    END;

  END LOOP;

END;

;

-- Просмотр подключений

select username ||' ( '||machine|| ' ) ' as "SCHEMANAME (USERNAME _MACHINE)", count ( 1 ) as SESN_QTY

from v$session 

where username is not null group by username, machine order by username ;

---

РАБОТА С ТАБЛИЧНЫМИ ПРОСТРАНСТВАМИ

-- Расширяем табличное пространство
ALTER TABLESPACE USERS ADD DATAFILE '/mnt/hd2tb/oracle_ee/oradata/ORACL11B/users25.dbf' SIZE 1000M AUTOEXTEND ON;

-- Уменьшаем размер tablespace
ALTER TABLESPACE USERS SHRINK SPACE;

-- Размер tablespace

SELECT a.tablespace_name, "Free, MB", "Total, MB" FROM

  (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS "Total, MB" FROM dba_data_files GROUP BY tablespace_name

  UNION

  SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS "Total, MB" FROM dba_temp_files GROUP BY tablespace_name) a,

  (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS "Free, MB" FROM dba_free_space GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name (+)

ORDER BY a.tablespace_name;

-- Узнаем на сколько мы можем уменьшить размер ТП
select dba_data_files.file_name,
   dba_data_files.file_id,
   dba_data_files.tablespace_name,
   ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
   ceil(blocks * db_block_size / 1024 / 1024) currsize,
   ceil(blocks * db_block_size / 1024 / 1024) -
   ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from   dba_data_files,
   (select file_id,
          max(block_id + blocks - 1) hwm
   from   dba_extents
   group  by file_id) b,
   (select value db_block_size from v$parameter where name = 'db_block_size') c
where  dba_data_files.file_id = b.file_id(+);

--Изменить размер ТП

ALTER TABLESPACE USERS RESIZE 1400G;

---

ДРУГИЕ ЗАПРОСЫ

-- Определите текущее значение размера SGA, выполнив следующую команду:
SHOW PARAMETER SGA_MAX_TARGET;

-- Сколько выделено памяти под экземпляр в Oracle Database
select name,display_value 
  from v$parameter 
 where name like 'mem%target' 
    or name like 'pga%' 
    or name like 'sga%';

-- Изменить размер SGA
ALTER SYSTEM SET sga_target = 7717519360 SCOPE=MEMORY;
alter system set sga_max_size=8G scope=spfile;

-- Посмотреть параметр маскимального времени выполнения запроса. При ошибке ORA-01555

select * from v$parameter

where name = 'undo_retention';

-- Посмотреть региональные параметры

select * from nls_database_parameters;

select * from nls_session_parameters;

--общее количество открытых курсоров на сессию

select a.value, s.username, s.sid, s.serial#

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#  and s.sid=a.sid

and b.name = 'opened cursors current'

ORDER BY VALUE desc;

--Топ-10 самых больших (по размеру) таблиц:

SELECT * FROM (

SELECT owner, segment_name, bytes/1024/1024 Mb

FROM dba_segments

WHERE segment_type = 'TABLE'

ORDER BY bytes/1024/1024 DESC)

WHERE rownum <= 10;