alter system flush shared_pool; -- flushes the cache

select * from v$sqlarea; -- for baseline

-- execute your code/sql here

select * from v$sqlarea; -- compare to baseline

keywords: sql history, past sql, previous sql statement

ALTER TABLE <table_name> ADD CONSTRAINT <primary_key_constraint_name> PRIMARY KEY (<column_name>) USING INDEX

ALTER TABLE <tablename> DROP PRIMARY KEY

SELECT * FROM user_ind_columns WHERE table_name='<tablename>' ORDER BY index_name, column_position

SELECT * FROM user_constraints WHERE table_name = '<tablename>'

SELECT * FROM user_cons_columns WHERE table_name = '<tablename>'

Here is the sql to view the indexes on an oracle table.  Just replace MYTABLENAME with yout table name:

select index_name, column_name, column_position from user_ind_columns
where table_name='MYTABLENAME' order by index_name, column_position

You might notice that after installing an ASP, ASP/VB6, or ASP.NET application, you get undesired results under oracle client driver versions Oracle9i Release 2 (9.2.0.x.x) and Oracle10g Release 1 (10.1.0.x.x).

This is due to an Oracle install bug dealing with file permissions.

 Due to a bug (see Note:215255.1) of Oracle installation on XP, you have to execute the following bug-fix:

 Solution Description -------------------- Oracle 9.2 Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:

 1. Log on to Windows as a user with Administrator privileges.

 2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME directory.

 3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.

 4. Click on the "Security" tab on the "Properties" window.

 5. Click on "Authenticated Users" item in the "Name" list (on Windows XP the "Name" list is called "Group or user names").
 
 6. Uncheck the "Read and Execute" box in the "Permissions" list (on Windows XP the "Permissions" list is called "Permissions for Authenticated Users"). This box will be under the "Allow" column.
 
 7. Check the "Read and Execute" box. This is the box you just unchecked.
 
 8. Click the "Apply" button.
 
 9. Click the "OK" button.
 
 10. Reboot your computer after these changes have been made. Re-execute the application and it should now work.

Oracle TNS Names Configuration

Tuesday, July 19, 2005

4:12 PM

A question I am frequently asked is how do I set up a PC's TNSNAMES.ORA file so that it contains an entry pointing at a particular oracle database instance.

Adding an ODBC DSN is out of the scope of this document, but I will mention the following:

Once this configuration is done you can use the ODBC Administrator to add an Oracle DSN.  In the Oracle ODBC Driver Configuration window, in the TNS Name Service text box, you can enter the net service name as you entered it in the setup below, or you can enter the fully qualified net service name as it appears in the tnsnames.ora file.

Ok, here is the configuration:

Please note that the following screen shots may slightly vary if this is not the first name service entry you have added.

Run the Oracle Net Configuration Assistant from:

Start Menu / All Programs / OraHome90 / Configuration and Migration Tools / Net Configuration Assistant.   (OraHome90 may be named something different.)

Select "Local Net Service Name configuration", then "Next".

Select "Add", then "Next".

Select "Oracle8i or later database or service", then "Next".

Enter the name of the service.  This will most likely need to be the fully qualified service name of the oracle service instance. (Not necessarily the name of the machine, because more than one oracle database service instance can run on a single machine.)

If you do not know the service name, one way to find it is to run the "Oracle Net Manager" on the server and expand "Oracle Net Configuration", then expand "Local", then expand "Service Naming".  This will then contain a list of service names.

Select "TCP", then "Next".

Enter the host name of the computer that the oracle database service is running on.  This can be the IP address, the NetBIOS computer name (like "computer1"), or fully qualified domain name of the computer (like "computer1.mydomain.com").

Select "Yes, perform a test", then "Next".

Because the first test is done using the "scott" and "tiger" username and password, it will likely fail and you must select "Change Login".

Change the default username and password to a valid username and password for the database service you entered.  Then click "OK".

After changing the login, the test will automatically rerun and hopefully you will have success.  If so, select "Next".

Enter any net service name you like here which will be used as a reference to the configuration you just set up.  Then select "Next".

At this point your configuration has been saved to your TNSNAMES.ORA which will be somewhere like:  C:\Program Files\Oracle\ora90\network\admin\tnsnames.ora

And will look something like:

# TNSNAMES.ORA Network Configuration File: C:\progra~1\oracle\ora90\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

MYSERVICENAME.MYDOMAIN.COM =

(DESCRIPTION =

(ADDRESS LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = computer1)(PORT = 1521))

)

(CONNECT DATA =

(SERVICE NAME = myoracleservice.mydomain.com)

)

)

Select "Next".

Select "Finish" and the configuration application will close.

To retrieve an unordered set of rows:

SELECT * FROM orders WHERE rownum < 2 ;

To retrieve rows of an ordered result set:

SELECT o.* FROM (SELECT * FROM orders ORDER BY orderid) o
WHERE rownum < 2 ;

This is because rownum is assigned to the result set when the records are retrieved and before they are sorted.

Note: In Sql Server, use the TOP keyword.

Sometimes (especially when running web applications) it's nice to see how many open connections you have to your database. Use this select statement to get them (ignore SID of <="5).

select * from v$lock

Cross reference SID with:

select * from v$session where sid=1234