Oracle RDBMS: Easily determine what parameters are set to using sqlplus without Oracle Enterprise Manager (OEM)

This is probably unnecessaryOracle for many of the Oracle RDBMS gray beards out there 😉 but if you’re asked to provide the number of client connections. Just fire up sqlplus or whatever tool of choice:

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     784
shared_server_sessions               integer
SQL>  show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     10
log_archive_max_processes            integer     4
processes                            integer     500

Not sure what the difference or the relationship is between processes and sessions are? It was asked in DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS over at Ask Tom.

Ask Tom

Share Button

HOWTO: List users that perform os authentication to log into the Oracle database instance (external users)

Oracle will Oraclerequire you to configure the Oracle RDBMS to use operating system authentication but if you inherit an Oracle instance, you will want to disable os authentication when possible. When the OS_AUTHENT_PREFIX is set, any os user that is created with “IDENTIFIED EXTERNALLY” will have the prefix. For example, in the below example, the value is “ops$”. The os user johnnybgood will have an internal Oracle user id of “ops$johnnybgood”. It’s a handy way to quickly identify such users but it isn’t full proof.

show parameter OS_AUTHENT_PREFIX
lang-NAME                            TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

To determine exactly which users are set up for os authentication run the following query:

set pagesize 5000
set linesize 999
set trimspool on

select gn.GLOBAL_NAME as "Instance", username, authentication_type
from dba_users du, global_name gn
where authentication_type = 'EXTERNAL'

Now, there are valid reasons to use os authentication but I would push LDAP long before os authentication.

From the Oracle Security Admin Guide:

Advantages of External Authentication

Following are the advantages of external authentication:

More choices of authentication mechanism are available, such as smart cards, fingerprints, Kerberos, or the operating system.

Many network authentication services, such as Kerberos support single sign-on, enabling users to have fewer passwords to remember.

If you are already using some external mechanism for authentication, such as one of those listed earlier, then there may be less administrative overhead to use that mechanism with the database as well.

Share Button

FW: Hardening the Oracle 11g Database – Initial Steps (Kevin Sheehan)

Kevin Sheehan wrote how to harden the Oracle 11g RDBMS. I highly recommend it:

TheOracle following is a basic set of hardening guidelines for an Oracle 11g database along with some scripts you may find useful. This list is by no means complete. It does not cover file permissions, authentication controls and user profiles, encryption, grants or auditing but it is a good place to start.

Or perhaps takes Slavic’s advice from his comment on this post and start with Oracle’s Checklist for Database Security. It gives a broader, though perhaps less detailed view, and covers some of the topics I have left out (for now). Also check out Slavik’s Musings on Database Security Blog.

Read more on Kevin’s blog.

Share Button

HowTo: List locked, expired and to expire dates for Oracle logins query for sqlplus/scripting


set pagesize 5000
set linesize 999
set trimspool on

column "Expire Date" format a20
column "Locked Date" format a20

    gn.GLOBAL_NAME as "Instance",
    du.expiry_date as "Expire Date",
    du.lock_date as "Locked Date",
    dp.limit as "Profile Password Expiration"
from dba_users du, global_name gn, dba_profiles dp
where (du.account_status IN ( 'OPEN', 'LOCKED' )  OR du.account_status like '%EXPIRE%')
  and du.profile = dp.profile
  and dp.resource_name = 'PASSWORD_LIFE_TIME'
order by du.account_status, du.expiry_date, du.username

Updated to show which login profile is being by a user and what the password expiration interval is per the login profile.

Share Button

HowTo: Comparing two tables and returning the # of rows that match FAST

The most intuitive way to write a comparison of two tables and spit out the # of rows that are the same involves a left join:

select 'Investment' as TableName, count(*) as RowCount
from Investment_A a, Investment_B b
  a.col1 = b.col1
  AND a.col2 = b.col2
  AND a.col3 = b.col3
  AND a.col4 = b.col4

This returns the correct answer but is very slow. Is there a better way? Of course!

select 'Investment' as TableName, count(*) as RowCount
   select 1 as num
   FROM (
      select * from Investment_A
      select * from Investment_B
   ) tmp
   GROUP BY col1, col2, col3, col4
   HAVING COUNT(*) > 1
) tmp2

By pushing the comparison off into the GROUP BY, we leverage the DBMS engine far more efficiently. There are two drawbacks:

  1. Readability of the SQL code
  2. Far more temporary storage is used for the GROUP BY. There is a real risk of running out of temporary storage if the tables are large.
Share Button

Wanted: Entry level or Mid-level Oracle DBA (Chicago, IL)

Position Responsibilities

• Manage database configurations, schemas, and space • Manage database and system alerts. • Support new application code releases / builds in various production and non-production environments • Provide performance tuning, problem research/resolution, code reviews and deployments, SQL support, and data movement • Manage database changes for releases and new projects • Provide SQL and application tuning assistance • Build Backups / Recovery and High Availability • Manage disaster recovery support and testing • Monitor database health and alerts • Provide production on-call support on rotational basis • Manage database and product deployments and repeated database maintenance and monitoring tasks • Troubleshooting database issues • Open & follow-up on change request tickets for internal users • Open and follow-up on service requests with Oracle vendor support

Position Requirements

• Degree or equivalent experience in either Oracle or Sybase. • BS in Computer Science or equivalent specializing in Oracle or Sybase • Good understanding of relational methodology • Good understanding of SQL Plus or equivalent • Good communication skills and the ability to work as a team member • Good analytical and problem solving skills. • Self starter with willingness to do independent research and trouble shooting. • Willingness to learn new database skills in a fast paced environment • Education or Experience with Unix shell scripts helpful • Good understanding of relational methodology • Must be comfortable in both self-managed and team driven environment • Experience with Microsoft Office Tools helpful

Interested? (say I referred you on the application form)

Share Button

How to change the default NLS_DATE_FORMAT (Date format) in Oracle 10g/11g

It is really really easy to change the default NLS_DATE_FORMAT setting but to be honest, you should set it at a session level IMHO.

We basically just need to run “ALTER SYSTEM SET NLS_DATE_FORMAT=’YYYY-MM-DD’ SCOPE=SPFILE” as a user with sysdba privileges. If you started the Oracle instance without a spfile (it should be located at $ORACLE_HOME/dbs/spfile[instance name].ora), you will receive the ORA-32001 error.

ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Just create a new spfile, restart:

select INSTANCE_NAME from v$instance;
create spfile='/oracle/10g/dbs/spfileUAT2.ora' from pfile='/oracle/10g/dbs/initUAT2.ora';


select INSTANCE_NAME from v$instance;
System altered.


SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

That’s it. 🙂

Share Button

How to: How to copy an Oracle tablespace across platforms – quick and dirty ;-)

On the source machine:
Set the tablespace(s) into read only mode

Alter tablespace <tablespace> read only

Export the tablespace meta data using export

expdp system/<password> DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES = <list of tablespaces separated by commas> TRANSPORT_FULL_CHECK=Y

If the dumpdir is not set up, you will get an ‘invalid’ directory error:

CREATE DIRECTORY dmpdir as ‘/somedir’;
GRANT read,write on DIRECTORY to system;

Export the data converting on the fly:
Determine the platform name for the destination machine:

rman TARGET /
RMAN> CONVERT TABLESPACE </list><list of tablespaces separated by commas> to PLATFORM ‘<platform name from previous step>’  FORMAT=’/somedir/%U’;
Share Button

How to disable the “uninstall password” to uninstall Symantec Antivirus

I was trying to install Oracle 11g client on to a WinXP box but Symantec Antivirus wouldn’t let me because it thought it was infected with over 800 viruses.  Same file was marked clean on another box.  So, I picked several of the viruses it said was infected with and looked for any evidence of them on the WinXP box.  No dice.  This led me to believe that Symantec Antivirus itself was somehow mucked up.

When I tried to uninstall Symantec Antivirus, it asked for a password.  When I put in the correct password, it refused it.  So how to uninstall it with a messed up password?  Easy..

Open up regedit (as an administrator) and change HKEY_LOCAL_MACHINE\SOFTWARE\INTEL\LANDesk\VirusProtect6\CurrentVersion\AdministratorOnly\Security\UseVPUninstallPassword to “0” (zero).

I was able to uninstall and reinstall Symantec Antivirus.

Share Button