Oracle

Logon Trigger Error ORA-00604

ekran

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
(
'your  username',
'your os user',
'your ip address',
'machine name',
'Database name',
'aktif',
SYSDATE);

Disabling or Enabling a Single Trigger:

ALTER TABLE evaluations DISABLE ALL TRIGGERS;
ALTER TABLE evaluations ENABLE ALL TRIGGERS;

if you have a user updating dbmain.db_login_params:

UPDATE dbmain.db_login_params
SET column1 = value1, column2 = value2, ...
WHERE condition;

Have a good day .

 

Standard
Oracle

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 and want to find one specific users code, but you will need to remove a.username from the above script:

AND a.username = '';

 

 

Standard
Oracle

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

  1. 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
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

20 rows selected.

SQL>

2. show oracle sga size :

SQL> show sga

Total System Global Area 8551575552 bytes
Fixed Size 2238616 bytes
Variable Size 4999612264 bytes
Database Buffers 3539992576 bytes
Redo Buffers 9732096 bytes
SQL>

 

 

Standard
Oracle

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.

 

Standard
Oracle

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 ON DIRECTORY EXPIMP TO {user};

3. expdp user/pass directory=EXPIMP dumpfile={filename} logfile={logfile} parallel={threads}

4. impdp user/pass directory=EXPIMP dumpfile={filename} logfile={filename} parallel={threads}

— For importing to new schema use remap_schema
impdp user/pass remap_schema={orig schema}:{new schema} directory=EXPIMP dumpfile={filename} logfile={filename} parallel={threads}
— For importing to new tablespace use remap_tablespace
impdp user/pass remap_schema={orig schema}:{new schema} remap_tablespace={orig tbs}:{new tbs} directory=EXPIMP dumpfile={filename} logfile={filename} parallel={threads}

— We can even specify multiple TBS (tablespace )  to remap
impdp user/pass remap_schema={orig schema}:{new schema} remap_tablespace={orig tbs}:{new tbs},{orig tbs 1}:{new tbs} directory=EXPIMP dumpfile={filename} logfile={filename} parallel={threads}

Standard
Oracle

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;

NAME
———
DBNAME

Standard