пятница, 18 ноября 2011 г.

Запретить пользователям подключаться в базе

RESTRICTED_SESSION_CLAUSE

ENABLE

Specify ENABLE to allow only users with RESTRICTED SESSION system privilege to log on to Oracle Database. Existing sessions are not terminated.



alter system enable restricted session;

select logins from v$instance;

LOGINS
----------
RESTRICTED


DISABLE

Specify DISABLE to reverse the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle Database. This is the default.

alter system disable restricted session;

select logins from v$instance;

LOGINS
----------
ALLOWED

среда, 14 сентября 2011 г.

Alternative quoting mechanism

    Oracle Database 10g introduced an alternative quoting syntax for both SQL and PL/SQL - the so-called alternative quoting mechanism (sometimes know as the user-defined quoting mechanism, or the q-quote syntax). It aims to increase usability when the value itself contains single quote characters. Here is an example in PL/SQL:

v1 varchar2(80) := q'{You can't do that}';
v2 varchar2(80) := q'[You can't do that]';

-- Sample of where
...where Last_Name = q'{O'Brien}'

    The opening q' and the closing ' are non-negotiable; The user chooses the inner parentheses, in this example { and }.

Also characters can be any of the following as long as they do not already appear in the string:
  • !
  • [ ]
  • { }
  • ( )
  • < >

GRANT ANY OBJECT PRIVILEGE

    До Oracle 9 Release 9.2 надо было обращаться от имени учетной записи, чтобы задать привилегии и выполнить операторы DDL для объектов, содержащихся в схеме данных. Начиная с Oracle 9 Release 9.2, привилегия GRANT ANY OBJECT PRIVILEGE (GAOP) позволяет ее обладателю создавать объекты и контролировать привилегии в схемах, к которым он не подключен. Поэтому никому, даже администраторам, не надо будет подключаться к схеме для данных.

Privileges in an Oracle Database

System Privileges

Here are some common system priveleges:
  • ADVISOR
  • ALTER DATABSE
  • ALTER SYSTEM
  • AUDIT SYSTEM
  • CREATE DATABASE LINK
  • CREATE TABLE
  • CREATE ANY INDEX
  • CREATE SESSION
  • CREATE TABLESPACE
  • CREATE USER
  • DROP USER
  • INSERT ANY TABLE
  • SELECT ANY DICTIONARY - This is a system-level privilege that is always active and allows the grantee to create objects. Also, it should be granted to developers to compile a code with a named object, the user must have been granted privileges by direct grant, not through the roles. SELECT ANY DICTIONARY allows select from all SYS owner tables such as TAB$, USER$, etc. This is not possible in the SELECT_CATALOG_ROLE. The SELECT ANY DICTIONARY is a system privilege, which remains active throughout the sessions and allows the user to create stored objects on objects on which it has privileges as a result of the grant (Difference between Select Any Dictionary and Select_Catalog_Role).

Granting All System Privileges

You can grant all system privileges to a user (except SELECT ANY DICTIONARY privilege), by specifying:
GRANT ALL PRIVILEGES TO scott;
ALL PRIVILEGES itself isn't a system privilege - it's a convenient way to grant all privileges in one step. You can revoke all system privileges similary, by using statement:
REVOKE ALL PRIVILEGES FROM scott;

Granting Any Object System Privilege

The GRANT ANY OBJECT PRIVILEGE privilege is a special system privilege that lets the grantee grant(and revoke) object privileges for objects in any schema.

  • A user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant and revoke any object privilege as if he were the actual object owner.
  • A user with the GRANT ANY OBJECT PRIVILEGE system privilege can create and drop any object in any schema as if he were the actual object owner.
  • All users with SYSDBA privilege automatically have the GRANT ANY OBJECT PRIVILEGE privilege.
  • When you connect as sysdba(user SYS), you are automatically granted this role with ADMIN OPTION.

GRANT grant any object privilege TO scott;
REVOKE grant any object privilege FROM scott; 


System Role Privileges

  • SELECT_CATALOG_ROLE - This role grants SELECT privileges on the data dictionary views. This is a role, which is a collection of privileges. A role can be specifically enabled or disabled and this role cannot be used to create any objects (tables).
  • EXECUTE_CATALOG_ROLE - This role grants EXECUTE privileges on packages and procedures in the data dictionary. 
  • DELETE_CATALOG_ROLE - This role enables users to delete records from the audit table, called SYS.AUD$.
You can also use the SELECT ANY DICTIONARY system privilege to grant a user (usually developers, which need compile procedures) the privilege to select data from any object in the SYS schema inside procedures code instead of SELECT_CATALOG_ROLE role, because to compile a code with a named object, the user must have been granted privileges by direct grant, not through the roles (Difference between Select Any Dictionary and Select_Catalog_Role).

The SYSDBA and SYSOPER System Privileges

You can't use with ADMIN OPTION when granting these roles; only a user connected as SYSDBA can grant (or revoke) these privileges to other users; and you can't grant this system privilege to role.
The SYSDBA system privilege includes the RESTRICTED SESSION privilege and has all system privileges with ADMIN OPTION, including the SYSOPER system privilege. The SYSDBA privilege lets you do the following:
  • Perform STARTUP and SHUTDOWN operations
  • Use the ALTER DATABASE command to open, mount, back up, or change a character set
  • Use the CREATE DATABASE command
  • Perform ARCHIVELOG and RECOVERY operations
  • Create an SPFILE
The SYSOPER privilege similarly includes the RESTRICTED SESSION privilege, and it lets you do the following:
  • Perform STARTUP and SHUTDOWN operations
  • Use the ALTER DATABASE command to open, mount, or back up
  • Perform ARCHIVELOG and RECOVERY operations
  • Create an SPFILE

List the system privileges assigned to a user

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee, username AS GRANTED_ROLE
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

воскресенье, 27 марта 2011 г.

Разбираем магический CUBE с помощью GROUPING_ID

    Задача CUBE взять множество группируемых колонок и создать под итоги для всех их возможных комбинаций CUBE. Часто все комбиинации не нужны, а только некоторые из них.

    Не всегда GROUPING SETS сможет получить только те итоги которые вы хотите. Или просто я не смог разобраться в нём.

    Но мне удалось понять функцию GROUPING_ID, для того чтобы выбрать только нужные итоги я строю матрицу, значениями в которой будет 0 - ноль или единица.

    Возьмем следующий пример: запрос в котором будет группировка по региону, городу, супермаркету. Итогом будет количество супермаркетов.

    Пример надуманный, и в нем можно использовать и GROUPING SETS, но задача показать как пользоваться функцией GROUPING_ID, для того чтобы из всего множества итогов, генерируемых CUBE выбирать только нужные.

with 
  test_cube as (
    select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 1' market, 2 cnt from dual
     union all
    select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 2' market, 3 cnt from dual
     union all
    select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 1' market, 1 cnt from dual
     union all
    select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 2' market, 1 cnt from dual
     union all
    select 'Южный регион' region, 'Ялта' city, 'Супермаркет 1' market, 2 cnt from dual
     union all
    select 'Южный регион' region, 'Симферополь' city, 'Супермаркет 2' market, 1 cnt from dual
     union all
    select 'Западный регион' region, 'Львов' city, 'Супермаркет 1' market, 4 cnt from dual
     union all
    select 'Западный регион' region, 'Львов' city, 'Супермаркет 2' market, 2 cnt from dual
     union all
    select 'Западный регион' region, 'Мукачево' city, 'Супермаркет 2' market, 1 cnt from dual
     union all
    select 'Северный регион' region, 'Чернигов' city, 'Супермаркет 1' market, 2 cnt from dual
     union all
    select 'Центральный регион' region, 'Киев' city, 'Супермаркет 1' market, 5 cnt from dual
     union all
    select 'Центральный регион' region, 'Киев' city, 'Супермаркет 2' market, 3 cnt from dual
  )
select grp_id,
       decode(grp_id, 
         0, 'Запись как есть в test_cube',
         1, 'Кол-во в разрезе региона & города',
         2, 'Кол-во в разрезе региона & магазина',
         3, 'Кол-во в каждом регионе',
         4, 'Кол-во в разрезе города & магазина',
         5, 'Кол-во в каждом городе',
         6, 'Кол-во каждого магазина',
         7, 'Кол-во супермаркетов в стране'
       ) grp_descr, 
       region, city, market, sum_cnt
  from (
        select grouping_id(region, city, market) grp_id,
               region, city, market, sum(cnt) sum_cnt
          from test_cube
         group by cube(region, city, market)
       )
 order by 1


    Количество возможных итогов получится (n-1)! - факториал, где n - это количество полей указанных в CUBE, в примере использовано 3 поля (region, city, market), 3! = 8 групп, нумерация групп начинается с нуля. Группа 0 - это записи как есть(не итоговые), а с единицы это вид итога. Если бы в CUBE использовалось 6 полей то вариантов групп было бы 6! = 120.

    Далее выведем только нужные нам итоги, например необходимо отобразить только:
  • 0 - Запись как есть в test_cube
  • 2 - Кол-во в разрезе региона & магазина
  • 5 - Кол-во в каждом городе
  • 6 - Кол-во каждого магазина

    В секции HAVING укажем только нужные нам группы:

        having grouping_id(region, city, market) in (0, 2, 5, 6)


    Но самое главное это понять как вычисляются значения групп функцией grouping_id. Почему для группировки количества магазинов "только по городу" имеет значение 5, а группировка "в разрезе региона & магазина" имеет значение 2?

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

        /* --  --  --  -- */ 
region   -- 0   1   1   1  -- 
city     -- 1   0   1   1  -- 
market   -- 0   1   0   1  -- 
        /* --  --  --  -- */ 
        -- поля по которым нужен итог 
        --   устанавливаем в ноль, 
        --   остальные поля в единицу 
        -- ------------------------------------ 
        -- 3 поля, сверху вниз пишем значения, 
        -- получаем десятичный код: 
        -- 2   5   6   7 
        -- ------------------------------------ 
        -- 2  == 010             - Кол-во в разрезе региона & магазина
        --     5  == 101         - Итог по (Городу) 
        --         6  == 110     - Итог по (Магазину)


    Чтобы вывести итог 2 ("в разрезе региона & магазина") - поле city устанавливаем в единицу, поля region и market в 0.

    Для итога 5 ("только по городу") - поле city устанавливаем в 0, поля region и market в 1.

    В итоге 6 ("только по магазину") - поле market устанавливаем в 0, а остальные поля в 1 (region и city).

Использование матрицы в запросе:

with 
  test_cube as (
    select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 1' market, 2 cnt from dual
     union all
    select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 2' market, 3 cnt from dual
     union all
    select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 1' market, 1 cnt from dual
     union all
    select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 2' market, 1 cnt from dual
     union all
    select 'Южный регион' region, 'Ялта' city, 'Супермаркет 1' market, 2 cnt from dual
     union all
    select 'Южный регион' region, 'Симферополь' city, 'Супермаркет 2' market, 1 cnt from dual
     union all
    select 'Западный регион' region, 'Львов' city, 'Супермаркет 1' market, 4 cnt from dual
     union all
    select 'Западный регион' region, 'Львов' city, 'Супермаркет 2' market, 2 cnt from dual
     union all
    select 'Западный регион' region, 'Мукачево' city, 'Супермаркет 2' market, 1 cnt from dual
     union all
    select 'Северный регион' region, 'Чернигов' city, 'Супермаркет 1' market, 2 cnt from dual
     union all
    select 'Центральный регион' region, 'Киев' city, 'Супермаркет 1' market, 5 cnt from dual
     union all
    select 'Центральный регион' region, 'Киев' city, 'Супермаркет 2' market, 3 cnt from dual
  )
select grp_id,
       decode(grp_id, 
         0, 'Запись как есть в test_cube',
         1, 'Кол-во в разрезе региона & города',
         2, 'Кол-во в разрезе региона & магазина',
         3, 'Кол-во в каждом регионе',
         4, 'Кол-во в разрезе города & магазина',
         5, 'Кол-во в каждом городе',
         6, 'Кол-во каждого магазина',
         7, 'Кол-во супермаркетов в стране'
       ) grp_descr, 
       region, city, market, sum_cnt
  from (
        select grouping_id( /* --  --  --  -- */ 
                 region,    -- 0   1   1   1  -- 
                 city,      -- 1   0   1   1  -- 
                 market     -- 0   1   0   1  -- 
                            /* -------------- */ 
                            -- поля по которым нужен итог 
                            --   устанавливаем в ноль, 
                            --   остальные поля в единицу 
                            -- ------------------------------------ 
                            -- 3 поля, сверху вниз пишем значения, 
                            -- получаем десятичный код: 
                            -- 2   5   6   7 
                            -- ------------------------------------ 
                            -- 2  == 010             - Кол-во в разрезе региона & магазина
                            --     5  == 101         - Итог по (Городу) 
                            --         6  == 110     - Итог по (Магазину)
               ) grp_id,             
               region, city, market, sum(cnt) sum_cnt
          from test_cube
        having grouping_id(region, city, market) in (0, 2, 5, 6) 
         group by cube(region, city, market)
       )
 order by 1;


Результат запроса:

    GRP_ID GRP_DESCR                           REGION             CITY        MARKET           SUM_CNT
---------- ----------------------------------- ------------------ ----------- ------------- ----------
         0 Запись как есть в test_cube         Северный регион    Чернигов    Супермаркет 1          2
         0 Запись как есть в test_cube         Западный регион    Львов       Супермаркет 1          4
         0 Запись как есть в test_cube         Южный регион       Ялта        Супермаркет 1          2
         0 Запись как есть в test_cube         Восточный регион   Луганск     Супермаркет 1          1
         0 Запись как есть в test_cube         Восточный регион   Харьков     Супермаркет 1          2
         0 Запись как есть в test_cube         Центральный регион Киев        Супермаркет 2          3
         0 Запись как есть в test_cube         Южный регион       Симферополь Супермаркет 2          1
         0 Запись как есть в test_cube         Западный регион    Львов       Супермаркет 2          2
         0 Запись как есть в test_cube         Западный регион    Мукачево    Супермаркет 2          1
         0 Запись как есть в test_cube         Восточный регион   Луганск     Супермаркет 2          1
         0 Запись как есть в test_cube         Восточный регион   Харьков     Супермаркет 2          3
         0 Запись как есть в test_cube         Центральный регион Киев        Супермаркет 1          5
         2 Кол-во в разрезе региона & магазина Северный регион                Супермаркет 1          2
         2 Кол-во в разрезе региона & магазина Западный регион                Супермаркет 1          4
         2 Кол-во в разрезе региона & магазина Южный регион                   Супермаркет 1          2
         2 Кол-во в разрезе региона & магазина Восточный регион               Супермаркет 1          3
         2 Кол-во в разрезе региона & магазина Центральный регион             Супермаркет 2          3
         2 Кол-во в разрезе региона & магазина Восточный регион               Супермаркет 2          4
         2 Кол-во в разрезе региона & магазина Западный регион                Супермаркет 2          3
         2 Кол-во в разрезе региона & магазина Южный регион                   Супермаркет 2          1
         2 Кол-во в разрезе региона & магазина Центральный регион             Супермаркет 1          5

    GRP_ID GRP_DESCR                           REGION             CITY        MARKET           SUM_CNT
---------- ----------------------------------- ------------------ ----------- ------------- ----------
         5 Кол-во в каждом городе                                 Мукачево                           1
         5 Кол-во в каждом городе                                 Чернигов                           2
         5 Кол-во в каждом городе                                 Харьков                            5
         5 Кол-во в каждом городе                                 Ялта                               2
         5 Кол-во в каждом городе                                 Львов                              6
         5 Кол-во в каждом городе                                 Луганск                            2
         5 Кол-во в каждом городе                                 Киев                               8
         5 Кол-во в каждом городе                                 Симферополь                        1
         6 Кол-во каждого магазина                                            Супермаркет 1         16
         6 Кол-во каждого магазина                                            Супермаркет 2         11

31 rows selected.


суббота, 26 марта 2011 г.

backup control file to trace

Скрипт созданный в трассировочном файле используется для восстановления контрольного файла после потери всех его копий.

Если база сконфигурирована надлежащим образом (имеет несколько копий управляющего файла на разных дисках и контроллерах), то мало вероятно что придется использовать скрипт из trace-а, тем не менее советуют резервировать управляющий файл с использованием трассировочного файла после каждого изменения физической структуры базы данных (добавление табличного пространства, файлов данных, групп журнальных файлов). Администратор получит историю изменений в текстовом виде, обязательно пригодится.

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

Трассировочные копии управляющего файла могут быть созданы с помощью Enterprice Manager, в Oracle 11g на странице Server, раздел Storage, ссылка Control Files, на закладке General, кнопка Backup To Trace.

Или вручную по команде SQL:


SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


Местоположение создаваемого трассировочного файла, задается параметром инициализации USER_DUMP_DEST. Его имя в формате: sid_ora_pid.trc, где pid - номер серверного процесса.

Сложновато было мне его найти в куче трассировочных файлов генерируемых сервером, использовал поиск в файле по следующим словам:

CREATE CONTROLFILE


Файл избавил от ненужной информации о трассировке событий и отправил в Subversion System.

Oracle 12c


SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/ctl_file.sql' NORESETLOGS;

NORESETLOGS - говорит Oracle записать один SQL оператор в трейс файл. Если не указать NORESETLOGS, то Oracle запишет 2 SQL в трейс файл: один для пересоздания контрол файла с NORESETLOGS опцией и один для пересоздания контрол файла с RESETLOGS.

вторник, 15 марта 2011 г.

How to finding Max SESSION_CACHED_CURSORS in use

Перепечатываю с блога Oracle Logbook


select max(value)
from v$sesstat natural join v$statname
where name = 'session cursor cache count';

-- How to finding maximum session_cached_cursors
select session_cached_cursors * 30+30 Cached_Cursors_Rounded,
       sessions_count
  from (
        select trunc(value/30)  SESSION_CACHED_CURSORS,
               count(*) sessions_count
          from V$sesstat natural join v$statname
         where name = 'session cursor cache count'
         group by trunc(value/30) order by 1
       );

CACHED_CURSORS_ROUNDED SESSIONS_COUNT 
---------------------- -------------- 
                    30            223     
                    60             32     
                    90             15     
                   120              6     
                   150              2     
                   180              3     
                   210              5     

7 rows selected.


В последней строке результата значение 210 это именно то значение в которое необходимо установить параметр SESSION_CACHED_CURSORS.

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

Версия для Oracle 11g с использованием LISTAGG, отображает какие usernames сколько курсоров используют:
set linesize 254
col sessions format a30
col usernames format a70

select session_cached_cursors * 30+30 Cached_Cursors_Rounded,
       sessions_count, 
       sessions, 
       usernames
  from (
        select SESSION_CACHED_CURSORS,
               sum(sessions_count) sessions_count,               
               LISTAGG(sessions, ',') WITHIN GROUP (ORDER BY sessions) sessions,               
               LISTAGG(username || '(' || user_cnt || ')', ',') WITHIN GROUP (ORDER BY sessions) usernames
          from (
                select trunc(value/30)  SESSION_CACHED_CURSORS,
                       count(*) sessions_count,                        
                       username,
                       LISTAGG(sid, ',') WITHIN GROUP (ORDER BY sid) sessions,
                       count(username) user_cnt 
                       --LISTAGG(username, ',') WITHIN GROUP (ORDER BY sid) usernames 
                  from v$sesstat ss natural join v$statname sn 
                  natural join v$session s 
                 where name = 'session cursor cache count' 
                 group by trunc(value/30), username
               )
         group by SESSION_CACHED_CURSORS
       )
 order by SESSION_CACHED_CURSORS;

Apply new SESSION_CACHED_CURSORS

SQL> alter system set session_cached_cursors=210 scope = spfile;
shutdown immediate;
startup;

понедельник, 14 марта 2011 г.

Duplicate control file (Дублирование управляющего файла)

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

Сначала изменим SPFILE:

SQL> ALTER SYSTEM SET control_files =
'$HOME/ORADATA/u01/ctrl01.ctl',
'$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;


Остановим БД в нормальном режиме:

SQL> shutdown


Затем создаем копии управляющего файла:

$ cp $HOME/ORADATA/u01/ctrl01.ctl
     $HOME/ORADATA/u02/ctrl02.ctl


И запускаем базу данных:

SQL> startup

воскресенье, 6 марта 2011 г.

Generation date range with specified increments

Запрос "Генерация диапазона дат с указанным приращением" мною используется когда надо сделать срезы итоговых данных с периодом в сутки или ежечасно. Другой вариант применения когда недостаточно данных в итоговых таблицах, например там хранится запись изменения состояния на определенную дату, а необходимо чтобы это состояние было на все даты до смены этого состояние на следующее значение.

Итак есть начало диапазона - date_begin, окончание диапазона - date_end (включая/не включая ее) и приращение - increase. Также был добавлен признак необходимости включения/исключения в/из диапазон(а) окончания диапазона (date_end), назовём его include_date_end.

Допустимые значения для include_date_end:
  • 0 - не включать date_end в последовательность;
  • 1 - включать

Представим всё это в виде запроса:

     select 
            to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
            to_date('02.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
            1/24 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
            0 include_date_end -- 0 - не включать date_end; 1 - включать
       from dual 


Для генерации диапазона используем возможности запроса connect by:

select level
  from dual
  connect by
    0 + level <= 7


Преобразуем запрос под наши нужды, используя with для удобства:

 -- Generation date range with specified increments
 -- increase 1/24 - один час
 -- 0 include_date_end - не включать date_end
with 
  range_vars as ( 
    select 
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
           to_date('02.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
           1/24 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
           0 include_date_end -- 0 - не включать date_end; 1 - включать
      from dual 
  ),
  range_dates as (
    select 
           date_begin + (level * increase)  - increase date_increase 
      from range_vars 
      connect by 
        date_begin + (level * increase) <= date_end + case include_date_end 
                                                        when 1 then increase
                                                        else 0
                                                      end 
  ) 
select to_char(date_increase, 'DD.MM.YYYY HH24:MI:SS') date_increase
  from range_dates


Полученный выше запрос генерирует диапазон дат между 01 октября 2010 года и 02 октября 2010 года с приращение в один час(1/24). Как видно из результата date_end не включена в диапазон:

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
01.10.2010 01:00:00
01.10.2010 02:00:00
01.10.2010 03:00:00
01.10.2010 04:00:00
01.10.2010 05:00:00
01.10.2010 06:00:00
01.10.2010 07:00:00
01.10.2010 08:00:00
01.10.2010 09:00:00
01.10.2010 10:00:00
01.10.2010 11:00:00
01.10.2010 12:00:00
01.10.2010 13:00:00
01.10.2010 14:00:00
01.10.2010 15:00:00
01.10.2010 16:00:00
01.10.2010 17:00:00
01.10.2010 18:00:00
01.10.2010 19:00:00
01.10.2010 20:00:00

DATE_INCREASE      
-------------------
01.10.2010 21:00:00
01.10.2010 22:00:00
01.10.2010 23:00:00

24 rows selected.


Можно указывать любое приращение, вот генерация диапазона по дням (включая date_end):

-- Generation date range with specified increments
 -- increase 1 - одни сутки
 -- 1 include_date_end - включать date_end
with 
  range_vars as ( 
    select 
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
           to_date('11.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
           1 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
           1 include_date_end -- 0 - не включать date_end; 1 - включать
      from dual 
  ),
  range_dates as (
    select 
           date_begin + (level * increase)  - increase date_increase 
      from range_vars 
      connect by 
        date_begin + (level * increase) <= date_end + case include_date_end 
                                                        when 1 then increase
                                                        else 0
                                                      end 
  ) 
select to_char(date_increase, 'DD.MM.YYYY HH24:MI:SS') date_increase
  from range_dates;

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
02.10.2010 00:00:00
03.10.2010 00:00:00
04.10.2010 00:00:00
05.10.2010 00:00:00
06.10.2010 00:00:00
07.10.2010 00:00:00
08.10.2010 00:00:00
09.10.2010 00:00:00
10.10.2010 00:00:00
11.10.2010 00:00:00

11 rows selected.


И на закуску для удобства использования создана pipelined function:

create or replace type t_date_table is table of date;
/

Type created.

create or replace function get_range_dates(
  p_date_begin date, p_date_end date,
  p_increase number, p_include_date_end number
) return t_date_table pipelined
as
begin
  for rec in (
    -- Generation date range with specified increments
    with 
      range_vars as ( 
        select 
               p_date_begin date_begin, 
               p_date_end date_end, 
               p_increase increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
               p_include_date_end include_date_end -- 0 - не включать date_end; 1 - включать
          from dual 
      ),
      range_dates as (
        select 
               date_begin + (level * increase)  - increase date_increase 
          from range_vars 
          connect by 
            date_begin + (level * increase) <= date_end + case include_date_end 
                                                            when 1 then increase
                                                            else 0
                                                          end 
      ) 
    select date_increase
      from range_dates
  ) loop
    pipe row (rec.date_increase);
  end loop;
      
  return;
end;
/

Function created.

-- Generation date range with specified increments
-- increase 1/2 - полдня
-- 1 include_date_end - включать date_end
select to_char(column_value, 'DD.MM.YYYY HH24:MI:SS') date_increase 
  from table(
         get_range_dates(
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS'), -- date_begin 
           to_date('05.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS'), -- date_end 
           1/2, -- increase полдня
           1 -- include_date_end
         )
       );

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
01.10.2010 12:00:00
02.10.2010 00:00:00
02.10.2010 12:00:00
03.10.2010 00:00:00
03.10.2010 12:00:00
04.10.2010 00:00:00
04.10.2010 12:00:00
05.10.2010 00:00:00

9 rows selected.