Archive

Archive for the ‘Oracle’ Category

Oracle Text Setup Script

November 1, 2012 Leave a comment

— Oracle Text Setup
— Provide grants to MYUSER to run Oracle Text
CONNECT / as sysdba
GRANT RESOURCE, CONNECT, CTXAPP TO MYUSER;

CONNECT ctxsys/ctxsys
GRANT EXECUTE ON CTXSYS.CTX_CLS TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_THES TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO MYUSER;

— Setup preferences for Oracle text
BEGIN
— Define table to use oracle text
CTX_DDL.CREATE_PREFERENCE(‘mytable_columns’, ‘MULTI_COLUMN_DATASTORE’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_columns’, ‘COLUMNS’,’MYTABLE_COL1, MYTABLE_COL2, MYTABLE_COL3, MYTABLE_COL4′);

— basic_lexer will cover english and most european languagues
CTX_DDL.CREATE_PREFERENCE(‘mytable_lexer’,’BASIC_LEXER’);

— Basic_wordlist preference to improve wildcard performance
CTX_DDL.CREATE_PREFERENCE(‘mytable_wordlist’, ‘BASIC_WORDLIST’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’PREFIX_INDEX’,’TRUE’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’PREFIX_MIN_LENGTH’, ‘3’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’PREFIX_MAX_LENGTH’, ‘4’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’SUBSTRING_INDEX’, ‘YES’);

— Storage preference for Oracle text indexes
CTX_DDL.CREATE_PREFERENCE(‘mytable_storage’, ‘BASIC_STORAGE’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’i_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’k_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’n_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’r_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’i_index_clause’,’TABLESPACE TABLESPACE_INDEXES’);

END;
/
— Setup Oracle text index
CONNECT MYUSER/MYUSER

ALTER TABLE MYTABLE_DETAILS ADD SEARCH_COL varchar2(1);

— Sync on commit will sync the index as soon as base table is updated
CREATE INDEX MYTABLE_TEXT_IDX ON MYTABLE_DETAILS (SEARCH_COL)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (‘DATASTORE CTXSYS.mytable_columns
LEXER CTXSYS.mytable_lexer
WORDLIST CTXSYS.mytable_wordlist
STORAGE CTXSYS.mytable_storage
SYNC (ON COMMIT)’);

— Query to use oracle text search
SQL> SELECT * FROM MYTABLE_DETAILS WHERE CONTAINS (SEARCH_COL, ‘FINDME’) > 0;

Categories: Oracle

Oracle: Historical Elapsed time by Queries

October 26, 2011 Leave a comment

SELECT SQL_TEXT,ROUND(X.ELAPSED_TIME) FROM DBA_HIST_SQLTEXT DHST,
(
SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.ELAPSED_TIME_DELTA/greatest(DHSS.EXECUTIONS_DELTA,1)/1000000) ELAPSED_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(
SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE
BEGIN_INTERVAL_TIME > to_date(’10/26/2011 08:59:00′,’mm/dd/yyyy hh24:mi:ss’) AND
END_INTERVAL_TIME < to_date('10/26/2011 10:01:00','mm/dd/yyyy hh24:mi:ss')

)
GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.ELAPSED_TIME DESC;

You can get the snapshot id using following query.
select * from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME desc

Categories: Oracle Tags:

How to generate AWR report if no access to Enterprise Manager

October 26, 2011 Leave a comment

Here is how you can generate AWR report through backend if you have no access to Enterprise Manager or browser.

Login to DB server.
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@awrrpt.sql

Example:
____________________________

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Oct 26 16:00:29 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> @awrrpt

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
793887637DEVDB1 1 DEVDB1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text

Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 793887637 1 DEVDB1 DEVDB1 usamt75uxpro
d01

Using 793887637for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
DEVDB1 DEVDB1 24778 26 Oct 2011 00:00 1
24779 26 Oct 2011 01:00 1
24780 26 Oct 2011 02:00 1
24781 26 Oct 2011 03:01 1
24782 26 Oct 2011 04:00 1
24783 26 Oct 2011 05:00 1
24784 26 Oct 2011 06:00 1
24785 26 Oct 2011 07:00 1
24786 26 Oct 2011 08:00 1
24787 26 Oct 2011 09:00 1
24788 26 Oct 2011 10:00 1
24789 26 Oct 2011 11:00 1
24790 26 Oct 2011 12:00 1
24791 26 Oct 2011 13:00 1
24792 26 Oct 2011 14:00 1
24793 26 Oct 2011 15:00 1
24794 26 Oct 2011 16:00 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24788
Begin Snapshot Id specified: 24788

Enter value for end_snap: 24789
End Snapshot Id specified: 24789

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24788_24789.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: oct_26_2011_10am_to_11am

Categories: Oracle Tags:

Oracle: Which Top 10 queries are taking Time NOW

October 26, 2011 Leave a comment

SELECT * FROM
(SELECT
sql_fulltext,
elapsed_time/executions/1000000,
executions,
last_load_time,
LAST_ACTIVE_TIME
FROM v$sql
Where executions > 0
ORDER BY elapsed_time desc )
WHERE ROWNUM < 11

Categories: Oracle Tags:

What is Oracle (ASM) Automatic Storage Management

October 15, 2011 Leave a comment

Very good documents to start on Oracle ASM. Happy Reading. J

Oracle 10g ASM Q and A.pdf

Oracle10gASM.pdf

Categories: Oracle Tags:

Oracle 11g Architecture Diagram

October 14, 2011 Leave a comment

Oracle 11g Architecture Diagram

Categories: Oracle Tags: