Enable SQLPROMPT to show current user/db in SQL Plus

To answer to your question of:

“How do you know what user you are logged on as and on what database?”

In SQL Plus if you type show user this will tell you what user you are logged on as

And if you type:

SELECT NAME FROM V$DATABASE;

This will return the database your currently logged onto, however this will depend on if you have permission to the v$ views which you wouldn’t have.

Alternatively you can:

SELECT PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME=’GLOBAL_DB_NAME’;

However this is not reliable as the GLOBAL NAME can be different to DB_NAME.

I would best advise you add the following line to the end of your glogin.sql file located in usually C:\oracle\product\10.2.0\client_1\sqlplus\admin (depending on where your oracle client is installed):

set sqlprompt "_user’@’_connect_identifier SQL>"

This will tell you on your SQL Prompt, what user you are logged in as and on what database.

ORA-01882: timezone region %s not found ERROR in Toad/sqlplus

If you are facing the following issue on your workstation, then you need to update your client timezone files.

clip_image002

I found the issue on IHORIZD connecting via Toad and running ‘Select * from dba_scheduler_jobs’.

The following metalink article resolves this issue – 417893.1 (How To Apply The V4 DST Patches To Windows Clients or Servers )

Background information on the error given in this article ID 414590.1 (Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade)

Tracing a lost transaction which is rolling back

If a long running transaction (indeed any transaction) is cancelled or fails, then Oracle needs to cleanup the uncommitted work that was done by this transaction so that other transactions can progress. This cleanup involves rolling back the uncommitted work.

You can investigate/evaluate how long SMON will run to recover transaction using V$FAST_START_TRANSACTIONS view which contains information about the progress of the transactions that Oracle is recovering.

Useful columns from v$fast_start_transactions are:
Column Datatype Description
STATE
VARCHAR2(16)
The state of the transaction may be TO BE RECOVERED, RECOVERED, or RECOVERING

UNDOBLOCKSDONE
NUMBER
The number of undo blocks completed on this transaction

UNDOBLOCKSTOTAL
NUMBER
The total number of undo blocks that need recovery

Also, to check if the transaction that needs rollback is still present.

select * from x$ktuxe where ktuxecfl = ‘DEAD’;

To determine the number of undo blocks required for rollback you can use the select statement below:

select distinct ktuxesiz from x$ktuxe;

To check whether there is a large number of temporary extents that migh being cleaned up run the following query a few times:

SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE=’TEMPORARY’;

FYI: Here is a simplified description of how AUM (Automatic Undo Management) works, and how the undo extents (in the UNDO tablespace) are being allocated to active transactions:
– When a new active transaction comes in, Oracle allocate new extents if there is free space available in the undo tablespace.
– The undo tablespace does *not* shrink nor does it return space back to the OS (no other tablespace does either). It internally changes the status of its extents something like from "used" to "unexpired" to "expired" to "free" – over time, depending on the status of the transaction (commited/uncommited) and the undo_retention time (expired or not).
– If autoextend is turned on, Oracle allocates new extents, thus the datafile grows as a result of that.
– Even after the transactions are completed those undo extents will remain in EXPIRED status and are *not* automatically deallocated as they will be reused when necessary. Once allocated you cannot deallocate them. Also, one cannot shrink the undo datafiles as for normal tablespaces. As the blocks are being used by undo extents (expired or not), oracle will not allow you to resize.

Installing UTL_MAIL on 11g

UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement and the security exposure this involves. In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions.

This package is now an invoker’s rights package and the invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect.

Install UTL_MAIL and define the SMTP_OUT_SERVER.

  • To install UTL_MAIL:

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
SQL> ALTER SYSTEM SET smtp_out_server = 'localhost' SCOPE=spfile;
  • You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file. However, if SMTP_OUT_SERVER is not defined, this invokes a default ofDB_DOMAIN which is guaranteed to be defined to perform appropriately.

Cannot modify table column in SQL Server 2008 using Table Designer

There is a new option added in SQL Server 2008 for modifying tables while using the Table Designed in Management Studio which will prevent saving any changes to a table that needs a drop/re-create of the table. The error that you would see in the Management Studio is:

“You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.”

If you want to modify a table property through the UI which requires a table recreation, then you need to change the following options in Management Studio:

Tools ->Options-> Designers -> Table and Database Designers -> uncheck the option “Prevent saving changes that require table re-creation”