^[pRoUd]^ 작업실

---------------------------------------------------------------------------
-- title : find pk without index
-- author : proud
-- discription : find pk without (unique) index - drop and readd
-- usage : @find_pk_without_idx
-- created : 2010.02.02
-- update date:
---------------------------------------------------------------------------

set serveroutput on

drop table result_pk_without_idx;

create table result_pk_without_idx
(
 script_text varchar2(4000)
)
nologging
compress
;

declare
v_drop_sql varchar2(4000);
v_add_sql varchar2(4000);
v_own varchar2(4000);
v_tab varchar2(4000);
v_con varchar2(4000);
v_idx varchar2(4000);
v_col varchar2(4000);
v_cnt pls_integer;

begin

dbms_output.enable(9999999);

for i in (
         select a.owner
               ,a.table_name
               ,a.constraint_name
               ,a.index_name
           from dba_constraints a
               ,dba_indexes b
          where a.index_name = b.index_name(+)
            and a.constraint_type = 'P'
            and b.index_name is null
         ) loop
v_own := i.owner;
v_tab := i.table_name;
v_con := i.constraint_name;
v_idx := i.index_name;

v_drop_sql := 'alter table '||v_own||'.'||v_tab||' drop constraint '||v_con;
v_add_sql := 'alter table '||v_own||'.'||v_tab||' add constraint '||v_con||' primary key (';

v_cnt := 0;

begin
for c in (
         select column_name
           from dba_cons_columns
          where owner = ''||v_own||''
            and table_name = ''||v_tab||''
          order by position
         ) loop

v_col := c.column_name;

if v_cnt > 0 then
v_add_sql := v_add_sql||','||v_col;
else
v_add_sql := v_add_sql||v_col;
end if;

v_cnt := v_cnt + 1;

end loop;
end;

v_add_sql := v_add_sql||')';

execute immediate 'insert into result_pk_without_idx values ('''||v_drop_sql||''')';
execute immediate 'insert into result_pk_without_idx values ('''||v_add_sql||''')';
execute immediate 'commit';
end loop;

dbms_output.put_line('select * from result_pk_without_idx;');
end;
/

http://www.tanelpoder.com/files/scripts/snapper.sql

-------------------------------------------------------
-- name : show_redo
-- author : Kim, jongbum aka proud
-- description : redo size by process
-- usage : @show_redo
-- tested version : oracle 9i , 10g
-------------------------------------------------------

column osuser format a20
column username format a20
column module format a50

select max(decode(a.process,b.spid,to_number(null),a.sid)) sid
      ,max(decode(a.process,b.spid,to_number(null),a.serial#)) serial#
      ,max(decode(a.process,b.spid,null,a.process)) process
      ,a.osuser
      ,a.username
      ,a.command
      ,a.pdml_enabled
      ,a.module
      ,a.status
      ,decode(a.sql_hash_value,0,a.prev_hash_value,a.sql_hash_value) sql_hash_value
      ,sum(c.value) redo_size
  from v$session a
      ,v$process b
      ,v$sesstat c
      ,v$statname d
 where a.paddr = b.addr
   and a.sid = c.sid
   and c.statistic# = d.statistic#
   and d.name = 'redo size'
 group by a.osuser
         ,a.username
         ,a.command
         ,a.pdml_enabled
         ,a.module
         ,a.status
         ,decode(a.sql_hash_value,0,a.prev_hash_value,a.sql_hash_value)
/

select status
      ,count(*) cnt
  from dba_objects
 where owner = upper('portal')
   and object_name like upper('sp_bip_user%')
   and object_type = upper('procedure')
 group by status

STATUS         CNT
------- ----------
INVALID          1
VALID            7

2 rows selected.

select count(*) cnt
  from dba_procedures
 where owner = upper('portal')
   and object_name like upper('sp_bip_user%')

       CNT
----------
         7

1 row selected.

SQL> !oerr ora 959
00959, 00000, "tablespace '%s' does not exist"
// *Cause:
// *Action:

select tablespace_name
  from dba_tables
 where owner = '[OWNER]'
   and table_name = '[TABLE NAME]'
;

select def_tablespace_name
  from dba_part_tables
 where owner = '[OWNER]'
    and table_name = '[TABLE NAME]'
;

alter table [OWNER].[TABLE NAME] modify default attributes tablespace [TABLESPACE NAME];

select a.sid,
       decode(a.type,'MR', 'Media Recovery',
                     'RT', 'Redo Thread',
                     'UN', 'User Name',
                     'TX', 'Transaction',
                     'TM', 'DML',
                     'UL', 'PL/SQL User Lock',
                     'DX', 'Distributed Xaction',
                     'CF', 'Control File',
                     'IS', 'Instance State',
                     'FS', 'File Set',
                     'IR', 'Instance Recovery',
                     'ST', 'Disk Space Transaction',
                     'IR', 'Instance Recovery',
                     'TS', 'Temp Segment',
                     'IV', 'Library Cache Invalidation',
                     'LS', 'Log Start or Switch',
                     'RW', 'Row Wait',
                     'SQ', 'Sequence Number',
                     'TE', 'Extend Table',
                     'TT', 'Temp Table',
                     a.type) lock_type,
       decode(a.lmode,0, 'None', /* Non Lock equivalent */
                      1, 'Null', /* N */
                      2, 'Row-S (SS)', /* L */
                      3, 'Row-X (SX)', /* R */
                      3, 'Row-X (SX)', /* R */
                      4, 'Share', /* S */
                      5, 'S/Row-X (SSX)', /* C */
                      6, 'Exclusive', /* X */
                      to_char(a.lmode)) mode_held,
                      decode(a.request,
                      0, 'None', /* Mon Lock equivalent */
                      1, 'Null', /* N */
                      2, 'Row-S (SS)', /* L */
                      3, 'Row-X (SX)', /* R */
                      4, 'Share', /* S */
                      5, 'S/Row-X (SSX)', /* C */
                      6, 'Exclusive', /* X */
                      to_char(a.request)) mode_requested,
       to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
  from v$lock a
 where (id1,id2) in (select b.id1, b.id2
                       from v$lock b
                      where b.id1=a.id1
                        and b.id2=a.id2
                        and b.request>0)
;

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') from dual;

/* the following hex conversion code is from Tom Kyte */
create or replace function to_base( p_dec in number, p_base in number ) 
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/


create or replace function to_dec
( p_str in varchar2, 
  p_from_base in number default 16 ) return number
is
l_num   number default 0;
l_hex   varchar2(16) default '0123456789ABCDEF';
begin
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/
show errors

create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/