Monday, December 12, 2005
tbsfree.sql
column pct_used format 999.9 heading "%|Used"
column name format a16 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
prompt =================================================================
with
free_space_by_tablespace as
( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ),
space_by_permanent_tablespace as
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name ),
space_by_temporary_tablespace as
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )
/* Now, finally to the query itself */
select b.tablespace_name name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from free_space_by_tablespace a
RIGHT OUTER JOIN
space_by_permanent_tablespace b on a.tablespace_name = b.tablespace_name
union all
select b.tablespace_name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from free_space_by_tablespace a
RIGHT OUTER JOIN
space_by_temporary_tablespace b on a.tablespace_name = b.tablespace_name
/
Monday, June 20, 2005
flat.cmd (Windows)
Flat.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @flat.sql %2
goto :done
:Usage
echo "usage flat un/pw [tables|views]"
echo "example flat scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
Flat.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
flat (unix)
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done
dump_csv.sql
create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ';',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query,
dbms_sql.native );
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
dbms_sql.define_column( l_theCursor, 1,
l_columnValue, 2000 );
l_status := dbms_sql.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output,
l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/
df_space.sql (need update)
select substr(file_name, 1, 40) file_name
, substr(tablespace_name, 1, 20) tablespace
, bytes / 1024 / 1024 Meg
, blocks
, autoextensible
, maxbytes
, increment_by
from dba_data_files
/
debug.sql
set echo off
create or replace
type Argv as
table of varchar2(4000);
/
create table debugTab(
userid varchar2(30) primary key,
modules varchar2(4000),
locat varchar2(4000),
filename varchar2(4000)
)
/
create or replace
trigger bi_fer_debugtab
before insert on debugtab for each row
begin
:new.modules := upper( :new.modules );
end;
/
create or replace
package debug as
--
-- version 1.0
-- clbeck - 13-OCT-98 - Initial version
--
-- PACKAGE TO DUMP DEBUG INFORMATION OF PL/SQL ROUTINE
-- TO A FILE DURING EXECUTION
--
--
-- This package allows the developer to selectively produce debug
-- iformation for pl/sql process.
--
-- Setup:
-- Make sure the utl_file_dir paramter is assigned in the init.ora
-- file. You need an entry for each dir that you want to be able to
-- write to.
-- eg.
-- utl_file_dir = /tmp
-- utl_file_dir = /home/clbeck/sql/debug
--
-- Usage:
-- There are two procedure to write debug information ( f and fa ).
-- Anywhere in your code that you want to print debug information use:
--
-- debug.f( 'Expected %s bytes, got %s bytes', l_expect, l_got );
--
-- This will replace the the first %s with the value of l_expect and the
-- second %s with the value of l_got.
--
-- If you have more than 10 %s in your string then you will need to use the fa
-- procedure like:
--
-- debug.fa( 'List: %s,%s,%s,%s,%s,%s',
-- argv( 1, 2, l_num, 'Chris', l_cnt, 10 ) );
--
-- Runtime:
-- To enable the debug run:
--
-- debug.init( 'myProc' );
--
-- This will cause only debug for the procedure/package named
-- myProc to be generated.
-- All other debug statements will generate no output.
-- To debug all procedures/packages,
-- set p_modules = 'ALL';
--
-- To stop debug run:
--
-- debug.clear;
--
-- Output:
-- The output looks like:
--
-- 981013 130530 (CLBECK.MYPROC, 221) this is my debug output
-- ^^^^^^ ^^^^^^ ^^^^^^^^^^^^^ ^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- date time owner.proc lineno message
--
--
-- Enhancements and Bugs:
--
-- Send all enhancements requests and bugs to me,
-- Christopher Beck (clbeck@us.oracle.com)
--
--
g_dir_locat constant varchar2(4000) := '/tmp';
emptyDebugArgv Argv;
--
-- Initializes the debuging for specified p_modules and will dump the
-- output to the p_dir directory on the server for the user p_user.
--
procedure init(
p_modules in varchar2 default 'ALL',
p_dir in varchar2 default g_dir_locat,
p_file in varchar2 default null,
p_user in varchar2 default user );
procedure f(
p_message in varchar2,
p_arg1 in varchar2 default null,
p_arg2 in varchar2 default null,
p_arg3 in varchar2 default null,
p_arg4 in varchar2 default null,
p_arg5 in varchar2 default null,
p_arg6 in varchar2 default null,
p_arg7 in varchar2 default null,
p_arg8 in varchar2 default null,
p_arg9 in varchar2 default null,
p_arg10 in varchar2 default null );
procedure fa(
p_message in varchar2,
p_args in Argv default emptyDebugArgv );
procedure clear(
p_user in varchar2 default user );
--
-- Returns the current status of debugging for the user p_user.
--
procedure status(
p_user in varchar2 default user,
p_modules out varchar2,
p_file out varchar2,
p_dir out varchar2 );
end debug;
/
show error
grant execute on debug to public
/
create or replace
package body debug as
g_owner varchar2(2000);
g_name varchar2(2000);
g_lineno number;
g_caller_t varchar2(2000);
g_file varchar2(2000);
procedure init(
p_modules in varchar2 default 'all',
p_dir in varchar2 default g_dir_locat,
p_file in varchar2 default null,
p_user in varchar2 default user ) is
--
r debugTab%rowtype;
begin
clear( p_user );
insert into debugTab values ( p_user, p_modules, p_dir, p_file );
end init;
procedure clear( p_user varchar2 default user ) is
begin
delete from debugTab where userid = p_user;
end clear;
procedure status(
p_user in varchar2 default user,
p_modules out varchar2,
p_file out varchar2,
p_dir out varchar2 ) is
begin
select modules, locat, filename
into p_modules, p_dir, p_file
from debugTab
where userid = p_user;
exception
when NO_DATA_FOUND then
p_modules := null;
p_dir := null;
p_file := null;
end status;
procedure who_called_me(
owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 ) is
--
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
--
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
--
if not found_stack then
if line like '%handle%number%name%' then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 13, 6 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
else
n := length( 'anonymous block ' );
end if;
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
line := substr( line, n );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end who_called_me;
function is_number( n varchar2 ) return boolean is
begin
if n between '0' and '9' then
return true;
end if;
return false;
end is_number;
function parse_it(
p_message in varchar2,
p_args in argv default emptyDebugArgv ) return varchar2 is
--
l_tmp long := p_message;
l_str long := null;
l_idx number;
l_numstr1 varchar2(10);
l_numstr2 varchar2(10);
l_tmp1 long;
l_str1 long;
l_num number;
l_char long;
begin
for i in 1 .. p_args.count loop
l_idx := instr( l_tmp, '%' ) ;
exit when nvl(l_idx,0) = 0;
l_str := l_str || substr( l_tmp, 1, l_idx-1 );
l_tmp := substr( l_tmp, l_idx+1 );
if substr( l_tmp, 1, 1 ) = 's' or
substr( l_tmp, 1, 1 ) = 'd' then
l_str := l_str || p_args(i);
l_tmp := substr( l_tmp, 2 );
elsif is_number( substr( l_tmp, 1, 1 ) ) or
substr( l_tmp, 1, 1 ) = '.' then
l_numstr1 := null;
l_numstr2 := null;
l_tmp1 := l_tmp;
l_str1 := l_str;
loop
exit when not is_number( substr( l_tmp1, 1, 1 ) );
l_numstr1 := l_numstr1 || substr( l_tmp1, 1, 1 );
l_tmp1 := substr( l_tmp1, 2 );
end loop;
if substr( l_tmp1, 1, 1 ) = '.' then
l_tmp1 := substr( l_tmp1, 2 );
if is_number( substr( l_tmp1, 1, 1 ) ) then
loop
exit when not is_number( substr( l_tmp1, 1, 1 ) );
l_numstr2 := l_numstr2 || substr( l_tmp1, 1, 1 );
l_tmp1 := substr( l_tmp1, 2 );
end loop;
else
l_tmp1 := '!' || l_tmp1;
end if;
end if;
begin
if substr( l_tmp1, 1, 1 ) = 's' then
l_tmp := substr( l_tmp1, 2 );
if l_numstr2 is null then
l_tmp1 := p_args(i);
else
l_tmp1 := substr( p_args(i), 1, l_numstr2 );
end if;
if l_numstr1 is not null then
l_tmp1 := lpad( l_tmp1, l_numstr1 );
end if;
l_str := l_str1 || l_tmp1;
elsif substr( l_tmp1, 1, 1 ) = 'd' then
l_tmp := substr( l_tmp1, 2 );
if l_numstr1 is null then
l_tmp1 := lpad( '9', 39, '9' );
else
l_tmp1 := lpad( '9', l_numstr1, '9' );
end if;
if l_numstr2 is not null then
l_tmp1 := substr( l_tmp1, 1, l_numstr1-l_numstr2 ) || '.' ||
substr( l_tmp1, -l_numstr2 );
end if;
l_str := l_str1 || to_char( to_number( p_args(i) ), l_tmp1 );
else
l_str := l_str || '%';
end if;
exception
when others then
l_str := l_str1 || 'XXXXXXXXXX';
end;
else
l_str := l_str || '%';
end if;
end loop;
return l_str || l_tmp;
exception
when others then
return l_str || l_tmp;
end parse_it;
procedure internal_f(
p_message in varchar2,
p_args in Argv default emptyDebugArgv ) is
--
l_locat varchar2(4000);
l_modules varchar2(4000);
l_filename varchar2(4000);
l_message long := null;
l_file utl_file.file_type;
l_date varchar2(255);
begin
select modules, locat, filename, to_char( sysdate, 'YYMMDD HH24MISS' )
into l_modules, l_locat, l_filename, l_date
from debugTab
where userid = user;
if instr( l_modules, nvl(g_name,'BLAH') ) = 0 and l_modules <> 'ALL' then
return;
end if;
if l_filename is not null then
g_file := l_filename;
end if;
l_message := parse_it( p_message, p_args );
/*
l_message := p_message;
begin
for i in 1 .. p_args.count loop
if instr( l_message, '%s' ) = 0 then
exit;
else
l_message := substr( l_message, 1, instr( l_message, '%s' )-1 ) ||
p_args(i) ||
substr( l_message, instr( l_message, '%s' )+2 );
end if;
end loop;
exception
when others then
null;
end;
*/
l_message := replace( l_message, '\n', chr(10) );
l_message := replace( l_message, '\t', chr(9) );
l_file := utl_file.fopen( l_locat, g_file, 'a', 32767 );
if not utl_file.is_open( l_file ) then
dbms_output.put_line( 'File not opened' );
end if;
if g_owner is null then
g_owner := user;
g_name := 'ANONYMOUS BLOCK';
end if;
utl_file.put( l_file, '' );
utl_file.put_line( l_file,
l_date ||
' (' || lpad( g_owner || '.' || g_name, 20 ) || ',' ||
lpad(g_lineno,4) || ') ' || l_message );
utl_file.fclose( l_file );
exception
when NO_DATA_FOUND then
-- dbms_output.put_line( sqlerrm );
null;
end internal_f;
procedure fa(
p_message in varchar2,
p_args in Argv default emptyDebugArgv ) is
begin
who_called_me( g_owner, g_name, g_lineno, g_caller_t );
internal_f( p_message, p_args );
end fa;
procedure f(
p_message in varchar2,
p_arg1 in varchar2 default null,
p_arg2 in varchar2 default null,
p_arg3 in varchar2 default null,
p_arg4 in varchar2 default null,
p_arg5 in varchar2 default null,
p_arg6 in varchar2 default null,
p_arg7 in varchar2 default null,
p_arg8 in varchar2 default null,
p_arg9 in varchar2 default null,
p_arg10 in varchar2 default null ) is
begin
who_called_me( g_owner, g_name, g_lineno, g_caller_t );
internal_f( p_message,
argv( substr( p_arg1, 1, 4000 ),
substr( p_arg2, 1, 4000 ),
substr( p_arg3, 1, 4000 ),
substr( p_arg4, 1, 4000 ),
substr( p_arg5, 1, 4000 ),
substr( p_arg6, 1, 4000 ),
substr( p_arg7, 1, 4000 ),
substr( p_arg8, 1, 4000 ),
substr( p_arg9, 1, 4000 ),
substr( p_arg10, 1, 4000 ) ) );
end f;
begin
g_file := 'DEBUGF_'||userenv('SESSIONID');
end debug;
/
show error
dba_procedures.sql
set echo on
create or replace view dba_procedures
as
select owner,
object_name name,
object_id,
object_type,
created,
last_ddl_time,
status,
(select decode( bitand(options,16), 16, 'INVOKER', 'DEFINER' )
from procedure$
where obj# = dba_objects.object_id ) auth_id
from dba_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY' )
/
create public synonym dba_procedures for sys.dba_procedures
/
set echo off
cria_user.sql
set echo on
create user &&1 identified by &&1
default tablespace &&2
temporary tablespace &3
quota unlimited on &&2
/
grant connect, resource to &&1
/
revoke unlimited tablespace from &&1
/
set echo off
cons.sql
column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&1')
/
calendario.sql
select to_char(dt,'yy') ano,
to_char(dt,'mm') Mes,
to_char(dt,'iw') Semana,
max(decode( to_char(dt,'d'), 2, to_char(dt,'dd'), null )) "2a",
max(decode( to_char(dt,'d'), 3, to_char(dt,'dd'), null )) "3a",
max(decode( to_char(dt,'d'), 4, to_char(dt,'dd'), null )) "4a",
max(decode( to_char(dt,'d'), 5, to_char(dt,'dd'), null )) "5a",
max(decode( to_char(dt,'d'), 6, to_char(dt,'dd'), null )) "6a",
max(decode( to_char(dt,'d'), 7, to_char(dt,'dd'), null )) "Sa",
max(decode( to_char(dt,'d'), 1, to_char(dt,'dd'), null )) "Do"
from ( select trunc(sysdate, 'yyyy')+rownum-1 dt
from all_objects
where rownum <= 366
)
group by to_char(dt,'yy') ,
to_char(dt,'mm') ,
to_char(dt,'iw')
order by 1,2,3
/
audit_new_old.sql
drop table audit_tbl
/
create table audit_tbl
( timestamp date,
who varchar2(30),
tname varchar2(30),
cname varchar2(30),
old varchar2(2000),
new varchar2(2000)
)
/
create or replace package audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 );
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date );
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number );
end;
/
create or replace package body audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in date, l_old in date )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in number, l_old in number )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;
end audit_pkg;
/
audit_dml.sql
set echo on
drop table audit_dml
/
create table audit_dml
( timestamp date,
who varchar2(30),
op varchar2(6),
seq number(6),
tname varchar2(30),
cname varchar2(30),
old varchar2(2000),
new varchar2(2000),
rid_old rowid,
rid_new rowid,
data_type char(1)
)
/
drop sequence x#_seq;
create sequence x#_seq;
create or replace package audit_pkg
as
procedure check_val(
l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,
l_rid_new in rowid,
l_rid_old in rowid,
l_op in varchar2,
l_seq in number
);
procedure check_val(
l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date,
l_rid_new in rowid,
l_rid_old in rowid,
l_op in varchar2,
l_seq in number
);
procedure check_val(
l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number,
l_rid_new in rowid,
l_rid_old in rowid,
l_op in varchar2,
l_seq in number
);
end;
/
create or replace package body audit_pkg
as
procedure check_val(
l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,
l_rid_new in rowid,
l_rid_old in rowid,
l_op in varchar2,
l_seq in number )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_dml values
( sysdate, sys_context('USERENV','OS_USER'), l_op, l_seq, upper(l_tname), upper(l_cname),
l_old, l_new, l_rid_old, l_rid_new, 'C' );
end if;
end;
procedure check_val(
l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date,
l_rid_new in rowid,
l_rid_old in rowid,
l_op in varchar2,
l_seq in number )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_dml values
( sysdate, sys_context('USERENV','OS_USER'), l_op,l_seq, upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ), l_rid_old, l_rid_new , 'D' );
end if;
end;
procedure check_val(
l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number,
l_rid_new in rowid,
l_rid_old in rowid,
l_op in varchar2,
l_seq in number )
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_dml values
( sysdate, sys_context('USERENV','OS_USER'), l_op, l_seq, upper(l_tname), upper(l_cname),
l_old, l_new, l_rid_old, l_rid_new , 'N' );
end if;
end;
end audit_pkg;
/
set echo off
audit_ddl.sql
set echo on
alter trigger auditddl_trg disable
/
drop table audit_ddl
/
create table audit_ddl (
timestamp date default sysdate,
owner varchar2(20) default user,
osuser varchar2(20),
object varchar2(30),
event varchar2(30),
type_obj varchar2(30))
/
create or replace trigger auditddl_trg
after truncate or drop or create or comment or grant or revoke on schema
begin
insert into audit_ddl (osuser, object, event, type_obj )
values (sys_context('USERENV','OS_USER'), ora_dict_obj_name, ora_sysevent, ora_dict_obj_type);
end;
/
set echo off
allusers.sql
column temporary_tablespace format a20
column default_tablespace format a20
select username, created, default_tablespace, temporary_tablespace, account_status
from dba_users
order by account_status, created
/
allsyn.sql
set echo on
select owner, synonym_name, table_owner, table_name from all_synonyms
where table_owner = upper('&1')
/
set termout off
undefine 1
set termout on
set echo off
Thursday, June 02, 2005
dbls.sql
column object_name format a30
column tablespace_name format a30
column object_type format a12
column status format a1
break on object_type skip 1
select object_type, object_name,
decode( status, 'INVALID', '*', '' ) status,
decode( object_type,
'TABLE', (select tablespace_name from user_tables where
table_name = object_name),
'INDEX', (select tablespace_name from user_indexes where
index_name = object_name),
null ) tablespace_name
from user_objects a
where object_name like upper('%&1%')
order by object_type, object_name
/
clear break
column status off
connect.sql
set termout off
save afiedt.buf replace
connect &1
@login
get afiedt.buf nolist
Tuesday, May 31, 2005
login.sql
define _editor=vi
-- Used by Trusted Oracle
column ROWLABEL format A15
-- Used for the SHOW ERRORS command
column LINE/COL format A8
column ERROR format A65 WORD_WRAPPED
-- Used for the SHOW SGA command
column name_col_plus_show_sga format a24
-- Defaults for SHOW PARAMETERS
column name_col_plus_show_param format a36 heading NAME
column value_col_plus_show_param format a30 heading VALUE
-- For backward compatibility
set pagesize 9999
set linesize 131
set editfile "/tmp/afiedt.buf"
set numwidth 13
set verify off
set termout off
set autoprint on
set sqlblanklines on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set nls_numeric_characters=',.';
alter session set optimizer_mode = choose;
column tree format a40
-- Defaults for SET AUTOTRACE EXPLAIN report
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a200
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
set serveroutput on size 1000000 format wrapped
set trimspool on
set long 10000
set arraysize 100
column plan_plus_exp format a80
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr(global_name, 1, decode( dot, 0,
length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on
set feedback on
