Oracle: апреля 2016

среда, 27 апреля 2016 г.

Создание ридонли админского аккаунта

В больших компаниях существует разделение между девелоперами и админами Оракла и девелоперам выдаются очень ограниченные права, что затрудняет поиска проблем.
Если ваше полиси позволяет создание аккаунта для чтения данных из системных таблиц, то его можно создать с помощью следующего скрипта:

CREATE ROLE USER_ADMIN_ROLE
/
GRANT CREATE SESSION TO USER_ADMIN_ROLE
/
GRANT SELECT_CATALOG_ROLE TO USER_ADMIN_ROLE
/
CREATE USER USER_ADMIN IDENTIFIED BY user_admin
/
GRANT USER_ADMIN_ROLE TO USER_ADMIN
/

После этого, можно настроить админский коннекшен в SQL Developer и мониторить состояние базы данных:

Проверка переноса всех строк при экспорте/импорте дампа

Если у вас есть возможность перед сворачиванием дампа, подсчитать количество строк в каждой таблице и типов объектов, то можно использовать следующий скрипт:

DECLARE
l_cnt NUMBER;
BEGIN
FOR rw IN (SELECT object_type, count(*) cnt
FROM user_objects
GROUP BY object_type
ORDER BY object_type)
LOOP
dbms_output.put_line(rw.object_type || '=' || rw.cnt);
END LOOP;
FOR rw IN (SELECT table_name
FROM user_tables
ORDER BY table_name)
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) l_cnt FROM ' || rw.table_name INTO l_cnt;
dbms_output.put_line(rw.table_name || '=' || l_cnt);
END LOOP;
END;
/

После импорта дампа, запускаем данный скрипт на схеме и сверяем с предыдущем запуском.

Конечно результаты можно было бы сохранить в таблицы и проводить сравнение с помощью SQL, но на данном этапе я остановился на текущем варианте и сохраняю результаты в текстовые файлы, а потом сравниваю их обычным компаратором файлов.

Сбор статистики по таблицам

После разворачивания дампа, я использую следующий скрипт для сбора статистики по таблицам:

begin

for r in (select table_name from user_tables)

loop

dbms_stats.unlock_table_stats(user, r.table_name);

dbms_stats.gather_table_stats(

ownname => user,

tabname => r.table_name,

estimate_percent => 100);

end loop;

end;

вторник, 26 апреля 2016 г.

Проверка правильности переноса дампа

Очень часто сталкиваюсь с проблемой, когда администраторы разворачивают дамп с неконсистентными данными, при этом теряются констрейнты, форенкеи.......
Для проверки о том что был ли дамп перенесен консистентно, я создал таблицу a$schema_statistics:

declare

v_table_name varchar2(30) := 'a$schema_statistics';

v_create_table varchar2(2000) := 'create table ' || v_table_name || ' '

|| '(object_type varchar2(30)'

|| ',object_name varchar2(4000)'

|| ',status varchar2(20))'

|| 'tablespace itap_data01';

v_trunc_table varchar2(200) := 'truncate table ' || v_table_name;

v_cnt integer;

begin

select count(*)

into v_cnt

from user_tables

where table_name = upper(v_table_name);

execute immediate case v_cnt

when 0 then v_create_table

else v_trunc_table

end;

И два представления:

CREATE OR REPLACE FORCE VIEW v_schema_statistics

WITH cons_columns AS

(SELECT table_name

|| '#'

|| listagg(column_name, ':') WITHIN GROUP (ORDER BY position) cons_name, constraint_name

FROM user_cons_columns uccs

GROUP BY table_name

, constraint_name)

SELECT 'CONSTRAINTS' AS object_type

, CASE

WHEN constraint_name LIKE 'SYS%'

THEN CASE urcs.constraint_type

WHEN 'R' THEN urcs.constraint_type

|| '='

|| (SELECT cons_name FROM cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

|| '&'

|| (SELECT cons_name FROM cons_columns cscs WHERE cscs.constraint_name = urcs.r_constraint_name)

WHEN 'P' THEN urcs.constraint_type

|| '='

|| (SELECT cons_name FROM cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

WHEN 'U' THEN urcs.constraint_type

|| '='

|| (SELECT cons_name from cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

WHEN 'C' THEN urcs.constraint_type

|| '='

|| (SELECT cons_name from cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

END

ELSE urcs.constraint_name

END object_name

, urcs.status

FROM user_constraints urcs

UNION ALL

SELECT 'INDEX' as object_type

, CASE

WHEN index_name LIKE 'SYS%'

THEN CASE index_type

WHEN 'LOB' then index_type || '=' || uris.table_name

ELSE (SELECT table_name

|| '#'

|| LISTAGG(column_name || '&' || descend, ':') WITHIN GROUP (ORDER BY column_position) index_name

FROM user_ind_columns uics

WHERE uics.index_name = uris.index_name

GROUP BY table_name)

END

ELSE index_name

END object_name

, uris.status

FROM user_indexes uris

WHERE uris.index_type <> 'LOB'

UNION ALL

SELECT object_type

, object_name

, status

FROM user_objects

WHERE object_type not in ('LOB', 'INDEX')

AND ((object_type <> 'TYPE') OR (object_name not LIKE 'SYS%'))

CREATE OR REPLACE FORCE VIEW v_schema_statistics_compare

SELECT *

FROM (SELECT CASE

WHEN sass.object_name IS NULL

OR vsss.object_name IS NULL THEN 'NOT MATCHED'

WHEN sass.status <> vsss.status THEN 'DIFF STATUS'

ELSE 'OK'

END compare_result

, sass.object_type AS a$_object_type

, sass.object_name AS a$_object_name

, sass.status AS a$_status

, vsss.object_type AS sh_object_type

, vsss.object_name AS sh_object_name

, vsss.status AS sh_status

FROM a$schema_statistics sass

FULL OUTER JOIN

v_schema_statistics vsss

ON sass.object_type = vsss.object_type

AND sass.object_name = vsss.object_name)

WHERE compare_result <> 'OK'

Первое представление v_schema_statistics отображает статистику по текущей схеме, а второе v_schema_statistics_compare сравнивает сохраненную статистику в таблице a$schema_statistics с текущей статистикой. При каждом обновлении схемы, я запускаю следующий скрипт;

truncate table a$schema_statistics

insert

into a$schema_statistics

( object_type

, object_name

, status

)

select object_type

, object_name

, status

from v_schema_statistics

commit

Он собирает статистику по схеме и сохраняет в таблице a$schema_statistics.

Если мне нужно узнать, целостность перенесенного дампа, я выполняю запрос

select * from v_schema_statistics_compare

Колонки с префиксом A$ отображают сохраненную статистику, а с префиксом SH текущую статистику по схеме.

среда, 20 апреля 2016 г.

Конкантенация строк в Oracle

В Оракле можно конкантенировать строки следующим способом:

select cast(wm_concat(a) as varchar2(255)) x
from (select '1' a from dual
union all
select '2' a from dual
union all
select '3' a from dual
union all
select '4' a from dual)
/

или начиная с Оракла 11

select LISTAGG(a, ', ') WITHIN GROUP (ORDER BY a) x
from (select '1' a from dual
union all
select '2' a from dual
union all
select '3' a from dual
union all
select '4' a from dual)
/

Удаление записи и всех связанных данных из других таблиц

Иногда бывает нужно удалить запись, но срабатывает констрейнт о том что есть подчиненные записи. И эта иерархия может быть очень длинная. Конечно, при использовании форенкеев с каскадным удалением, такой проблемы не возникает, но что если используются обычные форенкеи. Для решения данной проблемы, можно использовать следующий скрипт:

create table list_of_constraints as

select ddl_constraint

, substr(replace(ddl_constraint, ' ADD ', ' DROP '), 1, instr(ddl_constraint, ' FOREIGN KEY ')) as drop_constraint

, replace(ddl_constraint, ' ENABLE', ' ON DELETE CASCADE ENABLE') as cascade_constraint

from (select lpad(' ', 2*(level-1)) || table_name as table_name

, id

, parent_id

, constraint_name

, delete_rule

, r_constraint_name

, decode(constraint_type, 'R', dbms_metadata.get_ddl('REF_CONSTRAINT', constraint_name)) ddl_constraint

from (select case

when constraint_type = 'R' then (select constraint_name

from user_constraints urco

where urco.table_name = urcs.table_name

and urco.constraint_type = 'P')

else urcs.constraint_name

end as id

, urcs.r_constraint_name as parent_id

, urcs.table_name

, urcs.constraint_type

, urcs.constraint_name

, urcs.r_constraint_name

, urcs.delete_rule

from user_constraints urcs

where constraint_type in ('P', 'U', 'R')) obtr

connect by nocycle prior obtr.id = obtr.parent_id

start with obtr.table_name = 'AAAA'

and obtr.constraint_type in ('P'))

where ddl_constraint is not null

and delete_rule = 'NO ACTION'

---

begin

for r in (select drop_constraint, cascade_constraint from list_of_constraints)

loop

execute immediate r.drop_constraint;

execute immediate r.cascade_constraint;

end loop;

end;

--- DELETE ---------------------------------------------------------------------

delete from AAAA where name = 'bbbb'

--------------------------------------------------------------------------------

---

begin

for r in (select drop_constraint, ddl_constraint from list_of_constraints)

loop

execute immediate r.drop_constraint;

execute immediate r.ddl_constraint;

end loop;

end;

drop table list_of_constraints

В этом скрипте, мы вычисляем все подчиненные таблицы для таблице АААА. При этом во временную таблицу list_of_constraints мы записываем скрипты для пересоздания уже существующих форенкеев на форенкеи с каскадным удалением.

После пересоздания форенкеев, мы удаляем запись из таблицы АААА и потом восстанавливаем первоначальное состояние форенкеев.

вторник, 19 апреля 2016 г.

Получение значениий параметров для v$sql

Значение параметров для запросов из v$sql можно найти в представлении v$sql_bind_capture.

select *
from v$sql vsql
join v$sql_bind_capture vbce
on vsql.sql_id = vbce.sql_id
/

среда, 27 апреля 2016 г.

вторник, 26 апреля 2016 г.

среда, 20 апреля 2016 г.

вторник, 19 апреля 2016 г.

среда, 27 апреля 2016 г.

вторник, 26 апреля 2016 г.

среда, 20 апреля 2016 г.

вторник, 19 апреля 2016 г.