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 ;
Posts
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...
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
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...
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 ...
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...
update rows from multiple tables (correlated update)
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...