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 […]
Read More Make indexes unusable before big insertTo install latest version of Oracle ODBC: Go to http://www.oracle.com/technology/tech/oci/instantclient/ to download: Instant Client Package – Basic Instant Client Package – ODBC Unzip both of them into the same folder (e.g. c:\oracle). Add that folder name into the PATH environment variable (right click my computer, manage, system properties, advanced tab, environment variables) Create a new […]
Read More Install latest version of Oracle ODBCO’Reilly’s Steve Feuerstein Oracle PL/SQL Programming, 2nd Edition online at http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/
Read More O’Reilly’s Steve Feuerstein Oracle PL/SQL ProgrammingThis plugin is pretty much essential to get wordpress to display code: Syntax Highlighter and Code Prettifier Plugin for WordPress The other plugins I use are: wp-table Google XML Sitemaps Akismet
Read More Plugins for WordPress Code DisplayUse 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 […]
Read More chr(163) for £ pound characterchr(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;
Read More chr(13) chr(10) carriage return line feed CR LFOutput of sqltrpt is more readable with these settings: set pages 9999 lines 192 col plan_plus_exp form a132 @?/rdbms/admin/sqltrpt
Read More sqltrpt readable formattingvariable b1 varchar2(6) ; exec :b1 := ‘2009’ Used mainly for sql tuning rather than in real code.
Read More Setting bind variables in sql*plusIf you have accepted a sql profile from a tuning advisor recommandation like: execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_239607’, replace => TRUE); Then the regression commands to back it out are like: select name from dba_sql_profiles; exec dbms_sqltune.drop_sql_profile(‘MYNAME’)
Read More SQL profile dropWant to connect as a particular user but don’t know their password? You can temporarily change it and then reset it back, if you have DBA/alter any user privilege: set pages 9999 lines 132 long 9999 column col1 form a132 SELECT DBMS_METADATA.get_ddl ( ‘USER’ , ‘MYUSER’ ) AS col1 FROM DUAL ; ALTER USER myuser […]
Read More Change oracle password temporarily