Logon Trigger Error ORA-00604

User ID:  the user ID that was used to perform the sign-on operation Session ID:  the Oracle control session ID for the user Host:  the host name of the computer Logon date:  an Oracle date data type corresponding to the user login time. add a new user in the db_login_params INSERT INTO dbmain.db_login_params (username, osuser,IP_ADDRESS,machine,DB,TRIGGER_CONTROL,insert_date) VALUES … Continue reading Logon Trigger Error ORA-00604

Check Users Connected to Oracle Database

Hello, To find how many users are on the database issue the following. SQL> SELECT username FROM v$session; Show what users are running SQL> SELECT a.sid , a.serial# , a.username , b.sql_text FROM v$session a , v$sqlarea b WHERE a.sql_address=b.address; You can add the following to the script if you have a lot of users … Continue reading Check Users Connected to Oracle Database

How to find the Character seti and SGA size of the Oracle database.

show oracle characters : SQL> SELECT * FROM NLS_DATABASE_PARAMETERS 2 ; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P9 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR … Continue reading How to find the Character seti and SGA size of the Oracle database.

Sqlplus run in background with nohup

Sometimes you have to run a query that takes FOREVER, and you want to go home with your laptop. If you have access to a server with SQL*Plus, you can run the query there in the background. Here’s how: nohup sqlplus /as sysba @file.sql & show output tail -123f nohup.out have a good day.  

Oracle Database Import

Using power of Oracle Data Pump If you haven’t used oracle Data Pump and still using old IMP / EXP, it’s time for change. It’s very simple to use and at least 10 times faster (my experience) than IMP/EXP. Simple steps to configure 1. CREATE OR REPLACE DIRECTORY EXPIMP AS ‘{path}’; 2. GRANT READ, WRITE … Continue reading Oracle Database Import

Show Oracle DB Name and SID name

Hello To find the current DB_NAME and ORACLE_SID: Query the views v$database and v$thread. V$DATABASE gives DB_NAME V$THREAD gives ORACLE_SID If ORACLE_SID = DB_SID and db_name = DBNAME: To find the current value of ORACLE_SID: SVRMGR> select instance from v$thread; INSTANCE ---------------- DB_SID To find the current value of DB_NAME: SVRMGR> select name from v$database; … Continue reading Show Oracle DB Name and SID name