Oracle/SQL Server Links

1)PSOUG -  Oracle PL/SQL Code Library 

2)Sqlservercentral - 
A Microsoft SQL Server community of DBAs, developers and SQL Server users

Delete all old Oracle trace and audit files more than 14 days old

Delete all old Oracle trace and audit files more than 14 days old

- Oracle Tip by Burleson Consulting

Here is an example of a UNIX script for keeping the archived redo log directory free of elderly files. As we know, it is important to keep room in this directory, because Oracle may “lock-up” if he cannot write a current redo log to the archived redo log filesystem. This script could be used in coordination with Oracle Recovery Manager (rman) to only remove files after a full backup has been taken.

clean_arch.ksh
#!/bin/ksh

# Cleanup archive logs more than 7 days old
find /u01/app/oracle/admin/mysid/arch/arch_mysid*.arc -ctime +7 -exec rm {}
;

Now that we see how to do the cleanup for an individual directory, we can easily expand this approach to loop through every Oracle database name on the server (by using the oratab file), and remove the files from each directory. If you are using Solaris the oratab is located in /var/opt/oratab while HP/UX and AIX have the oratab file in the /etc directory.

clean_all.ksh
#!/bin/ksh

for ORACLE_SID in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut -f1 -d':'`
do
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d":" -f2`
DBA=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`/admin
find $DBA/$ORACLE_SID/bdump -name \*.trc -mtime +14 -exec rm {} \;
$DBA/$ORACLE_SID/udump -name \*.trc -mtime +14 -exec rm {} \;
find $ORACLE_HOME/rdbms/audit -name \*.aud -mtime +14 -exec rm {} \;
done

The above script loops through each database, visiting the bdump, udump and audit directories, removing all files more than 2 weeks old.

Installing Oracle Database 10g Products

This section covers the following topics:

■ Oracle Home Location for the Oracle Database 10g Products
■ Procedure for Installing Oracle Database 10g Products
■ Preparing Oracle Workflow Server for the Oracle Workflow Middle Tier

Installation

You need to install Oracle Database 10g Products in an existing Oracle Database 10g
release 2 (10.2) Oracle home. These products are:

■ Oracle JDBC Development Drivers
■ Oracle SQLJ
■ Database Examples
■ Oracle Text Knowledge Base
■ JAccelerator (NCOMP)
■ Intermedia Image Accelerator)
■ Oracle Workflow

Identifying the Oracle Home Directory Location

Before you install Oracle Database 10g Products into an existing Oracle home, you
need to identify the location of this Oracle home. If you do not know the path of the
Oracle home directory, you can check it using Oracle Universal Installer.

To check the path of the Oracle home directory:

1. From the Start menu, choose Programs, then Oracle - HOME_NAME, then Oracle
Installation Products, then Universal Installer.

2. When the Welcome window appears, click Installed Products.The Inventory window appears, listing all of the Oracle homes on the system and the products installed in each Oracle home.

3. In the Inventory window, expand each Oracle home and locate Oracle Database
10g 10.2.0.1.0.

4. Click Close and then Cancel to exit Oracle Universal Installer.

5. Have the Oracle home name available when you begin installing Oracle Database
10g Products, described next.

Procedure for Installing Oracle Database 10g Products

To install Oracle Database 10g Products:
1. Log on as a member of the Administrators group to the computer on which to
install Oracle components.

2. Make sure that the Oracle database that you plan to use for Oracle Workflow is
accessible and running.

You can use the Windows Services utility, located either in the Windows Control
Panel or from the Administrative Tools menu (under Start and then Programs), to
check that Oracle Database is running. Names of Oracle databases are preceded
with OracleService. Right-click the name of the service and from the menu,
choose Start.

3. Delete the ORACLE_HOME environment variable (from the System Control Panel) if
it exists.

Refer to your Microsoft online help for more information about deleting
environment variables.

4. Insert the Oracle Database installation media and navigate to the companion
directory. Alternatively, navigate to the directory where you downloaded or
copied the installation files.
Use the same installation media to install Oracle Database on all supported
Windows platforms.

5. Double-click setup.exe to start Oracle Universal Installer.

6. In the Welcome window click Next.

7. In the Select a Product to Install window, choose Oracle Database 10g Products
and click Next.

8. In the Specify Home Details window, do the following:

a. Name: Verify that the Oracle home specified is the Oracle Database Oracle
home. (The default Oracle home is offered.)
b. Path: Enter the directory location of the Oracle Database Oracle home where
you want to install the Oracle home files. (The directory of the default Oracle
home is offered.)

9. Click Next.

10. In the Product-specific Prerequisite Checks window, check for and correct any
errors that may have occurred when Oracle Universal Installer checked your
system.

11. Click Next.

12. In the Summary window, check the list of products that will be installed, and click
Install.

13. When the installation completes, click Exit and then click Yes to exit from Oracle
Universal Installer.

Uninstall Oracle

The simplest way to remove Oracle is to run the Oracle installer:

Start > Programs > Oracle Installation Products > Universal Installer

1. On the first screen click on "Deinstall Products..."
2. Expand the tree view (just so that the second level is visible) and make sure you select everything that is selectable.
3. Click on "Remove..."
4. On the confirmation screen click "Yes"
5. When it has finished click "Close" and then "Exit" to quit the installer

Whilst the Oracle installer removes many components there are a number of things that it leaves behind. In order to completely remove all traces of Oracle the following additional steps will need to be taken:

1. Stop any Oracle services that have been left running. (Start > Settings > Control Panel > Services.)
   Services which I have found left behind are 'OracleOraHome90TNSListener' and 'OracleServiceORACLE'. However there may be others depending on your installation. Look for any services with names starting with 'Oracle'.
2. Run regedit (Start > Run > Enter "regedit", click "Ok"), find and delete the following keys:
   
   HKEY_LOCAL_MACHINE
\SOFTWARE
\ORACLE

HKEY_LOCAL_MACHINE
\SYSTEM
\CurrentControlSet
\Services
\EventLog
\Application
\Oracle.oracle
   
   Note: I have had it reported that some people also have registry entries saved under HKEY_CURRENT_USER\SOFTWARE\ORACLE, this registry entry may be created by some Oracle utilities. If it exists then delete it.
3. Delete the Oracle home directory:
   "C:\Oracle"
   This will also remove your database files (unless you located them elsewhere, in which case you will need to delete them separately).
4. Delete the Oracle program Files directory:
   "C:\Program Files\Oracle"
5. Delete the Oracle programs profile directory:
   "C:\Documents and Settings\All Users\Start Menu\Programs\Oracle - OraHome90"
   if you did not first run the Oracle installer to remove Oracle then there may be other Oracle profile group directories to remove.
6. Some of the Oracle services may be left behind by the uninstall. Open ‘services’ on the control panel, make a note of which Oracle services remain and see the notes ‘How to remove a service’ to remove them.
7. If you didn't first run the Oracle Installer to remove Oracle then you may have some references to Oracle left in the path. To remove these: Start > Settings > Control Panel > System > Advanced > Environment Variables, look at both the use and system variable 'PATH' and edit them to remove any references to Oracle.

Sizing the Redo Log Buffer

Imagine you’ve inherited an existing Oracle database that is used to support
your company’s order-taking system. After examining the Shared Pool
and Database Buffer Cache hit ratios, you realize that they could probably
be improved by making these structures larger, but there is not sufficient
memory in the server to do so. The Redo Log Buffer’s Retry Ratio is low—
0.0000012.
Before you give up and ask the boss to order more memory for the server,
examine the size of the Redo Log Buffer. Chances are, with a Retry Ratio this
low, the Redo Log Buffer may be oversized and wasting memory.
Suppose you check the size of the Redo Log Buffer and discover that it is
10MB. Any value above 1MB will be largely unused because LGWR is signaled
to write when the Redo Log Buffer is one-third full or filled with 1MB
of redo entries. By reducing the size of the Redo Log Buffer to 1MB or less,
you can allocate the approximately 9MB remaining space to the Shared
Pool and/or Database Buffer Cache. This will likely improve the overall SGA
hit ratios without consuming any additional memory on the server.

How Big Should I Make the Shared Pool on My New Server?

Initial SGA Size Calculation

Instead of using the Oracle sample values, use the following rule of thumb
when setting the SGA size for a new system:

1. Server Physical Memory × .55 = Total Amount of Memory to Allocate to All SGAs (TSGA)
   
2. TSGA/Number of Oracle Instances on the Server = Total SGA Size Per Instance (TSGAI)
   

Using this TSGAI value, you can then calculate the SGA sizing for each individual instance:

• TSGAI × .45 = Total Memory Allocated to the Shared Pool

•  TSGAI × .45 = Total Memory Allocated to the Database Buffer Cache

•  TSGAI × .10 = Total Memory Allocated to the Redo Log Buffer

Use the DBMS_STATS package to perform the export and import operations

1. In the production database, create the STATS table in the JOE schema in the TOOLS tablespace. This table will be used to hold the production schema statistics.

SQL> EXECUTE DBMS_STATS.CREATE_STAT_TABLE
(‘JOE’,’STATS’,’TOOLS’);

2. Capture the current statistics for Joe’s schema and store them in the newly created STATS table.

SQL> EXECUTE DBMS_STATS.EXPORT_SCHEMA_STATS
(‘JOE’,’STATS’);

3. Use the Oracle Export utility from the OS command line to export the contents of the STATS table from the production database.

$ exp joe/sidney@PROD file=stats.dmp tables=
(STATS) log=stats.log

4. Move the export dump file from the production server to the Development server using FTP.

$ ftp devl
Connected to DEVL
220 DEVL FTP server (Tue Oct 22 16:48:12 EDT 2002) ready.
Name (DEVL:jjohnson): johnson
331 Password required for johnson.
Password:
230 User johnson logged in.
Remote system type is Unix.
Using binary mode to transfer files.
ftp> put stats.dmp
200 PORT command successful.
150 Opening BINARY mode data connection for stats.dmp
226 Transfer complete.
ftp> bye

5. In the Development database, create the STATS table in the JOE schema in the TOOLS tablespace. This table will hold the exported contents of the STATS table from the production database.

SQL> EXECUTE DBMS_STATS.CREATE_STAT_TABLE
(‘JOE’,’STATS’,’TOOLS’);

6. Use the Oracle Import utility from the operating system command line to import the STATS dump file created on the production server into the STATS table on the Development server.

$ imp joe/sidney@DEVL file=stats.dmp log=stats.log full=y

7. Move the statistics in Joe’s STATS table into the Development database’s data dictionary.

SQL> EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS
(‘JOE’,’STATS’);

The Importance of Histograms

On one occasion, I experienced a query that was running against a large table whose indexed columns did not have normally distributed data. A particular query on this table was taking over 18 hours to parse, execute, and fetch its rows. After we realized that the data, instead of being normally distributed between a low and high value, had spikes of high and low values throughout, we analyzed the table using the following options:

SQL> ANALYZE TABLE ABC COMPUTE STATISTICS FOR COLUMNS award
SIZE 100;

This command created a histogram that divided the data in the AWARD column of the ABC table into 100 separate slices. The histogram then determined the high and low value for each of the slices and stored those as part of the table and index statistics. These statistics gave the optimizer a much more accurate view of the data and the usefulness of the associated
indexes.

As a result, the same query that had taken 18 hours to complete now ran in only three minutes with no other changes. This clearly demonstrates the importance of using histograms when the column data being accessed is not normally distributed. Histogram information can be found in the DBA_HISTOGRAMS data dictionary view.

Fast guide to solve oracle error

Stumped by an Oracle error? This fast guide is sure to have the answer!

SearchOracle.com has compiled a list of every expert response pertaining to Oracle errors so you can find the answers you need, quickly and easily! If the error you're dealing with is not listed, or if your dilemma is not specifically addressed, just ask one of the experts for help and they will add the response to this ever-growing guide to common Oracle errors.

Stop searching for the answer and start resolving the problem, download this fast guide to common Oracle errors now:
http://oracle-tips.c.topica.com/maalmgXabG0XTbJFxJAb/

Turn on debug mode for a module by using DBMS_SQL within a PL/SQL Program

CREATE OR REPLACE PROCEDURE debugpkg (name IN VARCHAR2)
IS
 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
 abc INTEGER;
BEGIN
 DBMS_SQL.PARSE (cur,
    'ALTER PACKAGE ' || name || ' COMPILE DEBUG',
    DBMS_SQL.NATIVE);

 abc := DBMS_SQL.EXECUTE (cur);

 DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

How frequently data blocks are accessed from the buffer cache (Block Buffer Hit Ratio)

Oracle database maintains dynamic performance view V$BUFFER_POOL_STATISTICS with overall buffer usage statistics. This view maintains the following counts every time a data block is accessed either from the block buffers or from the disk:

NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT

DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads

Based on above statistics we can calculate the percentage of data blocks being accessed from the memory to that of the disk (block buffer hit ratio). The following SQL statement will return the block buffer hit ratio:

SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;

A hit ratio of 95% or greater is considered to be a good hit ratio for OLTP systems. The hit ratio for DSS (Decision Support System) may vary depending on the database load. A lower hit ratio means Oracle is performing more disk IO on the server. In such a situation, you can increase the size of database block buffers to increase the database performance. You may have to increase the physical memory on the server if the server starts swapping after increasing block buffers.

Find trace file in oracle - oradebug

In order to make a more informative trace file name, the following command can be used:

alter session set tracefile_identifier = my_session;

A trace file will then have this identifier (here: my_session) in it's filename.

The trace file's name can also be found with oradebug:

SQL> oradebug setmypid
SQL> oradebug tracefile_name

Oradebug

The oradebug utility falls into the “hidden” classification of utilities due to the lack of available documentation. The utility is invoked directly from SQL*Plus beginning in version 8.1.5 and Server Manager in releases prior to that. The utility can trace a user session as well as perform many other, more global, database tracing functions.

oradebug requires the SYSDBA privilege to execute (connect internal on older Oracle versions). The list of oradebug options can be viewed by typing oradebug help at the SQL*Plus prompt:

List of Select Privileges to access different functions

List of select privileges required on the fixed views to execute different functions

**DISPLAY_CURSOR

V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.

**DISPLAY_AWR

DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.

Grant on v$ views

Have you faced similar problem while providing Grant on v$ views

SQL> grant select on v$session to scott;
grant select on v$session to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Reason:

Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.

If you want to give permission to a V$ view you must give it like below

SQL> grant select on v_$session to scott;

Grant succeeded.

EXPLAIN PLAN

EXPLAIN PLAN

** Creating a PLAN_TABLE

CONNECT HR/your_password 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Table created.

CREATE TABLE plan_table
 (
  statement_id               VARCHAR2(30),
  timestamp                  DATE,
  remarks                    VARCHAR2(80),
  operation                  VARCHAR2(30),
  options                    VARCHAR2(30),
  object_node                VARCHAR2(128),
  object_owner               VARCHAR2(30),
  object_name                VARCHAR2(30),
  object_instance            NUMBER,
  object_type                VARCHAR2(30),
  optimizer                  VARCHAR2(255),
  search_columns             NUMBER,
  id                         NUMBER,
  parent_id                  NUMBER,
  position                   NUMBER,
  other                      LONG
 )

** If you want an output table with a different name

RENAME PLAN_TABLE TO my_plan_table;

** Next, you can run the following script to get a list
of the steps that Oracle will perform in order to execute
your query:

set echo on

delete from plan_table
 where statement_id = 'MINE';
commit;
COL operation   FORMAT A30
COL options     FORMAT A15
COL object_name FORMAT A20
EXPLAIN PLAN set statement_id = 'MINE' for

/* ------ Your SQL here ------*/
select * from scott.salgrade
/*----------------------------*/

/

set echo off

select operation, options, object_name
from plan_table
where statement_id = 'MINE'
start with id = 0
connect by prior id=parent_id
and prior statement_id = statement_id;
set echo on

** Displaying PLAN_TABLE Output with 
DBMS_XPLAN.DISPLAY procedure

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT 
FROM TABLE
(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

To learn more:Tutorial

AUTOTRACE in sqlplus

AUTOTRACE in sqlplus

The autotrace command causes Oracle to print out the execution plan for a command and details about the number of disc and buffer reads that have occurred during the execution.

SET AUTOTRACE ON

To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema.

* cd $oracle_home/rdbms/admin
* log into sqlplus as system
* run SQL> @utlxplan
* run SQL> create public synonym plan_table for plan_table
* run SQL> grant all on plan_table to public
* exit sqlplus and cd $oracle_home/sqlplus/admin
* log into sqlplus as SYS
* run SQL> @plustrce
* run SQL> grant plustrace to public

You can replace public with some user if you want. by making it public, you let anyone trace using sqlplus (not a bad thing in my opinion).

SQL queries that can make your life easy!!!

1)To list all your tables in Oracle server

Select * from cat;

2)To generate a KEEP for each package currently in the shared pool:

SELECT
'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');'
FROM v$db_object_cache
WHERE type='PACKAGE';

SELECT DISTINCT
'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');'
FROM user_source
WHERE type='PACKAGE';

SELECT DISTINCT
'EXECUTE DBMS_SHARED_POOL.KEEP('''||object_name||''');' 
FROM user_objects
WHERE object_type='PACKAGE';

3) To match a user trace file to the user session that generated it.

SELECT s.username, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.background is null;

4)To know which tables have rows in them that are
either chained or migrat ed.

select table_name, chain_cnt
from dba_tables
where owner = ’SCOTT’
and chain_cnt !=0;

5)Show all tablespaces used by a user

select table
space_name, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name;

6) Show table name, number of rows, block, empty blocks, avg row length

select table_name, num_rows, blocks, empty_blocks, avg_row_len
from dba_tables where
owner = 'SCOTT';

7)To see if any space is being wasted in the index

SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100
“Wasted Space”
FROM index_stats
WHERE name = ‘EMPLOYEE_LAST_NAME_IDX’;

8)To calculate the Data Dictionary Cache hit ratio

SELECT 1 - (SUM(getmisses)/SUM(gets))
"Data Dictionary Hit Ratio"
FROM v$rowcache;

9)To determine the current size of the Shared Pool

SELECT pool, sum(bytes) "SIZE"
FROM v$sgastat
WHERE pool = ’shared pool’
GROUP BY pool;

10)Calculating the Database Buffer Cache hit ratio using
V$SYSSTAT



11)To calculate hit ratios for each of the individual
Buffer Pools

SELECT name "Buffer Pool", 
1-(physical_reads / (db_block_gets + consistent_gets))
"Buffer Pool Hit Ratio" FROM v$buffer_pool_statistics
ORDER BY name;

12)To determine which tables are cached

SELECT owner, table_name
FROM dba_tables
WHERE LTRIM(cache) = ’Y’;

13)To calculate a Redo Log Buffer Retry Ratio

SELECT retries.value/entries.value
"Redo Log Buffer Retry Ratio"
FROM v$sysstat retries, v$sysstat entries
WHERE retries.name='redo buffer allocation retries'
AND entries.name='redo entries';


14)Checkpoint activity shown in V$SYSTEM_EVENT

select event,total_waits,average_wait from v$system_event
where event like '%check%' or event like '%log file switch%';

15)Checkpoint activity shown in V$SYSSTAT

select name,value from v$sysstat where name 
like '%background checkpoint%';

select tablespace_name , ceil(sum(bytes) / 1024 / 1024) "MB" from dba_extents where owner like '&user_id' group by tablespace_name order by tablespace_name /

16) Redo Log activity shown in V$SYSTEM_EVENT

 select  EVENT ,  TOTAL_WAITS ,AVERAGE_WAIT 
from v$system_event
where event='log file parallel write'
or event='log file switch completion';

17)V$LOCK to Monitor Lock Contention

SELECT s.username,
DECODE(l.type,'TM','TABLE LOCK','TX','ROW LOCK', NULL) "LOCK LEVEL",
o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL;

18) Identify the application user that is blocking other users

SELECT s.username
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid;

Loading files form a sql loader

Loading files form a sql loader

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat
file into one or more database tables.

The SQL*Loader control file is a text file into which you place a
description of the data to be loaded.
Date file contains the data.

Let Table in which i want to add data be E1 .Control file name be loader.ctl and data file be mydata.csv

loader.ctl ->

load data
into table E1
fields terminated by "," optionally enclosed by '"'
( empno, ename, sal, deptno )

mydata.csv ->
11,'Ann',50000,11
22,'Snow',80000,22
33,'Ash',90000,33
44,'Ronie',30000,44


Using SQL*Plus:

SQL> EDIT loader.ctl

copy and paste your control file, then save it.

SQL> EDIT mydata.csv

copy and paste your data into it, then save it.

SQL> HOST sqlldr scott/tiger control=loader.ctl log=loader.log data=mydata.csv

Notice that there is no space on either side of the equal sign in
"control=loader.ctl". This is a requirement .

To see what happened during the load.

SQL> EDIT loader.log