Learn PL SQL, Data Science and Automation

SGA - System Global Area SGA is a  group of shared memory area which is dedicated to oracle instance. Shared Pool Shared pool is dividing into two major areas which are : Library Cache :  Area which stores the SQL information such as select statements, cursors and execution plans. Data Dictionary Cache :  Which contains cache, user information and privileges, segments and extent information as well. Buffer Cache Buffer cache holds blocks of data from disk that have been recently read to satisfy a select statement or that contains modified data-blocks.  Redo Log Buffer Redo Log buffer holds the most recent changes to the data blocks in the data-files.    Large Pool This is an operational area of the SGA it is used for transactions that interact with more than one database. Java Pool Java Pool is used by the Oracle JVM ( Java Virtual Machine ) for all Java code & data within user session. Stream Pool Stream Pool is...

Login to server : sqlplus / as sysdba exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y or exp SYSTEM/password PARFILE=param.dat where param.dat contains the following information  FILE=dba.dmp   GRANTS=y   FULL=y   ROWS=y   LOG=dba.log To dump a single schema to disk. exp / FILE=dump.dmp OWNER=production Export Specific tables to disk exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table) Export Data in one user  exp / FILE=export.dmp TABLES=(table1,table2) Using imp:   To import the full database exported in the example above. imp SYSTEM/password FULL=y FIlE=dba.dmp To import just the dept and emp tables from the scott schema imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp) To import tables and change the owner  imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager) To import just the scott schema exported in t...

DECLARE CURSOR C1 IS   SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');   CURSOR C2     IS       SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');              CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)         IS           SELECT COUNT(*) AS COUNT_UP           FROM TABLE_NAME_3           WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID           AND COLUMN_CONDITION_02=CP_TABLE_01_ID; COUNT_UP NUMBER; BEGIN FOR R1 IN C1 LOOP     FOR R2 IN C2     LOOP          OPEN C3(R1.ID,R2.ID);        FETCH C3 INTO COUNT_UP;        CLOSE C3;               IF (COUNT_UP=0) THEN           INSERT           INTO TABL...

Sample String "ABCD123 ETS, NM" UPDATE TABLE_NAME SET COLUMN_NAME = SUBSTR( 'ABCD123 ETS, NM' , 0 , 4 ) || REPLACE ((SUBSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , '/' )), 5 ,INSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , ',/' )), ',' , 1 ) - 4 )), ' ' , '/' ) || REPLACE (SUBSTR( 'ABCD123 ETS, NM' ,INSTR( 'ABCD123 ETS, NM' , ',' , 1 ) + 2 ), 'NM' , 'NEW MEXICO' ) WHERE CONDITIONS;

WITH S AS ( /*SELECT QUERY*/ ) SELECT /*COLUMN NAMES*/ , LENGTH( FLOOR ( /*DECIMAL COLUMN*/ )) + NVL(LENGTH( RTRIM (SUBSTR(CN,INSTR(CN, '.' ) + 1 ), '0' )), 0 ) NUMBER_OF_DEGITS, NVL(LENGTH( RTRIM (SUBSTR(CN,INSTR(CN, '.' ) + 1 ), '0' )), 0 ) NUMBER_OF_DECIMALS FROM ( SELECT /*COLUMN NAMES*/ , TO_CHAR( /*DECIMAL COLUMN*/ , 'FM99999999999999999D99999999999999999' , 'NLS_NUMERIC_CHARACTERS = ''. ''' ) CN FROM S );

SELECT 'curl ' || 'https://www.testapp.net/test/SupportClass!actionClass.action ' || '-H ' || 'Origin: https://www.testapp.net ' || '-H ' || 'Accept-Encoding: gzip, deflate ' || '-H ' || 'Accept-Language: en-US,en;q=0.8 ' || '-H ' || 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.87 Safari/537.36 ' || '-H ' || 'Content-Type: application/x-www-form-urlencoded; charset=UTF-8 ' || '-H ' || 'Accept: */* ' || '-H ' || 'Referer: https://www.testapp.net/test/application.jsp ' || '-H ' || 'Cookie: JSESSIONID=5C30005FF6BF00A2FDD18A3C0E258115.app-2; JSESSIONID=QIPalPyhOP2D8HMlGSl7Uw**.node1; SSOID=be2bb104392d40c4a4fea96162906b10; pnctest=1 ' || '-H ' || 'Connection: keep-alive ' || '--data ' || '_operat...

REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC. DECLARE TYPE C1 IS REF CURSOR ; CURSOR C IS SELECT * FROM DUAL; REF_CURSOR RC; BEGIN IF (TO_CHAR(SYSDATE, 'DD' ) = 30 ) THEN OPEN REF_CURSOR FOR 'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) = 29 ) THEN OPEN REF_CURSOR FOR SELECT * FROM TABLE2; ELSE OPEN REF_CURSOR FOR SELECT * FROM DUAL; END IF ; OPEN C; END ;