Oracle SQL


Заливаем дамп с Oracle Enterprise на OracleXE

в создании данного материала мне помог мой коллега Куракин Роман
в данном материале упоминается дамп для одной схемы, но, для нескольких схем, дамп заливается аналогично

1. Cнимаем дамп на источнике
#>expdp schemas=USER1 dumpfile=dampfiledat.dmp

(пользователь sys as sysdba пароль /)

Дамп будет создан в папке
  /ora_home/oracle/admin/orcl/dpdump/


2. Переносим дамп на компьютер, где установлен OraceXE в аналогичную папку
например
c:\oraclexe\app\oracle\admin\XE\dpdump\expdat.dmp для Windows
        или    /u01/app/oracle/admin/XE/dpdump/expdat.dmp для *nix 
систем

3. создаем пользователя на XE
create tablespace U_tbl datafile 'user1.dat' size 300M autoextend on;

create temporary tablespace U_TEMP tempfile 'U_temp.dat' size 600M autoextend on;

create user USER1 identified by password1 default tablespace U_tbl  temporary tablespace U_TEMP;

grant create session to USER1;

grant create table to USER1;

grant unlimited tablespace to USER1;

grant connect to pl;

grant resource to pl;

если необходимо 

grant dba to USER1;


4. Если пользователь уже существует и содержит старые данные то удаляем пользователя и создаем заново, табличное пространство пересоздавать не надо:

* drop user USER1 cascade;


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

#> impdp USER1/password1 schemas=USER1 dumpfile=expdat.dmp transform=SEGMENT_CREATION:N PARTITION_OPTIONS=MERGE
добавлено: 30 янв 17 просмотры: 390, комментарии: 2



Анализ отчетов AWR

Анализ отчетов AWR


1.Что такое AWR отчеты
2. Для чего используются AWR отчеты
3. Построение AWR отчетов
4. Системные представления AWR репозитория отчетов
5. AWR отчет в формате HTML
6. Основные параметры отчетов AWR
7. На что еще следует обратить внимание!

1. Что такое AWR отчеты

Automatic Workload Repository представляет из себя набор внутренних таблиц словаря данных БД Oracle и специальный фоновый процесс MMON, который появился в версии Oracle10g.
Периодически AWR создает статистическую копию (снимок) и сохраняет информацию в таблицах расположенных в табличном пространстве SYSAUX. По умолчанию регулярный период сбора установлен на 60 минут. Это значение может быть уменьшено до 10 минут при желании. Механизм сбора статистической копии (awr snapshot) установлен в базе данных 10G по умолчанию и в отличии от пакета statspack установки на автоматический сбор информации не требуется.

Скрипт формирования AWR заполняет специальный AWR репозиторий набор таблиц и представлений словаря данных Oracle помощью специального системного процесса NMON.


2.Для чего используются AWR отчеты

Automatic Workload Repository (AWR) используется для сбора статистики производительности, включая:

Время ожидания ресурсов базы данных (foreground и background wait events). Эту статистическую информацию можно использовать для первоначального определения 'узких' мест в производительности базы данных
читать дальше...
добавлено: 01 ноя 16 просмотры: 2315, комментарии: 0



Поиск известного значения в неизвестной базе...

Чалышев М.М www.orasource.ru ; резюме

Иногда, в процессе работы ,приходится сталкиваться с задачами,
когда знаешь некоторое значение в базе , например уникальный идентификационный номер,
и необходимо определить в каких таблицах, и каких столбцах базы данное значение расположено

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

То есть, ты видишь на экране условное значение , необходимо разобраться , из каких таблиц и столбцов значение считывается.

Приведу несколько примеров, которые лично мне помогли решить данную задачу.

Подготовим тестовые данные
create table t1(n number);
create table t2(n number);
create table t3(n number);
create table t4(n number);
insert into t1 select level from dual connect by level < 10;
insert into t2 select level from dual connect by level < 20;
insert into t3 select level from dual connect by level < 10;
insert into t4 select level from dual connect by level < 20;
commit;

соберем статистику по таблицам необходимой схемы
begin
 	dbms_stats.gather_schema_stats(ownname => 'TRADE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>FALSE);
end;

читать дальше...
добавлено: 21 окт 15 просмотры: 1686, комментарии: 0



Использование DBMS_METADATA

Чалышев М.М www.orasource.ru ; резюме

Часто в работе возникают ситуации , когда необходимо посмотреть исходный код DLL обьека , а специальных графических средств нет под рукой
в этом нам поможет пакет DBMS_METADATA и метод GET_DDL
DBMS_METADATA.GET_DDL -- входные параметры
object_type - Тип обьека (TABLESPACE, CONSTRAINT , CONSTRAINT, Index)
name - Наименование обьекта например : USERS
schema - схема , по умолчанию схема сессии пользователя или SYS
приведем некоторые примеры

-- извлечь исходный код для табличного пространства USERS
select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual
/*
  CREATE TABLESPACE "USERS" DATAFILE 
  '/path/users01.dbf' SIZE 52000
  AUTOEXTEND ON NEXT 130000 MAXSIZE 32007M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE 
  '/path/users01.dbf' RESIZE 917504000
  */


-- извлечь исходный код для ограничения PK_EMP из схемы SCOTT
select DBMS_METADATA.GET_DDL('CONSTRAINT','PK_EMP','SCOTT') from dual
/*
  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE 
*/


-- извлечь исходный код для таблицы EMP из схемы SCOTT
select DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') from dual
/*
  CREATE TABLE "SCOTT"."EMP" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 */
добавлено: 31 авг 15 просмотры: 1240, комментарии: 0



MODEL. SQL высший пилотаж. Часть 2. Сложные последовательности.

Чалышев М.М www.orasource.ru ; резюме

Используем Model для создания массивов
-- создание одномерного массива
SELECT *
FROM dual
    MODEL DIMENSION BY (0 as т1)
    MEASURES (cast(dummy as varchar2(20)) as ct) 
    RULES (        
        ct[0] = '1',
        ct[1] = '2',       
        ct[1] = '3',
        ct[2] = '4',
        ct[3] = '5'
    ) order by 1;
--Т1	CT
--0	1
--1	3
--2	4
--3	5


читать дальше...
добавлено: 03 июл 15 просмотры: 2572, комментарии: 0



Model. SQL - высший пилотаж , имитация Excel

Чалышев М.М www.orasource.ru ; моё резюме
изучаем SQL группа в контакте - присоединяйтесь

Оператор sql Model позволяет рассматривать результат запроса как многомерный массив
при этом в SQL задаем оси измерения этого массива (идентифицируем данные по осям)
использование Model, так же позволит нам подводить промежуточные и общие итоги, с применением агрегатных функции

SELECT *
FROM table1 -- таблица или запрос
MODEL DIMENSION BY (field1 , field2, ..)--оси, определение осей измерений по которым мы строим массив (поля для поиска уникальной ячейки)
    MEASURES (field3) -- определяющее поле 
    RULES (    cnt['res1', 'res2'] = res3 -- результат который вносится массив
            )  ORDER BY field1; -- сортировка по полю 


итак, нет ничего лучше для понимания сложной SQL конструкции, чем живой пример
продемонстрируем возможности model -- модели

create table pen(prt number, cnt number, color varchar2(15)); 
-- таблица(ручки) где (prt - партия поставки), cnt - количество в данной поставке, color - цвет ручек в поставке (red, green, black) )
-- заполним таблицу 
insert into pen(prt,cnt,color) values(1,5,'red');
insert into pen(prt,cnt,color) values(1,5,'black');
insert into pen(prt,cnt,color) values(2,3,'green');
insert into pen(prt,cnt,color) values(2,1,'red');
insert into pen(prt,cnt,color) values(3,1,'red');
insert into pen(prt,cnt,color) values(4,4,'black');
insert into pen(prt,cnt,color) values(7,3,'red');


определим измерения это color , prt

SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10 -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
            )
ORDER BY prt;
--   	PRT	COLOR	CNT
--	1	black	5
--	1	red	50
--	2	red	10
--	2	green	3
--	3	red	10
--	4	black	4
--	7	red	30

-- добавим итоги
SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        --cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10, -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
        cnt[NULL, 'SUMM RED'] = sum(cnt)[ANY, 'red'], --  итог, только по red
        cnt[NULL, 'SUMM'] = sum(cnt)[ANY, ANY] -- общий итог 
            )
ORDER BY prt;

--     prt color cnt
--	1	red	5
--	1	black	5
--      2	green	3
--	2	red	1
--	3	red	1
--	4	black	4
--	7	red	3
--		SUMM RED	10
--		SUMM	32

читать дальше...
добавлено: 02 июл 15 просмотры: 4274, комментарии: 0



Создание табличной (pipelined) функции с помощью динамического SQL запроса в ORACLE

Чалышев М.М www.orasource.ru ; моё резюме
изучаем SQL группа в контакте - присоединяйтесь

Подари, продай, обменяй - множество недорогих и отличных вещей объявления вместо AVITO - ВКонтакте

Иногда возникает необходимость вернуть результат некоторой функции с на основе запроса сформированного динамически, во время выполнения функции.
Продемонстрируем как это сделать.
Подготовим тестовый запрос - пусть это будет запрос из таблицы all_objects

select o.OBJECT_NAME, o.OBJECT_TYPE from all_objects o
 where upper(object_name) like upper('t%') and rownum < 35


Определим типы возвращаемых данных
читать дальше...
добавлено: 19 июн 15 просмотры: 2421, комментарии: 0



Конструкция MERGE, вставка - обновление

Чалышев М.М www.orasource.ru ; моё резюме
изучаем SQL группа в контакте - присоединяйтесь

Подари, продай, обменяй - множество недорогих и отличных вещей объявления вместо
AVITO - ВКонтакте


Назначение
Позволяет дополнять и обновлять данные одной таблицы - данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется Update, а если нет - Insert. Причем нельзя изменять поля таблицы в секции Update, по которым идет связывание двух таблиц.

Является командой DML!

Синтаксис
MERGE INTO TABLE_NAME 
USING table_reference ON (condition) WHEN MATCHED 
THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED 
THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...) ;

читать дальше...
добавлено: 17 апр 15 просмотры: 20962, комментарии: 0



Аналитический SQL Oracle за 20 минут

Аналитический SQL демонстрирует исключительную производительность, запросы с использованием Аналитического SQL потребляют меньше ресурсов и выполняются быстрее.
Чалышев М.М www.orasource.ru ; моё резюме
изучаем SQL группа в контакте - присоединяйтесь

Применяется в основном для отчетности , для следующих типов задач
1. Запросы рейтингов, первых N
2. Запросы с накопительным итогом
3. Запросы с конструкцией окна
4. Может применятся и для оптимизации запросов

Какие бывают функции в аналитическом, разберём основные
ROW_NUMBER() – номер строки в группе
LAG(f, n,m) –f имя поля, n предыдущее значение в группе, m – по умолчанию
LEAD(f, n,m) -f имя поля, n последующее значение в группе, m – по умолчанию
FIRST_VALUE(f) – f имя поля, первое значение в группе ,
LAST_VALUE(f) –f имя поля, последнее значение в группе
STD_DEV(f) – f имя поля, значение стандартного распределения в группе
SUM(f) – f имя поля, накопительная сумма по группе
AVG (f)– f имя поля, среднее по группе заданной групп
RANK(f) – f имя поля, относительный ранг записи в группе


Синтаксис
SELECT аналитическая функция OVER([PARTITION партицирование…] 
ORDER BY (упорядочивание выражение 2 [,…] [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}]) а


читать дальше...
добавлено: 15 апр 15 просмотры: 27286, комментарии: 2



INSERT ALL - Множественная вставка

Чалышев М.М www.orasource.ru
изучаем SQL группа в контакте - присоединяйтесь
Подари, продай, обменяй - множество недорогих и отличных вещей объявления вместо AVITO - ВКонтакте

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


INSERT ALL  Синтаксис
INSERT ALL INTO <table_name1> VALUES <value_list)
INTO <table_name2> VALUES <value_list>)
...<SELECT Statement>;
Где table_name1, table_name1 таблицы ,  
...<SELECT Statement> - запрос  для вставки данных

Приведем пример
Создадим две таблицы tab1,tab2
create table tab1(id number , val number);

create table tab2(id number , val number);



Используя инструкцию INSERT ALL INTO добавим данные в эти таблицы

insert into tab2 values(9000,9000);
commit;
insert all 
into tab2(id,val) values(1001,1112)
into tab1(id,val) select id,val from tab2 ;
commit;


Второй вариант вставки

insert all 
into tab2(id,val) values(1001,1112)
into tab1(id,val) select id,val from tab2 ;


Дополнительный интерес представляет конструкция insert all с условным выражением приведем пример использования

INSERT ALL WHEN id1 <> 11 THEN INTO tab2
values
  (id1, val1)
  select id as id1, val as val1 from tab1;
добавлено: 09 апр 15 просмотры: 3485, комментарии: 2