To determine an Oracle database's character set:

select value from nls_database_parameters where parameter='NLS_CHARACTERSET'

UTF8 was the UTF-8 encoded character set in Oracle8 and 8i.

AL32UTF8 is the UTF-8 encoded character set introduced in Oracle9i.

VB6 reports UTF8 characters as 3 bytes, and AL32UTF8 characters as 4 bytes.

How "character-based" sizing in Oracle works

It requires setting an instance or session parameter (e.g., alter session set NLS_LENGTH_SEMANTICS = 'CHAR';).  This is available starting in 9i.  Then, subsequent column declarations such as VARCHAR2(10) really mean 10 characters and not 10 bytes.  You can also be explicit on the declaration by saying VARCHAR2(10 CHAR), but that is not as flexible obviously (because it will always mean CHAR even if the database is currently set to default to BYTE).

When doing a DESC command, the keyword "BYTE" or "CHAR" only shows up in the parentheses (as in your dump below) if the table is declared in the mode opposite what your current SQL*Plus session is set to.

Writing East Asian characters languages (such as Japanese)

In order for a VB6 or .Net application to write Japanese characters to an Oracle UTF8 database, the East Asian language files must be installed on the machine running the application that writes to the database.  This is done in XP and Windows 2003 from Start->Control Panel->Regional and Language Options->Language tab.  If they are not installed, no error will occur but the Japanese characters will be written as question marks.  It is unclear why this is the case however it has been confirmed with a UTF8 Oracle database, VB6 and .Net applications, and Japanese characters.  It is assumed that this applies to all East Asian languages as well as AL32UTF8 databases.

Snippet from an Oracle document describing these character sets' support and origin:

~~~~~~~~~~~~~~~ begin snip ~~~~~~~~~~~~~~~

UTF8

UTF8 was the UTF-8 encoded character set in Oracle8 and 8i. It followed the Unicode 2.1 standard between Oracle 8.0 and 8.1.6, and was upgraded to Unicode version 3.0 for versions 8.1.7, 9i and 10g. To maintain compatibility with existing installations this character set will remain at Unicode 3.0 in future Oracle releases. Although specific supplementary characters were not assigned to Unicode until version 3.1, the allocation for these characters were already defined in 3.0. So if supplementary characters are inserted in a UTF8 database, it will not corrupt the actual data inside the database. They will be treated as 2 separate undefined characters, occupying 6 bytes in storage. We recommend that customers switch to AL32UTF8 for full supplementary character support.

AL32UTF8

This is the UTF-8 encoded character set introduced in Oracle9i. AL32UTF8 is the database character set that supports the latest version (4.01 in Oracle 10.2) of the Unicode standard. It also provides support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes. AL32UTF8 was introduced because when UTF8 was designed (in the times of Oracle8) there was no concept of supplementary characters, therefore UTF8 has a maximum of 3 bytes per character. Changing the design of UTF8 would break backward compatibility, so a new character set was introduced. The introduction of surrogate pairs should mean that no significant architecture changes are needed in future versions of the Unicode standard, so currently the plan is to keep enhancing AL32UTF8 as necessary to support future version of the Unicode standard. For example in Oracle 10.1 this character set implemented the Unicode 3.2 standard, in Oracle 10.2 that has been updated to support the Unicode 4.01 standard.

~~~~~~~~~~~~~~~ end snip ~~~~~~~~~~~~~~~

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