,

Использование RECYCLE BIN в ORACLE DB

вторник, 9 июня 2009 г. 0 коммент.

В 10 версии добавилась возможность востанавливать удаленные таблицы и индексы.

Для того что бы включить\выключить корзину:



-- Отключить корзину для сессии
ALTER SESSION SET RECYCLEBIN=OFF;
-- Отключить корзину
ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;

-- Включить корзину для сессии
ALTER SESSION SET RECYCLEBIN=ON;
-- Включить корзину
ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;



По умолчанию корзина включена.

Чтобы посмотреть удаленные объеты можно воспользовать командой:


-- выбор удаленных объектов пользователя
select *
from user_recyclebin;
-- выбор всех удаленных объектов
select *
from dba_recyclebin;


Чтобы удалить объеты из корзины:


-- удалить все реинкарнации table1 из корзины
purge table table1;
-- удалить table1 из корзины по системному имени
purge table "BIN$0Vq5kNlvTS6G/uyKOlzdAw==$0";
-- удалить все реинкарнации index1 из корзины
purge table table1;
-- удалить index1 из корзины по системному имени
purge table "BIN$0Vq5kNlvSD6G/uyKOlzdAw==$0";
-- удалить все объекты пользователя "FK" из тайблспейса ts_fk.
purge tablespace ts_fk user fk;
-- удалить все объекты из тайблспейса ts_fk.
purge tablespace ts_fk;
-- удалить все объекты из корзины текущего пользователя
purge recyclebin;
-- удалить все объекты из корзины, включая системные объеткты
purge dba_recyclebin;


Подробнее о команде PURGE можно посмотреть здесь: Oracle® Database SQL Reference 10g Release 2 (10.2) PURGE

Для восстановления удаленных таблиц нужно воспользоваться командами:


-- востановить последению удаленную версию table1
flashback table table1 to before drop;
-- востановить УКАЗАННУЮ удаленнную версию table1
flashback table "BIN$CxaooGNESZa4GThzDSX5SQ==$0" to before drop;



Подробнее о корзине можно почитать тут: Using Oracle's recycle bin

Читать полностью

,

Иерархический xml в ORACLE DB

пятница, 5 июня 2009 г. 0 коммент.

Обычно функций xmlelement, xmlagg, xmlattributes и других подобных хватает на все случаи жизни. Но тут понадобилось создать иерархический xml на основе существующий иерархии.

Возьмем для примера существующую иерархию из схемы SCOTT:


select emp.empno, emp.ename, emp.job
from emp
start with emp.mgr is null
connect by prior emp.empno = emp.mgr;

Для построения иерархии воспользуеся пакетом DBMS_XMLGEN. Примеры использования можно посмотреть здесь: Generating XML Using DBMS_XMLGEN. Для этого создадим функцию.

create or replace package body xml_api is

function get_xml_hierarchy( ps_sql varchar2
) return xmltype
is
qryctx dbms_xmlgen.ctxhandle;
vclob clob;
vresult xmltype;
begin
qryctx := dbms_xmlgen.newcontextFromHierarchy(ps_sql);
vclob := dbms_xmlgen.getxml(qryctx);
vresult := xmltype(vclob);
dbms_xmlgen.closecontext(qryctx);
return vresult;
end get_xml_hierarchy;

end xml_api;

Функция использует функцию dbms_xmlgen.newcontextFromHierarchy, которая принимает один параметр, это иерархический запрос. Для этого запроса есть несколько условий:

  1. еще раз, это должен быть иерархический запрос;

  2. результат запроса должен состоять из 2 колонок;

  3. первая колонка должна быть уровнем, LEVEL

  4. вторая колонка должна быть типа xmltype;


Строим xml:

select xml_api.get_xml_hierarchy(
'
select level,
xmlelement(employee,
xmlattributes(emp.empno, emp.ename, emp.job)) as xmlelem
from emp
start with emp.mgr is null
connect by prior emp.empno = emp.mgr
')
from dual;

И смотрим результат:

<?xml version="1.0"?>
<EMPLOYEE EMPNO="7839" ENAME="KING" JOB="PRESIDENT">
<EMPLOYEE EMPNO="7566" ENAME="JONES" JOB="MANAGER">
<EMPLOYEE EMPNO="7788" ENAME="SCOTT" JOB="ANALYST">
<EMPLOYEE EMPNO="7876" ENAME="ADAMS" JOB="CLERK"/>
</EMPLOYEE>
<EMPLOYEE EMPNO="7902" ENAME="FORD" JOB="ANALYST">
<EMPLOYEE EMPNO="7369" ENAME="SMITH" JOB="CLERK"/>
</EMPLOYEE>
</EMPLOYEE>
<EMPLOYEE EMPNO="7698" ENAME="BLAKE" JOB="MANAGER">
<EMPLOYEE EMPNO="7499" ENAME="ALLEN" JOB="SALESMAN"/>
<EMPLOYEE EMPNO="7521" ENAME="WARD" JOB="SALESMAN"/>
<EMPLOYEE EMPNO="7654" ENAME="MARTIN" JOB="SALESMAN"/>
<EMPLOYEE EMPNO="7844" ENAME="TURNER" JOB="SALESMAN"/>
<EMPLOYEE EMPNO="7900" ENAME="JAMES" JOB="CLERK"/>
</EMPLOYEE>
<EMPLOYEE EMPNO="7782" ENAME="CLARK" JOB="MANAGER">
<EMPLOYEE EMPNO="7934" ENAME="MILLER" JOB="CLERK"/>
</EMPLOYEE>
</EMPLOYEE>
Читать полностью

, ,

Формирование заголовок для XML в ORACLE DB

Столкнулся с проблемой, что в ORACLE DB нет "правильной" функции для формирования xml заголовка. Кажется в 10.2 появилась функция XMLRoot , но в фукции нельзя указать кодировку xml, а хотелось бы добавить заголовок подобного типа.


<?xml version="1.0" encoding="windows-1251"?>

Заголовок можно конкатенировать к xml, но существуют ограничения по длине в 32K.
Поэтому пришлось написать свою функцию для добавления заголовка.

create or replace package body xml_api is

function add_xml_root( ps_root varchar2
,pcl_body clob
) return clob
is
vcl_temp clob;
begin
dbms_lob.createtemporary(vcl_temp, true, dbms_lob.session);

dbms_lob.open(vcl_temp, DBMS_LOB.LOB_READWRITE);
dbms_lob.writeappend(vcl_temp, length(ps_root), ps_root);
dbms_lob.append(vcl_temp, pcl_body);
dbms_lob.close(vcl_temp);

return vcl_temp;
end add_xml_root;

end xml_api;

Посмотрим результат на данных их схемы SCOTT.

select xml_api.add_xml_root('<?xml version="1.0" encoding="windows-1251"?>',
xmlelement(employee_list,
xmlagg(xmlelement(employee,
xmlattributes(emp.empno, emp.ename)))).getclobval())
from emp;



<?xml version="1.0" encoding="windows-1251"?>
<EMPLOYEE_LIST>
<EMPLOYEE EMPNO="7369" ENAME="SMITH"/>
<EMPLOYEE EMPNO="7499" ENAME="ALLEN"/>
<EMPLOYEE EMPNO="7521" ENAME="WARD"/>
<EMPLOYEE EMPNO="7566" ENAME="JONES"/>
<EMPLOYEE EMPNO="7654" ENAME="MARTIN"/>
<EMPLOYEE EMPNO="7698" ENAME="BLAKE"/>
<EMPLOYEE EMPNO="7782" ENAME="CLARK"/>
<EMPLOYEE EMPNO="7788" ENAME="SCOTT"/>
<EMPLOYEE EMPNO="7839" ENAME="KING"/>
<EMPLOYEE EMPNO="7844" ENAME="TURNER"/>
<EMPLOYEE EMPNO="7876" ENAME="ADAMS"/>
<EMPLOYEE EMPNO="7900" ENAME="JAMES"/>
<EMPLOYEE EMPNO="7902" ENAME="FORD"/>
<EMPLOYEE EMPNO="7934" ENAME="MILLER"/>
</EMPLOYEE_LIST>
Читать полностью