Learn Database Concepts

rem ----------------------------------------------------------------------- rem Purpose:   Simple password encryption package to demonstrate how rem                  values can be encrypted and decrypted using Oracle's rem                  DBMS Obfuscation Toolkit rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql rem Author:     Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- ---- create table to store encrypted data -- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator. CREATE TABLE USERS_INFO (   USERNAME VARCHAR2(20 BYTE) , PASS VARCHAR2(20 BYTE) )users; ----------------------------------------------------------------------- ----------------------------------------------------------------------- CREATE OR REPLACE PACKAGE PASS...

rem ----------------------------------------------------------------------- rem Purpose:   Simple password encryption package to demonstrate how rem                  values can be encrypted and decrypted using Oracle's rem                  DBMS Obfuscation Toolkit rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql rem Author:     Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- ---- create table to store encrypted data -- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator. CREATE TABLE USERS_INFO (   USERNAME VARCHAR2(20 BYTE) , PASS VARCHAR2(20 BYTE) )users; ----------------------------------------------------------------------- ----------------------------------------------------------------------- CREATE ...

declare         cursor c_job is select * from job;         r_job c_job%ROWTYPE;         cursor c_empInjob (cin_jobNo NUMBER) is select * from employee where empno = cin_jobNo;         r_emp c_empInjob%ROWTYPE;     begin         open c_job;         loop            fetch c_job into r_job;            exit when c_job%NOTFOUND;            open c_empInjob (r_job.empno);            loop                fetch c_empInjob into r_emp;                exit when c_empInjob%NOTFOUND;            end loop;            close c_empInjob;        end loop;        clo...

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL. In this article, we are going to look at four scenarios for Oracle cross table update. Suppose we have two tables Categories and Categories_Test. See screenshots below. lets take two tables TABA & TABB: Records in TABA: Records in TABB: 1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B. The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.  update TABA A set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID); 2. Update data in two columns in table A based on a common column in table B. If you need to update multiple columns simultaneously, use comma to...

How Secure can we make our Oracle Databases?? This is a routine question that runs in minds of most database administrators.   HOW SECURE ARE OUR DATABASES. CAN WE MAKE IT ANYMORE SECURE . I am writing this post to share my experience and knowledge on securing databases. I personally follow below tips to secure my databases:  1. Make sure we only grant access to those users that really need to access database. 2. Remove all the unnecessary grants/privileges from users/roles. 3. Frequently audit database users Failed Logins in order to verify who is trying to login and their actions. 4. If a user is requesting elevated privileges, make sure you talk to them and understand their requirements. 5. Grant no more access than what needed. 6. At times users might need access temporarily. Make sure these temporary access are revoked after tasks are completed. 7. Define a fine boundary on who can access what?? 8. Use User profiles / Audit to ensure all activities are tracked. 9....

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...

 Problem: [oracle@linux5 database]$ . runInstaller bash: /bin/install/.oui: No such file or directory [oracle@linux5 database]$ uname -a Linux linux5 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux Solution: [oracle@linux5 database]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB.   Actual 20461 MB    Passed Checking swap space: must be greater than 150 MB.   Actual 4031 MB    Passed Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-11-22_09-46-02AM. Please wait ...[oracle@linux5 database]$