^[pRoUd]^ 작업실

16:49:53 SQL> create table check_dml_time     
16:50:22   2  (
16:50:24   3   fix_column varchar2(10)
16:50:38   4  ,test_column varchar2(10)
16:50:49   5  )
16:50:50   6  ;

Table created.

Elapsed: 00:00:00.02
16:50:51 SQL> insert into check_dml_time values ('FIX','FIRST');

1 row created.

Elapsed: 00:00:00.01
16:51:21 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
16:51:23 SQL> select created, last_ddl_time from user_objects where object_name = upper('check_dml_time');

CREATED           LAST_DDL_TIME
----------------- -----------------
20100726 16:50:51 20100726 16:50:51

1 row selected.

Elapsed: 00:00:00.02
16:52:01 SQL> select ora_rowscn from check_dml_time;

          ORA_ROWSCN
--------------------
      11466381392945

1 row selected.

Elapsed: 00:00:00.01
16:52:31 SQL> select scn_to_timestamp(ora_rowscn) from check_dml_time;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
2010/07/26 16:51:23

1 row selected.

Elapsed: 00:00:00.01
16:53:31 SQL> update check_dml_time 
16:53:43   2     set test_column = 'UPDATED'
16:53:50   3  ;

1 row updated.

Elapsed: 00:00:00.01
16:53:55 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
16:54:00 SQL> select scn_to_timestamp(ora_rowscn) from check_dml_time;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
2010/07/26 16:53:59

1 row selected.

Elapsed: 00:00:00.00

CREATE PUBLIC DATABASE LINK db_link_nm
CONNECT TO id IDENTIFIED BY "password!"
USING db_service_nm;

WITH test AS
(
SELECT '40' code, 30 cnt FROM dual
UNION ALL SELECT '30', 50 FROM dual
UNION ALL SELECT '10', 20 FROM dual
)
SELECT code
, SUM(cnt) cnt
, RATIO_TO_REPORT(SUM(cnt)) OVER(PARTITION BY GROUPING(code)) * 100 ratio
FROM test
GROUP BY ROLLUP(code)
;

column viewname format a100

with tab
as
(
select '0001' code, 'A' name from dual union all
select '0001' code, 'B' name from dual union all
select '0001' code, 'C' name from dual union all
select '0002' code, 'D' name from dual union all
select '0002' code, 'A' name from dual union all
select '0003' code, 'F' name from dual union all
select '0003' code, 'E' name from dual union all
select '0003' code, 'B' name from dual union all
select '0003' code, 'C' name from dual
)
select x.code
      ,substr(max(sys_connect_by_path(x.name,', ')),2) viewname
  from (
       select code
             ,name
             ,row_number() over (partition by code order by code ,name) rn
         from tab
       ) x
 start with x.rn = 1
connect by prior x.code = x.code
   and prior x.rn = x.rn - 1
 group by x.code
/

with tab
as
(
select '0001' code, 'A' name from dual union all
select '0001' code, 'B' name from dual union all
select '0001' code, 'C' name from dual union all
select '0002' code, 'D' name from dual union all
select '0002' code, 'A' name from dual union all
select '0003' code, 'F' name from dual union all
select '0003' code, 'E' name from dual union all
select '0003' code, 'B' name from dual union all
select '0003' code, 'C' name from dual
)
select x.code
      ,substr(xmlagg(xmlelement(x,' ,',x.name).extract('//text()')),3) viewnames
  from tab x
 group by x.code
/

Caution: This is a very powerful system privilege, as it lets the grantee bypass application-driven security policies. Database administrators should use caution when granting this privilege.

grant exempt access policy to dw;
revoke exempt access policy from dw;

CREATE OR REPLACE FUNCTION CLOB2BLOB (p_clob in CLOB)
return BLOB
is
-- transforming CLOB a BLOB
p_blob blob;
l_off number default 1;
l_amt number default 4096;
l_offWrite number default 1;
l_amtWrite number;
l_str varchar2(4096 char);
begin
DBMS_LOB.CREATETEMPORARY(p_blob, TRUE);
begin
loop
dbms_lob.read ( p_clob, l_amt, l_off, l_str );

l_amtWrite := utl_raw.length ( utl_raw.cast_to_raw( l_str) );
dbms_lob.write( p_blob, l_amtWrite, l_offWrite,
utl_raw.cast_to_raw( l_str ) );

l_offWrite := l_offWrite + l_amtWrite;

l_off := l_off + l_amt;
l_amt := 4096;
end loop;
exception
when no_data_found then
NULL;
end;
return p_blob;
end CLOB2BLOB;
/

-- long raw -> blob

create table blob_table_name
as
select
         to_lob(long_raw_col) as blob_col
  from long_raw_table_name
/

-- create blob2clob function

CREATE OR REPLACE FUNCTION "BLOB2CLOB" (b IN BLOB)
RETURN CLOB
IS
l_blob blob;
l_clob clob := 'x';
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
BEGIN
  l_blob := b;
  IF dbms_lob.getlength(b) != 0 THEN
  DBMS_LOB.CONVERTTOCLOB
  ( dest_lob => l_clob
  , src_blob => l_blob
  , amount => dbms_lob.lobmaxsize
  , dest_offset => l_dest_offsset
  , src_offset => l_src_offsset
  , blob_csid => dbms_lob.default_csid
  , lang_context => l_lang_context
  , warning => l_warning
  );
  END IF;
RETURN l_clob;
END BLOB2CLOB;
/

-- blob --> clob

create table clob_table_name
as
select
          blob2clob(blob_col) as clob_col
  from blob_table_name
/

Monday, December 21, 2009

Upgrading from 9i to 11g and the implicit migration from RBO

SQL> variable report clob;



-- Migrate the Stored Outlines in the OLDPLAN category

SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name=>'CATEGORY',

attribute_value => 'OLDPLAN');



-- Migrate all Stored Outlines

SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE(   attribute_name=>'ALL');

http://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/

kill -URG 29086

kill -s SIGURG 29086

kill -s SIGINT 29086

kill -2 29086