TNS less connection in Oracle.

I have tested this connection method in Oracle 8i, 9i, 10g and in 11g and it works fine.

D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 16:59:44 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=ntwssrv)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
Connected.
SQL>

If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file.

For 10g and 11g you can use following method as well. This method does not work for 8i or 9i databases.

==========
D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 17:09:47 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger@ntwssrv:1521/aries
Connected.

===========
[oracle@oracle11gr1 oracle]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 24 17:27:40 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn scott/tiger@oracle11gr1:1521/orcl
Connected.
SQL>

Resolving ORA-12705 on Client

This tip resolves Oracle client error: “ORA-12705: Cannot access NLS data files or invalid environment specified“. According to Oracle documentation, “ORA-12705 can happen under the following conditions:

1. ORA-12705 with incorrect NLS_LANG parameter
2. ORA-12705 with incorrectly specified ORA_NLSx
3. ORA-12705 from incorrect Oracle install or configuration
4. ORA-12705 when using Special Character sets
5. ORA-12705 when connecting with SQL*Net
6. ORA-12705 during migrations
7. ORA-12705 when connecting with SQL*Plus
8. ORA-12705 when connecting with language pre-compilers
9. ORA-12705 during Export/Import

Try doing the following to fix this error on the client side. On the client Windows machine (this tip is only for Windows), The NLS_LANG must be unset in the Windows registry (re-naming works the best because you know what you changed). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it. Then try SQLPlus again.

Limit user sessions in Oracle

How can I limit the number of times a single user can connect to the database at any one time ?

We can do this by limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration:

D:\>sqlplus sys/sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 21 10:46:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE

SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 1;

Profile created.

SQL> create user sree identified by sree123
default tablespace sbdata profile sesslimit;

User created.

SQL> grant create session to sree;

Grant succeeded.

If you want to add this profile to existing user then:

SQL> alter user sree profile sesslimit;

SQL> connect sree/sree123
Connected.

-- I'll start second session in another terminal.

SQL> connect sree/sree123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

==========

SQL> SELECT DISTINCT resource_name, limit
FROM dba_profiles
ORDER BY resource_name;

SQL> drop profile sesslimit cascade;

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production With the Partitioning, OLAP and Data Mining options

IMPORTANT LINKS

Oracle By Example (OBE)

The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment.
Direct Link to all Articles

Installing Oracle Database 11g on Linux
Direct Link to Article

Installing Oracle Database 11g on Windows
Direct Link to Article

Exploring Your Database with Enterprise Manager Database Control
Direct Link to Article

Using Database Replay to Perform Real-World Testing
Direct Link to Article

Creating a Physical Standby Database
Direct Link to Article

Oracle by Example Series: Oracle Database 11g
Direct Link to Articles

Oracle by Example Series: Oracle Database 10g
Direct Link to Articles

Automating Installation of Oracle Database 10g and Red Hat Enterprise Linux
Direct Link to Article

RAC LINKS

Biggest challenge in learning / understanding RAC is lack of hands on experience because of expensive hardware, network cards, interconnect and cluster file system. The following links cover step by step Oracle RAC installation and configuration
on single laptop/desktop using VMWare Server without investing anything extra on hardware (using two virtual machines, virtual network cards and virtual shared disk for Oracle RAC database shared files system ).

Download VMware Server (for Windows and Linux systems)
Download VMWARE

Installing Oracle Database 10g on Windows by Using Real Application Clusters (RAC) and Automated Storage Management (ASM)
Direct Link to Article

Steps for configuring/building Oracle RAC Database on laptop/desktop machine (without investing any thing on hardware).
Direct Link to Article

Oracle 10g RAC installation using Unbreakable Linux
Direct Link to Article

Installing Oracle RAC 10g Release 1 on Linux x86
Direct Link to Article

Installation of Oracle 10g release 2 (10.2.0.1) RAC on Linux (CentOS 4) using VMware Server with no additional shared disk devices.
Direct Link to Article

Installing Oracle RAC 10g Release 2 on Linux x86
Direct Link to Article

Oracle 10g RAC On Windows 2003 Using VMware Server
Direct Link to Article

Install Oracle RAC 10g on Oracle Enterprise Linux Using VMware Server
Direct Link to Article

The Oracle-on-Linux Installation Menu
Direct Link to Article

Installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3
Direct Link to Article

Convert a Single Instance Database to Oracle RAC 10g on RHEL3
Direct Link to Article

Code to shrink Your Tablespaces

Use it at your own risk.


SET verify OFF
COLUMN file_name format a50 WORD_WRAPPED
COLUMN smallest format 999,990 heading "Smallest|Size|Poss."
COLUMN currsize format 999,990 heading "Current|Size"
COLUMN savings format 999,990 heading "Poss.|Savings"
BREAK on REPORT
COMPUTE sum of savings on REPORT
COLUMN value new_val BLKSIZE
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size';

SELECT FILE_NAME,
CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SMALLEST,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) CURRSIZE,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) - CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SAVINGS
FROM DBA_DATA_FILES A,
(SELECT FILE_ID,
MAX(BLOCK_ID + BLOCKS - 1) HWM
FROM DBA_EXTENTS
GROUP BY FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID (+);

SELECT 'alter database datafile '''
||File_Name
||''' resize '
||Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024)
||'m;' cmd
FROM dba_Data_Files a,
(SELECT File_Id,
MAX(Block_Id + Blocks - 1) hwm
FROM dba_Extents
GROUP BY File_Id) b
WHERE a.File_Id = b.File_Id (+)
AND Ceil(Blocks *&& blkSize / 1024 / 1024) - Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024) > 0;

Cool Scripts for daily DBA activities

I will be adding scripts to this post when ever I come across a good one.

Note: Test the scripts before using on a Production database.
Finely Format the SQL statements before use.

Calculate the Database Size

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;
=========================================================
Heavy CPU SQL

This will generate the top SQL statements that produce heavy CPU usage

set termout on
set feedback on
set pagesize 132

#spool cpusql.lis

SELECT username,address, hash_value,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",sql_text
FROM v$sqlarea,dba_users
WHERE buffer_gets > 50000
and executions > 0
and v$sqlarea.parsing_user_id = dba_users.user_id
order by 4 desc;

#spool off;
=========================================================
Calculate the Table Size

SELECT Segment_Name Table_Name,
SUM(Bytes) / (1024 * 1024) Table_Size_Meg
FROM dba_Extents
WHERE Owner = 'SCOTT'
AND Segment_Name = 'DEPT'
AND Segment_Type = 'TABLE'
GROUP BY Segment_Name
/

=============================================================================
Determine Tablespace Usage

SELECT a.TableSpace_Name,
Round(a.Bytes_Alloc / 1024 / 1024,2) Megs_Alloc,
Round(Nvl(b.Bytes_Free,0) / 1024 / 1024,2) Megs_Free,
Round((a.Bytes_Alloc - Nvl(b.Bytes_Free,0)) / 1024 / 1024,
2) Megs_Used,
Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Free,
100 - Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Used,
Round(MaxBytes / 1048576,2) MAX
FROM (SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Alloc,
SUM(DECODE(f.AutoexTensible,'YES',f.MaxBytes,
'NO',f.Bytes)) MaxBytes
FROM dba_Data_Files f
GROUP BY TableSpace_Name) a,
(SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Free
FROM dba_Free_Space f
GROUP BY TableSpace_Name) b
WHERE a.TableSpace_Name = b.TableSpace_Name (+)
UNION ALL
SELECT h.TableSpace_Name,
Round(SUM(h.Bytes_Free + h.Bytes_Used) / 1048576,
2) Megs_Alloc,
Round(SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / 1048576,
2) Megs_Free,
Round(SUM(Nvl(p.Bytes_Used,0)) / 1048576,2) Megs_Used,
Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Free,
100 - Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Used,
Round(SUM(f.MaxBytes) / 1048576,2) MAX
FROM sys.v_$temp_Space_Header h,
sys.v_$temp_Extent_Pool p,
dba_temp_Files f
WHERE p.File_Id (+) = h.File_Id
AND p.TableSpace_Name (+) = h.TableSpace_Name
AND f.File_Id = h.File_Id
AND f.TableSpace_Name = h.TableSpace_Name
GROUP BY h.TableSpace_Name
ORDER BY 1
-------------------------------------------------------------------------
SELECT TableSpace_Name "Tablespace",
COUNT(Bytes) "Pieces",
MIN(Bytes) "Min",
Round(Avg(Bytes)) "Average",
MAX(Bytes) "Max",
SUM(Bytes) "Total"
FROM sys.dba_Free_Space
GROUP BY TableSpace_Name

==========================================================================
Track your import process:

SELECT Substr(sql_Text,Instr(sql_Text,'INTO "'),30) Table_Name,
Rows_Processed,
Round((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) Minutes,
Trunc(Rows_Processed / ((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) Rows_Per_Minute
FROM sys.v_$sqlArea
WHERE sql_Text LIKE 'INSERT %INTO "%'
AND Command_Type = 2
AND Open_Versions > 0;
===============================================================================
Details of parameters for SPfile modifications

SELECT NAME,
Isses_modIfiAble,
Issys_modIfiAble,
IsInstance_modIfiAble
FROM v$Parameter
ORDER BY NAME;
==============================================================================
Query to find the difference between two dates omitting weekends and holidays.


SELECT (To_date('31/01/2008','dd/mm/rrrr') - To_date('01/01/2008','dd/mm/rrrr') + 1) - (SELECT COUNT(Days)
FROM (SELECT To_char(To_date('01/01/2008','dd/mm/rrrr') + LEVEL,'D') Days
FROM Dual CONNECT BY LEVEL <= 31) WHERE Days IN ('7','1')) DAY
FROM Dual;
===============================================================================

Deleting Duplicate Records

You may have come across numerous situations where duplicate records needs to be deleted from some table quickly. More often than not this is due to an application or data transformation issue and the number of duplicate rows are generally small related to the overall number of rows in the table. DBAs struggle with this task and spend way too much time and so coming up with elaborate scripts.

Assuming the table looks like this:

ACCOUNT
ACCOUNT_ID
STATUS_CODE
FIRST_NAME
LAST_NAME
STREET_ADDRESS

In this example, there is no primary key or unique constraint preventing duplicates from being inserted. The "logical primary key" is ACCOUNT_ID and STATUS_CODE.

First we might want to view the duplicate records to assist with troubleshooting and this can be done with this query:

SELECT Account_Id,
Status_Code,
COUNT(* ) OccurAnces
FROM ACCOUNT
GROUP BY Account_Id,Status_Code
HAVING COUNT(* ) > 1;

And to delete them we rely on Oracle’s rowed which is a unique "key" that exists on every table row:

DELETE
FROM ACCOUNT A
WHERE
(ACCOUNT_ID,STATUS_CODE) IN (
SELECT
ACCOUNT_ID,
STATUS_CODE,
FROM ACCOUNT
GROUP BY
ACCOUNT_ID,
STATUS_CODE
HAVING
COUNT(*) > 1) AND
ROWID != (
SELECT
MIN(ROWID)
FROM ACCOUNT
WHERE
ACCOUNT_ID = A.ACCOUNT_ID AND
STATUS_CODE = A.STATUS_CODE);

Alternatively this more simplified query can be used, but in some cases it might not execute as fast as the above query because of having to do more random I/O:

DELETE FROM ACCOUNT a
WHERE RowId != (SELECT MIN(RowId)
FROM ACCOUNT
WHERE Account_Id = a.Account_Id
AND Status_Code = a.Status_Code);

Please make sure that you thoroughly understand your duplicate record problem and what makes a record unique before deleting duplicate records in a table. Make sure you test the above SQL statements in a test environment with a representative test case before attempting this in a production environment

DBVERIFY

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.


There are two command-line interfaces to DBVERIFY


1.Using DBVERIFY to Validate Disk Blocks of a Single Datafile

D:/>dbv FILE=D:\oracle\product\10.1.0\oradata\ORCL\users.dbf FEEDBACK=100

------------------------------------------------------------

2.Using DBVERIFY to Validate a Segment
If we want to check segment level you can use SEGMENT_ID

SEGMENT_ID = Segment ID (tsn.relfile.block)

we can get all three values from below query.

SQL> Grant sysdba to scott;

SQL>SELECT t.ts#,
s.Header_File,
s.Header_Block
FROM v$TableSpace t,
dba_Segments s
WHERE s.Owner = 'SCOTT'
AND s.Segment_Name = 'EMP'
AND t.NAME = s.TableSpace_Name;


TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
4 4 27

SQL> host dbv userid=scott/tiger segment_id=4.4.27