Refresh Database Objects, Stats Gathering and Password update with value

# Full Schema Refresh
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'CSI');

# How to change Oracle password and restore with old one if you don't know current password

Step 1: Get the password value of user
SQL> select USER#,NAME,PASSWORD from user$ where name='CSI';

     USER# NAME    PASSWORD
---------- ------- ------------------
        63 CSI     A31A78DDCB990AE2

Step2: Change the user password with a new value and perform the activity.
SQL> ALTER USER CSI IDENTIFIED BY mynewpassword;

SQL> select USER#,NAME,PASSWORD from user$ where name='CSI';
     USER# NAME    PASSWORD
---------- ------- ------------------
        63 CSI     D3998D50331ED867

Step3: After completion of activity revert back to original password.
SQL> ALTER USER CSI IDENTIFIED BY VALUES 'A31A78DDCB990AE2';

How to Find Oracle Locked Objects and Session Issue

# Query to Identify Locked object in session
set pages 200 lines 200
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, A.Session_id FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID;
select SID, SERIAL#, USER#, USERNAME,PROGRAM,SCHEMANAME,SCHEMA#, STATUS from v$session where USERNAME='SYS';
select SID,SERIAL#,SCHEMANAME,OSUSER, status from v$session where username='SYS';

# Create and Kill the session using below statements
select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='USER';
alter system kill session '223,987' immediate;

How to Generate DDL Statements for an Object in Oracle.

Use dbms_metadata.get_ddl package to Generate DDL Statements for a different type of Objects in Oracle Database usage as below: 

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OWNER') from dual; -If you are executing as SYS or SYSTEM

OR

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME') from dual; -If you are already connected with Schema

Examples: 

# Generate DDL for all Objects of Schema.

SQL> set feedback off

SQL> set echo off 

SQL> set heading 999

SQL> set lines 100 

SQL> set long 90000

SQL> select dbms_metadata.GET_DDL(u.object_type,u.object_name,'SCOTT') from dba_objects u where owner = 'SCOTT';

# Generate DDL for Specific Object of Schema.

SQL> set feedback off

SQL> set echo off

SQL> set heading 999

SQL> set lines 100 

SQL> set long 90000

SQL> spool DDL_SCOTT_EMP.lst;

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')

--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"

   ( "EMPNO" NUMBER(4,0),

"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,

"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0),

CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE,

CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

 REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

SQL> spool off;

Different ways to fetch Incremental data from oracle tables

Incremental Query 
Method 1: Fetch the record between two records
SELECT  * FROM (SELECT  emp.*, rownum rn FROM emp ) WHERE  rn between 2 and 5;

Method 2: Fetch the record between two records
SELECT  * FROM (SELECT  q.*, rownum rn FROM( SELECT  * FROM  emp ) q ) WHERE  rn BETWEEN 2 AND 5;

Method 3: Fetch the last record form table
SELECT  * FROM (SELECT  q.*, rownum rn FROM (SELECT * FROM  emp) q ) WHERE  rn=(select max(rownum) from emp);