Posts

How To Export And Import Statistics In Oracle

How To Export And Import Statistics In Oracle Step 1: If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful. Here i will take export of statistics of a table ARVIND.TEST from PROD and import into TEST DEMO: create a table to store the stats: --- ARVIND is the owner of the stats table, STAT_TEST - name of the stats table PROD> exec DBMS_STATS.CREATE_STAT_TABLE('ARVIND','STAT_TEST','SYSAUX'); PL/SQL procedure successfully completed. SQL>  select owner,table_name from dba_tables where table_name='STAT_TEST'; SQL> / OWNER      TABLE_NAME ------------ ------------ ARVIND      STAT_TEST SQL> PROD> exec dbms_stats.export_table_stats(ownname=>'ARVIND', tabname=>'TEST', stattab=>'STAT_TEST', cascade=>true); PL/SQL procedu...
Post a Comment
Read more

Automate recyclebin purging in oracle

Automate recyclebin purge in oracle Setup this simple scheduler job as sysdba to purge the objects in the  recycbin . This is one of the most space cosuming location that often dba's forget to cleanup and the objects get piled up occupying lot of space. Based on how long you want to save these dropped object setup a job under scheduler to run below plsql block either daily, weekly or monthly.   I suggest to run weekly. -- For user_recyclebin purge -- -- plsql -- declare VSQL varchar2(500); VSQL1 varchar2(500); Vcnt number(5); begin select count(*) into Vcnt from user_recyclebin; /***   Optional if you would like to keep record count of objects purged -- Uncomment if you would like to keep this insert into SYS.PURGE_STATS (obj_count) values (Vcnt); commit; **/ if Vcnt>0 then VSQL1:='purge  user_recyclebin '; execute immediate VSQL1; dbms_output.put_line('DBA RECYCLEBIN has been purged.'); end if; end; / -- For dba_recyclebin purge -- -- plsql -...
Post a Comment
Read more

Data Pump Exit Codes

oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$ About to export specified tables via Conventional Path ... . . exporting table                           AUD$     494321 rows exported Export terminated successfully without warnings. oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $? 0 oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log LRM-00101: unknown parameter name 'logifle' IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help IMP-00000: Import terminated unsuccessfully oracle@Linux01:[/u01/oracle/DPUMP] $ echo $? 1 Can be used in export shell scripts for status verification: if test $status -eq 0 then echo "export was successfull ." else echo " export was not successfull . " fi Also check below page fore reference : https://docs.oracle.com/database/121/SUTIL/GUID-...
Post a Comment
Read more

Setup Oracle database using Docker container.

Image
Step-1: Install docker container. Based on your windows OS Version. if you are using windows 7 will need docker engine and Kitematic  if you are on windows 10 or higher use :  Docker Community Edition ( Download ) Docker Instructions  :  https://docs.docker.com/install/ Once installed you will see Docker Quickstart Terminal and Kitematic. open docker machine and then Kitematic. Search for  oracle XE   11g  by  Seth89.  Download and install the below shown container Once installed You will have oracle database installed and ready to use. you can ignore the below error message   /docker-entrypoint-init.d/cache: no such file or dir. Another way to confirm successful installation is you will see  Unauthorized  image in web preview section Make sure you have  bridge  network configured. As below Check if below configured ports are configured. How to conn...
Post a Comment
Read more

Restore archivelogs from RMAN backup

Restore archive logs from RMAN backup rman> restore archivelog from logseq=37501 until logseq=37798 thread=1; or rmna> restore archivelog between sequence 37501 and 37798 ;
Post a Comment
Read more

expdp exclude schema or table

expdp export exclude syntax: Here is a simple example on how to use exclude in your export cmds. ---------------------------------------------- Exclude Tables ---------------------------------------------- Using parfile: First create a parfile with details. $ vi impdp_full.par --add the details to parfile directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4  EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3') Then execute using parfile: expdp SCOTT/XXXXXX parfile=impdp_full.par on cmdline: expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT  parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"   ---------------------------------------------- Exclude Schemas ---------------------------------------------- First create a parfile with details. $ vi expdp_full.par --add the details to parfile directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.lo...
Post a Comment
Read more

Setting up Optach environment variable

Setting up Optach environment variable : For Korn / Bourne shell: % export PATH=$PATH:$ORACLE_HOME/OPatch For C Shell: % setenv PATH $PATH:$ORACLE_HOME/OPatch
Post a Comment
Read more

Simple password encryption package to demonstrate how

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...
Post a Comment
Read more

package to encrypt data inside database using DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT

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 ...
Post a Comment
Read more

How to create multiple loops in plsql procedure

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...
Post a Comment
Read more

update rows from multiple tables (correlated update)

Image
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...
Post a Comment
Read more

How to Secure our Oracle Databases

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....
Post a Comment
Read more

java.lang.SecurityException: The jurisdiction policy files are not signed by a trusted signer

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...
Post a Comment
Read more

bash: /bin/install/.oui: No such file or directory

 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]$
Post a Comment
Read more

uninstall java on linux

If you are not sure of what the dependent packages that might be blocking java then you can also use yum remove jdk* This will also take care of dependent rpms . [root@linux06 usr]# yum remove jdk1.8.0_111-1.8.0_111-fcs.i586 Loaded plugins: refresh-packagekit, security Setting up Remove Process Resolving Dependencies --> Running transaction check ---> Package jdk1.8.0_111.i586 2000:1.8.0_111-fcs will be erased --> Processing Dependency: java for package: jna-3.2.4-2.el6.x86_64 --> Running transaction check ---> Package jna.x86_64 0:3.2.4-2.el6 will be erased --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================  Package           Arch        Version                 Repository ...
Post a Comment
Read more

Is it safe to move/recreate alertlog while the database is up and running

 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 ...
Post a Comment
Read more

Directory permissions granted to a user in database

Querying directory permissions granted to a user SELECT grantee, table_name directory_name, LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY grantee)   FROM dba_tab_privs  WHERE table_name =' DPUMP' group by GRANTEE,TABLE_NAME; SAMPLE output: GRANTEE              DIRECTORY_NAME                 GRANTS             -------------------- ------------------------------ -------------------- SCOTT                  DPUMP                       READ,WRITE          TIGER              ...
Post a Comment
Read more

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

Usage : we can use dbshut script file in $ORACLE_HOME/bin to shutdown  database & listener.  [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_ orcl oracle   21133 19211  0 11:01 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$ dbshut Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/shutdown.log [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   21287 19211  0 11:09 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$ Error : Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr [oracle@Linux03 bin]$ dbshut Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr  Solution (same as above): edit dbshut script and change From : ORACLE_HOME_LISTNER=$1   To : ORACLE_HOME_LISTNER=$ORACLE_HOME Note :   One pre-req for this script...
Post a Comment
Read more

dbstart: line 275: ORACLE_HOME_LISTNER: command not found

Usage : we can use dbstart script file in $ORACLE_HOME/bin to start database & listener. [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   20588 19211  0 10:56 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$ dbstart Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/startup.log [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl oracle   21035 19211  0 10:57 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$  Common error with dbstart script : Error : /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found [oracle@Linux03 bin]$ dbstart /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found Solution : Edit dbstart script and change (~ line 275) From : ORACLE_HOME_LISTNER=$1 To...
Post a Comment
Read more

expdp content=data_only

[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...
Post a Comment
Read more