Learn Database Concepts

I was trying to Install OID (Oracle Identity Manager) and I got this error : Problem:         at oracle.as.install.engine.modules.configuration.standard.StandardConfigActionManager.start(StandardConfigActionManager.java:186)         at oracle.as.install.engine.modules.configuration.boot.ConfigurationExtension.kickstart(ConfigurationExtension.java:81)         at oracle.as.install.engine.modules.configuration.ConfigurationModule.run(ConfigurationModule.java:86)         at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.SecurityException: Can not initialize cryptographic mechanism         at javax.crypto.JceSecurity.<clinit>(JceSecurity.java:88)         ... 31 more Caused by: java.lang.SecurityException: The jurisdiction policy files are not signed by a trusted sig...

 Is it safe to move/recreate alertlog while the database is up and running?? It is totally safe to "mv" or rename it while we are running. Since chopping part of it out would be lengthly process, there is a good chance we would write to it while you are editing it so I would not advise trying to "chop" part off -- just mv the whole thing and we'll start anew in another file. If you want to keep the last N lines "online", after you mv the file, tail the last 100 lines to "alert_also.log" or something before you archive off the rest. [oracle@Linux03 trace]$ ls -ll alert_* -rw-r-----. 1 oracle oracle    488012 Nov 14 10:23 alert_orcl.log I will rename the existing alertlog file to something   [oracle@Linux03 trace]$ mv alert_orcl.log alert_orcl_Pre_14Nov2016.log [oracle@Linux03 trace]$ ls -ll alert_* -rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log [oracle@Linux03 trace]$ ls -ll alert_* Now lets create some activity ...

[oracle@oracle1 dpump]$ expdp atest/password directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log Export: Release 11.2.0.1.0 - Production on Wed Feb 11 10:58:23 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ATEST"."SYS_EXPORT_TABLE_01":  atest/******** directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB . . exported "ATEST"."TEST_TAB1"                         5.937 KB      11 rows Master table "ATES...

Note : Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed.   There could be a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows us to kill these Oracle sessions.  The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number. First you have to identify the session to be killed with alter system kill session. select SID,SERIAL#,STATUS,SCHEMANAME,PROGRAM from v$session; The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued. SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'; Sometimes Oracle is not able to kill the session immediately with the alter system kill session  command alone. Upon issuing the alter system kill session  command, the session will b...

If you have configured IDLE_TIME inr your user profile. IDLE_TIME  Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit Lets say a session has been idle for 10 Minutes. Session will continue to show as idle even after the idle_time for that user,as specified in that user's profile, has expired. When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. After this, the session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session. Reference this oracle Document for more information. Once the oracle session is changed to SNIPED status, we can kill that se...

Using special characters in ORACLE DB passwords   Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login. To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example : I created a user with password using special characters. SQL> alter user C##atest identified by "atest113.."; User C##ATEST altered. [oracle@Linux03 admin]$ sqlplus C##atest/atest113.. SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Solution to this is to use single quote: [oracle@Linux03 admin]$ sqlplus ...

  configure password verify function in oracle database We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba. [oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/ [oracle@Linux03 admin]$ ls utlpwdmg.sql [oracle@Linux03 admin]$ sqlplus / as sysdba SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql Function created. Grant succeeded. Function created. Grant succeeded. Function created. Grant succeeded. Profile altered. Note : You can query current default profile settings using as below select * from DBA_PROFILES where profile='DEFAULT'; Starting from 12c this file (utlpw...

  configure password verify function in oracle database We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba.   [oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/ [oracle@Linux03 admin]$ ls utlpwdmg.sql [oracle@Linux03 admin]$ sqlplus / as sysdba SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql Function created. Grant succeeded. Function created. Grant succeeded. Function created. Grant succeeded. Profile altered. Note : You can query current default profile settings using as below select * from DBA_PROFILES where profile='DEFAULT'; Starting from 12c this file (utl...

ENABLE AUDITING IN ORACLE DATABASE SERVER SETUP FOR DB AUDITING Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command. SQL> SHOW PARAMETER AUDIT NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN                                ...

ENABLE AUDITING IN ORACLE DATABASE SERVER SETUP FOR DB AUDITING Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command. SQL> SHOW PARAMETER AUDIT NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN                                ...

select grants on tables to users or roles SELECT   OWNER,   GRANTEE,   GRANTOR,   TABLE_NAME,   PRIVILEGE FROM   DBA_TAB_PRIVS WHERE   table_name   = 'TABLENAME' AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE') AND GRANTEE   IN ('ROLE1','ROLE2') ORDER BY   1,2; select grants on table to a user thru role or direct priv select Grantee,'Granted Through Role' as Grant_Type, role, table_name from role_tab_privs rtp, dba_role_privs drp where rtp.role = drp.granted_role and table_name = 'TABLENAME' union select Grantee,'Direct Grant' as Grant_type, null as role, table_name from dba_tab_privs where table_name = 'TABLENAME' ;

Online table redefinition of a table  Lets create a table with some dependent objects for this setup. DROP PROCEDURE get_description; DROP VIEW redef_tab_v; DROP SEQUENCE redef_tab_seq; DROP TABLE redef_tab PURGE; CREATE TABLE redef_tab (   id           NUMBER,   description  VARCHAR2(50),   CONSTRAINT redef_tab_pk PRIMARY KEY (id) ); CREATE VIEW redef_tab_v AS SELECT * FROM redef_tab; CREATE SEQUENCE redef_tab_seq; CREATE OR REPLACE PROCEDURE get_description (   p_id          IN  redef_tab.id%TYPE,   p_description OUT redef_tab.description%TYPE) AS BEGIN   SELECT description   INTO   p_description   FROM   redef_tab   WHERE  id = p_id; END; / CREATE OR REPLACE TRIGGER redef_tab_bir BEFORE INSERT ON redef_tab FOR EACH ROW WHEN (new.id IS NULL) BEGIN   :new.id := redef_tab_seq.NEXTVAL; END; / Lets confirm ...

ORA-00020: maximum number of processes This error occurs when your total numbers of sessions connecting to oracle database has exceeded the max limit set in parameter file. Simplest way to overcome this error is to reset the max sessions value to a greater value than existing.Here is how to do it   oracle@LINUX201:[~] $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:20:26 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-00020: maximum number of processes (500) exceeded oracle@LINUX201:[~] $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:23:42 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-00020: maximum number of processes (500) exceeded Enter user-name: Disconnected from ORACLE I wasn't able get into the oracle database to kill some database session. So I tried to kill few sessions on OS to make my way into DB. oracle@LINUX201:[~] $ ps -ef|grep oracle . . . . . . ....

Global Temporary Tables in Oracle Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following: A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table. During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped. This statement creates a temporary table that is transaction specific: NOTE :   Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table. ***********************************...

Inserting Data with DML Error Logging: When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature. To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time. DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements. -------------------------------------------------------- --  DDL for Table ATEST1 ---------------------...

SELECT   E.EMPNO,   E.ENAME,   E.JOB,   D.DEPTNO,   D.LOC,   E.SAL FROM   scott.emp E LEFT JOIN SCOTT.DEPT D ON   E.DEPTNO=D.DEPTNO; SELECT   E.EMPNO,   E.ENAME,   E.JOB,   D.DEPTNO,   D.LOC,   E.SAL,   (     SELECT      grade     FROM       SCOTT.SALGRADE S     WHERE       E.SAL BETWEEN S.LOSAL AND S.HISAL   ) AS SALGRADE FROM   scott.emp E LEFT JOIN SCOTT.DEPT D ON   E.DEPTNO=D.DEPTNO;