slackorama

 

Oracle

Page history last edited by s 1 yr ago

  1. Find the db links
  2. Using variables in your scripts
    1. Basics
    2. To undefine a variable
    3. Helpful Link
  3. Get Oracle product version
  4. Dates and times
  5. Using JPub for queue stuff
  6. Add comments to a table or column
  7. find out the details of a constraint
  8. Session information from Oracle
  9. Strip out newline characters in varchars
  10. How does one disable interactive prompting in SQL*Plus?
  11. How does one escape special characters when building SQL queries?
  12. Performance Tuning
    1. Running tkprof
    2. Code instrumentation
  13. Dictionary Releated Queries
    1. Get a list of all dictionary tables with their comments
    2. Get the columns of a table with their comments
  14. Queue Related Queries
    1. Get List of Subscribers
  15. RecycleBin in 10g
    1. Purge that sucker
  16. Benchmarking code
  17. Plan For Fun and Profit

Find the db links

select * from user_db_links;

select * from dba_db_links;

 

Using variables in your scripts

Basics

& defines a temporary variable

&& defines a permanent variable

 

To undefine a variable

Where 'variablename' is the name you want to redefine

undef variablename

 

Helpful Link

Spice up your SQL Scripts with Variables

 

Get Oracle product version

select * from product_component_version;

 

Dates and times

Oracle Dates and Times

 

Using JPub for queue stuff

jpub

-user=username/password

-sql=some_message_type:SomeClass

-usertypes=oracle

-methods=false

-url=jdbc:oracle:some.host:1521:db

-package=com.package.foo

more info

 

Used this for Oracle Advanced Queueing

 

Add comments to a table or column

Add a comment to the data dictionary:

 

Syntax:

 

COMMENT ON TABLE [schema.]table IS 'text'

COMMENT ON TABLE [schema.]view IS 'text'

COMMENT ON TABLE [schema.]materialized_view IS 'text'

COMMENT ON COLUMN [schema.]table.column IS 'text'

COMMENT ON COLUMN [schema.]view.column IS 'text'

COMMENT ON COLUMN [schema.]materialized_view.column IS 'text'

 

To drop a comment from the database, set it to the empty string ' '.

 

 

find out the details of a constraint

select * from user_constraints where constraint_name = 'CONSTRAINT_NAME_HERE'

 

Session information from Oracle

Get information like what machines are connected to the db

select * from v$session

 

Strip out newline characters in varchars

select replace(val,chr(13)||chr(10)) from some-table

 

 

How does one disable interactive prompting in SQL*Plus?

If you run a script that contains "&" symbols, SQL*Plus thinks that you want to prompt the user for a value. Some clients allow one to escape the ampersand characer with a backslash, however, that doesn't work from SQL*Plus. Here is a couple of solutions:

 

SET ESCAPE ON

SET ESCAPE "\"

SELECT 'You \& me' FROM DUAL;

 

or

 

SET DEFINE ?

SELECT 'You & me' FROM DUAL;

 

Atanas Kebedjiev provided this solution:

 

SELECT 'You '|Chr(38)|' Me' FROM DUAL;

 

Note: You can disable substitution variable prompting altogether by issuing the SET DEFINE OFF command.

 

How does one escape special characters when building SQL queries?

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:

SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';

 

Use two quotes for every one displayed. Example:

SELECT 'Franks''s Oracle site' FROM DUAL;

SELECT 'A ''quoted'' word.' FROM DUAL;

SELECT 'A ''''double quoted'''' word.' FROM DUAL;

 

Performance Tuning

Running tkprof

tkprof input_tracefile.trc output1.txt sys=no explain=userid/pwd sort=prsela, exeela, fchela

 

Code instrumentation

Will generate a tracefile on the db server:

alter session set tracefile_identifier='id-of-tracefile';

alter session set statistics_level=all;

alter session set "_rowsource_execution_statistics"=true;

alter session set events '10046 trace name context forever, level 12';

*****place query here*****

exit;

Dictionary Releated Queries

Get a list of all dictionary tables with their comments

select table_name, comments

from dictionary

order by table_name;

 

Get the columns of a table with their comments

select column_name, comments

from dict_columns

where table_name = 'ALL_TAB_COLUMNS';

 

Queue Related Queries

 

Get List of Subscribers

  select 
    queue_name,
    consumer_name 
  from 
    user_queue_subscribers;

 

RecycleBin in 10g

Purge that sucker

 

PURGE recyclebin;

 

Benchmarking code


SET serveroutput ON
DECLARE
        nLoops CONSTANT NUMBER := 1000000;
        nStart NUMBER;
        val DATE;
BEGIN
        dbms_output.enable(buffer_size => 10000);
        nStart := dbms_utility.get_time();
        FOR i IN 1..nLoops
        LOOP
--              val := ssm_epoch_to_date(0);
                val := epoch_to_date(0);
        END LOOP;
        dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

        nStart := dbms_utility.get_time();
        FOR i  IN 1..nLoops
        LOOP
                val := to_date('01/01/1970','MM/DD/YYYY') + (0/(60 * 60 * 24));
        END LOOP;
        dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');
END;

 

Plan For Fun and Profit

 


set echo on

delete from plan_table
 where statement_id = 'ssm_qry';
commit;
COL operation   FORMAT A30
COL options     FORMAT A15
COL object_name FORMAT A20
EXPLAIN PLAN set statement_id = 'ssm_qry' for
-- ************************************************
-- PUT YOUR SQL HERE
-- ************************************************

-- Arguments to dbms_xplan.display: 
--  * table
--  * statement_id
--  * format:  'TYPICAL','BASIC', 'ALL', 'SERIAL'.
select * from TABLE(dbms_xplan.display('PLAN_TABLE','ssm_qry','BASIC'));

set echo off

Comments (0)

You don't have permission to comment on this page.

Printable version
 
Anonymous Create an account or Log in