Oracle Patterns

DatabaseObjects - Indexes - create_index_during_execution.pl

#
#   This script belongs to create_index_during_execution.plsql, go there
#   for a little more information.
#
use warnings;
use strict;

# https://github.com/ReneNyffenegger/OracleTool

use lib 'C:\github\OracleTool';
use OracleTool qw(connect_db);

my $connection_string = shift;

my $dbh = connect_db($connection_string);

# $dbh->do("declare s number; begin s:=sys.dbms_pipe.create_pipe('index_during_exec'); end;");

my $sth = $dbh -> prepare (qq{

  declare
    s integer;
  begin
    s := sys.dbms_pipe.receive_message('index_during_exec');

    if s = 0 then 
      dbms_pipe.unpack_message(:msg); 
    end if; 

  end;

});

my $msg = ' ' x 200;

$sth -> bind_param_inout(':msg', \$msg, 200, {TYPE=>DBI::SQL_VARCHAR});
$sth -> execute;

print "Msg recevied: $msg\n";

if ($msg eq 'create index now') {
  $dbh -> do ('create index tq84_1000000_ix on tq84_1000000(text)');
  print "Index built\n";
}

$dbh -> rollback;

DatabaseObjects - Indexes - create_index_during_execution.plsql

--
--  This file and the perl script create_index_during_execution.pl is
--  used to demonstrated that an index can be built while a PL/SQL
--  package accesses a table on which the index should be used.
--
--  First, the table tq84_1000000 is createed and filled with 1000000
--  records. Then the package tq84_1000000 iterates 51 times and
--  selects a specific record from the table. After 4 or so selects
--  it writes into a pipe that the index should now be built. This
--  is the perl script's part: it waits for the message on the pipe
--  and as soon as the message arrives, it creates the index.
--
--  While the plsql package iterates and selects from the table,
--  it prints (dbms_output) how long it took to select for each
--  statement. These times should go down dramatically as soon
--  as the index is built.
--
--  Start the perl script first, then run this file in SQL*Plus.
--
create table tq84_1000000 (
  num    number,
  text   varchar2(1000)
);


begin

  for i in 1 .. 1000000 loop

      insert into tq84_1000000 values (
        i,
        to_char(date '-4712-01-01' + i-1, 'jsp')
      );

  end loop;

end;
/

commit;

-- select count(*) from tq84_1000000;
-- select * from tq84_1000000 where num = 555555;

create package tq84_index_during_exec as

    procedure main;

end tq84_index_during_exec;
/


create package body tq84_index_during_exec as

   procedure main is
       v_num number;

       v_start_time number;

   begin

       for i in 0 .. 50 loop

           
           v_start_time := dbms_utility.get_time;

           select num into v_num
             from tq84_1000000
            where text = 'five hundred fifty-five thousand five hundred fifty-five';

           dbms_output.put_line('select took: ' || (dbms_utility.get_time - v_start_time) / 100 || ' secs');

           if i = 3 then

              declare
                status number;
              begin
                sys.dbms_pipe.pack_message('create index now');
                status := sys.dbms_pipe.send_message('index_during_exec');
              end;

           end if;

       end loop;

   end main;

end tq84_index_during_exec;
/

show errors


set timing on

exec tq84_index_during_exec.main

set timing off


drop package tq84_index_during_exec;

--drop index tq84_1000000_ix;
drop table tq84_1000000 purge;

DatabaseObjects - Indexes - foreign-key.sql

drop table tq84_c purge;
drop table tq84_p purge;

create table tq84_p (
  col_ix_1 varchar2(10) not null,
  col_ix_2 varchar2(10)     null,
  col_data varchar2(10),
  --
  constraint tq84_p_ix unique (col_ix_1, col_ix_2)
);

create table tq84_c (
  id  number,
  col_fk_1 not null,
  col_fk_2     null,
  -- ---------------------------------------------------------------------------------------
  -- Foreign keys can be created to unique indexes (instead of primary keys):
  --
  constraint tq84_fk foreign key (col_fk_1, col_fk_2) references tq84_p (col_ix_1, col_ix_2)
  -- ---------------------------------------------------------------------------------------
);

desc tq84_c;
-- Name     Null     Typ          
-- -------- -------- ------------ 
-- ID                NUMBER       
-- COL_FK_1 NOT NULL VARCHAR2(10) 
-- COL_FK_2          VARCHAR2(10)
--

insert into tq84_p values ('foo' , 'bar',    'baz'   );
insert into tq84_p values ('null',  null,    'null'  );
insert into tq84_p values ('null', 'null', '''null''');

select * from tq84_p;

insert into tq84_c values (1, 'foo' , 'bar');
insert into tq84_c values (2, 'null', 'null');
insert into tq84_c values (3, 'null',  null);

-- ----------------------------------------------------------------
-- This record cannot be inserted because the values 'bla', 'bla'
-- don't exist in the parent table:
--
insert into tq84_c values (4, 'bla' ,  'bla');

-- ----------------------------------------------------------------
-- However, this record CAN be inserted because null values are
-- not checked:
--
insert into tq84_c values (5, 'bla' ,  null);

DatabaseObjects - Indexes - global_partitioned_index.sql

create table tq84_table (
  id  number,
  txt varchar2(10),
  dt  date
);


create index tq84_global_partitioned_index on tq84_table (dt)
       global partition by range(dt) (
              partition tq84_ix_2010 values less than ( date '2011-01-01' ),
              partition tq84_ix_2011 values less than ( date '2012-01-01' ), 
              partition tq84_ix_2012 values less than ( date '2013-01-01' ),
              partition tq84_ix_9999 values less than (       maxvalue   ) 
       );


select
  index_name,
  index_type,
  partitioned
from
  user_indexes
where
  table_name = 'TQ84_TABLE';

--

select
  partition_name
from
  user_ind_partitions
where
  index_name = 'TQ84_GLOBAL_PARTITIONED_INDEX';



drop table tq84_table purge;

DatabaseObjects - Indexes - own-namespace.sql

--
-- The name of an index is in a different namespace thant
-- the table. So, the following two statements
-- cause no error:
--

create table tq84_foo (a number, b number);
create index tq84_foo on tq84_foo(a);

DatabaseObjects - Indexes - partial-index.sql

drop   table tq84_partial_index_table purge;
create table tq84_partial_index_table (
  id     number primary key,
  part   varchar2( 3) not null check (part in ('foo', 'bar', 'baz')),
  col_1  varchar2(10) not null,
  col_2  varchar2(10)
)
partition by list (part) (
  partition tq84_partial_index_part_foo values ('foo') indexing on,
  partition tq84_partial_index_part_bar values ('bar') indexing off,
  partition tq84_partial_index_part_baz values ('baz') indexing on
);

create index tq84_ix_partial_index
    on tq84_partial_index_table(col_1)
    INDEXING PARTIAL local
;

insert into tq84_partial_index_table values (1, 'foo', 'abc', 'def');
insert into tq84_partial_index_table values (2, 'bar', 'ghi', 'jkl');
insert into tq84_partial_index_table values (3, 'baz', 'mno', 'pqr');

select
  partition_name,
  status
from
  user_ind_partitions
where
  index_name = 'TQ84_IX_PARTIAL_INDEX';

DatabaseObjects - Indexes - unique_indexes_null_values.sql

create table tq84_ix (
  col_1   number,
  col_2   number,
  constraint tq84_ix_uq unique (col_1, col_2)
);


insert into tq84_ix values (    1,    1 );
insert into tq84_ix values (    1,    2 );
insert into tq84_ix values (    1, null );
insert into tq84_ix values (    2, null );

insert into tq84_ix values ( null, null );
insert into tq84_ix values ( null, null );
insert into tq84_ix values ( null, null );
insert into tq84_ix values ( null, null );

insert into tq84_ix values ( null,    1 );
insert into tq84_ix values ( null,    2 );

-- insert into tq84_ix values ( null,    2 );  -- unique constraint (META.TQ84_IX_UQ) violated
-- insert into tq84_ix values (    1, null );  -- unique constraint (META.TQ84_IX_UQ) violated

drop table tq84_ix;

DatabaseObjects - Tables - AlterTable - add_foreign_key.sql

create table tq84_table_with_pk (
       col_1 number primary key,
       col_2 number
);

create table tq84_table_for_fk (
       col_3 number,
       col_4 number
);



alter table tq84_table_for_fk 
  add constraint tq84_FK foreign key
     (col_3) references tq84_table_with_pk;


select
  table_name,
  r_constraint_name
from
  user_constraints
where
  constraint_name = 'TQ84_FK';


drop table tq84_table_for_fk  purge;
drop table tq84_table_with_pk purge;

DatabaseObjects - Tables - AlterTable - drop_column.sql

create table tq84_table (
  col_1 number,
  col_2 date,
  col_3 varchar2(10),
  col_4 varchar2(10)
);

insert into tq84_table values (1, sysdate, 'x', 'y');

alter table tq84_table drop column col_1;
alter table tq84_table drop (col_2, col_3);

desc tq84_table;

select * from tq84_table;

drop table tq84_table purge;

DatabaseObjects - Tables - AlterTable - mark_column_unused.sql

create table tq84_table (
  col_1 number,
  col_2 date,
  col_3 varchar2(10),
  col_4 varchar2(10)
);


alter table tq84_table set unused (col_2, col_3);

desc tq84_table;

select table_name, count from user_unused_col_tabs where table_name = 'TQ84_TABLE';

alter table tq84_table drop unused columns /* checkpoint 1000000 */ ;

select * from user_unused_col_tabs where table_name = 'TQ84_TABLE';

drop table tq84_table purge;

DatabaseObjects - Tables - AlterTable - read_only.sql

create table tq84_table (
  col_1 number,
  col_2 date
);

insert into tq84_table values (1, sysdate);

alter table tq84_table read only;

-- ORA-12081: update operation not allowed on table:
insert into tq84_table values (2, null);

drop table tq84_table purge;

DatabaseObjects - Tables - AttributeClustering - interleaved-ordering-01.sql

create table tq84_dimension (
  id      number primary key,
  flag_1  varchar2(2) not null,
  flag_2  varchar2(2) not null,
  col_1   varchar2(20),
  col_2   varchar2(20),
  col_3   varchar2(50)
)
clustering
  by interleaved order (
    flag_1,
    flag_2
  );


create table tq84_fact (
  id           number primary key,
  dimension_id not null references tq84_dimension,
  fact_1       varchar2(50),
  fact_2       varchar2(50),
  fact_3       varchar2(50)
)
clustering
  tq84_fact join tq84_dimension on (tq84_fact.dimension_id = tq84_dimension.id)
  by interleaved order (
    tq84_dimension.col_1,
    tq84_dimension.col_2,
    tq84_fact.fact_1,
    tq84_fact.fact_2
);
  

select
  table_name,
  clustering_type,
  on_load,
  on_datamovement,
  with_zonemap
from
  user_clustering_tables
where
  table_name like 'TQ84_%';


drop table tq84_fact      purge;
drop table tq84_dimension purge;

DatabaseObjects - Tables - AttributeClustering - linear-ordering-01.sql

create table tq84_dimension (
  id      number primary key,
  flag_1  varchar2(2) not null,
  flag_2  varchar2(2) not null,
  col_1   varchar2(20),
  col_2   varchar2(20),
  col_3   varchar2(50)
)
clustering
  by linear order (
    flag_1,
    flag_2
  );


create table tq84_fact (
  id           number primary key,
  dimension_id not null references tq84_dimension,
  fact_1       varchar2(50),
  fact_2       varchar2(50),
  fact_3       varchar2(50)
)
clustering
  tq84_fact join tq84_dimension on (tq84_fact.dimension_id = tq84_dimension.id)
  by linear order (
    tq84_dimension.col_1,
    tq84_dimension.col_2,
    tq84_fact.fact_1,
    tq84_fact.fact_2
);
  

select
  table_name,
  clustering_type,
  on_load,
  on_datamovement,
  with_zonemap
from
  user_clustering_tables
where
  table_name like 'TQ84_%';


drop table tq84_fact      purge;
drop table tq84_dimension purge;

DatabaseObjects - Tables - AttributeClustering - show-reduction-in-used-db-blocks.sql

set timing on

drop table tq84_stage purge;
drop table tq84_clustering;

create table tq84_stage as
select
  c                      flag,
  lpad('x', 1000, 'x')   data_1,
  lpad('x', 1000, 'y')   data_2
from (
  select
    chr(ascii('m') + 2.7 * dbms_random.normal) c
  from
    dual
  connect by level < 100000
)
where
  c between 'a' and 'z';

commit;
  

create table tq84_clustering (
  flag   varchar2(   1) not null,
  data_1 varchar2(1000) not null,
  data_2 varchar2(1000) not null
)
clustering by linear order(flag)
  yes on load
  yes on data movement
;

insert /*+ append clustering */ into tq84_clustering
select * from tq84_stage;
commit;

select
  tab,
--max_block_no,
--min_block_no,
  max_block_no - min_block_no   diff_block_no,
  cnt_dist_block_no,
  max_rel_fno,
  min_rel_fno
from (
  select
   'stage'                                        as      tab,
    min  (         dbms_rowid.rowid_relative_fno (rowid)) min_rel_fno,
    max  (         dbms_rowid.rowid_relative_fno (rowid)) max_rel_fno,
    min  (         dbms_rowid.rowid_block_number (rowid)) min_block_no,
    max  (         dbms_rowid.rowid_block_number (rowid)) max_block_no,
    count(distinct dbms_rowid.rowid_block_number (rowid)) cnt_dist_block_no
  from
    tq84_stage
  where
    flag = 'g'
union all
  select
   'clustering'                                  as       tab,
    min  (         dbms_rowid.rowid_relative_fno (rowid)) min_rel_fno,
    max  (         dbms_rowid.rowid_relative_fno (rowid)) max_rel_fno,
    min  (         dbms_rowid.rowid_block_number (rowid)) min_block_no,
    max  (         dbms_rowid.rowid_block_number (rowid)) max_block_no,
    count(distinct dbms_rowid.rowid_block_number (rowid)) cnt_dist_block_no
  from
    tq84_clustering
  where
    flag = 'g'
);

DatabaseObjects - Tables - AttributeClustering - user_tables.sql

create table tq84_clustering (
  id      number primary key,
  flag_1  varchar2(2) not null,
  flag_2  varchar2(2) not null,
  col_1   varchar2(20),
  col_2   varchar2(20),
  col_3   varchar2(50)
)
clustering
  by linear order (
    flag_1,
    flag_2
  );
  
select * from user_tables where clustering = 'YES';

drop table tq84_clustering;

DatabaseObjects - Tables - default-values - example-01.sql

create sequence tq84_seq start with 1 increment by 1;

create table tq84_tab (
--                     clause           expression
-- ----- ------------  ---------------  ----------------  -----------
   id    number        default          tq84_seq.nextval  primary key,
   txt_1 varchar2(10)                                     not null,
   txt_2 varchar2(10)  default on null 'n/a'              not null
);

insert into tq84_tab (txt_1, txt_2) values ('one', 'foo');
insert into tq84_tab (txt_1, txt_2) values ('two', 'bar');
insert into tq84_tab (txt_1       ) values ('three');

select * from tq84_tab;
-- 
--         ID TXT_1      TXT_2
-- ---------- ---------- ----------
--          1 one        foo
--          2 two        bar
--          3 three      n/a

drop table    tq84_tab;
drop sequence tq84_seq;

DatabaseObjects - Tables - Heap - create_heap_table.sql

create table tq84_heap_table
-- { relational properties
   (
     -- { column definitions
        col_1    number,
        col_2    varchar2(20),
        col_3    date,
        col_4    number,
     -- }
     -- { virtual column definitions
                 -- According to tahiti, virtual columns are
                 -- only possible in heap tables (at least in 11.2)
        col_5    as (col_1 + col_4),
     -- }
     -- { out of line constraint
        constraint tq84_heap_table_pk primary key (col_1)
        -- { constraint state
           not deferrable       -- or deferrable
           initially immediate  -- or initially deferred
           norely               -- or rely
           -- { using index clause
              using index
                --  SCHEMANAME.INDEXNAME    or
                -- (create index statement) or
                -- { index properties
                   online
                   tablespace users
                   -- { index attributes
                      -- { physical attributes clause
                      -- }
                      -- { logging clause
                      -- }
                   -- }
                -- }
                sort         -- or no_sort
            --  reverse
                visible      -- or invisible
                -- { parallel clause
                -- }
           -- }
           enable               -- or disable
           novalidate           -- or validate
           -- { exceptions clause
           -- }
        -- }
        ,
        constraint tq84_heap_table_fk foreign key (col_4) references tq84_heap_table
     -- }
     -- { supplemental logging props
     -- }
   )
-- }
-- { physical properties
   -- { deferred segment creation
      segment creation immediate /* or deferred */
   -- }
   ORGANIZATION HEAP     -- This is the default.
   -- { segment attributes clause
      -- { physical attributes clause
--       pctfree  10
--       pctused  40
         initrans  2
         -- { storage clause
            storage (
              initial     10M
              next        10M
              maxextents  unlimited
              -- { maxsize clause
              -- }
              pctincrease 0
              -- freelists
              -- freelist groups
              -- optimal
              -- buffer_pool
              -- flash_cache
              -- encrypt
            )
         -- }
      -- }
      tablespace users
      -- { logging clause
         logging
      -- }
   -- }
   -- { table compression
          compress basic
      --  compress for oltp
      --  compress for archive /* high */ low  /* ORA-64307 */
      --  compress for query high /* low */
   -- }
-- }
-- { table  properties
   -- { column properties
      -- { object type col properties
      -- }
      -- { nested table col properties
      -- }
      -- { varray col properties
         --  (lob partition storage)
      -- }
      -- { lob storage properties
         --  (lob partition storage)
      -- }
      -- { xml type column properties
      -- }
   -- }
   -- { table partitioning clause
   -- }
   -- nocache
   -- result cache (mode default|force)
   -- { parallel clause
   -- }
   -- rowdependencies
   -- { enable disable clause
   -- }
   -- { row movement clause
   -- enable row movement ORA-14066: illegal option for a non-partitioned index-organized table
   -- }
   -- { flashback archive clause
--    no flashback archive
   -- }
-- }
/


select object_name, object_type from user_objects where object_name like 'TQ84_HEAP_TABLE%';

-- The created segment's name is the primary key's name:
select segment_name,  bytes/1024/1024 from user_segments where segment_name like 'TQ84_HEAP_TABLE%';


drop table tq84_heap_table purge;

DatabaseObjects - Tables - identity-column - example-01.sql

create table tq84_identity (
  id  number generated as identity,
  txt varchar2(10)
);

insert into tq84_identity (txt) values ('one'  );
insert into tq84_identity (txt) values ('two'  );
insert into tq84_identity (txt) values ('three');
insert into tq84_identity (txt) values ('four' );
insert into tq84_identity (txt) values ('five' );

declare
    id_generated number;
begin
    insert into tq84_identity (txt) values ('six'  ) returning id into id_generated;
    dbms_output.put_line('id_generated: ' || id_generated);
 -- id_generated: 6
end;
/

select * from tq84_identity;
-- 
--         ID TXT
-- ---------- ----------
--          1 one
--          2 two
--          3 three
--          4 four
--          5 five
--          6 six

drop table tq84_identity purge;

DatabaseObjects - Tables - ILM - add-policy.sql

create table tq84_ilm_policy (
  id    number primary key,
  col_1 varchar2(10),
  col_2 varchar2(10)
)
  ilm add policy row store compress advanced segment after 3 days of no modification
;

-- Alternatively, with alter table:
--
--    alter table tq84_ilm_policy 
--       ilm add policy row store compress advanced segment after 3 days of no modification;

DatabaseObjects - Tables - IndexOrganized - create_iot.sql

create table tq84_index_organized_table
-- { relational properties
   (
     -- { column definitions
        col_1    number,
        col_2    varchar2(20),
        col_3    date,
     -- }
     -- { out of line constraint
        constraint tq84_index_organized_table_pk primary key (
          col_1,
          col_2,
          col_3
        )
     -- }
     -- { supplemental logging props
     -- }
   )
-- }
-- { physical properties
   -- { deferred segment creation
      segment creation immediate /* or deferred */
   -- }
   ORGANIZATION INDEX
   -- { segment attributes clause
      -- { physical attributes clause
--       pctfree  10 -- ORA-25192: invalid option for an index-organized table
--       pctused  40
         initrans  2
         -- { storage clause
            storage (
              initial     10M
              next        10M
              maxextents  unlimited
              -- { maxsize clause
              -- }
              pctincrease 0
              -- freelists
              -- freelist groups
              -- optimal
              -- buffer_pool
              -- flash_cache
              -- encrypt
            )
         -- }
      -- }
      tablespace users
      -- { logging clause
         logging
      -- }
   -- }
   -- { index org table clause
      -- { mapping table clause
      -- }
   -- PCTTHRESHOLD 0
      -- { key compression
      -- }
      -- { index org overflow clause
      -- }
   -- }
-- }
-- { table  properties
   -- { column properties
   -- }
   -- { table partitioning clause
   -- }
-- nocache ORA-25192: invalid option for an index-organized table
   -- result cache (mode default|force)
   -- { parallel clause
   -- }
   -- rowdependencies
   -- { enable disable clause
   -- }
   -- { row movement clause
--    enable row movement ORA-14066: illegal option for a non-partitioned index-organized table
   -- }
   -- { flashback archive clause
--    no flashback archive
   -- }
-- }
/


select object_name, object_type from user_objects where object_name like 'TQ84_INDEX_ORGANIZED_TABLE%';

-- The created segment's name is the primary key's name:
select segment_name,  bytes/1024/1024 from user_segments where segment_name like 'TQ84_INDEX_ORGANIZED_TABLE%';


drop table tq84_index_organized_table purge;

DatabaseObjects - Tables - Locks - lockmodes.sql

create table tq84_lock_row_share           (col1 number);
create table tq84_lock_row_exclusive       (col1 number);
create table tq84_lock_share_update        (col1 number);
create table tq84_lock_share               (col1 number);
create table tq84_lock_share_row_exclusive (col1 number);
create table tq84_lock_exclusive           (col1 number);

lock table tq84_lock_row_share           in row   share      mode;
lock table tq84_lock_row_exclusive       in row   exclusive  mode;
lock table tq84_lock_share_update        in share update     mode;
lock table tq84_lock_share               in share            mode;
lock table tq84_lock_share_row_exclusive in row exclusive    mode;
lock table tq84_lock_exclusive           in exclusive        mode;

-- Locking a table creates a "TM" lock.

select 
  lmode, 
  decode (
    lmode,
    0, 'none           ',
    1, 'null       NULL',
    2, 'row-S      SS  ',
    3, 'row-X      SX  ',
    4, 'share      S   ',
    5, 'S/row-X    SSX ',
    6, 'exclusive  X   ',
       '?          ?   '
  ) lmode_decoded,
  object_name 
  from v$lock join user_objects on v$lock.id1 = user_objects.object_id
 where sid = sys_context('USERENV','SID') and
       type = 'TM'
 order by lmode;

drop table tq84_lock_row_share           purge;
drop table tq84_lock_row_exclusive       purge;
drop table tq84_lock_share_update        purge;
drop table tq84_lock_share               purge;
drop table tq84_lock_share_row_exclusive purge;
drop table tq84_lock_exclusive           purge;

DatabaseObjects - Tables - RecycleBin - purge_tablespace.sql

purge tablespace users;

DatabaseObjects - Tables - row-archival - example-01.sql

create table tq84_row_archival (
  col_1 number,
  col_2 varchar2(10)
) row archival;


desc tq84_row_archival;

column column_name format a20
column data_type   format a20
select
  column_id,
  column_name,
  data_type,
  hidden_column
from
  user_tab_cols
where
  table_name = 'TQ84_ROW_ARCHIVAL';
-- 
--  COLUMN_ID COLUMN_NAME          DATA_TYPE            HID
-- ---------- -------------------- -------------------- ---
--            ORA_ARCHIVE_STATE    VARCHAR2             YES
--          1 COL_1                NUMBER               NO
--          2 COL_2                VARCHAR2             NO



insert into tq84_row_archival values ( 1, 'one'  );
insert into tq84_row_archival values ( 2, 'two'  );
insert into tq84_row_archival values ( 3, 'three');
insert into tq84_row_archival values ( 4, 'four' );
insert into tq84_row_archival values ( 5, 'five' );

update tq84_row_archival set ora_archive_state = 1 where col_2 like 't%';

select * from tq84_row_archival;
-- 
--      COL_1 COL_2
-- ---------- ----------
--          1 one
--          4 four
--          5 five

alter session set row archival visibility = all;

select * from tq84_row_archival;
-- 
--      COL_1 COL_2
-- ---------- ----------
--          1 one
--          2 two
--          3 three
--          4 four
--          5 five


drop table tq84_row_archival;

DatabaseObjects - Tables - storage_clause - maxsize_01.sql

create table tq84_maxsize (
  col_1 varchar2(4000)
)
storage ( maxsize 1 M );

declare
  bytes_prev number := 0;
  bytes_curr number;
  i number := 1;


  procedure bytes_of_segment is -- {
  begin
    select bytes into bytes_curr
      from user_segments
     where segment_name = 'TQ84_MAXSIZE';

  end bytes_of_segment; -- }

  procedure report_i_and_bytes is -- {
  begin

         dbms_output.put_line(to_char(i              , '9999') || ': '   || 
                              to_char(bytes_prev/1024, '9999') || ' -> ' ||
                              to_char(bytes_curr/1024, '9999') || '  ' 
         );
  end report_i_and_bytes; -- }

begin

  while i < 1000*1000/4000 loop
      insert into tq84_maxsize values (lpad('x', 4000, 'x'));
      i := i+1;
      
      bytes_of_segment;

      if bytes_curr != bytes_prev then
         
         report_i_and_bytes;

         bytes_prev := bytes_curr;

      end if;
      
      
  end loop;

exception when others then
  dbms_output.put_line(sqlerrm);
  
  report_i_and_bytes;
end;
/

drop table tq84_maxsize;

--   Output:
--
--     2:     0 ->    64
--     7:    64 ->   128
--    15:   128 ->   192
--    22:   192 ->   256
--    30:   256 ->   320
--    37:   320 ->   384
--    45:   384 ->   448
--    52:   448 ->   512
--    60:   512 ->   576
--    67:   576 ->   640
--    75:   640 ->   704
--    82:   704 ->   768
--    90:   768 ->   832
--    97:   832 ->   896
--   105:   896 ->   960
--   112:   960 ->  1024
-- ORA-60004: adding (256) blocks to table ERGARCIA.TQ84_MAXSIZE with MAXSIZE (128)
--   119:  1024 ->  1024

DatabaseObjects - Types - compile_force_with_dependends.plsql

create type tq84_some_type as object (
  a number,
  b number
);
/

create type tq84_some_table as table of tq84_some_type;
/



create or replace type tq84_some_type force as object (
   a varchar2(10),
   b number
);
/


select status from user_objects where object_name = 'TQ84_SOME_TABLE';

declare
   a tq84_some_table := tq84_some_table();
begin

   a.extend;
   a(a.count) := tq84_some_type('foo', 1);
end;
/

select status from user_objects where object_name = 'TQ84_SOME_TABLE';

drop type tq84_some_table;
drop type tq84_some_type;

DatabaseObjects - Views - UpdateableJoinViews - test_01.sql

--
--  Compare /SQL/delete/subquery.sql
--
create table tq84_table_1 (
   id    number primary key,  -- Note: without primary key, all columns in the view would become non-updatable.
   txt   varchar2(10)
);

create table tq84_table_2 (
   id    number,
   txt   varchar2(10)
);


create view tq84_updatable_join_view as
  select
    t1.id  id_1,
    t2.id  id_2,
    t1.txt txt_1,
    t2.txt txt_2
  from
    tq84_table_1   t1 join
    tq84_table_2   t2 on t1.id = t2.id;


select
  column_name,
  updatable
from
  user_updatable_columns
where
  table_name = 'TQ84_UPDATABLE_JOIN_VIEW';

drop view  tq84_updatable_join_view;
drop table tq84_table_2 purge;
drop table tq84_table_1 purge;

Installed - data-dictionary - user_all_dba_names.sql

--
-- 2019-09-03: use user_all_dba_cdb_view.sql
--
column obj format a30

with u as ( select substr(object_name, 6) obj, object_name from dba_objects where owner = 'SYS' and object_name like 'USER\_%' escape '\'),
     a as ( select substr(object_name, 5) obj, object_name from dba_objects where owner = 'SYS' and object_name like  'ALL\_%' escape '\'),
     d as ( select substr(object_name, 5) obj, object_name from dba_objects where owner = 'SYS' and object_name like  'DBA\_%' escape '\'),
     c as ( select substr(object_name, 5) obj, object_name from dba_objects where owner = 'SYS' and object_name like  'CDB\_%' escape '\'),
     o as ( select obj from u union
            select obj from a union
            select obj from d union
            select obj from c)
select
  nvl2(u.obj, 'USER', '-'),
  nvl2(a.obj, 'ALL' , '-'),
  nvl2(d.obj, 'DBA' , '-'),
  nvl2(d.obj, 'CDB' , '-'),
       o.obj 
from
  o                  left join
  u on o.obj = u.obj left join
  a on o.obj = a.obj left join
  d on o.obj = d.obj left join
  c on o.obj = c.obj
order by
  o.obj;

Installed - data-dictionary - user_all_dba_names_2_html.sql

@spool user_all_dba_names.html
select '<table>' from dual;

with u as ( select substr(object_name, 6) obj, object_name from dba_objects where owner = 'SYS' and object_name like 'USER\_%' escape '\'), -- '
     a as ( select substr(object_name, 5) obj, object_name from dba_objects where owner = 'SYS' and object_name like  'ALL\_%' escape '\'), -- '
     d as ( select substr(object_name, 5) obj, object_name from dba_objects where owner = 'SYS' and object_name like  'DBA\_%' escape '\'), -- '
     o as ( select obj from u union
            select obj from a union
            select obj from d)
select
 '<tr>' ||
 '<td>Todo:</td>' ||
   '<td>' || nvl2(u.obj,   '<a href="' || rpad(lower(o.obj || '"'), 26) || '>user_' || rpad(lower(o.obj), 25) || '</a>', rpad(' ', 70)) || '</td>' ||
   '<td>' || nvl2(a.obj,   '<a href="' || rpad(lower(o.obj || '"'), 26) || '>all_'  || rpad(lower(o.obj), 25) || '</a>', rpad(' ', 70)) || '</td>' ||
   '<td>' || nvl2(d.obj,   '<a href="' || rpad(lower(o.obj || '"'), 26) || '>dba_'  || rpad(lower(o.obj), 25) || '</a>', rpad(' ', 70)) || '</td>' ||
 '</tr>'
from
  o                  left join
  u on o.obj = u.obj left join
  a on o.obj = a.obj left join
  d on o.obj = d.obj
order by
  o.obj;

select '<table>' from dual;
@spool off

Installed - data-dictionary - addm - findings - joins.sql

select
   tsk.execution_start,
   tsk.execution_end,
-- fin.finding_id,
   fin.finding_name,
   fin.type,
-- fin.type_id,
   fin.message,
   fin.impact_type,
   fin.impact,
   fin.parent,
   fin.object_id,
   tsk.fdg_count,
   tsk.recommendation_count
-- fin.task_id,
-- fin.task_name,
from
   dba_addm_findings fin join
   dba_addm_tasks    tsk on fin.task_id = tsk.task_id
order by
   tsk.execution_start desc,
   fin.type_id, -- 1: Problem, 2 Symptom, 4: Information
   fin.finding_id;

Installed - data-dictionary - addm - tasks - show.sql

select
  owner,
--advisor_id,
  advisor_name,  
  active_sessions,       -- Avg active sessions during analysis period
  task_id,
  parent_task_id,
  parent_rxec_id,
  task_name,
  recommendation_count,
  fdg_count,             -- Number of findings for ADDM tasks, appears in list of findings in default ADDM report
  execution_start,
  execution_end,
  begin_snap_id,
  end_snap_id,
  begin_time,
  end_time,
--meter_level,           -- Reserved for future use
  requested_analysis,
--pct_completion_time,
--progress_metric,
--metric_units,
--activity_counter,
  error_message,
  source,
  how_created,
  read_only,
  system_task,
  status,
--status#,
--status_message,
  last_execution
  description,
  execution_type,
  execution_type#,
  execution_description,
  created,
  last_modified,
  dbid,
  dbname,
  dbversion,
  analysis_version,
  database_time
from
  dba_addm_tasks
order by
  owner,
  execution_start desc
;

Installed - data-dictionary - alert - arguments - show.sql

select
  sequence_id,
  reason_message_id,
  reason_argument_count,
  reason_argument_1,
  reason_argument_2,
  reason_argument_3,
  reason_argument_4,
  reason_argument_5,
  action_message_id,
  action_argument_count,
  action_argument_1,
  action_argument_2,
  action_argument_3,
  action_argument_4,
  action_argument_5
from
  dba_alert_arguments;

Installed - data-dictionary - alert - history - show.sql

select
--reason_id,
  owner,
  object_name,
  subobject_name,
  object_type,
  reason
from
  dba_alert_history
order by
  sequence_id desc;

Installed - data-dictionary - audit - exists - show.sql

select
  os_username,
  timestamp,
  owner,
  obj_name,
  action_name,
  returncode,
  sql_text
from
  dba_audit_exists;

Installed - data-dictionary - catalog - table_types.sql

select
--count(*),
  lower(table_type) table_type
from
  dba_catalog
group by
  table_type
order by
  table_type;
--
-- sequence
-- synonym
-- table
-- view

Installed - data-dictionary - col - comments - example-01.sql

create table tq84_col_comments (
  col_nm number,
  col_vc varchar2(10),
  col_dt date
);

comment on column tq84_col_comments.col_nm is 'Column with numbers';
comment on column tq84_col_comments.col_vc is 'Column with varchars';
comment on column tq84_col_comments.col_dt is 'Column with dates';

select
  column_name,
  substrb(comments, 1, 50) comments
from
  user_col_comments
where
  table_name = 'TQ84_COL_COMMENTS';
-- COLUMN_NAME                    COMMENTS
-- ------------------------------ --------------------------------------------------
-- COL_NM                         Column with numbers
-- COL_VC                         Column with varchars
-- COL_DT                         Column with dates

drop table tq84_col_comments purge;

Installed - data-dictionary - coll_types - example-01.sql

create type tq84_coll_obj as object (
  dummy number
);
/

create type tq84_coll_tab    as table of tq84_coll_obj;
/

create type tq84_coll_var    as varying array (1000) of tq84_coll_obj;
/

create type tq84_coll_var_nn as varying array (1000) of tq84_coll_obj not null;
/

select
  type_name,
  coll_type,
  elem_type_name,
--elem_type_owner
  nulls_stored
from
  user_coll_types
where
  type_name like 'TQ84_COLL%';


drop type tq84_coll_var_nn;
drop type tq84_coll_var;
drop type tq84_coll_tab;
drop type tq84_coll_obj;

Installed - data-dictionary - common_audit_trail - logon-logoff.sql

select /*+ parallel(16) */
  logon__.os_user,
  logon__.db_user,
  logon__.extended_timestamp,
  logoff_.extended_timestamp,
  logoff_.statement_type,     -- LOGOFF, LOGOFF BY CLEANUP
  logon__.session_id
from
  dba_common_audit_trail logon__                                                      left join
  dba_common_audit_trail logoff_ on logon__.session_id     =  logoff_.session_id and
                                    logoff_.statement_type like 'LOGOFF%'
where
  logon__.os_user        = 'rnyffenegger' and
  logon__.statement_type = 'LOGON'
order by
  logon__.extended_timestamp desc;

Installed - data-dictionary - common_audit_trail - show.sql

select /*+ parallel(16) */
-- audit_type,
-- session_id,
-- proxy_sessionid,
-- statementid,
-- entryid,
   extended_timestamp,
-- global_uid,
   db_user,
   priv_used,          -- System privilege used to execute the action
   sql_text,
   client_id,
-- econtext_id,
-- ext_name,
   os_user,
   userhost,
-- os_process,
-- terminal,
   instance_number,
   object_schema,
   object_name,
-- policy_name,
-- new_owner,
-- new_name,
-- action,
   statement_type,
   audit_option,
-- transactionid,
   returncode,
-- scn,
   comment_text,
   sql_bind,
   obj_privilege,      -- Object privileges granted or revoked by a grant or revoke stmt.
   sys_privilege,      -- System privileges granted or revoked by a grant or revoke stmt.
   admin_option,
   os_privilege,
   grantee,
   ses_actions,
-- logoff_time,
-- logoff_lread,       -- Number of logical reads for the session 
-- logoff_pread,       -- Number of physical reads for the session
-- logoff_lwrite,      -- Number of logical writes for the session
-- logoff_dlock,       -- Number of deadlocks detected during the session
-- session_cpu,        -- Amount of CPU time used by the Oracle session
-- obj_edition_name,
   dbid
from
  dba_common_audit_trail
where
  os_user = 'rnyffenegger'
order by
  extended_timestamp desc;

Installed - data-dictionary - connect_role_grantees - show.sql

select
  grantee,
  path_of_connect_role_grant,
  admin_opt
from
  dba_connect_role_grantees
order by
  grantee;

Installed - data-dictionary - cons_columns - show_constraint_columns.sql

create table tq84_cons (
  col_a  number,
  col_b  varchar2(10),
  col_c  date,
  col_d  varchar2(10),
  col_e  number,
  --
  constraint tq84_cons_pk primary key(col_e, col_b)
);

select
  column_name
from
  user_cons_columns
where
  constraint_name = 'TQ84_CONS_PK'
order by
  position;
-- COL_E
-- COL_B
  
drop table tq84_cons purge;

Installed - data-dictionary - context - show.sql

select
  schema,
  namespace,
  package,
  type
from
  dba_context
order by
  schema,
  namespace;

Installed - data-dictionary - datapump - sessions - show.sql

select
  owner_name,
  job_name,
  inst_id,
  saddr,
  session_type
from
  dba_datapump_sessions
order by
  owner_name,
  job_name;

Installed - data-dictionary - data_files - show.sql

select
   status,
   online_status,
   tablespace_name,
   to_char(bytes       /1024/1024, '999,999.00') mb,
   to_char(user_bytes  /1024/1024, '999,999.00') user_mb,
   to_char(maxbytes    /1024/1024, '999,999.00') max_mb,
-- blocks,
-- user_blocks
   file_id,
   relative_fno,
   increment_by,
   file_name
from
   dba_data_files
order by
   status,
   online_status,
   tablespace_name;

Installed - data-dictionary - ddl_locks - show.sql

select
  owner,
  name,
  type,
  mode_held,
  mode_requested,
  session_id
from
  dba_ddl_locks
order by
  case mode_requested
       when 'Exclusive' then 1
       when 'Share'     then 2
       when 'None'      then 3
       else                  4 end,
  owner,
  name
;

Installed - data-dictionary - def_audit_opts - show.sql

select
  alt  "alter"    ,
  aud  "audit"    ,
  com  "comment"  ,
  del  "delete"   ,
  gra  "grant"    ,
  ind  "index"    ,
  ins  "insert"   ,
  loc  "lock"     ,
  ren  "rename"   ,
  sel  "select"   ,
  upd  "update"   ,
--ref                 -- Obsolete
  exe  "execute"  ,
  fbk  "flashback",
  rea  "read"
from
  all_def_audit_opts
;

Installed - data-dictionary - directories - privileges.sql

--
-- Show privileges on directories
--
select
  dir.directory_name                                   dir_name,
  prv.grantee                                           grantee,
  max(case when prv.privilege = 'READ'    then 'r' end)        r,
  max(case when prv.privilege = 'WRITE'   then 'w' end)        w,
  max(case when prv.privilege = 'EXECUTE' then 'e' end)        e,
  dir.directory_path                                    dir_path
from
  dba_directories       dir left join
  dba_tab_privs         prv on dir.directory_name = prv.table_name
group by
  dir.directory_name,
  dir.directory_path,
  prv.grantee
order by
  dir.directory_name,
  prv.grantee;

Installed - data-dictionary - dml_locks - example-01.sql

create table tq84_dml_lock_test (
  pk number primary key
);

select * from dba_dml_locks;

insert into tq84_dml_lock_test values (1);

select
  mode_held,
  mode_requested,
--last_convert       currend_mode_granted_secs_ago,     
  blocking_others
from
  dba_dml_locks
where
  owner = user;
--
-- Row-X (SX)	None	Not Blocking
-- 

drop table tq84_dml_lock_test purge;

Installed - data-dictionary - errors - joins.sql

select
  to_char(obj.created, 'yyyy-mm-dd')  obj_created,
--err.message_number,
  err.name,
  err.type,
  err.line,
  substr(err.text, 1, 100) err_text,
  src.text
from
  dba_errors  err                                               join
  dba_objects obj on err.owner = obj.owner            and
                     err.name  = obj.object_name      and
                     err.type  = obj.object_type           left join
  dba_source  src on err.owner = src.owner            and
                     err.name  = src.name             and
                     err.type  = src.type             and
                     err.line  = src.line
where
  err.text    not in ('PL/SQL: SQL Statement ignored', 'PL/SQL Statement ignored')
order by
  obj.created
 ;

Installed - data-dictionary - feature_usage_statistics - overview.sql

select
  name,
  detected_usages,
  currently_used,
  description,
  first_usage_date,
  last_usage_date
from
  dba_feature_usage_statistics
where
  version = '11.2.0.3.0';

Installed - data-dictionary - invalid_objects - show.sql

select
   owner,
   object_name,
   subobject_name,
-- object_id,
-- data_object_id,
   object_type,
   last_ddl_time,
   status,
   timestamp,
   temporary,
   generated,
   secondary,
   namespace,
   edition_name
from
  dba_invalid_objects
order by
  owner,
  object_name;

Installed - data-dictionary - jobs - show.sql

select
  job,
  log_user,
  priv_user,
  schema_user,
  last_date,
--last_sec,
--this_date,
--this_sec,
  next_date,
--next_sec,
  round(total_time),
  broken,
  interval,
  failures,
  what
--nls_env,
--misc_env,
--instance
from
  dba_jobs
order by
  job;

Installed - data-dictionary - lock - show.sql

select
  ses.sid,
  ses.serial#,
  ses.username,
  ses.osuser,
  ses.status,
  ses.program,
  ses.logon_time,
  ses.event,
  lck.lock_type,
  lck.mode_held,
  lck.mode_requested,
  lck.lock_id1,
  lck.lock_id2,
  lck.last_convert,
  lck.blocking_others
from
  dba_lock  lck  left join
  v$session ses              on lck.session_id = ses.sid
where
  ses.osuser = 'rnyffenegger'
order by
  ses.username,
  ses.osuser;

Installed - data-dictionary - logstdby - parameters - parameter-names.sql

select
  lower(name)
from
  dba_logstdby_parameters
order by
  lower(name);
-- 
-- allow_transformation
-- apply_servers
-- event_log_dest
-- log_auto_delete
-- log_auto_del_retention_target
-- max_events_recorded
-- max_servers
-- max_sga
-- prepare_servers
-- preserve_commit_order
-- record_applied_ddl
-- record_skip_ddl
-- record_skip_errors
-- record_unsupported_operations

Installed - data-dictionary - logstdby - skip - show.sql

select
  owner,
  name,
  statement_opt,
  use_like,
  esc,
  proc,
  error
from
  dba_logstdby_skip
order by
  owner;

Installed - data-dictionary - logstdby - unsupported - show.sql

select
  owner,
  table_name,
  column_name,
  attributes,
  data_type
from
  dba_logstdby_unsupported
order by
  owner,
  table_name,
  column_name;

Installed - data-dictionary - logstdby - unsupported - table - show.sql

select
  owner,
  table_name
from
  dba_logstdby_unsupported_table
order by
  owner,
  table_name;

Installed - data-dictionary - obj - audit_opts - show.sql

select
  owner,
  object_name,
  object_type,
  alt,
  aud,
  com,
  del,
  gra,
  ind,
  loc,
  ren,
  sel,
  upd,
  ref,
  exe,
  cre,
  rea,
  wri,
  fbk
from
  dba_obj_audit_opts
order by
  owner,
  object_name;

Installed - data-dictionary - outstanding_alerts - show.sql

select
 -- sequence_id
 -- reasonid,     -- Join with v$alert_Types
    reason,
    suggested_action,
    metric_value,
    message_type,
    message_level,
    advisor_name
    owner,
    object_name,
    subobject_name,
    object_type,
    message_group
 -- time_suggested,
 -- creation_time
from
    dba_outstanding_alerts;

Installed - data-dictionary - password_limits - resource_names.sql

select
  resource_name
from
  user_password_limits
order by
  resource_name;
-- 
-- FAILED_LOGIN_ATTEMPTS
-- PASSWORD_GRACE_TIME
-- PASSWORD_LIFE_TIME
-- PASSWORD_LOCK_TIME
-- PASSWORD_REUSE_MAX
-- PASSWORD_REUSE_TIME
-- PASSWORD_VERIFY_FUNCTION

Installed - data-dictionary - registry - banners - show.sql

select
  banner
from
  all_registry_banners
order by
  lower(banner);

Installed - data-dictionary - registry - database - show.sql

select
  platform_id,
  platform_name,
  edition
from
  dba_registry_database;

Installed - data-dictionary - represoultion - method - show.sql

select
  conflict_type,
  method_name
from
  dba_represolution_method
order by
  conflict_type,
  method_name;

Installed - data-dictionary - resource - incarnations - show.sql

select
  resource_type,
  resource_name,
  db_unique_name,
  db_domain,
  instance_name,
  host_name,
  startup_time
from
  dba_resource_incarnations;

Installed - data-dictionary - resource - limits - show.sql

select 
  lower(resource_name),
  limit
from
  user_resource_limits
order by
  lower(resource_name);

Installed - data-dictionary - role - role_privs - show.sql

select * from role_role_privs;

Installed - data-dictionary - role - tab_privs - show-privs-for-specific-role.sql

select
  owner,
  table_name,
  column_name,
  privilege,
  grantable
from
  role_tab_privs
where
  role = 'R_DEVELOP'
order by
  owner,
  table_name,
  column_name nulls last,
  privilege;

Installed - data-dictionary - segments - find-largest.sql

select /*+ parallel(16) */
  round(bytes / 1024/1024/1024, 2) size_gb,
  owner,
  segment_name,
  tablespace_name,
  segment_type,
  segment_subtype
from
  dba_segments
order by
  bytes desc nulls last;

Installed - data-dictionary - snapshots - show.sql

select
  owner,
  name,
--table_name,
--master_owner,
  master,
--master_link,
  can_use_log,
  updatable,
  refresh_method,
  refresh_mode,
--error,
--fr_operations,
--cr_operations,
  type,
  last_refresh,
  next,
  start_with,
  refresh_group,
--update_trig,
--update_log,
--master_rollback_seg,
--prebuilt,
  status,
  query
from
  dba_snapshots
order by
  owner,
  name;

Installed - data-dictionary - sql - management_config - parameter_names.sql

select
  lower(parameter_name)
from
  dba_sql_management_config
order by
  parameter_name;
--
-- plan_retention_weeks
-- space_budget_percent

Installed - data-dictionary - sql - monitor_usage - show.sql

select
  num_db_reports,
  num_em_reports,
  first_db_report_time,
  last_db_report_time,
  first_em_report_time,
  last_em_report_time
from
  dba_sql_monitor_usage
order by
  first_db_report_time;

Installed - data-dictionary - sqlset - show.sql

select
  id,
  owner,
  name,
  description,
  statement_count,
  con_dbid,
  created,
  last_modified
from
  dba_sqlset
order by
  owner,
  created desc;

Installed - data-dictionary - stmt_audit_option_map - names.sql

select
  lower(name)
from
  stmt_audit_option_map
order by
  name;
--
-- administer any sql tuning set
-- administer database trigger
-- administer key management
-- administer resource manager
-- administer sql management object
-- administer sql tuning set
-- advisor
-- all statements
-- alter any assembly
-- alter any cluster
-- alter any cube
-- alter any cube build process
-- alter any cube dimension
-- alter any dimension
-- alter any edition
-- alter any evaluation context
-- alter any index
-- alter any indextype
-- alter any library
-- alter any materialized view
-- alter any measure folder
-- alter any mining model
-- alter any operator
-- alter any outline
-- alter any procedure
-- alter any role
-- alter any rule
-- alter any rule set
-- alter any sequence
-- alter any sql profile
-- alter any sql translation profile
-- alter any table
-- alter any trigger
-- alter any type
-- alter database
-- alter database link
-- alter java class
-- alter java resource
-- alter java source
-- alter mining model
-- alter profile
-- alter public database link
-- alter resource cost
-- alter rollback segment
-- alter sequence
-- alter session
-- alter sql translation profile
-- alter system
-- alter table
-- alter tablespace
-- alter user
-- analyze any
-- analyze any dictionary
-- audit any
-- backup any table
-- become user
-- change notification
-- cluster
-- comment any mining model
-- comment any table
-- comment edition
-- comment mining model
-- comment table
-- context
-- create any assembly
-- create any cluster
-- create any context
-- create any credential
-- create any cube
-- create any cube build process
-- create any cube dimension
-- create any dimension
-- create any directory
-- create any edition
-- create any evaluation context
-- create any index
-- create any indextype
-- create any job
-- create any library
-- create any materialized view
-- create any measure folder
-- create any mining model
-- create any operator
-- create any outline
-- create any procedure
-- create any rule
-- create any rule set
-- create any sequence
-- create any sql profile
-- create any sql translation profile
-- create any synonym
-- create any table
-- create any trigger
-- create any type
-- create any view
-- create assembly
-- create cluster
-- create credential
-- create cube
-- create cube build process
-- create cube dimension
-- create database link
-- create dimension
-- create evaluation context
-- create external job
-- create indextype
-- create java class
-- create java resource
-- create java source
-- create job
-- create library
-- create materialized view
-- create measure folder
-- create mining model
-- create operator
-- create pluggable database
-- create procedure
-- create profile
-- create public database link
-- create public synonym
-- create role
-- create rollback segment
-- create rule
-- create rule set
-- create sequence
-- create session
-- create sql translation profile
-- create synonym
-- create table
-- create tablespace
-- create trigger
-- create type
-- create user
-- create view
-- database link
-- debug any procedure
-- debug connect session
-- debug procedure
-- delete any cube dimension
-- delete any measure folder
-- delete any table
-- delete table
-- dequeue any queue
-- dimension
-- directory
-- direct_path load
-- direct_path unload
-- drop any assembly
-- drop any cluster
-- drop any context
-- drop any cube
-- drop any cube build process
-- drop any cube dimension
-- drop any dimension
-- drop any directory
-- drop any edition
-- drop any evaluation context
-- drop any index
-- drop any indextype
-- drop any library
-- drop any materialized view
-- drop any measure folder
-- drop any mining model
-- drop any operator
-- drop any outline
-- drop any procedure
-- drop any role
-- drop any rule
-- drop any rule set
-- drop any sequence
-- drop any sql profile
-- drop any sql translation profile
-- drop any synonym
-- drop any table
-- drop any trigger
-- drop any type
-- drop any view
-- drop java class
-- drop java resource
-- drop java source
-- drop profile
-- drop public database link
-- drop public synonym
-- drop rollback segment
-- drop sql translation profile
-- drop tablespace
-- drop user
-- em express connect
-- enqueue any queue
-- execute any assembly
-- execute any class
-- execute any evaluation context
-- execute any indextype
-- execute any library
-- execute any operator
-- execute any procedure
-- execute any program
-- execute any rule
-- execute any rule set
-- execute any type
-- execute assembly
-- execute library
-- execute procedure
-- exempt access policy
-- exempt ddl redaction policy
-- exempt dml redaction policy
-- exempt identity policy
-- exempt redaction policy
-- export full database
-- flashback any table
-- flashback archive administer
-- force any transaction
-- force transaction
-- global query rewrite
-- grant any object privilege
-- grant any privilege
-- grant any role
-- grant directory
-- grant edition
-- grant mining model
-- grant procedure
-- grant sequence
-- grant sql translation profile
-- grant table
-- grant type
-- grant user
-- import full database
-- index
-- inherit any privileges
-- inherit privileges
-- insert any cube dimension
-- insert any measure folder
-- insert any table
-- insert table
-- keep date time
-- keep sysguid
-- lock any table
-- lock table
-- logmining
-- manage any file group
-- manage any queue
-- manage file group
-- manage scheduler
-- manage tablespace
-- materialized view
-- merge any view
-- mining model
-- network
-- not exists
-- on commit refresh
-- outline
-- pluggable database
-- procedure
-- profile
-- public database link
-- public synonym
-- purge dba_recyclebin
-- query rewrite
-- read any file group
-- read any table
-- redefine any table
-- restricted session
-- resumable
-- role
-- rollback segment
-- select any cube
-- select any cube build process
-- select any cube dimension
-- select any dictionary
-- select any measure folder
-- select any mining model
-- select any sequence
-- select any table
-- select any transaction
-- select mining model
-- select sequence
-- select table
-- sequence
-- set container
-- sql translation profile
-- synonym
-- sysbackup
-- sysdba
-- sysdg
-- syskm
-- sysoper
-- system audit
-- system grant
-- table
-- tablespace
-- translate any sql
-- translate sql
-- trigger
-- type
-- under any table
-- under any type
-- under any view
-- unlimited tablespace
-- update any cube
-- update any cube build process
-- update any cube dimension
-- update any table
-- update table
-- use any sql translation profile
-- use edition
-- use sql translation profile
-- user
-- view

Installed - data-dictionary - stored_settings - show.sql

select
  owner,
  object_name,
  param_name,
  param_value,
  object_type,
  origin_con_id
--object_id
from
  dba_stored_settings
order by
  owner,
  object_name;

Installed - data-dictionary - streams - columns - show.sql

select
  owner,
  table_name,
  column_name,
  sync_capture_version,
  sync_capture_reason,
  apply_version,
  apply_reason
from
  dba_streams_columns
order by
  owner,
  table_name,
  column_name;

Installed - data-dictionary - streams - newly_supported - show.sql

select
  owner,
  table_name,
  reason,
  compatible
from
  dba_streams_newly_supported
order by
  owner,
  table_name;

Installed - data-dictionary - streams - unsupported - show.sql

select
  owner,
  table_name,
  reason,
  auto_filtered
from
  dba_streams_unsupported
order by
  owner,
  table_name;

Installed - data-dictionary - summaries - show.sql

select
  owner,
  summary_name,
  container_owner,
  container_name,
  refresh_method,
  contains_views,
  summary,
  unusable,
  restricted_syntax,
  inc_refreshable,
  known_stale,
  query_len
  query,
--last_refresh_scn,
  last_refresh_date,
  fullrefreshtim,
  increfreshtim
from
  dba_summaries
order by
  owner,
  last_refresh_date desc;

Installed - data-dictionary - summary - aggregates - show.sql

select
  owner,
  summary_name,
--position_in_select,
  container_column,
  agg_function,
  distinctflag,
  measure
from
  dba_summary_aggregates
order by
  owner,
  summary_name,
  position_in_select;

Installed - data-dictionary - summary - detail_tables - show.sql

select
  owner,
  summary_name,
  detail_owner,
  detail_relation,
  detail_type,
  detail_alias
from
  dba_summary_detail_tables
order by
  owner,
  summary_name;

Installed - data-dictionary - summary - joins - show.sql

select
  owner,
  summary_name,
  detailobj1_owner,
  detailobj1_relation,
  detailobj1_column,
  operator,
  detailobj2_owner,
  detailobj2_relation,
  detailobj2_column
from
  dba_summary_joins
order by
  owner,
  summary_name;

Installed - data-dictionary - summary - keys - show.sql

select
  owner,
  summary_name,
  position_in_select,
  container_column,
  detailobj_owner,
  detailobj_name,
  detailobj_alias,
  detailobj_type,
  detailobj_column
from
  dba_summary_keys
order by
  owner,
  summary_name,
  position_in_select;

Installed - data-dictionary - sum_delta - joins.sql

select /*+ parallel(16) */
  obj.owner,
  obj.object_name,
  obj.object_type,
--sdt.tableobj#,
--sdt.partitionobj#,
  sdt.dmloperation,
  sdt.scn,
  sdt.timestamp,
  sdt.lowrowid,
  sdt.highrowid,
  sdt.sequence,
  sdt.xid
from
  all_sumdelta  sdt   left join
  dba_objects   obj on sdt.tableobj# = obj.object_id
where
  sdt.timestamp <= sysdate  -- ???
order by
  sdt.timestamp desc;

Installed - data-dictionary - supplemental_logging - show.sql

select
  minimal,
  primary_key,
  unique_index,
  foreign_key,
  all_column,
  procedural
from
  dba_supplemental_logging;

Installed - data-dictionary - tab - cols - hidden-and-virtual-columns.sql

select
  owner,
  table_name,
  column_name,
  hidden_column,
  virtual_column,
  data_type,
  qualified_col_name,
  column_id,
  internal_column_id,
  segment_column_id
from
  dba_tab_cols
where
  hidden_column  <> 'NO' or
  virtual_column <> 'NO'
order by
  owner,
  table_name,
  column_id;

Installed - data-dictionary - tab - columns - find-table-with-name-of-2-columns.sql

--
--    Find the name of a table given two of its attribute/column names.
--

create table tq84_foo (
  col_abc  number,
  col_cde  number,
  col_efg  number,
  col_hij  number
);

create table tq84_bar (
  col_abc  number,
  col_hij  number,
  col_klm  date
);

create table tq84_baz (
  col_abc  number,
  col_hij  number,
  col_nop  date
);


select
  a.owner,
  a.table_name
--a.column_name,
--b.column_name
from
  all_tab_columns  a                                 join
  all_tab_columns  b on a.owner      = b.owner and
                        a.table_name = b.table_name
where
  a.column_name  = 'COL_ABC' and
  b.column_name  = 'COL_KLM';


drop table tq84_baz purge;
drop table tq84_bar purge;
drop table tq84_foo purge;

Installed - data-dictionary - tab - comments - show.sql

select
  owner,
  table_name,
  comments,
  table_type,
  origin_con_id
from
  dba_tab_comments
where
  comments is not null
order by
  owner,
  table_name
;

Installed - data-dictionary - tab - modifications - show.sql

select
  table_owner,
  table_name,
  partition_name,
  subpartition_name,
  inserts,
  updates,
  deletes,
  truncated,
  drop_segments,
  timestamp
from
  dba_tab_modifications
order by
  table_owner,
  timestamp desc;

Installed - data-dictionary - tab - privs - show-privs-for-specific-user.sql

select
  owner,
  table_name,
  grantor,
  privilege,
  grantable,
  hierarchy
from
  dba_tab_privs
where
  grantee = 'RENE'
order by
  owner,
  table_name,
  privilege;

Installed - data-dictionary - temp - free_space - show.sql

select
  tablespace_name,
  round(tablespace_size /1024/1204/1024, 2)  size_gb,
  round(allocated_space /1024/1024/1024, 2) alloc_gb,
  round(free_space      /1024/1024/1024, 2)  free_gb
from
  dba_temp_free_space;

Installed - data-dictionary - transformations - show.sql

select
  transformation_id,
  owner,
  name,
  from_type,
  to_type
from
  dba_transformations
order by
  owner,
  name;

Installed - data-dictionary - trigger - cols - show.sql

select
  table_owner,
  table_name,
  column_name,
  column_list,
  column_usage,
  trigger_owner,
  trigger_name
from
  dba_trigger_cols
order by
  table_owner,
  table_name,
  column_name;

Installed - data-dictionary - type - attrs - example-01.sql

create type tq84_obj as object (
  foo varchar2(10),
  bar number,
  baz date,
  member procedure proc
);
/

column attr_name      format a10
column attr_type_name format a10
select
  attr_name,
  attr_type_mod,
  attr_type_name,
  length
from
  user_type_attrs
where
  type_name = 'TQ84_OBJ'
order by
  attr_no;
--
-- ATTR_NAME  ATTR_TY ATTR_TYPE_     LENGTH
-- ---------- ------- ---------- ----------
-- FOO                VARCHAR2           10
-- BAR                NUMBER
-- BAZ                DATE


drop type tq84_obj;

Installed - data-dictionary - type - methods - example-01.sql

create type tq84_obj as object (
  dummy date,
        member procedure proc_1,
        static procedure proc_2,
        member function  func_1 return number,
  final member function  func_2 return varchar2
);
/

create type body tq84_obj as
        member procedure proc_1 is begin null;  end proc_1;
        static procedure proc_2 is begin null;  end proc_2;
        member function  func_1 return number   is begin return null; end func_1;
  final member function  func_2 return varchar2 is begin return null; end func_2;
end;
/
show errors;

column method_name format a10
select
  method_name,
  method_type,
  parameters,
  final,
  instantiable
from
  user_type_methods
where
  type_name = 'TQ84_OBJ'
order by
  method_no;
--
-- METHOD_NAM METHOD PARAMETERS FIN INS
-- ---------- ------ ---------- --- ---
-- PROC_1     PUBLIC          1 NO  YES
-- PROC_2     PUBLIC          0 NO  YES
-- FUNC_1     PUBLIC          1 NO  YES
-- FUNC_2     PUBLIC          1 YES YES

drop type tq84_obj;

Installed - data-dictionary - types - example-01.sql

create type tq84_obj as object (
   attr_1 varchar2(10),
   attr_2 number,
   attr_3 date,
   member procedure member_proc_1,
   member procedure member_proc_2,
   static procedure static_proc_1,
   static procedure static_proc_2
) not final;
/

create type body tq84_obj as

  member procedure member_proc_1 is begin null; end member_proc_1;
  member procedure member_proc_2 is begin null; end member_proc_2;

  static procedure static_proc_1 is begin null; end static_proc_1;
  static procedure static_proc_2 is begin null; end static_proc_2;

end;
/
show errors

create type tq84_obj_under_obj under tq84_obj (
  attr_4 varchar2(10),
  overriding member procedure member_proc_2
);
/
show errors;

create type body tq84_obj_under_obj as
  overriding member procedcure member_proc_2 is begin null; end member_proc_2;
end;
/


column      type_name format a20
column supertype_name format a20
select
  type_name,
  attributes,
  methods,
  final,
  supertype_name,
  incomplete
from
  user_types
where
  type_name like 'TQ84_OBJ%';
--
-- TYPE_NAME            ATTRIBUTES    METHODS FIN SUPERTYPE_NAME       INC
-- -------------------- ---------- ---------- --- -------------------- ---
-- TQ84_OBJ                      3          4 NO                       NO
-- TQ84_OBJ_UNDER_OBJ            4          5 YES TQ84_OBJ             NO

drop type tq84_obj_under_obj;
drop type tq84_obj;

Installed - data-dictionary - unused - col_tabs - show.sql

select
  owner,
  table_name,
  count
from
  dba_unused_col_tabs
order by
  owner,
  table_name;

Installed - data-dictionary - ustats - show.sql

select
  object_owner,
  object_name,
  partition_name,
  object_type,
  association,
  column_name,
  statstype_schema,
  statstype_name,
  statistics
from
  dba_ustats
order by
  object_owner,
  object_name;

Installed - data-dictionary - warning_settings - show.sql

select
  owner,
  object_name,
  object_type,
  warning,
  setting,
  object_id
from
  dba_warning_settings
order by
  owner,
  object_name,
  object_type;

Installed - data-dictionary - xml - tables - show.sql

select
  owner,
  table_name,
  xmlschema,
  schema_owner,
  element_name,
  storage_type,
  anyschema,
  nonschema
from
  dba_xml_tables
order by
  owner,
  table_name;

Installed - data-dictionary - xs - users - show.sql

select
  name,
  guid,
  external_source,
  roles_default_enabled,
  status,
  account_status,
  lock_date,
  expiry_date,
  profile,
  schema,
  start_date,
  end_date,
  direct_logon_user,
  verifier_type,
  description
from
  dba_xs_users
order by
  name;

Installed - dbms - audit_mgmt - set_audit_trail_location.sql

prompt
prompt   Initially, AUD$ and FGA_LOG$ both are in the 
prompt   system tablespace:
prompt

select
  table_name,
  tablespace_name
from
  dba_tables
where
  table_name in ('AUD$', 'FGA_LOG$');


prompt
prompt   Create dedicated tablespace for fga_log$
prompt


create tablespace 
  FGA_LOG_TS
  datafile 'C:\ORACLE\DBMANUAL_FILES\FGA_LOG.DBF'
  size 1M
  extent management local autoallocate
  segment space management auto
;


prompt
prompt   Move audit trail FGA_LOG$ to new tablespace
prompt

connect sys as sysdba

begin

  dbms_audit_mgmt.set_audit_trail_location(
    audit_trail_type            =>  dbms_audit_mgmt.audit_trail_fga_std,
    audit_trail_location_value  => 'FGA_LOG_TS'
  );

end;
/

Installed - dbms - audit_mgmt - archiving - archive.sql

--
--     Archives a portion of fga_log$ into fga_log_archive
--     and then purges the archived portion from fga_log$.
--
--     The idea is to call this script repeatedly.
--
--     Init needs to have been called once.
--

insert into rene.fga_log_archive
select 
  sessionid,
--dbuid,
  osuid,
  oshst,
  clientid,
--extid,
  obj$schema,
  obj$name,
  policyname,
--scn,
  sqltext,
--lsqltext,
--sqlbind,
--comment$text,
--plhol,
  stmt_type,
  ntimestamp#,
--proxy$sid,
--user$guid,
--instance#,
--process#,
--xid,
--auditid,
  statement,
--entryid,
--dbid,
--lsqlbind,
  obj$edition
from
  sys.fga_log$;


begin

  sys.dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type     => sys.dbms_audit_mgmt.audit_trail_fga_std,
     last_archive_time    => systimestamp
  );

  sys.dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        => sys.dbms_audit_mgmt.audit_trail_fga_std,
   use_last_arch_timestamp => true
  );

end;
/

Installed - dbms - audit_mgmt - archiving - bug_9164488.sql

--
--   With bug 9164488, it is possible to use clean_audit_trail with 
--   the following workaround. No idea if it is endorsed by Oracle.
--   
--   See http://dba.stackexchange.com/questions/33250/archiving-fga-log-sys-dbms-audit-mgmt-clean-audit-trail-doesnt-delete-anythin
--

begin

  sys.dbms_audit_mgmt.create_purge_job (
     audit_trail_type           => sys.dbms_audit_mgmt.audit_trail_fga_std,
     audit_trail_purge_interval => 999,
     audit_trail_purge_name     =>'Purge_Test',
     use_last_arch_timestamp    => true
  );

end;
/

Installed - dbms - audit_mgmt - archiving - init.sql

connect sys as sysdba

drop table rene.fga_log_archive;

--  Create the table to store the
--  archived audit data:

create table rene.fga_log_archive (
  sessionid                    number not null,
--timestamp#                   date,        -- Desupported anyway, use ntimestamp# instead.
--dbuid                        varchar2(30),
  osuid                        varchar2(255),
  oshst                        varchar2(128),
  clientid                     varchar2(64),
--extid                        varchar2(4000),
  obj$schema                   varchar2(30),
  obj$name                     varchar2(128),
  policyname                   varchar2(30),
--scn                          number,
  sqltext                      varchar2(4000),
--lsqltext                     clob,
--sqlbind                      varchar2(4000),
--comment$text                 varchar2(4000),
--plhol                        long,
  stmt_type                    number,
  ntimestamp#                  timestamp(6),
--proxy$sid                    number,
--user$guid                    varchar2(32),
--instance#                    number,
--process#                     varchar2(16),
--xid                          raw(8),
--auditid                      varchar2(64),
  statement                    number,
--entryid                      number,
--dbid                         number,
--lsqlbind                     clob,
  obj$edition                  varchar2(30)
)
partition by range (ntimestamp#)
interval (numtoyminterval(1, 'month')) (
  partition fga_log_archive_init_part values less than (date '2013-01-01')
);


---

begin

  dbms_audit_mgmt.init_cleanup(
     audit_trail_type          => dbms_audit_mgmt.audit_trail_fga_std,
     default_cleanup_interval  => 999
  );

end;
/

Installed - dbms - cube_log - default_name.sql

declare
  v  varchar2(100); 
begin

  for c in (
    select 'type_operations'        n from dual union all
    select 'type_rejected_records'  n from dual union all
    select 'type_dimension_compile' n from dual union all 
    select 'type_build'             n from dual
  ) loop


    execute immediate 'begin :v := dbms_cube_log.default_name(dbms_cube_log.' || c.n || '); end;' using out v;

    dbms_output.put_line(rpad(c.n || ': ', 26) || v);

  end loop;


end;
/

-- type_operations:          CUBE_OPERATIONS_LOG
-- type_rejected_records:    CUBE_REJECTED_RECORDS
-- type_dimension_compile:   CUBE_DIMENSION_COMPILE
-- type_build:               CUBE_BUILD_LOG

Installed - dbms - cube_log - table_create.sql

--
-- Create table cube_build_log (See default_name.sql)
--
exec dbms_cube_log.table_create(dbms_cube_log.type_build)


select
  object_type,
  sysdate - created
from
  user_objects where object_name = 'CUBE_BUILD_LOG';


desc cube_build_log

Installed - dbms - lock - blue.sql

prompt
prompt The "blue" session
prompt ==================
prompt


variable lockhandle varchar2(100)

variable x_mode     number
exec :x_mode := dbms_lock.x_mode

exec dbms_lock.allocate_unique('tq84-lock', :lockhandle)
exec dbms_output.put_line ('lockhandle ' ||  :lockhandle || ' allocated');
select case dbms_lock.request(:lockhandle, :x_mode) 
       when 0 then 'Success'
       when 1 then 'Timeout'
       when 2 then 'Deadlock'
       when 3 then 'Parameter error'
       when 4 then 'Already own lock'
       when 5 then 'Illegal lock handle' end from dual;


prompt Go now to the green session and press enter
prompt then press enter here to commit the blue session
accept x prompt " "

commit;

prompt commit issued.
prompt The green session should still be blocked.
prompt Press enter here to terminate the blue
prompt session. This should unblock the green
prompt session.
accept x prompt " "


exit;

Installed - dbms - lock - green.sql

prompt
prompt The "green" session
prompt ===================
prompt

prompt the Blue session has allocated a lock
prompt press enter to try to allocate the same lock
accept x prompt "in the green session as well."
prompt


variable lockhandle varchar2(100) 

variable x_mode     number
exec :x_mode := dbms_lock.x_mode

exec dbms_lock.allocate_unique('tq84-lock', :lockhandle)
exec dbms_output.put_line ('lockhandle ' ||  :lockhandle || ' allocated');
select case dbms_lock.request(:lockhandle, :x_mode) 
       when 0 then 'Success'
       when 1 then 'Timeout'
       when 2 then 'Deadlock'
       when 3 then 'Parameter error'
       when 4 then 'Already own lock'
       when 5 then 'Illegal lock handle' end from dual;

exit;

Installed - dbms - lock - run.bat

@set   connection_string=rene/rene

@start session_blue.bat
@start session_green.bat

Installed - dbms - lock - session_blue.bat

@rem echo %connection_string%

@color 1f

@sqlplus -S %connection_string% @blue

Installed - dbms - lock - session_green.bat

@rem echo %connection_string%

@color 2f

@sqlplus -S %connection_string% @green

Installed - dbms - lock - stop_sessions - blue.sql

declare

   lock_handle_stop    varchar2(100);
   lock_handle_running varchar2(100);

   success number;
   dummy   number;


begin
   -- We indicate that we're running
   dbms_lock.allocate_unique('running', lock_handle_running);
   success := dbms_lock.request(lock_handle_running, dbms_lock.s_mode, 0);



   dbms_lock.allocate_unique('stop-iteration', lock_handle_stop);

   loop

   --  We try to allocate a shared lock on lock_handle_stop.
   --
   --
   --  If another session already holds the lock exclusively,
   --  request will return 1 (that is: timeout).
   --
   --  We wait 0 seconds, so that we immediatly can
   --  exit the loop.


       success := dbms_lock.request(lock_handle_stop, dbms_lock.s_mode, 0.1);

       --  We also release the lock immediatly, so that we give
       --  another session the chance to lock it exclusively:
       dummy   := dbms_lock.release(lock_handle_stop);


       if success = 1 then -- timeout has occured
          exit;
       end if;

   --  Wait for 5 to 10 seconds
   --
   --  This would be the "critical part".
       dbms_lock.sleep(dbms_random.value(5,10));

   end loop;

   --         We're not really running anymore, so
   --         we release the according lock, so that
   --         the "green session" can enslock on this ure we're
   --         not running anymore by successfully
   --         requesting an exclusive lock
   dummy   := dbms_lock.release(lock_handle_running);

end;
/

exit

Installed - dbms - lock - stop_sessions - green.sql

prompt
prompt press enter to stop the blue sessions
accept x prompt " "

declare

   lock_handle_stop      varchar2(100);
   lock_handle_running   varchar2(100);

   success number;
   dummy   number;


begin
   dbms_lock.allocate_unique('running'       , lock_handle_running);
   dbms_lock.allocate_unique('stop-iteration', lock_handle_stop   );


   -- We try to request an exclusive lock.
   -- As soon as we got it, we know that the other jobs are not
   -- in the "critical part"
   --
   -- We try 60 seconds only
   success := dbms_lock.request(lock_handle_stop, dbms_lock.x_mode, 60);

   if success = 1 then
      dbms_output.put_line('Timeout, could not stop other jobs');
   else

      success := dbms_lock.request(lock_handle_stop, dbms_lock.x_mode, 60);

   -- Now that we have exclusively gotten the running lock (which means that
   -- the "critical part" can only be left, but not entered anymore, we
   -- also want the running lock exclusively which we get as soon as all
   --"blue" jobs have indicated they're leaving the processing.
      success := dbms_lock.request(lock_handle_running, dbms_lock.x_mode, 60);

      dbms_output.put_line('Other jobs should now be terminated, success: ' || success);
   end if;

   dummy   := dbms_lock.release(lock_handle_stop);
end;
/

exit

Installed - dbms - lock - stop_sessions - run.bat

@set   connection_string=rene/rene

@rem   --------------------------------
@rem   start four sessions

@start session_blue.bat
@start session_blue.bat
@start session_blue.bat
@start session_blue.bat



@rem   --------------------------------
@rem   start another session to stop the
@rem   executions of the other four 
@rem   sessions:

@start session_green.bat

Installed - dbms - lock - stop_sessions - session_blue.bat

@color 1f

@sqlplus -S %connection_string% @blue

Installed - dbms - lock - stop_sessions - session_green.bat

@color 2f

@sqlplus -S %connection_string% @green

Installed - dbms - metadata - extract_table_definition.sql

create table tq84_t (
   id     number,
   col_1  varchar2(20),
   col_2  date,
   --
   constraint tq84_t_cpk primary key (id)
);


create unique index tq84_t_ix1 on tq84_t(col_1);
create        index tq84_t_ix2 on tq84_t(col_2);


create table tq84_def (id number primary key, def clob);
create sequence tq84_seq;


declare
  def varchar2(32000);
begin

  def := dbms_metadata.get_ddl('TABLE', 'TQ84_T');

  insert into tq84_def values (tq84_seq.nextval, def);

  for ix in (select index_name from user_indexes where table_name = 'TQ84_T') loop

      def := dbms_metadata.get_ddl('INDEX', ix.index_name);
      insert into tq84_def values (tq84_seq.nextval, def);

  end loop;

end;
/

select def from tq84_def order by id;

drop sequence tq84_seq;
drop table    tq84_def purge;
drop table    tq84_t   purge;

Installed - dbms - metadata - user.sql

select dbms_metadata.get_ddl('USER', 'RENE') from dual;

Installed - dbms - mining - model - attributes - show.sql

select
  owner,
  model_name,
  attribute_name,
  attribute_type,
  data_type,
  data_length,
  data_precision,
  data_scale,
  usage_type,
  target,
  attribute_spec
from
  dba_mining_model_attributes
order by
  owner,
  model_name,
  attribute_name;

Installed - dbms - mining - models - show.sql

select
  owner,
  model_name,
  mining_function,
  algorithm,
  creation_date,
  build_duration,
  model_size,
  comments
from
  dba_mining_models
order by
  owner,
  model_name;

Installed - dbms - mining - settings - show.sql

select
  owner,
  model_name,
  setting_name,
  setting_value,
  setting_type
from
  dba_mining_model_settings
order by
  owner,
  model_name,
  setting_name;

Installed - dbms - predictive_analytics - explain_and_predict_and_profile.sql

drop table tq84_pred_data       purge;
drop table tq84_pred_result     purge;
drop table tq84_pred_prediction purge;

create table tq84_pred_data (
  id    number  primary key,
  x_1   number,
  x_2   number,
  x_3   number,
  res   number
);

declare
  row_ tq84_pred_data%rowtype;

  rnd  number;
  x2   number;
  dt   number;

begin



  for i in 1 .. 100 loop

    row_.id  := i;
    row_.x_1 := 4 * dbms_random.normal + 10;
    row_.x_2 := 5 * dbms_random.normal +  6;
    row_.x_3 := 6 * dbms_random.normal + 12;

    if    row_.x_1 < 7    then
          row_.res := 0;
    else

      if row_.x_2 < 8 then
         row_.res := 1;
      else

        if row_.x_3 < 11 then
          row_.res := 0;
        else
          row_.res := 1;
        end if;

      end if;

    end if;
       

    insert into tq84_pred_data values row_;
  end loop;

end;
/

commit;

-- select count(*) from tq84_pred_data;
-- select       *  from tq84_pred_data;
   select count(*), res from tq84_pred_data group by res;

begin

  dbms_predictive_analytics.explain (
    data_table_name     => 'tq84_pred_data',
    explain_column_name => 'res',
    result_table_name   => 'tq84_pred_result'
  );

end;
/

select
  substrb(attribute_name   , 1, 30) attribute_name,
  substrb(attribute_subname, 1, 30) attribute_subname,
  explanatory_value                 explanatory_value,
  rank                              rank
from
  tq84_pred_result
order by
  rank;


declare
  v_accuracy number;
begin

  dbms_predictive_analytics.predict (
    accuracy            =>  v_accuracy,
    data_table_name     => 'tq84_pred_data',
    case_id_column_name => 'id',
    target_column_name  => 'res',
    result_table_name   => 'tq84_pred_prediction'
  );

  dbms_output.put_line('accuracy: ' || v_accuracy);

end;
/

select 
  p.prediction,
  d.res
from
  tq84_pred_prediction p join
  tq84_pred_data       d on p.id = d.id;


begin

  dbms_predictive_analytics.profile (
    data_table_name    => 'tq84_pred_data',
    target_column_name => 'res',
    result_table_name  => 'tq84_pred_profile'
  );

end;
/

select
  count(*),
  sum(record_count)
from
  tq84_pred_profile;

select
--profile_id,
--record_count,
  description
from
  tq84_pred_profile;

Installed - dbms - stats - avg_row_len.sql

--
--  db_block_size: 8192
--

create table tq84_avg_row_len (
  text_1  varchar2(1000),
  text_2  varchar2(1000),
  dt      date    
);



insert into tq84_avg_row_len values (lpad('*',  50, '*'), lpad('!',  100, '!') , sysdate + 1000);
insert into tq84_avg_row_len values (lpad('*', 421, '*'), null                 , null          );
insert into tq84_avg_row_len values (null               , null                 , null          );
insert into tq84_avg_row_len values (lpad('*', 800, '*'), lpad('!',    4, '!') , sysdate - 1000);
insert into tq84_avg_row_len values (lpad('*',   7, '*'), lpad('!',  896, '!') , null          );
insert into tq84_avg_row_len values (lpad('*', 139, '*'), null                 , sysdate       );
insert into tq84_avg_row_len values (lpad('*',  26, '*'), lpad('!',   39, '!') , sysdate       );

select sum(

   1                                         + -- for each row
   --------------------------------------
   nvl2(text_1, vsize(text_1)+1, 0)          + 
   nvl2(text_2, vsize(text_2)+1, 0)          +
   nvl2(dt    , vsize(dt    )  , 0)
-- case when dt is null then 0 else 7 end       -- A date occupies 7 bytes
   --------------------------------------
  ) / count(*) avg_row_len
from
  tq84_avg_row_len;


exec dbms_stats.gather_table_stats(user, 'tq84_avg_row_len', method_opt => 'for all columns');

select
  avg_row_len
from
  user_tables
where
  table_name = 'TQ84_AVG_ROW_LEN';


drop table tq84_avg_row_len purge;

Installed - dbms - stats - constants.sql

declare
  v varchar2(100);
begin


  dbms_output.new_line;

  for c in (
    select 'dbms_stats.add_global_prefs'    n from dual union all
    select 'dbms_stats.auto_cascade'        n from dual union all
    select 'dbms_stats.auto_degree'         n from dual union all
    select 'dbms_stats.auto_invalidate'     n from dual union all
    select 'dbms_stats.auto_sample_size'    n from dual union all
    select 'dbms_stats.purge_all'           n from dual union all
    select 'dbms_stats.reclaim_synopsis'    n from dual 
  ) loop

    begin
      execute immediate q'!
        declare
          function tc (b boolean ) return varchar2 is begin if b is null then return 'null'; elsif b then return 'true'; else return 'false'; end if; end;
          function tc (c varchar2) return varchar2 is begin return c; end;
        begin :1 := tc(!' || c.n || '); end;' using out v;
      dbms_output.put_line(rpad(c.n, 27) || ': ' || v);

    exception when others then
      dbms_output.put_line(c.n || ': ' || sqlerrm);
    end;

  end loop;

  dbms_output.new_line;
  
end;
/

Installed - dbms - stats - gather_table_stats_01.sql

drop table tq84_tab_stat purge;

create table tq84_tab_stat (
  pk      number primary key,
  nm      number,
  vc      varchar2(20),
  dt      date
);


declare
  rec  tq84_tab_stat%rowtype;
begin

  for i in 0 .. 999 loop

    rec.pk := i + 100000;

    rec.nm := case
      when i < 300  then 5
      when i < 500  then 7
      when i < 600  then 3.141
      when i < 700  then null
      else               i/3 end;

    rec.vc := case
      when i < 400  then 'foo bar baz'
      when i < 700  then 'ab cdefgh ijklmno'
      when i < 800  then 'zgraggen'
      when i < 900  then  dbms_random.string('x', 10)
      else                null end;

    rec.dt := case
      when i < 200  then  date '2010-08-13'
      when i < 300  then  to_date ('2015-05-20 13:56:07', 'yyyy-mm-dd hh24:mi:ss')
      when i < 800  then  date '2016-01-01' + 1127/500 * i
      else                null end;

    insert into tq84_tab_stat values rec;
          

  end loop;

end;
/

commit;

exec dbms_stats.gather_table_stats(user, 'tq84_tab_stat', method_opt => 'for all columns size 10');

column column_name format a4
column data_type   format a20

select
  h.column_name,
  h.endpoint_number,
  --endpoint_number - lag(endpoint_number) over (order by ) x,
--substrb(h.endpoint_actual_value, 1, 50),
  case c.data_type
    when 'NUMBER'   then to_char(h.endpoint_value)
    when 'DATE'     then to_char(to_date(to_char(h.endpoint_value, 'FM99999999') || '.' || to_char(86400 * mod(h.endpoint_value, 1), 'FM99999'), 'J.sssss'))
    when 'VARCHAR2' then chr(to_number(substr(to_char(h.endpoint_value, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 2, 2), 'XX')) ||
                         chr(to_number(substr(to_char(h.endpoint_value, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 4, 2), 'XX')) ||
                         chr(to_number(substr(to_char(h.endpoint_value, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 6, 2), 'XX')) ||
                         chr(to_number(substr(to_char(h.endpoint_value, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 8, 2), 'XX')) ||
                         chr(to_number(substr(to_char(h.endpoint_value, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),10, 2), 'XX')) ||
                         chr(to_number(substr(to_char(h.endpoint_value, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),12, 2), 'XX')) 
  end
from
  user_tab_histograms h  join
  user_tab_columns    c  on h.table_name  = c.table_name and
                            h.column_name = c.column_name
where
  h.table_name = 'TQ84_TAB_STAT'
order by
  c.column_id,
  h.endpoint_number;

select
  column_name,
  num_distinct,
--low_value,
--high_value,
  density,
  num_nulls,
  num_buckets,
  sample_size,
  global_stats,
  user_stats,
  histogram
from
  user_tab_col_statistics 
where
  table_name = 'TQ84_TAB_STAT';

Installed - dbms - stats - get_prefs.sql

select
  p.name,
  substr(dbms_stats.get_prefs(upper(p.name)), 1, 30) val
from (
  select 'autostats_target'            name from dual union all
  select 'cascade'                     name from dual union all
  select 'concurrent'                  name from dual union all
  select 'degree'                      name from dual union all
  select 'estimate_percent'            name from dual union all
  select 'method_opt'                  name from dual union all
  select 'no_invalidate'               name from dual union all
  select 'granularity'                 name from dual union all
  select 'publish'                     name from dual union all
  select 'incremental'                 name from dual union all
  select 'incremental_staleness'       name from dual union all
  select 'incremental_level'           name from dual union all
  select 'stale_percent'               name from dual union all
  select 'global_temp_table_stats'     name from dual union all
--select 'table_cached_blockes'        name from dual union all
  select 'options'                     name from dual
) p;

Installed - dbms - stats - transaction.sql

--
--  Most of the procedures in dbms_stats commit the current transaction,
--  perform the operation, and then commit again.
--

drop table tq84_tab_stat_trx purge;

create table tq84_tab_stat_trx (
  col  number
);

insert into tq84_tab_stat_trx values (1);
commit;

insert into tq84_tab_stat_trx values (2);

exec dbms_stats.gather_table_stats(user, 'tq84_tab_stat_trx', method_opt => 'for all columns size 10');

select
  num_distinct
from
  user_tab_col_statistics
where
  table_name  = 'TQ84_TAB_STAT_TRX' and
  column_name = 'COL';


rollback;

select * from tq84_tab_stat_trx;

Installed - dbms - xa - session_01.sql

declare

    ret_txt varchar2(100);
begin

 -- tmnoflags: create new transaction branch
    ret_txt := tq84_xa_result_to_string( sys.dbms_xa.xa_start(dbms_xa_xid(42), dbms_xa.tmnoflags) );
    if ret_txt != 'OK' then
       dbms_output.put_line('Error opening transaction branch: ' || ret_txt);
       return;
    end if;

    insert into tq84_xa values ('This is session one');

    ret_txt := tq84_xa_result_to_string( sys.dbms_xa.xa_end(dbms_xa_xid(42), dbms_xa.tmsuspend) );
    if ret_txt != 'OK' then
       dbms_output.put_line('Error: ' || ret_txt);
       return;
    end if;



end;
/

select * from v$global_transaction where globalid = dbms_xa_xid(42).gtrid;

Installed - dbms - xa - session_02.sql

select * from tq84_xa;


declare


    ret_txt varchar2(100);

begin

 -- Note: tmresume rather than tmnoflags
    ret_txt := tq84_xa_result_to_string( sys.dbms_xa.xa_start(dbms_xa_xid(42), dbms_xa.tmresume) );
    if ret_txt != 'OK' then
       dbms_output.put_line('Error opening transaction branch: ' || ret_txt);
       return;
    end if;

    insert into tq84_xa values ('This is session two');

 -- Note tmsuccess rather than tmsuspend
    ret_txt := tq84_xa_result_to_string( sys.dbms_xa.xa_end(dbms_xa_xid(42), dbms_xa.tmsuccess) );
    if ret_txt != 'OK' then
       dbms_output.put_line('Error: ' || ret_txt);
       return;
    end if;



end;
/

select * from tq84_xa;

Installed - dbms - xa - session_03.sql

select * from tq84_xa;

declare
    ret_txt varchar2(100);
begin

    ret_txt := tq84_xa_result_to_string( sys.dbms_xa.xa_commit(dbms_xa_xid(42), onePhase => true) );
    
    if ret_txt != 'OK' then
       dbms_output.put_line('Error committing: ' || ret_txt);
    end if;
end;
/

select * from tq84_xa;

Installed - dbms - xa - setup.sql

@drop_if_exists tq84_xa

create table tq84_xa (
   txt       varchar2(40)
);

@drop_if_exists tq84_xa_result_to_string

create function tq84_xa_result_to_string(res number) return varchar2 as
begin

  return 
    case res
      when sys.dbms_xa.xa_ok           then 'OK'
      when sys.dbms_xa.xa_rbbase       then 'XA_RBBASE: Inclusive lower bound of the rollback codes'
      when sys.dbms_xa.xa_rbrollback   then 'XA_RBROLLBACK: Rollback was caused by an unspecified reason'
      when sys.dbms_xa.xa_rbcommfail   then 'XA_RBCOMMFAIL: Rollback was caused by a communication failure'
      when sys.dbms_xa.xa_rbdeadlock   then 'XA_RBDEADLOCK: Deadlock was detected'
      when sys.dbms_xa.xa_rbintegrity  then 'XA_RBINTEGRITY: Condition that violates the integrity of the resources was detected'
      when sys.dbms_xa.xa_rbother      then 'XA_RBOTHER: Resource manager rolled back the transaction for an unlisted reason'
      when sys.dbms_xa.xa_rbproto      then 'XA_RBPROTO: Protocol error occurred in the resource manager'
      when sys.dbms_xa.xa_rbtimeout    then 'XA_RBTIMEOUT: Transaction branch took long'
      when sys.dbms_xa.xa_rbtransient  then 'XA_RBTRANSIENT: May retry the transaction branch'
      when sys.dbms_xa.xa_rbend        then 'XA_RBEND: Inclusive upper bound of the rollback codes'
      when sys.dbms_xa.xa_nomigrate    then 'XA_NOMIGRATE: Transaction branch may have been heuristically completed'
      when sys.dbms_xa.xa_heurhaz      then 'XA_HEURHAZ: Transaction branch may have been heuristically completed'
      when sys.dbms_xa.xa_heurcom      then 'XA_HEURCOM: Transaction branch has been heuristically committed'
      when sys.dbms_xa.xa_heurrb       then 'XA_HEURRB: Transaction branch has been heuristically rolled back'
      when sys.dbms_xa.xa_heurmix      then 'XA_HEURMIX: Some of the transaction branches have been heuristically committed, others rolled back'
      when sys.dbms_xa.xa_retry        then 'XA_RETRY: Routine returned with no effect and may be re-issued'
      when sys.dbms_xa.xa_rdonly       then 'XA_RDONLY: Transaction was read-only and has been committed'
      when sys.dbms_xa.xa_ok           then 'XA_OK: Normal execution'
      when sys.dbms_xa.xaer_async      then 'XAER_ASYNC: Asynchronous operation already outstanding'
      when sys.dbms_xa.xaer_rmerr      then 'XAER_RMERR: Resource manager error occurred in the transaction branch'
      when sys.dbms_xa.xaer_nota       then 'XAER_NOTA: XID is not valid'
      when sys.dbms_xa.xaer_inval      then 'XAER_INVAL: Invalid arguments were given'
      when sys.dbms_xa.xaer_proto      then 'XAER_PROTO: Routine invoked in an improper context'
      when sys.dbms_xa.xaer_rmfail     then 'XAER_RMFAIL: Resource manager unavailable'
      when sys.dbms_xa.xaer_dupid      then 'XAER_DUPID: XID already exists'
      when sys.dbms_xa.xaer_outside    then 'XAER_OUTSIDE: Resource manager doing work outside global transaction'
      else '?'
    end;


end tq84_xa_result_to_string;
/

PL-SQL - accessible_by - example-01.plsql

create or replace package tq84_base_functionality
  ---------------------------
  ACCESSIBLE BY (tq84_pkg_ok)
  ---------------------------
is
  procedure p;
end tq84_base_functionality;
/

create or replace package body tq84_base_functionality as
  
  procedure p is begin
    dbms_output.put_line('tq84_base_functionality.p was called');
  end p;

end tq84_base_functionality;
/

create or replace package tq84_pkg_ok as
  procedure p;
end tq84_pkg_ok;
/

create or replace package tq84_pkg_not_ok as
  procedure p;
end tq84_pkg_not_ok;
/

create or replace package body tq84_pkg_ok as

  procedure p is begin
    tq84_base_functionality.p;
  end p;

end tq84_pkg_ok;
/

create or replace package body tq84_pkg_not_ok as
  procedure p is begin
    null;
--  tq84_base_functionality.p;

--  PLS-00904: insufficient privilege to access object TQ84_BASE_FUNCTIONALITY
  end p;
end tq84_pkg_not_ok;
/

drop package tq84_base_functionality;
drop package tq84_pkg_ok;
drop package tq84_pkg_not_ok;

PL-SQL - blocks - error_in_declare.plsql

-- where is an error caught that originates from with
-- a declare block?


declare  -- Outer Block {

   o  varchar2(10) := 'outer';

begin

   declare  -- Inner Block {

      i  varchar2(1) := 'inner'; -- Oh oh...

   begin

      dbms_output.put_line('not reached');

   exception when others then

   -- The exception is NOT caught here!
     
      dbms_output.put_line('Inner exception: ' || sqlerrm);

   end; -- }

exception when others then
  
   dbms_output.put_line('Outer exception: ' || sqlerrm);

end; -- }
/

PL-SQL - blocks - nested_proc_with_same_name.plsql

declare

  procedure p (a in varchar2) is 

     procedure p (a in varchar2) is
     begin

         dbms_output.put_line('inner: ' || a);

     end p;

  begin

    p('outer: ' || a);

  end p;

begin

  p('foo');
  
end;
/

PL-SQL - bulk_collect - no_data_found.plsql

create type tq84_bulk_collect_tab as table of varchar2(100);
/

create table tq84_bulk_collect_table (
  col1 number,
  col2 varchar2(100)
);

insert into tq84_bulk_collect_table values (1, 'one');
insert into tq84_bulk_collect_table values (2, 'two');
insert into tq84_bulk_collect_table values (3, 'three');


declare

  b  tq84_bulk_collect_tab;

begin

  select col2 bulk collect into b 
    from tq84_bulk_collect_table 
   where col1 > 100; -- No records match!

   dbms_output.put_line ('b.count: ' || b.count);

exception when no_data_found then

-- Not reached:
   dbms_output.put_line ('no_data_found');

end;
/

drop table tq84_bulk_collect_table purge;
drop type  tq84_bulk_collect_tab;

PL-SQL - bulk_collect - group_by - multiple_layers.plsql

-- Tables {

create table tq84_a (
       id    number primary key,
       txt   varchar2(10)
);

create table tq84_b (
       id    number primary key,
       id_a  references tq84_a,
       txt   varchar2(10)
);

create table tq84_c (
       id    number primary key,
       id_b  references tq84_b,
       txt   varchar2(10)
);

-- }

-- Inserts {

insert into tq84_a values (   1, 'one');

       insert into tq84_b values (  11,  1, 'A');

              insert into tq84_c values (111, 11, '(');
              insert into tq84_c values (112, 11, ')');

       insert into tq84_b values (  12,  1, 'B');

              insert into tq84_c values (121, 12, '!');
              insert into tq84_c values (122, 12, '?');
              insert into tq84_c values (123, 12, '.');
              insert into tq84_c values (124, 12, ',');

       insert into tq84_b values (  13,  1, 'C');

insert into tq84_a values (   2, 'two');


insert into tq84_a values (   3, 'two');

       insert into tq84_b values (  31,  3, 'Y');
       insert into tq84_b values (  32,  3, 'Z');

-- }

-- Types {

create type tq84_c_o as object (
       id   number,
       txt  varchar2(10)
);
/

create type tq84_c_t as table of tq84_c_o;
/

create type tq84_b_o as object (
       id   number,
       txt  varchar2(10),
       c    tq84_c_t
);
/

create type tq84_b_t as table of tq84_b_o;
/

create type tq84_a_o as object (
       id   number,
       txt  varchar2(10),
       b    tq84_b_t
);
/

create type tq84_a_t as table of tq84_a_o;
/

-- }

declare

  complete_tree tq84_a_t;

begin

  select tq84_a_o (
    a.id,
    a.txt,
    -----
      cast ( collect (tq84_b_o ( b.id, b.txt, null ) ) as tq84_b_t )
  ) bulk collect into complete_tree
  from 
        tq84_a a                   join
        tq84_b b on a.id = b.id_a
  group by 
        a.id,
        a.txt;

  dbms_output.new_line;
  for a in 1 .. complete_tree.count loop

      dbms_output.put_line('Id: ' || complete_tree(a).id || ', txt: ' || complete_tree(a).txt);

      for b in 1 .. complete_tree(a).b.count loop

          dbms_output.put_line('   Id: ' || complete_tree(a).b(b).id || ', txt: ' || complete_tree(a).b(b).txt);

          -- ? if complete_tree(a).b(b).c is not null then
          -- ?    for c in 1 .. complete_tree(a).b(b).c.count loop
          -- ?
          -- ?        dbms_output.put_line('      Id: ' || complete_tree(a).b(b).c(c).id || ', txt: ' || complete_tree(a).b(b).c(c).txt);
          -- ?
          -- ?    end loop;
          -- ?    dbms_output.new_line;
          -- ? end if;

      end loop;
      dbms_output.new_line;

  end loop;


--
--  select tq84_outer (o.i,
--                     o.j,
--                     cast(collect(  tq84_inner(i.n, i.t)  order by i.n) as tq84_inner_t)
--                    )
--    bulk collect into t
--    from tq84_o    o    join
--         tq84_i    i    on o.i = i.i
--   group by o.i, o.j;
--
--   --
--
---- does not work  select tq84_outer__ (o.i, -- {
---- does not work                     o.j,
---- does not work                     cast(collect(  tq84_inner__(i.n, i.t)  order by i.n) as tq84_inner_t__)
---- does not work                    )
---- does not work    bulk collect into t
---- does not work    from tq84_o    o    join
---- does not work         tq84_i    i    on o.i = i.i
---- does not work   group by o.i, o.j; -- }
--
--   --
--
--   dbms_output.put_line('Cnt: ' || t.count);
--
--   dbms_output.put_line('Cnt (2): ' || t(2).inner_.count);
--
--   dbms_output.put_line(t(3).inner_(2).t);
--
--   ----------------------------------------------------
--
--   for r in ( 
--       select i, j from table(t)
--   ) loop -- {
--
--     dbms_output.put_line('i: ' || r.i || ', j: ' || r.j);
--
--   end loop; -- }
--
--   ----------------------------------------------------
--
--   dbms_output.new_line;
--
--   ----------------------------------------------------
--
--   for r in ( 
--       select 
--         outer_.i, 
--         outer_.j,
--         inner_.n,
--         inner_.t
--       from 
--         table(t)             outer_, 
--         table(outer_.inner_) inner_
--   ) loop -- {
--
--     dbms_output.put_line('i: ' || r.i || ', j: ' || r.j || ', n: ' || r.n || ', t: ' || r.t);
--
--   end loop; -- }
--
--   ----------------------------------------------------
--
end;
/

drop type  tq84_a_t force;
drop type  tq84_a_o force;
drop type  tq84_b_t force;
drop type  tq84_b_o force;
drop type  tq84_c_t force;
drop type  tq84_c_o force;

drop table tq84_c purge;
drop table tq84_b purge;
drop table tq84_a purge;

PL-SQL - bulk_collect - group_by - test_01.plsql

create type tq84_inner as object (
  n number,
  t varchar2(10)
);
/

create type tq84_inner_t as table of tq84_inner;
/

create type tq84_outer as object (
  i       number,
  j       varchar2(10),
  inner_  tq84_inner_t
);
/

create type tq84_outer_t as table of tq84_outer;
/

create table tq84_o (
  i  number primary key,
  j  varchar2(10)
);

create table tq84_i (
  i   references tq84_o,
  n   number,
  t   varchar2(10)
);

-- Inserts {
insert into tq84_o values (1, 'one');
insert into tq84_o values (2, 'two');
insert into tq84_o values (3, 'three');
insert into tq84_o values (4, 'four');

insert into tq84_i values (1, 1, 'apple');
insert into tq84_i values (1, 2, 'pear');
insert into tq84_i values (1, 3, 'lemon');
insert into tq84_i values (1, 4, 'grape');

insert into tq84_i values (2, 1, 'car');
insert into tq84_i values (2, 2, 'bike');

insert into tq84_i values (3, 1, 'foo');
insert into tq84_i values (3, 2, 'bar');
insert into tq84_i values (3, 3, 'baz');
-- }

declare

  t tq84_outer_t;

-- does not work  type tq84_inner__ is record ( -- {
-- does not work    n number,
-- does not work    t varchar2(10)
-- does not work  );
-- does not work
-- does not work  type tq84_inner_t__ is table of tq84_inner__;
-- does not work
-- does not work  type tq84_outer__ is record (
-- does not work    i       number,
-- does not work    j       varchar2(10),
-- does not work    inner_  tq84_inner_t
-- does not work  );
-- does not work  
-- does not work  type tq84_outer_t__ is table of tq84_outer__;
-- does not work
-- does not work  t__ tq84_outer_t__; -- }

begin

  select tq84_outer (o.i,
                     o.j,
                     cast(collect(  tq84_inner(i.n, i.t)  order by i.n) as tq84_inner_t)
                    )
    bulk collect into t
    from tq84_o    o    join
         tq84_i    i    on o.i = i.i
   group by o.i, o.j;

   --

-- does not work  select tq84_outer__ (o.i, -- {
-- does not work                     o.j,
-- does not work                     cast(collect(  tq84_inner__(i.n, i.t)  order by i.n) as tq84_inner_t__)
-- does not work                    )
-- does not work    bulk collect into t
-- does not work    from tq84_o    o    join
-- does not work         tq84_i    i    on o.i = i.i
-- does not work   group by o.i, o.j; -- }

   --

   dbms_output.put_line('Cnt: ' || t.count);

   dbms_output.put_line('Cnt (2): ' || t(2).inner_.count);

   dbms_output.put_line(t(3).inner_(2).t);

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

   for r in ( 
       select i, j from table(t)
   ) loop -- {

     dbms_output.put_line('i: ' || r.i || ', j: ' || r.j);

   end loop; -- }

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

   dbms_output.new_line;

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

   for r in ( 
       select 
         outer_.i, 
         outer_.j,
         inner_.n,
         inner_.t
       from 
         table(t)             outer_, 
         table(outer_.inner_) inner_
   ) loop -- {

     dbms_output.put_line('i: ' || r.i || ', j: ' || r.j || ', n: ' || r.n || ', t: ' || r.t);

   end loop; -- }

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

end;
/


drop table tq84_i purge;
drop table tq84_o purge;

drop type tq84_outer_t;
drop type tq84_outer;
drop type tq84_inner_t;
drop type tq84_inner force;

PL-SQL - case - which_line_reports_error.plsql

create or replace package tq84_case as

  procedure d;

end tq84_case;
/

create or replace package body tq84_case as

  procedure d as
    a number := 4;
    b number := 3;
  begin


    case a when 1 then output.print('a = 1'); -- Line 9
           when 2 then output.print('a = 2');
           when 3 then output.print('a = 3');
           when 4 then case b
                       when 1 then output.print('b = 1');
                       when 2 then output.print('b = 2');
                       end case;

    end case;

  end d;

end tq84_case;
/


--   Which line is reported as having an error?
exec tq84_case.d

PL-SQL - CollectionTypes - IndexByTables-aka-AssociativeArrays - exists.plsql

declare

  type r is record (
    nm number,
    vc varchar2(20)
  );

  type t is table of r index by varchar2(10);

  v    t;

  procedure out_1 (key_ in varchar2) is -- {
  begin
     dbms_output.put_line(key_ || ': ' || v(key_).nm || ' - ' || v(key_).vc);
  exception 
    when no_data_found then
         dbms_output.put_line('No entry found for ' || key_);
    when others then
         raise;
  end out_1; -- }
  
  procedure out_2 (key_ in varchar2) is -- {
  begin
     if v.exists(key_) then
        dbms_output.put_line(key_ || ': ' || v(key_).nm || ' - ' || v(key_).vc);
     else
        dbms_output.put_line('No entry found for ' || key_);
     end if;
  end out_2; -- }
  

begin

  v('foo').nm  :=  42; v('foo').vc  := 'forty-two';
  v('bar').nm  :=  11; v('bar').vc  := 'eleven';

  dbms_output.new_line;

  out_1('foo');
  out_1('bar');
  out_1('baz');

  dbms_output.new_line;

  out_2('foo');
  out_2('bar');
  out_2('baz');
  
  dbms_output.new_line;

end;
/

PL-SQL - CollectionTypes - IndexByTables-aka-AssociativeArrays - iterate_over.plsql

declare

  type r is record (
    nm number,
    vc varchar2(20)
  );

  type t is table of r index by varchar2(10);

  v    t;


  key_  varchar2(10);

begin

  v('foo').nm  :=  42; v('foo').vc  := 'forty-two';
  v('bar').nm  :=  11; v('bar').vc  := 'eleven';
  v('baz').nm  :=  20; v('baz').vc  := 'twenty';

  key_ := v.first;

  while key_ is not null loop
        dbms_output.put_line(key_ || ': ' || v(key_).nm || ' - ' || v(key_).vc);
        key_ := v.next(key_);
  end loop;
  
end;
/

PL-SQL - CollectionTypes - NestedTables - count.plsql

create type tq84_number_tab as table of number;
/


declare

  numbers tq84_number_tab := tq84_number_tab(1, 2, 3, 4, 5);

begin

  -- count is evaluated once, at the beginning
  -- of the loop.
  for i in 1 .. numbers.count loop

      dbms_output.put_line(numbers(i));

      numbers.extend;
      numbers(numbers.count) := numbers.count;

      exit when i > 100;

  end loop;

end;
/


drop type tq84_number_tab;

PL-SQL - CollectionTypes - NestedTables - select_cardinality.sql

create type tq84_obj as object (
   a   number,
   b   varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/


create table tq84_table (
  id     number,
  obj_t  tq84_obj_t
)
nested table obj_t store as tq84_table_t;


insert into tq84_table values (1, tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two' )                     ));
insert into tq84_table values (2, tq84_obj_t(tq84_obj(1, 'eins'), tq84_obj(2, 'zwei'), tq84_obj(3, 'drei')));

select
  t.id,
  cardinality(t.obj_t)
from
  tq84_table     t;



drop table tq84_table;
drop type tq84_obj_t;
drop type tq84_obj;

PL-SQL - CollectionTypes - NestedTables - select_from_table_table.sql

create type tq84_obj as object (
   a   number,
   b   varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/


create table tq84_table (
  id         number,
  obj_t_1    tq84_obj_t,
  obj_t_2    tq84_obj_t
)
nested table obj_t_1 store as tq84_table_t_1,
nested table obj_t_2 store as tq84_table_t_2;

insert into tq84_table values (1,
  tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two' )                     ),
  tq84_obj_t(tq84_obj(1, 'eins'), tq84_obj(2, 'zwei'), tq84_obj(3, 'drei'))
);

insert into tq84_table values (1,
  tq84_obj_t(tq84_obj(42, 'foo' )),
  tq84_obj_t(                   ))
);

insert into tq84_table values (1,
  tq84_obj_t(tq84_obj(98, 'X'   )),
  tq84_obj_t(tq84_obj(99, 'Y'   ))
);

select t.id, u.a, u.b, v.a, v.b from
  tq84_table t,
  table(t.obj_t_1) u,
  table(t.obj_t_2) v;

drop table tq84_table;
drop type tq84_obj_t;
drop type tq84_obj;

PL-SQL - CollectionTypes - NestedTables - select_from_the.sql

create type tq84_obj as object (
   a   number,
   b   varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/


create table tq84_table (
  id     number,
  obj_t  tq84_obj_t
)
nested table obj_t store as tq84_table_t;


insert into tq84_table values (1, tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two' )                     ));
insert into tq84_table values (2, tq84_obj_t(tq84_obj(1, 'eins'), tq84_obj(2, 'zwei'), tq84_obj(3, 'drei')));

select * from the (select obj_t from tq84_table where rownum = 1);

select
  t.id,
  u.a,
  u.b
from
  tq84_table t,
  the (select obj_t from tq84_table u where u.id = t.id) u
--the (select obj_t from t) u   -- <== Does not return a record!
 ;

drop table tq84_table;
drop type tq84_obj_t;
drop type tq84_obj;

PL-SQL - CollectionTypes - NestedTables - select_from_the_the.sql

create type tq84_obj as object (
   a   number,
   b   varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/


create table tq84_table (
  id         number,
  obj_t_1    tq84_obj_t,
  obj_t_2    tq84_obj_t
)
nested table obj_t_1 store as tq84_table_t_1,
nested table obj_t_2 store as tq84_table_t_2;

insert into tq84_table values (1,
  tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two' )                     ),
  tq84_obj_t(tq84_obj(1, 'eins'), tq84_obj(2, 'zwei'), tq84_obj(3, 'drei'))
);

insert into tq84_table values (2,
  tq84_obj_t(tq84_obj(42, 'foo' )),
  tq84_obj_t(                   ))
);

insert into tq84_table values (3,
  tq84_obj_t(tq84_obj(98, 'X'   )),
  tq84_obj_t(tq84_obj(99, 'Y'   ))
);

select t.id, u.a, u.b, v.a, v.b from
  tq84_table t,
  the(select t.obj_t_1 from tq84_table u where u.id = t.id) u,
  the(select t.obj_t_2 from tq84_table v where v.id = t.id) v;

drop table tq84_table;
drop type tq84_obj_t;
drop type tq84_obj;

PL-SQL - CollectionTypes - NestedTables - where_member_of.sql

create table tq84_member_of (
  id   number primary key,
  txt  varchar2(10)
);

insert into tq84_member_of values (1, 'one'  );
insert into tq84_member_of values (2, 'two'  );
insert into tq84_member_of values (3, 'three');
insert into tq84_member_of values (4, 'four' );

create type tq84_number_tab as table of number;
/


select * from tq84_member_of 
 where id member of tq84_number_tab( 2, 4, 6);

drop type tq84_number_tab;

drop table tq84_member_of;

PL-SQL - cursor - cursor-attributes.sql

create table tq84_cursor_attr_test (
  a number
);

begin

  insert into tq84_cursor_attr_test select rownum from all_objects where rownum <= 10;
  dbms_output.put_line('rowcount: ' || sql%rowcount);

  insert into tq84_cursor_attr_test select * from tq84_cursor_attr_test;
  dbms_output.put_line('rowcount: ' || sql%rowcount);

  insert into tq84_cursor_attr_test select * from tq84_cursor_attr_test;
  dbms_output.put_line('rowcount: ' || sql%rowcount);

  insert into tq84_cursor_attr_test select * from tq84_cursor_attr_test;
  dbms_output.put_line('rowcount: ' || sql%rowcount);

  delete from tq84_cursor_attr_test where a = 5;
  if sql%found then
     dbms_output.put_line(sql%rowcount || ' rows where deleted');
  else
     dbms_output.put_line('no rows were deleted');
  end if;

  delete from tq84_cursor_attr_test where a = 5;
  if sql%notfound then
     dbms_output.put_line('no rows were deleted');
  else
     dbms_output.put_line(sql%rowcount || ' rows where deleted');
  end if;

  delete from tq84_cursor_attr_test where a=2;
  delete from tq84_cursor_attr_test where a=3;
  dbms_output.put_line('sql%rowcount before commit: ' || sql%rowcount);

  commit;

  dbms_output.put_line('sql%rowcount after commit: ' || sql%rowcount);

end;
/

drop table tq84_cursor_attr_test purge;

PL-SQL - cursor - for_rec_in_cur.sql

drop table tq84_cursor;
create table tq84_cursor (
  a number,
  b varchar2(10)
);

insert into tq84_cursor values (1, 'one'  );
insert into tq84_cursor values (2, 'two'  );
insert into tq84_cursor values (3, 'three');
insert into tq84_cursor values (4, 'four' );

declare

  cursor cur(a_ number) is
    select * from tq84_cursor
     where a > a_;

begin

  for rec in cur(2) loop
    dbms_output.put_line('a: ' || rec.a || ', b: ' || rec.b);
  end loop;

end;
/

PL-SQL - cursor - open_close.sql

create table tq84_open_close_cursor (
       a  number,
       b  varchar2(20)
);

insert into tq84_open_close_cursor values (1, 'one');
insert into tq84_open_close_cursor values (2, 'two');
insert into tq84_open_close_cursor values (3, 'three');
insert into tq84_open_close_cursor values (3, 'one plus two');


declare -- {


  procedure open_close_cur(p_sel in number) is  -- {

      text varchar2(20);
  
      cursor cur(sel in number) is
             select b 
               from tq84_open_close_cursor
              where a = sel;

   begin

      dbms_output.put_line('p_sel: ' || p_sel);

      open cur(p_sel);

      dbms_output.put_line('  cursor opened');

      fetch cur into text;
      dbms_output.put_line('  feched');
  
      if cur%found then
         dbms_output.put_line('  found, text = ' || text);
      end if;

      close cur;
      dbms_output.put_line('  cursor closed');

      dbms_output.new_line;

  end open_close_cur; -- }


begin

  open_close_cur( 1); 
  open_close_cur( 3); 
  open_close_cur(99); 

end; -- }
/


drop table tq84_open_close_cursor purge;

PL-SQL - cursor - rowcount.sql

create table tq84_rowcount (a number);

insert into tq84_rowcount values (  42);
insert into tq84_rowcount values (  12);
insert into tq84_rowcount values (1000);


declare
  
   cursor c is select * from tq84_rowcount;

begin

  for r in c loop

      dbms_output.put_line(to_char(r.a, '9999') || ' ' || c%rowcount);

  end loop;

end;
/

drop table tq84_rowcount purge;

PL-SQL - cursor - while_found.sql

drop table tq84_cursor;
create table tq84_cursor (
  a number,
  b varchar2(10)
);

insert into tq84_cursor values (1, 'one'  );
insert into tq84_cursor values (2, 'two'  );
insert into tq84_cursor values (3, 'three');
insert into tq84_cursor values (4, 'four' );

declare

  cursor cur(a_ number) is
    select * from tq84_cursor
     where a > a_;

  rec cur%rowtype;

begin

  open cur(1);

  fetch cur into rec;
  while cur%found loop

    dbms_output.put_line('a: ' || rec.a || ', b: ' || rec.b);
    fetch cur into rec;

  end loop;

  close cur;

end;
/

PL-SQL - exception - pragma_init_with_same_number - catcher.pkb

create or replace package body tq84_catcher as

    procedure go is 
    begin

        tq84_thrower_1.go;

    exception
      
      when tq84_thrower_2.exc then
      --
      --   Note, tq84_thrower_2.exc is caught, altough, technically,
      --         tq84_thrower_1.exc is thrown. This is because
      --         both exception share the same errcode (-20777)
      --
           dbms_output.put_line('caught tq84_thrower_2.exc, sqlerrm: ' || sqlerrm);
           
      when others             then
           dbms_output.put_line('caught other, sqlerrm: ' || sqlerrm);
        
    end go;

end tq84_catcher;
/

PL-SQL - exception - pragma_init_with_same_number - catcher.pks

create or replace package tq84_catcher as

    procedure go;

end tq84_catcher;
/

PL-SQL - exception - pragma_init_with_same_number - thrower_1.pkb

create or replace package body tq84_thrower_1 as

    procedure go is begin
        raise_application_error(-20777, 'Package is tq84_thrower_1');
    end go;

end tq84_thrower_1;
/

PL-SQL - exception - pragma_init_with_same_number - thrower_1.pks

create or replace package tq84_thrower_1 as

    procedure go;

    exc exception; 
    pragma exception_init(exc, -20777);

end tq84_thrower_1;
/

PL-SQL - exception - pragma_init_with_same_number - thrower_2.pkb

create or replace package body tq84_thrower_2 as

    procedure go is begin
        raise_application_error(-20777, 'Package is tq84_thrower_2');
    end go;

end tq84_thrower_2;
/

PL-SQL - exception - pragma_init_with_same_number - thrower_2.pks

create or replace package tq84_thrower_2 as

    procedure go;

    exc exception; 
    pragma exception_init(exc, -20777);

end tq84_thrower_2;
/

PL-SQL - forall - compare_time.sql

create table tq84_for_all (
  col_1 number       primary key,
  col_2 varchar2(10)
);

create or replace package tq84_for_all_pkg as

  procedure run_without;
  procedure run_with;

end tq84_for_all_pkg;
/

create or replace package body tq84_for_all_pkg as

  procedure run_without is -- {
    t0 number;
    t1 number;
  begin

    t0 := dbms_utility.get_time;

    for i in 1 .. 100000 loop

      insert into tq84_for_all values(i, 'foo ' || i);

    end loop;

    t1 := dbms_utility.get_time;

    dbms_output.put_line('Without: ' || ((t1-t0) / 100) || ' seconds');

  end run_without; -- }

  procedure run_with is -- {
     type vals_t is table of tq84_for_all%rowtype index by pls_integer;
     vals vals_t;

    t0 number;
    t1 number;

  begin

    t0 := dbms_utility.get_time;

    for i in 1 .. 100000 loop

       vals(i).col_1 :=  i + 100000;
       vals(i).col_2 := 'foo ' || i;

    end loop;

    forall i in 1 .. 10000 insert into tq84_for_all values vals(i); -- (vals(i).col_1, vals(i).col_2);

    t1 := dbms_utility.get_time;
    dbms_output.put_line('With   : ' || ((t1-t0) / 100) || ' seconds');

  end run_with; -- }

end tq84_for_all_pkg;
/
show errors;

exec tq84_for_all_pkg.run_without
exec tq84_for_all_pkg.run_with

drop table tq84_for_all purge;
drop package tq84_for_all_pkg;

PL-SQL - loop - continue.plsql

begin


  for i in 1 .. 5 loop

      dbms_output.put_line('i: ' || i || '   ');

      for j in 1 .. 5 loop

          dbms_output.put_line('    j: ' || j);

          continue when i+j > 7;

          dbms_output.put_line('*');



      end loop;

  end loop;


  dbms_output.put_line('x');


end;
/

PL-SQL - loop - for_in_x_x.plsql

begin

  dbms_output.put_line('for i in 5 .. 5');
  for i in 5 .. 5 loop
      dbms_output.put_line('i: ' || i);
  end loop;

end;
/

PL-SQL - loop - reverse.plsql

begin

  for i in REVERSE 1 .. 10 loop

      dbms_output.put_line(i);

  end loop;

end;
/

PL-SQL - loop - select_stmt_as_string.plsql

--
--     An SQL Select statement as a string.
--
--     Looping over its result set.
--


create table tq84_loop_ex (
   n number,
   v varchar2(10)
);

insert into tq84_loop_ex values (1, 'one'  );
insert into tq84_loop_ex values (2, 'two'  );
insert into tq84_loop_ex values (3, 'three');


declare

  type cur_t is ref cursor;
  cur  cur_t;

  rec  tq84_loop_ex%rowtype;

  stmt varchar2(100) := 'select n, v from tq84_loop_ex';
  
begin

  open cur for stmt; 
  
  loop

       fetch cur into rec;
       exit when cur%notfound;

       dbms_output.put_line(rec.n || ', ' || rec.v);

  end loop;
  
  close cur;

end;
/


drop table tq84_loop_ex purge;

SQL - alter - package - compile_plsql_ccflags.sql

create or replace package tq84_pkg as -- {

    procedure p;

end tq84_pkg; -- }
/

create or replace package body tq84_pkg as -- {

    procedure p is
    begin

      $if $$tq84_flg $then
          dbms_output.put_line('$$tq84_flg is true');
      $else
          dbms_output.put_line('$$tq84_flg is not true');
      $end

    end p;

end tq84_pkg; -- }
/

exec tq84_pkg.p
-- $$tq84_flg is not true

alter package tq84_pkg compile plsql_ccflags='tq84_flg:true';

exec tq84_pkg.p
-- $$tq84_flg is true

drop package tq84_pkg;

SQL - alter - session - time_zone.sql

select
  dbtimezone,
  sessiontimezone
from
  dual;
--
-- DBTIME SESSIONTIMEZONE
-- ------ -----------------------
-- +02:00 +01:00


alter session set time_zone = '-3:00';

select
  dbtimezone,
  sessiontimezone
from
  dual;
--
-- DBTIME SESSIONTIMEZONE
-- ------ ---------------------
-- +02:00 -03:00

SQL - functions - between.sql

create table tq84_between (
  val  varchar2(10)    not null,
  dt_1 date            not null,
  dt_2 date            not null
);

alter session set nls_date_format  = 'dd.mm.yyyy hh24:mi:ss';

insert into tq84_between values ('one', '18.07.2010 15:30:19', '20.08.2010 16:20:18');
insert into tq84_between values ('two',                        '20.08.2010 16:20:18', '05.09.2016 07:51:32');


select * from tq84_between where '04.02.2009 08:08:08' between dt_1 and dt_2;

select * from tq84_between where '19.08.2010 22:23:24' between dt_1 and dt_2;

select * from tq84_between where '20.08.2010 16:20:18' between dt_1 and dt_2;

drop table tq84_between purge;

SQL - functions - multiset - except.sql

create type tq84_obj as object (
  a number,
  b varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/

create table tq84_tab (
  id  varchar2(10),
  val tq84_obj_t
)
nested table val store as tq84_tab_val;

insert into tq84_tab values ('1-3', tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two'  ), tq84_obj(3, 'three')                      ));
insert into tq84_tab values ('2-5', tq84_obj_t(tq84_obj(2, 'two' ), tq84_obj(3, 'three'), tq84_obj(4, 'four' ), tq84_obj(5, 'five' )));
insert into tq84_tab values ('4'  , tq84_obj_t(tq84_obj(4, 'four')                                                                  ));
insert into tq84_tab values ('n/a', tq84_obj_t(                                                                                     ));


break on what skip 1

select
  a.id || ' except ' || b.id what,
  t.*
from
  tq84_tab a,
  tq84_tab b,
  table(a.val multiset except b.val) t
where
  a.id = '1-3' and b.id = '2-5' or
  a.id = '2-5' and b.id = '4'   or
  a.id = '4'   and b.id = 'n/a';
-- 
-- WHAT                                  A B
-- ---------------------------- ---------- ----------
-- 1-3 except 2-5                        1 one
-- 
-- 2-5 except 4                          2 two
--                                       3 three
--                                       5 five
-- 
-- 4 except n/a                          4 four

drop table tq84_tab;
drop type  tq84_obj_t;
drop type  tq84_obj;

SQL - functions - multiset - intersect.sql

create type tq84_obj as object (
  a number,
  b varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/

create table tq84_tab (
  id  varchar2(10),
  val tq84_obj_t
)
nested table val store as tq84_tab_val;

insert into tq84_tab values ('1-3', tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two'  ), tq84_obj(3, 'three')                      ));
insert into tq84_tab values ('2-5', tq84_obj_t(tq84_obj(2, 'two' ), tq84_obj(3, 'three'), tq84_obj(4, 'four' ), tq84_obj(5, 'five' )));
insert into tq84_tab values ('4'  , tq84_obj_t(tq84_obj(4, 'four')                                                                  ));
insert into tq84_tab values ('n/a', tq84_obj_t(                                                                                     ));


break on what skip 1

select
  a.id || ' /\ ' || b.id what,
  t.*
from
  tq84_tab a,
  tq84_tab b,
  table(a.val multiset intersect b.val) t
where
  a.id = '1-3' and b.id = '2-5' or
  a.id = '2-5' and b.id = '4'   or
  a.id = '4'   and b.id = 'n/a';
-- 
-- WHAT                              A B
-- ------------------------ ---------- ----------
-- 1-3 /\ 2-5                        2 two
--                                   3 three
-- 
-- 2-5 /\ 4                          4 four

drop table tq84_tab;
drop type  tq84_obj_t;
drop type  tq84_obj;

SQL - functions - multiset - powermultiset_by_cardinality.sql

create type tq84_obj as object (
  a number,
  b varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/

create type tq84_obj_t_t as table of tq84_obj_t;
/

create table tq84_tab (
  id  varchar2(10),
  val tq84_obj_t
)
nested table val store as tq84_tab_val;



insert into tq84_tab values ('1-3', tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two'  ), tq84_obj(3, 'three')                      ));
insert into tq84_tab values ('2-5', tq84_obj_t(tq84_obj(2, 'two' ), tq84_obj(3, 'three'), tq84_obj(4, 'four' ), tq84_obj(5, 'five' )));
insert into tq84_tab values ('4'  , tq84_obj_t(tq84_obj(4, 'four')                                                                  ));
insert into tq84_tab values ('n/a', tq84_obj_t(                                                                                     ));

select
--t.id,
  t1.a,
  t2.a
--cast (powermultiset_by_cardinality(val, 3) as tq84_obj_t_t)
from
  tq84_tab t,
  table(powermultiset_by_cardinality(t.val, 3)) tt,
  table(tt.column_value) t1,
  table(tt.column_value) t2
where
  t.id = '2-5';
-- 
--          A          A
-- ---------- ----------
--          2          2
--          2          3
--          2          4
--          3          2
--          3          3
--          3          4
--          4          2
--          4          3
--          4          4
--          2          2
--          2          3
--          2          5
--          3          2
--          3          3
--          3          5
--          5          2
--          5          3
--          5          5
--          2          2
--          2          4
--          2          5
--          4          2
--          4          4
--          4          5
--          5          2
--          5          4
--          5          5
--          3          3
--          3          4
--          3          5
--          4          3
--          4          4
--          4          5
--          5          3
--          5          4
--          5          5



drop table tq84_tab;


begin -- Why is that even necessary???

  for r in (select name from user_dependencies where referenced_name = 'TQ84_OBJ_T') loop
    dbms_output.put_line('dropping ' || r.name);
    execute immediate 'drop type "' || r.name || '"';
  end loop;

end;
/


drop type  tq84_obj_t;
drop type  tq84_obj;

SQL - functions - multiset - union.sql

create type tq84_obj as object (
  a number,
  b varchar2(10)
);
/

create type tq84_obj_t as table of tq84_obj;
/

create table tq84_tab (
  id  varchar2(10),
  val tq84_obj_t
)
nested table val store as tq84_tab_val;

insert into tq84_tab values ('1-3', tq84_obj_t(tq84_obj(1, 'one' ), tq84_obj(2, 'two'  ), tq84_obj(3, 'three')                      ));
insert into tq84_tab values ('2-5', tq84_obj_t(tq84_obj(2, 'two' ), tq84_obj(3, 'three'), tq84_obj(4, 'four' ), tq84_obj(5, 'five' )));
insert into tq84_tab values ('4'  , tq84_obj_t(tq84_obj(4, 'four')                                                                  ));
insert into tq84_tab values ('n/a', tq84_obj_t(                                                                                     ));


break on what skip 1

select
  a.id || ' \/ ' || b.id what,
  t.*
from
  tq84_tab a,
  tq84_tab b,
  table(a.val multiset union b.val) t
where
  a.id = '1-3' and b.id = '2-5' or
  a.id = '2-5' and b.id = '4'   or
  a.id = '4'   and b.id = 'n/a';
-- 
-- WHAT                              A B
-- ------------------------ ---------- ----------
-- 1-3 \/ 2-5                        1 one
--                                   2 two
--                                   3 three
--                                   2 two
--                                   3 three
--                                   4 four
--                                   5 five
-- 
-- 2-5 \/ 4                          2 two
--                                   3 three
--                                   4 four
--                                   5 five
--                                   4 four
-- 
-- 4 \/ n/a                          4 four


drop table tq84_tab;
drop type  tq84_obj_t;
drop type  tq84_obj;

SQL - functions - strings - joining.sql

create table tq84_joining_columns (
  col_a  varchar2(10),
  col_b  varchar2(10),
  col_c  varchar2(10)
);


insert into tq84_joining_columns values ('foo', 'bar', 'baz');
insert into tq84_joining_columns values ('one', 'two',  null);
insert into tq84_joining_columns values ('abc',  null, 'def');
insert into tq84_joining_columns values ('ghi',  null,  null);
insert into tq84_joining_columns values ( null, 'jkl', 'mno');
insert into tq84_joining_columns values ( null, 'pqr',  null);
insert into tq84_joining_columns values ( null,  null, 'stu');
insert into tq84_joining_columns values ( null,  null,  null);


select
  trim(both ',' from
    regexp_replace( 
      col_a || ',' || col_b || ',' || col_c,
      ',+',
      ','
    )
  )
from
  tq84_joining_columns;



drop table tq84_joining_columns purge;

SQL - functions - strings - repeating_strings.sql

declare
  result varchar2(100);
  str    varchar2( 10);
  n      number;
begin

  str    :='foo';
  n      :=   4;  -- repeat 4 times

  result := rpad(str, n*length(str), str);

  dbms_output.put_line('result: ' || result);
end;
/

SQL - select - 12c - with_function.sql

create table tq84_with_function (
  id  number,
  txt varchar2(10)
);

insert into tq84_with_function values (1, 'one');
insert into tq84_with_function values (2, 'two');

with function dup (txt in varchar2) return varchar2 as
begin
  return txt || '-' || txt;
end dup;
select
  id,
  substr(dup(txt), 1, 40) dup
from
  tq84_with_function
/

drop table tq84_with_function;

SQL - select - 12c - row_limiting_clause - fetch_first_n_percent_rows_only.sql

create table tq84_table (
  id  number,
  txt varchar2(10)
);

begin

  for r in 1 .. 10000 loop

      insert into tq84_table values (r, dbms_random.string('a', 10));

  end loop;

end;
/

-- select 10 (= 0.1 percent of 10000) records:
select * from tq84_table
order by txt
fetch first 0.1 percent rows only;


drop table tq84_table purge;

SQL - select - 12c - row_limiting_clause - fetch_first_n_rows_only.sql

create table tq84_table (
   id     number,
   txt    varchar2(10)
);

insert into tq84_table values ( 2, 'two'  );
insert into tq84_table values ( 1, 'one'  );
insert into tq84_table values ( 6, 'six'  );
insert into tq84_table values ( 8, 'eight');
insert into tq84_table values ( 3, 'three');
insert into tq84_table values ( 7, 'seven');
insert into tq84_table values ( 4, 'four' );
insert into tq84_table values ( 0, 'zero' );
insert into tq84_table values ( 9, 'nine' );
insert into tq84_table values (10, 'ten'  );
insert into tq84_table values ( 5, 'five' );

commit;


select
  id,
  txt
from
  tq84_table
order by
  id
fetch first row only;
--
--          ID TXT
--  ---------- ----------
--           0 zero

select
  id,
  txt
from
  tq84_table
order by
  id desc
fetch first 3 rows only;

--
--         ID TXT
-- ---------- ----------
--         10 ten
--          9 nine
--          8 eight


drop table tq84_table purge;

SQL - select - 12c - row_limiting_clause - offset_n_rows_fetch_next_m_rows.sql

create table tq84_table (
   id     number,
   txt    varchar2(10)
);

insert into tq84_table values ( 2, 'two'  );
insert into tq84_table values ( 1, 'one'  );
insert into tq84_table values ( 6, 'six'  );
insert into tq84_table values ( 8, 'eight');
insert into tq84_table values ( 3, 'three');
insert into tq84_table values ( 7, 'seven');
insert into tq84_table values ( 4, 'four' );
insert into tq84_table values ( 9, 'nine' );
insert into tq84_table values (10, 'ten'  );
insert into tq84_table values ( 5, 'five' );


select
  id,
  txt
from
  tq84_table
order by
  id
offset     4 rows       -- skip first 4 records
fetch next 3 rows only  -- fetch next 3 records
;
--
--         ID TXT
-- ---------- ----------
--          5 five
--          6 six
--          7 seven

drop table tq84_table purge;

SQL - select - analytical_functions - cume_dist.sql

create table tq84_cume (
  num number
);

insert into tq84_cume values ( 1);
insert into tq84_cume values ( 2);
insert into tq84_cume values ( 2);
insert into tq84_cume values ( 9);
insert into tq84_cume values (10);


select
    num,
    cume_dist   () over (order by num)                               cume_dst,
   (rank        () over (order by num)    ) / (count(*) over ()    ) cume_st_calc
from
  tq84_cume
order by
    num;
--       NUM   CUME_DST CUME_ST_CALC
------------ ---------- ------------
--         1         .2           .2
--         2         .6           .4     <- Note disrepancy here!
--         2         .6           .4     <- Note disrepancy here!
--         9         .8           .8
--        10          1            1

drop table tq84_cume;

SQL - select - analytical_functions - cumulative_sum.sql

--
--     http://stackoverflow.com/a/21315483/180275
--
create table tq84_sum_data_by_week (
  chrg_dt   date,
  wrk_hr    number
);

insert into tq84_sum_data_by_week values (date '2014-01-01', 4);
insert into tq84_sum_data_by_week values (date '2014-01-02', 8);
insert into tq84_sum_data_by_week values (date '2014-01-15', 7);


with fridays as (
  select
    date '2014-01-03' + (level-1) * 7 date_
  from dual
    connect by level <= 3
)
select 
  fridays.date_,
  sum(sum(weeks.wrk_hr)) over (order by fridays.date_)  total_hrs
from 
  fridays               left   outer join
  tq84_sum_data_by_week weeks
  on fridays.date_ = next_day(weeks.chrg_dt, 'fridays')
group by
  fridays.date_;

drop table tq84_sum_data_by_week purge;

SQL - select - analytical_functions - cumulative_value_at_date.sql

--
-- tq84_x contains values (for examples: loans) that are valid
-- within a specific time frame (from_ until to_).
create table tq84_x(
  from_  date,
  to_    date,
  val_   number
);

insert into tq84_x values (date '2015-11-06', date '2015-12-03',    10);
insert into tq84_x values (date '2015-11-18', date '2015-11-27',    40);
insert into tq84_x values (date '2015-11-20', date '2015-12-09',   100);

--
-- The task is now to find the cumulative value when it changes:

with x as (
  select  from_ dt,  val_ from tq84_x union all
  select  to_   dt, -val_ from tq84_x
)
select
  dt,
  sum(val_) over (order by dt) cumulative_val_at_dt
from
  x;

-- DT                  CUMULATIVE_VAL_AT_DT
-- ------------------- --------------------
-- 06.11.2015 00:00:00                   10
-- 18.11.2015 00:00:00                   50
-- 20.11.2015 00:00:00                  150
-- 27.11.2015 00:00:00                  110
-- 03.12.2015 00:00:00                  100
-- 09.12.2015 00:00:00                    0


drop table tq84_x purge;

SQL - select - analytical_functions - find-free-time-in-calendar.sql

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

create table tq84_calendar (
  dt_begin date not null,
  dt_end   date not null,
  what     varchar2(20),
  --
  check (dt_begin < dt_end)
);

insert into tq84_calendar
with entries as (
  select '08:00' start_, '08:30' end_, 'Meeting 1' what from dual union all
  select '09:00' start_, '10:00' end_, 'Meeting 2' what from dual union all
  select '10:30' start_, '11:00' end_, 'Meeting 3' what from dual union all
  select '11:00' start_, '11:30' end_, 'Meeting 4' what from dual union all
  select '12:00' start_, '13:30' end_, 'Lunch'     what from dual union all
  select '15:00' start_, '16:00' end_, 'Meeting 5' what from dual union all
  select '16:30' start_, '17:00' end_, 'Meeting 6' what from dual
)
select
   '2010-10-10 ' || start_ || ':00',
   '2010-10-10 ' || end_   || ':00',
    what
from
  entries;

select
  to_char(free_begin, 'hh24:mi') free_begin,
  to_char(free_end  , 'hh24:mi') free_end
from (
  select
    max (dt_end  ) over (order by dt_begin) free_begin,
    lead(dt_begin) over (order by dt_begin) free_end
  from
    tq84_calendar
)
where free_begin < free_end;
-- 
-- FREE_ FREE_
-- ----- -----
-- 08:30 09:00
-- 10:00 10:30
-- 11:30 12:00
-- 13:30 15:00
-- 16:00 16:30

drop table tq84_calendar;

SQL - select - analytical_functions - find_duplicate_newest.sql

--
--   Demonstration: how to find «duplicate newest entries».
--
create table tq84_t (
  id       varchar2(10),
  tm       date,
  val      number
);

--   Find each record where an id's newest tm occurs
--   more than once.



insert into tq84_t values ('foo', date '2000-05-09', 10);
insert into tq84_t values ('foo', date '2004-11-28', 12);
insert into tq84_t values ('foo', date '2012-07-15', 14);

insert into tq84_t values ('bar', date '2001-05-09',  9);
insert into tq84_t values ('bar', date '2003-02-04', 17);
insert into tq84_t values ('bar', date '2011-06-12', 16)  /* We want this and the next record, because            */;
insert into tq84_t values ('bar', date '2011-06-12', 19)  /* 2011-06-12 occurs twice and is most recent for 'bar' */ ;

insert into tq84_t values ('baz', date '2002-08-23', 21);
insert into tq84_t values ('baz', date '2005-05-19', 25)  /* We don't want this record. 2005-05-19 occurs twice for 'baz' */;
insert into tq84_t values ('baz', date '2005-05-19', 23)  /* but 2013-01-30 is newer                                      */;
insert into tq84_t values ('baz', date '2013-01-30', 13);

select * from (
  select
    id,
    tm,
    count(*)     over (partition by id,         tm     ) cnt,
    dense_rank() over (partition by id order by tm desc) dr,
    val
  from
    tq84_t
)
where
  cnt > 1 and
  dr  = 1;


drop table tq84_t purge;

SQL - select - analytical_functions - group_by.sql

create table tq84_analytical_group_by (
       item    varchar2(10),
       val     number
);


insert into tq84_analytical_group_by values ('abc',   10);
insert into tq84_analytical_group_by values ('abc',   15);
insert into tq84_analytical_group_by values ('abc',   20);

insert into tq84_analytical_group_by values ('DEF',    7);
insert into tq84_analytical_group_by values ('DEF',   12);

insert into tq84_analytical_group_by values ('123',    7);
insert into tq84_analytical_group_by values ('123',    8);
insert into tq84_analytical_group_by values ('123',   14);

select
  item,
  sum(val),
  sum(sum(val)) over () val_total,
  max(sum(val)) over () val_max
from
  tq84_analytical_group_by
group by
  item;


drop table tq84_analytical_group_by;

SQL - select - analytical_functions - percent_rank.sql

create table tq84_percent (
  num number
);

insert into tq84_percent values ( 1);
insert into tq84_percent values ( 4);
insert into tq84_percent values ( 2);
insert into tq84_percent values ( 9);
insert into tq84_percent values ( 9);
insert into tq84_percent values (10);


select
    num,
    percent_rank() over (order by num)                               pct_rnk,
   (        rank() over (order by num) - 1) / (count(*) over ()  -1) pct_rnk_calc
from
  tq84_percent
order by
    num;
--        NUM    PCT_RNK PCT_RNK_CALC
-- ---------- ---------- ------------
--          1          0            0
--          2         .2           .2
--          4         .4           .4
--          9         .6           .6
--          9         .6           .6
--         10          1            1

select
  percent_rank( 0  ) within group (order by num) pct_rnk__0,
  percent_rank( 0.9) within group (order by num) pct_rnk__0_9,
  percent_rank( 1.0) within group (order by num) pct_rnk__1_0,
  percent_rank( 1.1) within group (order by num) pct_rnk__1_1,
  percent_rank( 2  ) within group (order by num) pct_rnk__2,
  percent_rank( 3  ) within group (order by num) pct_rnk__3,
  percent_rank( 4  ) within group (order by num) pct_rnk__4,
  percent_rank(10  ) within group (order by num) pct_rnk_10,
  percent_rank(11  ) within group (order by num) pct_rnk_11
from
  tq84_percent;
--
-- PCT_RNK__0 PCT_RNK__0_9 PCT_RNK__1_0 PCT_RNK__1_1 PCT_RNK__2 PCT_RNK__3 PCT_RNK__4 PCT_RNK_10 PCT_RNK_11
-- ---------- ------------ ------------ ------------ ---------- ---------- ---------- ---------- ----------
--          0            0            0   .166666667 .166666667 .333333333 .333333333 .833333333          1


drop table tq84_percent;

SQL - select - analytical_functions - range_between.sql

create table tq84_range_between (
   name      varchar2 (10) primary key,
   age       number  not null check (age = trunc(age)),
   sex       char(1) not null check (sex in ('m','f')),
   salary    number  not null
);


insert into tq84_range_between values ('Diana' ,30, 'f',    10000);
insert into tq84_range_between values ('Jane'  ,30, 'f',     9000);
insert into tq84_range_between values ('Peter' ,30, 'm',     7000);
insert into tq84_range_between values ('Tom'   ,30, 'm',    12000);

insert into tq84_range_between values ('Frank' ,32, 'm',    14000);

insert into tq84_range_between values ('Aloe'  ,33, 'f',    13000);

insert into tq84_range_between values ('sandy' ,34, 'f',    15000);


select
  name,
  age,
  sex,
  avg(salary) over (partition by sex order by age range between age * 0.1 preceding and age * 0.1 following) salary_avg_in_10_pct_range
from 
  tq84_range_between
order by
  age
  ;


-- NAME              AGE S SALARY_AVG_IN_10_PCT_RANGE
-- ---------- ---------- - --------------------------
-- Jane               30 f                 10666.6667         (10000+ 9000+13000) / 3
-- Diana              30 f                 10666.6667
-- Peter              30 m                      11000         ( 7000+12000+14000) / 3
-- Tom                30 m                      11000
-- Frank              32 m                      11000
-- Aloe               33 f                      11750         (10000+ 9000+13000+15000) / 4
-- sandy              34 f                      14000         (15000+ 13000) / 2



drop table tq84_range_between purge;

SQL - select - analytical_functions - keep_dense_rank - column_values_of_row_with_max_value.sql

create table tq84_a (
  a  varchar2(10),
  b  number
);

insert into tq84_a values ('a', 1);
insert into tq84_a values ('b', 2);
insert into tq84_a values ('a', 4);
insert into tq84_a values ('b', 3);

select
  max(a) keep (dense_rank first order by b desc)  a,
  max(b) keep (dense_rank first order by b desc)  b
from
  tq84_a
;

drop table tq84_a purge;

SQL - select - analytical_functions - keep_dense_rank - group_by_vs_over.sql

create table tq84_group_by_vs_over (
  a varchar2(3),
  b number
);


insert into tq84_group_by_vs_over values ('foo', 42);
insert into tq84_group_by_vs_over values ('foo', 10);
insert into tq84_group_by_vs_over values ('foo', 13);

insert into tq84_group_by_vs_over values ('bar',  7);
insert into tq84_group_by_vs_over values ('bar', 39);
insert into tq84_group_by_vs_over values ('bar', 88);

insert into tq84_group_by_vs_over values ('baz', 21);

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

select
  max(b) keep (dense_rank first order by b desc) over (partition by a) max_b_for_a,
  --                                             ^^^^^^^^^^^^^^^^^^^^^
  --                                             Missing in 2nd query
  a
from tq84_group_by_vs_over
;

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

select
  max(b) keep (dense_rank first order by b desc)  max_b_for_a,
  a
from tq84_group_by_vs_over
  group by a -- <<< Missing in 1st query
;

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

drop table tq84_group_by_vs_over purge;

SQL - select - analytical_functions - windowing_clause - count.sql

create table tq84_table (
  part    varchar2(3),
  val     number
);

insert into tq84_table values ('bar',    2);
insert into tq84_table values ('bar',    3);
insert into tq84_table values ('bar',   10);
insert into tq84_table values ('bar',  100);

insert into tq84_table values ('baz',   50);
insert into tq84_table values ('baz',   60);
insert into tq84_table values ('baz',   70);
insert into tq84_table values ('baz',   72);

insert into tq84_table values ('foo',    1);
insert into tq84_table values ('foo',    3);
insert into tq84_table values ('foo',    6);
insert into tq84_table values ('foo',   10);
insert into tq84_table values ('foo',   11);
insert into tq84_table values ('foo',   12);
insert into tq84_table values ('foo',   13);
insert into tq84_table values ('foo',   20);


select
  part,
  val,
  count(*) over (partition by part order by val rows  between 2 preceding and 2 following) c_rows,
  count(*) over (partition by part order by val range between 2 preceding and 2 following) c_range
from
  tq84_table;



drop table tq84_table purge;

SQL - select - analytical_functions - windowing_clause - default.sql

--
-- The defalt for the windowing clause is:
--   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
--

create table tq84_table (
  i number,
  s number
);

insert into tq84_table values (1,  1);
insert into tq84_table values (2,  1);
insert into tq84_table values (3,  2);
insert into tq84_table values (4,  3);
insert into tq84_table values (5,  5);
insert into tq84_table values (6,  8);
insert into tq84_table values (7, 13);
insert into tq84_table values (8, 20);

select
  s,
  sum  (s) over (order by i                                                  ) sum_1,
  sum  (s) over (order by i range between unbounded preceding and current row) sum_2,
  count(*) over (order by i                                                  ) count_1,
  count(*) over (order by i range between unbounded preceding and current row) count_2
from
  tq84_table;

drop table tq84_table purge;

SQL - select - in - columns_of_outer_statement.sql

create table tq84_in_outer (
  outer_1  number,
  outer_2  varchar2(10)
);

create table tq84_in_inner (
  inner_1  number,
  inner_2  varchar2(10)
);

insert into tq84_in_outer values (1, 'outer one');
insert into tq84_in_outer values (2, 'outer two');

insert into tq84_in_inner values (1, 'inner one');
insert into tq84_in_inner values (3, 'inner three');

select * from tq84_in_outer
where
  outer_1 in (
     select
        outer_1
      from
        tq84_in_inner
      where
        inner_1 = 1
  );
        


drop table tq84_in_outer purge;
drop table tq84_in_inner purge;

SQL - select - misc - ascii-histogram.sql

create table tq84_normal_dist_of_chars as
select
   c
from (
  select
    chr(ascii('m') + 2.7* dbms_random.normal) c
  from
     dual connect by level < 1000000
)
where
   c between 'a' and 'z'
;


column hist format a100
with hist as (
  select 100 max_width from dual
),
chars as (
  select
    count(*) cnt,
    c
  from
    tq84_normal_dist_of_chars
  group by
    c
)
select
  c,
  lpad('X', chars.cnt / max(chars.cnt) over () * hist.max_width, 'X') hist
from
  chars  cross join
  hist
order by
  c;

--
-- C HIST
-- - ----------------------------------------------------------------------------------------------------
-- a
-- b
-- c
-- d
-- e XX
-- f XXXXX
-- g XXXXXXXXXXXXX
-- h XXXXXXXXXXXXXXXXXXXXXXXXX
-- i XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- j XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- k XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- l XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- m XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- n XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- o XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- p XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- q XXXXXXXXXXXXXXXXXXXXXXXXXX
-- r XXXXXXXXXXXXX
-- s XXXXX
-- t XX
-- u
-- v
-- w
-- x
-- y
-- z 
--


drop table tq84_normal_dist_of_chars purge;

SQL - select - misc - sample - select-approx-5-percent.sql

create table tq84_sample_src (
  id number
);

insert into tq84_sample_src
select level from dual
connect by level <= 1000;

select count(*) from tq84_sample_src;
-- 1000

create table tq84_sample_dest as
select * from tq84_sample_src sample (5);

select count(*) from tq84_sample_dest;
-- 52

select * from tq84_sample_dest;

drop table tq84_sample_src  purge;
drop table tq84_sample_dest purge;

SQL - select - order_by - test_01.sql

create table tq84_order_by (
  txt   varchar2(10),
  value number
);

insert into tq84_order_by values ('two'  ,    2);
insert into tq84_order_by values ('null' , null);
insert into tq84_order_by values ('four' ,    4);
insert into tq84_order_by values ('three',    3);
insert into tq84_order_by values ('one'  ,    1);

select * from tq84_order_by order by value;
-- TXT             VALUE
-- ---------- ----------
-- one                 1
-- two                 2
-- three               3
-- four                4
-- null

select * from tq84_order_by order by value nulls first;
-- TXT             VALUE
-- ---------- ----------
-- null
-- one                 1
-- two                 2
-- three               3
-- four                4

select * from tq84_order_by order by value desc;
-- TXT             VALUE
-- ---------- ----------
-- null
-- four                4
-- three               3
-- two                 2
-- one                 1

select * from tq84_order_by order by value desc nulls last;
-- TXT             VALUE
-- ---------- ----------
-- four                4
-- three               3
-- two                 2
-- one                 1
-- null

drop table tq84_order_by purge;

SQL - select - partition_extension_clause - select_from_partition.sql

create table tq84_partitioned (
  col_p  varchar2(10),
  dat    varchar2(10)
) partition by list(col_p) (
  partition part_foo values ('foo'),
  partition part_bar values ('bar'),
  partition part_baz values ('baz')
);


insert into tq84_partitioned values ('foo', 'one'  );
insert into tq84_partitioned values ('bar', 'two'  );
insert into tq84_partitioned values ('baz', 'three');
insert into tq84_partitioned values ('foo', 'four' );
insert into tq84_partitioned values ('bar', 'five' );
insert into tq84_partitioned values ('baz', 'six'  );

select * from tq84_partitioned partition(part_bar);
-- COL_P      DAT
-- ---------- ----------
-- bar        two
-- bar        five

drop table tq84_partitioned purge;

create table tq84_partitioned (
  col_p  varchar2(10),
  dat    date
) partition by range(dat) (
  partition part_min values less than (date '9999-12-31'),
  partition part_max values less than (maxvalue         )
);


insert into tq84_partitioned values ('sysdate'   , sysdate         );
insert into tq84_partitioned values ('null'      , null            );
insert into tq84_partitioned values ('9999-12-31',date '9999-12-31');
insert into tq84_partitioned values ('9999-12-30',date '9999-12-30');

select * from tq84_partitioned partition(part_min);
-- COL_P      DAT
-- ---------- -------------------
-- sysdate    12.10.2016 10:01:46
-- 9999-12-30 30.12.9999 00:00:00

select * from tq84_partitioned partition(part_max);
-- COL_P      DAT
-- ---------- -------------------
-- null
-- 9999-12-31 31.12.9999 00:00:00

drop table tq84_partitioned purge;

SQL - select - regression - regr_slope.sql

create table tq84_regr_slope (
  x  number,
  y1 number,
  y2 number,
  y3 number,
  y4 number,
  y5 number
);


declare
  x_   number;
begin

  for x__ in 0 .. 100 loop

      x_ := x__ / 10;

      insert into tq84_regr_slope values (

        x_,
        50,
        x_ * 10,
        x_ * x_,
       (x_ - 3) * (x_ - 3),
        sin(x_)
      );

      
  end loop;
end;
/


select * from tq84_regr_slope order by x;

select
  regr_slope(x, y1),
  regr_slope(x, y2),
  regr_slope(x, y3),
  regr_slope(x, y4),
  regr_slope(x, y5)
from
  tq84_regr_slope;


drop table tq84_regr_slope purge;

SQL - select - skip_locked - call_process_next_item.sql

set feedback off

set timing on
begin
  tq84_process_next_item; 
end;
/

exit

SQL - select - skip_locked - create_tab.sql

create table tq84_queue (
  item    number(7) primary key,
  status  varchar2(4) not null check (status in ('TODO', 'DONE'))
);

SQL - select - skip_locked - insert_initial.sql

set feedback off

insert into tq84_queue values (4, 'TODO');
insert into tq84_queue values (5, 'TODO');
insert into tq84_queue values (6, 'TODO');
insert into tq84_queue values (1, 'TODO');
insert into tq84_queue values (2, 'TODO');
insert into tq84_queue values (3, 'TODO');

commit;

SQL - select - skip_locked - install.sql

drop procedure tq84_process_next_item;
drop table tq84_queue;

@create_tab.sql
@insert_initial.sql
@process_next_item.plsql

prompt execute process_items.bat now.

SQL - select - skip_locked - process_items.bat

@rem
@rem  There are 6 items to be processed in tq84_queue.
@rem  
@rem  So, we call call_process_next_item 7 times, once
@rem  for each item and once to determine how Oracle
@rem  handles the queue if there are no more entries
@rem  in it.
@rem 

start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 
start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 
start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 
start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 
start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 
start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 
start cmd.exe /k sqlplus -S rene/rene @call_process_next_item 

SQL - select - skip_locked - process_next_item.plsql

create or replace procedure tq84_process_next_item is 

    start_  date := sysdate;
    end_    date;

begin

    for next_item in (

        select * from tq84_queue
         where status = 'TODO'
         order by item
           for update skip locked

    ) loop

        dbms_output.put_line('Processing item ' || next_item.item);

    --  Pretend the processing an item takes between
    --  ten and twenty seconds:
        dbms_lock.sleep(dbms_random.value(10, 20));

        update tq84_queue 
           set status = 'DONE'
         where item = next_item.item;

        exit; -- Process one item only.

    end loop;


    commit;

    end_  := sysdate;

    dbms_output.put_line('Processing time: ' || to_char(start_, 'hh24:mi:ss') || ' - ' || 
                                                to_char(end_  , 'hh24:mi:ss') || '   ' ||
                         'Total: ' || ( (end_ - start_) * 24*60*60) || ' seconds');

end tq84_process_next_item;
/

UsersRoles - CloneUser - create_clone_script.sql

connect / as sysdba

set heading off
set pages   0
set long    1000000
set termout off

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true                              );
--exec dbms_metadata.set_remap_param    (dbms_metadata.session_transform, 'REMAP_SCHEMA', 'USER_TO_BE_CLONED', 'CLONED_USER');
--exec dbms_metadata.set_remap_param    (dbms_metadata.session_transform, 'REMAP_NAME'  , 'USER_TO_BE_CLONED', 'CLONED_USER');

spool create_cloned_user.sql

select replace(dbms_metadata.get_ddl          ('USER'            , 'USER_TO_BE_CLONED'), '"USER_TO_BE_CLONED"','CLONED_USER') from dual;
select replace(dbms_metadata.get_granted_ddl  ('SYSTEM_GRANT'    , 'USER_TO_BE_CLONED'), '"USER_TO_BE_CLONED"','CLONED_USER') from dual;
select replace(dbms_metadata.get_granted_ddl  ('OBJECT_GRANT'    , 'USER_TO_BE_CLONED'), '"USER_TO_BE_CLONED"','CLONED_USER') from dual;
select replace(dbms_metadata.get_granted_ddl  ('ROLE_GRANT'      , 'USER_TO_BE_CLONED'), '"USER_TO_BE_CLONED"','CLONED_USER') from dual;
select replace(dbms_metadata.get_granted_ddl  ('TABLESPACE_QUOTA', 'USER_TO_BE_CLONED'), '"USER_TO_BE_CLONED"','CLONED_USER') from dual;

spool off
set termout on

UsersRoles - CloneUser - create_schema.sql

connect / as sysdba

drop user a_user cascade;
drop user user_to_be_cloned;
drop role a_role;

create user a_user identified by a_password;
create user user_to_be_clonedidentified by "Secret*49" quota 10M on users;
create role a_role;

create table a_user.table_01 (id number);
create table a_user.table_02 (id number);
create table a_user.table_03 (id number);

grant
  create session,
  create table
to
  user_to_be_cloned;


grant select, insert on a_user.table_01 to user_to_be_cloned;
grant all            on a_user.table_02 to a_role;

grant a_role                            to user_to_be_cloned;

UsersRoles - Profiles - password_verify_function.sql

create table rene.changed_passwords (
  usr  varchar2(30),
  pw   varchar2(30),
  pwo  varchar2(30)
);

connect sys/IamSysdba as sysdba

create function sys.tq84_pw_change (
  username     in varchar2,
  password     in varchar2,
  password_old in varchar2
) 
  return boolean
is
  
    procedure ins is
      pragma autonomous_transaction;
    begin

        insert into rene.changed_passwords values (
          username,
          password, 
          password_old
        );

        commit;

    end ins;

begin

    ins;

    return true;

end tq84_pw_change;
/

connect rene/rene

create profile tq84_pw_profile
       limit    password_verify_function tq84_pw_change;


create user test_user 
       identified by test_user
       profile tq84_pw_profile;

grant create session to test_user;

connect test_user/test_user

alter user test_user identified by new_pw replace test_user;

connect rene/rene

drop user test_user cascade;
drop profile  tq84_pw_profile;

select * from changed_passwords;

connect sys/IamSysdba as sysdba
drop function tq84_pw_change;
drop table rene.changed_passwords;

connect rene/rene

See also