Avoiding Mistakes with ITIL (IT Infrastructure Library)
ITIL is best practice advice and guidance. It is a common mistake to take as prescriptive. Needs adapted/customised to be organisation specific. So is not a silver bullet. Not a standard. You cannot be ITIL compliant. You need to adopt and adapt components of ITIL. Decide on the ITIL tools you are going to use and then modify them to suit. Continue reading
This allows you to get code listings for all code (procedures, views, materialized views, etc.) that reference particular tables:
set long 200000 pages 0 verify off lines 131 feed off
column txt format a121 word_wrapped
column spoolfile new_value spoolfile noprint
spool go.tmp
select '@2 '|| decode(type , 'MATERIALIZED VIEW' , 'MATERIALIZED_VIEW' , type) ||' '||name||' '||owner
from dba_dependencies where referenced_name in ('TABLE1','TABLE2','TABLE3')
order by 1 ;
spool off
@go.tmp
That calls files 2.sql which contains:
select lower( '&1' ||'.'|| '&2' || '.sql' ) spoolfile from dual ;
spool &spoolfile
select dbms_metadata.get_ddl('&1','&2','&3') txt from dual;
spool off
Send email from unix with attachment, which in this case has been zipped with a password:
zip -P mypassword /tmp/myfile.zip myfile.csv mutt -a /tmp/myfile.zip -s "Zipfile Attached" user@email.com < /dev/null
Make indexes unusable before doing a big insert, then rebuild them at the end, is faster than insert with indexes in place and no risk of forgetting to recreate a dropped index:
create table af ( mycol varchar2(100) ) ; create index af1 on af ( mycol ) ; alter index af1 unusable ; insert into af (mycol) select * from big_table ; select status from dba_indexes where index_name = 'AF1' ; alter index af1 rebuild ; select status from dba_indexes where index_name = 'AF1' ;
Code to make a lot of indexes unusable in one go is:
select 'alter index '||owner||'.'||index_name||' unusable ;' from dba_indexes where owner = 'MYSCHEMA' ;
To install latest version of Oracle ODBC:
Go to http://www.oracle.com/technology/tech/oci/instantclient/ to download:
Unzip both of them into the same folder (e.g. c:\oracle).
You can then add and test oracle odbc connections in Control Panel Administrative Tools.
O’Reilly’s Steve Feuerstein Oracle PL/SQL Programming, 2nd Edition online at http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/
This plugin is pretty much essential to get wordpress to display code:
The other plugins I use are:
Use chr(163) for the ‘£’ character, especially in stored SQL such as create procedure scripts, where you can’t be sure what client character settings might be used to re-create the procedure.
SELECT m.description, chr(163)||TO_CHAR(m.weekly_rate, '999.99') amount from my_table m;
This plsql gives the full list of ASCII codes:
set serveroutput on size 10240
declare
i number;
j number;
k number;
begin
for i in 2..15 loop
for j in 1..16 loop
k:=i*16+j;
dbms_output.put((to_char(k,'000'))||':'||chr(k)||' ');
if k mod 8 = 0 then
dbms_output.put_line(' ');
end if;
end loop;
end loop;
end;
/
(From http://www.orafaq.com/wiki/ASCII )
chr(13) and chr(10) are the carriage return line feed CR LF characters, so to strip them out:
select replace(replace(m.long_notes,chr(10),' '),chr(13),' ') from mytable m;
Output of sqltrpt is more readable with these settings:
set pages 9999 lines 192 col plan_plus_exp form a132 @?/rdbms/admin/sqltrpt