Oracle SQL


500 000 !!! ура !

итак, свершилось, на моем блоге 500 000 посещений
Просмотров
Сегодня 503
В этом месяце 14803
Всего 500111
добавлено: 28 мар 17 просмотры: 1029, комментарии: 3



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

Подсчитать количество дней недели в заданном году
то есть сколько было в указанном году понедельников вторников воскресений ...
можно следующим образом :
   select count(1) dwcount,
          to_char(to_date('01/01/1940', 'DD/MM/YYYY') + level - 1, 'DAY') daywe -- 1940 это год , который нам нужен
     from dual
   connect by level <
              to_number(to_char(Trunc(To_Date('1940' + 1, 'YYYY'), 'YYYY') - 1, -- 1940 это год , который нам нужен
                                'ddd')) -- считаем колч дней 
    group by to_char(to_date('01/01/1940', 'DD/MM/YYYY') + level - 1, 'DAY'); -- группируем запрос по дням
добавлено: 20 мар 17 просмотры: 798, комментарии: 0



индекс на вторичном ключе, зачем он нужен? секретные материалы архитектора СУБД

проще всего разобраться в этом на примерах
создадим две таблицы

create table tsp(n number primary key); 
-- заполним таблицу данными
insert into tsp
select level from dual connect by level < 100001;

-- создадим вторую таблицу

create table ts(n number primary key, m number, CONSTRAINT fk_ts
    FOREIGN KEY (m)
    REFERENCES tsp(n));

для этой таблицы существует вторичный ключ от m на поле n таблицы tsp
заполним таблицу ts данными
insert into ts(n,m)
select level,mod(level,90000)+1 from dual connect by level < 1000000;

-- попытаемся удалить строки из этой таблицы
delete tsp where n > 99000

-- операция выполнялась 112 секунд
читать дальше...
добавлено: 13 мар 17 просмотры: 1016, комментарии: 5



Обновление данных WHERE CURRENT OF

Если вы используете для обновления или удаления данных курсор select for update, то использование WHERE CURRENT OF
в этом случае наиболее удобно.
Выражение CURRENT как показано используется после конструкции WHERE и имеет следующий синтаксис
UPDATE имя таблицы
  SET обновляемые поля и значения
  WHERE CURRENT OF имя курсора;

для обновления данных

Рассмотрим несколько примеров:
подготовим тестовые данные
create table tab11 as select * from all_objects where rownum < 25;


обновление данных с использованием WHERE CURRNT OF
declare 
    cursor C is
    select *
      from tab11 t
     FOR UPDATE OF object_name, owner; 
begin
  FOR i in C loop
   update tab11 set object_name = 'update1' , owner = 'OW'  
   where current of C;
  end loop; 
  commit;  
end;


таким образом, мы, обновляем каждую запись нашего курсора заданным значением.
добавлено: 13 мар 17 просмотры: 721, комментарии: 0



простой и элегантный способ вставки записей в таблицу без перечисления полей

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

для демонстрации создадим некоторую таблицу на основе all_objects
 create table s_obj as select * from all_objects where rownum < 5

в данном случае таблица содержит 4 записи
с помощью инструкции PL SQL заполним данную таблицу следующим образом
begin
  for i in (select * from all_objects where rownum < 21) loop 
    i.object_name := 'prefix'||i.owner; --меняем данные перед вставкой
    insert into s_obj values i;
  end loop; 
end;

commit;
проверим
select * from s_obj
SYS	ICOL$ ...
SYS	I_USER1 ...
...
SYS	prefixSYS ...
SYS	prefixSYS ...
...


подобную методику вы сможете применять в своих функциях и процедурах pl sql
добавлено: 13 мар 17 просмотры: 845, комментарии: 0



как размножить строки... cross join и connect by

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

итак , нам необходимо размножить строки в некоторой таблице, на основе заданного запроса
пусть это будет следующий запрос
select object_name from all_objects where object_type = 'CLUSTER'

этот запрос вернет нам 10 строк , по условиям задачи необходимо повторить эти 10 строк по 10 раз

создадим таблицу с размноженными строками , здесь на поможет конструкция cross join и оператор иерархии connect by
вот скрипт создания такой таблицы:
 create table copy1 as select * from 
 (select level lnl from dual connect by level < 11) 
cross join (select object_name from all_objects where object_type = 'CLUSTER') 

где level < 11 количество вставляемых строк

подсчитаем количество строк в данной таблице
 select count(*) from copy1

-- 100 строк, все верно

продемонстрируем работу оператора insert применительно к данной задаче
очистим таблицу
delete from copy1;

произведем вставку
insert into copy1(lnl, object_name) 
select * from (select level lnl from dual connect by level < 11) cross join 
(select object_name from all_objects where object_type = 'CLUSTER')


посмотрим, что получилось
select object_name,count(1) from copy1 group by object_name

успех...
добавлено: 13 мар 17 просмотры: 657, комментарии: 0



Использование типа Timestamp в oracle

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

TIMESTAMP – специальный тип данных в oracle расширяющий возможности типа данных date

Представлен в трех вариантах:
timestamp
Год, месяц, часы , минуты , секунды

timestamp with local time zone
то же что и предыдущий тип данных, но дополнительно включает еще и следующие значения временных зон
time_zone_hour и time_zone_minute или же time_zone_region

timestamp with local time zone
тип данных со значением часового пояса

поля данных типа timestamp

Создадим таблицу c полями указанных типов
create table ttmstamp(t1 timestamp, t2 timestamp with time zone, t3 timestamp with local time zone);


Для работы с типом Timestamp существует специальная функция
CURRENT_TIMESTAMP, которая возвращает текущее время дата со значениями часового пояса заданного в параметрах сессии .
У данной функции нет параметров.
Пример использования
ALTER SESSION SET TIME_ZONE = '-3:0';

select CURRENT_TIMESTAMP from dual;


ALTER SESSION SET TIME_ZONE = '-4:0';
select CURRENT_TIMESTAMP from dual;

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



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

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

GUID некоторая уникальная последовательность символов, в некоторых случаях, может использоваться в качестве первичного ключа.
Рассмотрим основные работы с GUID в ORACLE.

Получить GUID в ORACLE можно, воспользовавшись функцией
sys_guid()
запрос в этом случае будет выглядеть следующим образом
select sys_guid() from dual

результат
4A9B3CF364FB92CAE050A8C0670A0D3A
Для получения GUID в PL SQL используются несколько аналогичная команда
Следует так же отметить, что для ранения GUID в ORACLE используются следующие типы данных
raw(16) и varchar2(32);

следующие примеры демонстрируют работу c GUID в PL/SQL ORACLE
declare
  p_raw raw(16); 
begin
  p_raw := sys_guid;
  dbms_output.put_line(p_raw);
end;

результат 4A9B3CF3650092CAE050A8C0670A0D3A

declare
  p_vc2 varchar2(32);
begin
  p_vc2 := sys_guid;
  dbms_output.put_line(p_vc2);
end;

результат 4A9B3CF3652292CAE050A8C0670A0D3A
читать дальше...
добавлено: 13 мар 17 просмотры: 724, комментарии: 1



Заливаем дамп с 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 USER1;

grant resource to USER1;

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

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 просмотры: 719, комментарии: 4



Анализ отчетов 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 просмотры: 3318, комментарии: 0