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 -- declare
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-34D0DEE7-3530-42DC-BE01-C2588CC73CE5.htm#SUTIL38
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 connect to oracle database in the container  : Based on the  IP Address  and  por
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