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

THIS WAY HAS WORKED WELL FOR ME:

The easiest way I have found to immediatley shrink the log file is to detach the database, rename the .LDF file then reattach the .MDF file. In SQL Server 2005 you'll need to remove the missing .LDF file from the list before attaching, and one will be created for you.

I DID NOT GET THIS ROUTINE BELOW TO WORK WITH SQL SERVER 2005:

From the April 2001 edition of SQL Server Magazine:

Shrinking Log Files Immediately

You can use the DBCC Shrinkfile statement in SQL Server 7.0 to shrink the size of a specified data file or log file for a related database. But SQL Server professionals often ask me why the DBCC Shrinkfile command doesn't shrink the size of their transaction logs immediately and how to make their transaction logs smaller.

SQL Server 7.0 doesn't immediately shrink log files when you issue the DBCC Shrinkfile command. The DBCC Shrinkfile operation occurs only at checkpoints or transaction log backups. SQL Server segments each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. SQL Server marks the VLFs as truncateable either after SQL Server has backed them up or at checkpoints. At any given time, you might have VLFs with free or reusable space at the beginning, middle, and end of the log. Only when the VLFs that SQL Server marked as truncateable are at the end of the log file can the DBCC Shrinkfile operation remove the VLFs and shrink the log file. Because SQL Server can shrink a log file only to a virtual-log-file boundary, you can't shrink a log file to a size smaller than the size of a virtual log file—even if you aren't using the log file. (For more information about virtual log files, see SQL Server Books Online—BOL.)

Listing 1 shows a script that shrinks the log file immediately after you stop running the script. The script first marks a shrinkpoint, which tells the DBCC Shrinkfile (or the DBCC Shrinkdatabase) command where to shrink the log file to. The script then forces truncateable VLFs to the end of the log file and issues a BACKUP command to truncate the log. In my experience, you need to run the script for 3 to 4 minutes before stopping it manually.

—Simon Su  yqsu@microsoft.com

Listing 1: Script to Shrink a Log File

/* Run "SELECT fileid, name,filename FROM <db_name>..sysfiles" to get
the fileid you want to shrink. */

USE <db_name>
GO
DBCC shrinkfile(<fileid>,notruncate)
DBCC shrinkfile(<fileid>,truncateonly)
CREATE TABLE t1 (char1 char(4000))
GO
DECLARE @i int
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 values ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE t1
backup log <db_name> with truncate_only
END
GO

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

Microsft SQL Server 2005 does not include the Service Manager system tray application, but a 3rd party application is to the rescue.

http://www.sqldbatips.com/showarticle.asp?ID=46

This works great for a developer's workstation running SQL Server 2005 where you may not want the database running all the time.

DECLARE @TruncateStatement nvarchar(2000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'TRUNCATE TABLE ' +
   QUOTENAME(TABLE_SCHEMA) +
   N'.' + QUOTENAME(TABLE_NAME)
FROM
   INFORMATION_SCHEMA.TABLES
WHERE
   TABLE_TYPE = 'BASE TABLE' AND
   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
   N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
   EXEC(@TruncateStatement)
END
-- Clean up work
CLOSE TruncateStatements
DEALLOCATE TruncateStatements

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.

I can’t tell you how many times I’ve done an import and ended up with the wrong database owner.  Use this following script to change the owner on all tables in a database.

In the sql below, set the @oldowner and @newowner variables appropriately.

DECLARE @oldowner sysname, @newowner sysname, @sql varchar(1000)

SELECT
  @oldowner = 'OLD_OWNER',
  @newowner = 'dbo',
  @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @oldowner + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @newowner + ''''

EXECUTE sp_MSforeachtable @sql

(I found this somewhere on the internet some time ago but have lost it's reference.)

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