Step by Step to change all FND_USER password using Pl/sql

Step by Step to change all  FND_USER  password without old password using pl/sql

 DECLARE
   x   BOOLEAN;
   CURSOR c_user
   IS
      SELECT *
  FROM fnd_user
 WHERE     NVL (end_date, SYSDATE + 1) > SYSDATE
       AND user_name NOT IN
              ('ANONYMOUS',
               'AUTOINSTALL',
               'CONCURRENT MANAGER',
               'FEEDER SYSTEM',
               'GUEST',
               'INITIAL SETUP',
               'APPSMGR',
               'SYSADMIN',
               'WIZARD',
               'IEXADMIN',
               'ASGADM',
               'MOBILEADM',
               'IBE_GUEST',
               'OP_SYSADMIN',
               'OP_CUST_CARE_ADMIN',
               'PORTAL30',
               'PORTAL30_SSO',
               'ASGUEST',
               'IBE_ADMIN',
               'IBEGUEST',
               'IRC_EXT_GUEST',
               'IRC_EMP_GUEST',
               'INDUSTRY DATA',
               'ASADMIN',
               'XML_USER',
               'ORACLE12.0.0',
               'ORACLE12.1.0',
               'ORACLE12.2.0',
               'ORACLE12.3.0',
               'ORACLE12.4.0',
               'ORACLE12.5.0',
               'ORACLE12.6.0',
               'ORACLE12.7.0',
               'ORACLE12.8.0',
               'ORACLE12.9.0',
               'AME_INVALID_APPROVER');
BEGIN
   FOR i IN c_user
   LOOP
      BEGIN
         x  := fnd_user_pkg.ChangePassword ( username      => i.user_name,
                                             newpassword   => 'welcome'   );
                                            
      --  dbms_output.put_line('password sucessfully changed for' || i.user_name);
      EXCEPTION
         WHEN OTHERS THEN
        DBMS_OUTPUT.put_line ( 'Error encountered while restting password for users and the Error Detail is ' || SQLERRM);
      END;
   END LOOP;
END;

Step by Step Enable or Disable the Oracle Triggers

Step by Step Enable or Disable the All Oracle Triggers in Single shot

Example:-

 BEGIN
   FOR i IN (SELECT trigger_name
               FROM all_triggers
              WHERE trigger_name LIKE 'JAI%' AND status = 'DISABLED')
   LOOP
     EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE';  
  
     EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' ENABLE';
           
      END LOOP;
END;
/

How to Check the Pach Level in Oracle Apps

This will be required for Oracle tar with Oracle corp. The following statement is mostly used by DBA

/* Formatted on 10/11/2011 5:46:09 PM (QP5 v5.163.1008.3004) */
  SELECT patch_level, application_name
    FROM fnd_product_installations a, fnd_application_tl b
   WHERE patch_level IS NOT NULL AND a.application_id = b.application_id
ORDER BY application_name;

Output:-

PATCH_LEVEL,APPLICATION_NAME
11i.MST.A, ????? ?????
11i.ABM.G.4,Activity Based Management
11i.BEN.O.2,Advanced Benefits
11i.IEC.R,Advanced Outbound Telephony
11i.QP.J,Advanced Pricing
11i.EGO.C,Advanced Product Catalog
11i.MSC.I,Advanced Supply Chain Planning
11i.ALR.G,Alert
11i.AZ.H.2,Application Implementation
11i.FND.H,Application Object Library
11i.RG.I,Application Report Generator
11i.BIS.L.6,Applications BIS
11i.AD.I.6,Applications DBA
11i.JA.I,Asia/Pacific Localizations
11i.FA.P,Assets
11i.VEA.J,Automotive
11i.BSC.H,Balanced Scorecard
11i.FPT.D,Banking Center

How to check which concurrent program attached to which responsibility

Execute following select statement to know the responsibilities names to which your concurrent program is attached

 SELECT responsibility_name
  FROM fnd_responsibility_tl a,
       fnd_responsibility c,                       
       fnd_request_group_units d,
       fnd_concurrent_programs_tl b
 WHERE     a.responsibility_id = c.responsibility_id
       AND c.request_group_id = d.request_group_id
       AND b.concurrent_program_id = d.request_unit_id
       AND UPPER (b.USER_CONCURRENT_PROGRAM_NAME) =
              UPPER ('Enter your program Name');

Example:-
/* Formatted on 10/11/2011 5:27:36 PM (QP5 v5.163.1008.3004) */
SELECT responsibility_name
  FROM fnd_responsibility_tl a,
       fnd_responsibility c,                      
       fnd_request_group_units d,
       fnd_concurrent_programs_tl b
 WHERE     a.responsibility_id = c.responsibility_id
       AND c.request_group_id = d.request_group_id
       AND b.concurrent_program_id = d.request_unit_id
       AND UPPER (b.USER_CONCURRENT_PROGRAM_NAME) =
              UPPER ('Qtel CI TO IRB Interface Program');

How to get the FND User name and Responsibility

SELECT c.user_name , RESPONSIBILITY_NAME

    FROM FND_USER_RESP_GROUPS_DIRECT a,
            FND_RESPONSIBILITY_VL b,
            fnd_user c
   WHERE     a.user_id(+) = c.user_id
         AND a.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID(+)
         AND c.user_name = NVL (:user_name, c.user_name)
         --and RESPONSIBILITY_NAME is null
ORDER BY 1;
:user_name =  Enter your username
Output :-

USER_NAME	RESPONSIBILITY
THALAIMUTHU	Application Developer
THALAIMUTHU	System Administrator
THALAIMUTHU	Oracle Sales Administrator
THALAIMUTHU	Quality
THALAIMUTHU	Bills of Material
THALAIMUTHU	Inventory
THALAIMUTHU	Workflow Administrator Web Applications
THALAIMUTHU	Order Management Super User
THALAIMUTHU	Oracle Pricing Manager
THALAIMUTHU	CRM Resource Manager
THALAIMUTHU	CRM Task Manager
THALAIMUTHU	SFM System Administrator
THALAIMUTHU	Quoting Sales Manager
THALAIMUTHU	Customer Support
THALAIMUTHU	CRM Administrator
THALAIMUTHU	CRM HTML Administration
THALAIMUTHU	Call Center Administration
THALAIMUTHU	Oracle Installed Base Admin
THALAIMUTHU	Oracle Installed Base User
THALAIMUTHU	CRM Application Foundation User
THALAIMUTHU	CRM Employee
THALAIMUTHU	XML Publisher Administrator
THALAIMUTHU	Workflow Administrator Event Manager
THALAIMUTHU	Workflow Administrator Web (New)
THALAIMUTHU	Workflow User Web (New)
THALAIMUTHU	Number Management
THALAIMUTHU	Number Management User
THALAIMUTHU	Qtel Installation Team
THALAIMUTHU	Qtel BackOffice Responsibility
THALAIMUTHU	NMS Admin
THALAIMUTHU	Call Center Agent
THALAIMUTHU	Ban Call Centre Representative

How to recover deleted Procedure Package Table

Backup your current Procedure:-SQL> select text from dba_source where name='XXQTL_DATA';
TEXT
--------------------------------------------------------------------------------
PACKAGE BODY XXQTL_DATA
PACKAGE              xxqtl_data
PACKAGE BODY              xxqtl_data
IS
IS   --Set of global variable for holding event sources to get DN and  EN values
IS
/******************************************************************************
   g_delen               VARCHAR2 (50)  := 'EN';
/******************************************************************************
Package       : APPS_APPLMGR.xxqtl_data
   --Global variable to hold DEL  EN event source.
Package       : APPS_APPLMGR.xxqtl_comptel
.............
         fnd_file.put_line (fnd_file.LOG, 'Exception Occured ' || SQLERRM);
         DBMS_OUTPUT.put_line ('Exception Occured ' || SQLERRM);
   END process;
END;
27551 rows selected.
Drop your Current Procedure :-
SQL> drop procedure XXQTL_DATA;
Procedure dropped.
SQL> connect / as sysdba
Connected.
Recover your old Procedure :-
SQL> SELECT text FROM dba_source AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '180' MINUTE WHERE name = 'XXQTL_DATA';
TEXT
--------------------------------------------------------------------------------
PACKAGE BODY XXQTL_DATA
PACKAGE              xxqtl_data
PACKAGE BODY              xxqtl_data
IS
IS   --Set of global variable for holding event sources to get DN and  EN values
IS
/******************************************************************************
   g_delen               VARCHAR2 (50)  := 'EN';
/******************************************************************************
Package       : APPS_APPLMGR.xxqtl_data
   --Global variable to hold DEL  EN event source.
Package       : APPS_APPLMGR.xxqtl_comptel
.............
         fnd_file.put_line (fnd_file.LOG, 'Exception Occured ' || SQLERRM);
         DBMS_OUTPUT.put_line ('Exception Occured ' || SQLERRM);
   END process;
END;
27551 rows selected.

How to add Responsibility using PL/SQL

BEGIN
   fnd_user_pkg.addresp ('MUTHU',                   /*Application User Name */
                         'SYSADMIN',                 /*get from Query Below */
                         'SYSTEM_ADMINISTRATOR',     /*get from Query Below */
                         'STANDARD',                  /* Most of cases it is ‘STANDARD’ so you can hard code it */
                         'SystemAdmin',            /* Any comments you want */
                         SYSDATE,                     /* Sysdate From Today */
                         NULL);    /* Sysdate + 365 Rights for Next One Year*/
END;
commit;Execute Below Query You will get the detail :-
 
SELECT MUT.APPLICATION_SHORT_NAME, MUT.APPLICATION_NAME,SHI.RESPONSIBILITY_KEY, SHI.RESPONSIBILITY_NAME
FROM FND_APPLICATION_VL MUT, FND_RESPONSIBILITY_VL SHI
WHERE SHI.APPLICATION_ID=MUT.APPLICATION_ID
ORDER BY SHI.RESPONSIBILITY_NAME;

How to change the FND USER password with out Old password using PL/sql script

begin
fnd_user_pkg.changepassword('USERNAME', 'PASSWORD');
 end;-- Input (Mandatory)
-- username: User Name
-- newpassword New Password
Examble :-
declare
 x  boolean;
     BEGIN
        x  :=    FND_USER_PKG.CHANGEPASSWORD ('MUTHU','shibanikutty');
     END;
 commit;

How to change the FND USER password with Old password using PL/sql script

BEGIN
   fnd_user_pkg.changepassword ('USERNAME', 'OLDPASSWORD', 'NEWPASSWORD');
END;
  Input (Mandatory)
  username:       User Name
  oldpassword     Old Password
  newpassword     New PasswordExample:-
DECLARE
   x   BOOLEAN;
BEGIN
   x := fnd_user_pkg.changepassword ('SHIBANI', 'shibanikutty', 'welcome');
END;

How to delete the Resposibility using PL/SQL script

BEGIN
   fnd_user_pkg.delresp ('SCOTT',  ---- User Name
                         'FND',    ---Application Short Name
                         'APPLICATION_DEVELOPER', ---Responsibility Key
                         'STANDARD');   --Security Group Key
END;Execute below Query you will get the Application Short Name and Responsibility Key
SELECT MUT.APPLICATION_SHORT_NAME, SHI.RESPONSIBILITY_KEY, SHI.RESPONSIBILITY_NAME
 FROM FND_APPLICATION_VL MUT, FND_RESPONSIBILITY_VL SHI
 WHERE SHI.APPLICATION_ID=MUT.APPLICATION_ID
 ORDER BY SHI.RESPONSIBILITY_NAME;
Examble:-
BEGIN
   fnd_user_pkg.delresp ('MUTHU',
                         'INV',
                         'INVENTORY',
                         'STANDARD');
END;

How to recover Oracle Dropped Table

[oracle@testserver db_1]$ sqlplus scott/oracleSQL> select * from emp;
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
SCOTT BIN$p1LI8Mt6uzXgQAB/AQAetw==$0
EMP DROP TABLE
USERS 2005-10-22:22:23:20 2011-07-05:18:10:12
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
636508 YES YES 51151 51151
51151 8
SQL>drop table EMP;
Table dropped.
SQL> select * from EMP;
select * from EMP
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>conn sys/oracle as sysdba ---- Login as sysdba
Connected.
SQL> FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP;
Flashback complete.
SQL> conn scott/oracle
Connected.
SQL>select * from emp;
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
SCOTT BIN$p1LI8Mt6uzXgQAB/AQAetw==$0
EMP DROP TABLE
USERS 2005-10-22:22:23:20 2011-07-05:18:10:12
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
636508 YES YES 51151 51151
51151 8
SQL>

How to check your installed product is full or shared

SELECT a.application_name,
a.application_id,
application_short_name,
DECODE(b.status,'I','I',
DECODE(b.status,'S','S',
DECODE(b.status,'N','N',b.status)))
FROM fnd_application_vl a, fnd_product_installations b
WHERE a.application_id = b.application_id
AND b.application_id IN (453,800,801,804,805,808,809,810,8301,8302,8303,8403)
ORDER BY a.application_id;

Status :-
Human Resources Intelligence    453    HRI    I
Human Resources    800    PER    I
Payroll    801    PAY    I
SSP    804    SSP    I
Advanced Benefits    805    BEN    S
Time and Labor    808    HXT    I
Time and Labor Engine    809    HXC    I
Learning Management    810    OTA    I
US Federal Human Resources    8301    GHR    I
Public Sector HR    8302    PQH    I
Public Sector Payroll    8303    PQP    I
Labor Distribution    8403    PSP    S

Output is    S    -  Shared
OutPut is   I    -  Full

How to Check tablespace size and freespace

SELECT d.status,
         db.name dbname,
         d.tablespace_name tsname,
         d.extent_management,
         d.allocation_type,
         TO_CHAR (NVL (d.min_extlen / 1024, 0),
                  '99G999G990D90',
                  'NLS_NUMERIC_CHARACTERS = '',.'' ')
            "SIZE In MB",
         d.contents "Type",
         CASE
            WHEN (d.contents = 'TEMP')
            THEN
               TO_CHAR (NVL (a.bytes / 1024 / 1024, 0),
                        '99G999G990D90',
                        'NLS_NUMERIC_CHARACTERS = '',.'' ')
            ELSE
               TO_CHAR (NVL (t.bytes / 1024 / 1024, 0),
                        '99G999G990D90',
                        'NLS_NUMERIC_CHARACTERS = '',.'' ')
         END
            AS "Size In MB",
         TO_CHAR (NVL ( (a.bytes - NVL (f.bytes, 0)) / 1024 / 1024, 0),
                  '99G999G990D90',
                  'NLS_NUMERIC_CHARACTERS = '',.'' ')
            "Used MB",
         TO_CHAR (NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0),
                  '990D90',
                  'NLS_NUMERIC_CHARACTERS = '',.'' ')
            "Used Size"
    FROM sys.dba_tablespaces d,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_data_files
          GROUP BY tablespace_name) a,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_temp_files
          GROUP BY tablespace_name) t,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_free_space
          GROUP BY tablespace_name) f,
         v$database db
   WHERE     d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = f.tablespace_name(+)
         AND d.tablespace_name = t.tablespace_name(+)
ORDER BY 10 DESC;

Query :-2
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Query :-3
select b.tablespace_name, tbs_size Total_Size, a.free_space Free
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name order by Total_SIZE desc;

How to create the FND_USER using pl/sql script

Create the FND_USER using pl/sql script 


/* Formatted on 25-Mar-11 01:20:26 PM (QP5 v5.163.1008.3004) */
DECLARE
   P_HIRE_DATE         DATE;
   P_USER_NAME         VARCHAR2 (32767);
   P_PASSWORD          VARCHAR2 (32767);
   P_USER_START_DATE   DATE;
   P_USER_END_DATE     DATE;
   P_EMAIL_ADDRESS     VARCHAR2 (32767);
   P_FAX               VARCHAR2 (32767);
   P_DESCRIPTION       VARCHAR2 (32767);
   P_PASSWORD_DATE     DATE;
   P_LANGUAGE          VARCHAR2 (32767);
   P_HOST_PORT         VARCHAR2 (32767);
   P_EMPLOYEE_ID       VARCHAR2 (32767);
   P_CUSTOMER_ID       VARCHAR2 (32767);
   P_SUPPLIER_ID       VARCHAR2 (32767);
   P_USER_ID           NUMBER;
BEGIN
   P_HIRE_DATE := NULL;
   P_USER_NAME := 'muthu';
   P_PASSWORD := 'welcome1';
   P_USER_START_DATE := SYSDATE;
   P_USER_END_DATE := NULL;
   P_EMAIL_ADDRESS := NULL;
   P_FAX := NULL;
   P_DESCRIPTION := NULL;
   P_PASSWORD_DATE := NULL;
   P_LANGUAGE := 'AMERICAN';
   P_HOST_PORT := NULL;
   P_EMPLOYEE_ID := NULL;
   P_CUSTOMER_ID := NULL;
   P_SUPPLIER_ID := NULL;
   P_USER_ID := NULL;
   DBMS_OUTPUT.put_line ('entry');
   APPS.HR_USER_ACCT_INTERNAL.CREATE_FND_USER (P_HIRE_DATE,
                                               P_USER_NAME,
                                               P_PASSWORD,
                                               P_USER_START_DATE,
                                               P_USER_END_DATE,
                                               P_EMAIL_ADDRESS,
                                               P_FAX,
                                               P_DESCRIPTION,
                                               P_PASSWORD_DATE,
                                               P_LANGUAGE,
                                               P_HOST_PORT,
                                               P_EMPLOYEE_ID,
                                               P_CUSTOMER_ID,
                                               P_SUPPLIER_ID,
                                               P_USER_ID);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('error occured in user creation' || SQLERRM);
      ROLLBACK;
END;

THEN
UPDATE fnd_user
SET password_lifespan_days = 45
WHERE user_id = 'MUTHU';

How to check the Long running Concurrent Process

SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC

Output:-
REQUEST_ID,SPID,RESPONSIBILITY_NAME,C.CONCURRENT_PROGRAM_NAME||':'||CTL.USER_CONCURRENT_PROGRAM_NAME,DESCRIPTION,ARGUMENT_TEXT,NODE_NAME,DB_INSTANCE,LOGFILE_NAME,LOGFILE_NODE_NAME,OUTFILE_NAME,CONCURRENT_QUEUE_NAME,PHASE_CODE,STATUS_CODE,COMPLETION_TEXT,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE,USER_NAME,MINS,AVG_MINS,DURATION_IN_HOURS
1655903,17481,Inventory,INCOIN: Import Items,,, 1, 1, 1, 1, 4100, 1,OUR12-APPS1,OURPROD,/applprod/conc_log/log/l1655903.req,OUR12-APPS1,/applprod/conc_log/out/o1655903.out,STANDARD,R,R,,11-Feb-11 12:08:44 PM,,SETUPUSER,9.23333333333333,162.816666666667,

show memory allocations for the database sessions

show memory allocations for the database sessions

SELECT NVL(a.username,'(oracle)') AS username,
       a.module,
       a.program,
       Trunc(b.value/1024) AS memory_kb
FROM   v$session a,
       v$sesstat b,
       v$statname c
WHERE  a.sid = b.sid
AND    b.statistic# = c.statistic#
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC;

How to check the general health of the system ?

How to check the general health of the system ?

SELECT file_id,
       tablespace_name,
       file_name,
       status
FROM   sys.dba_data_files;

SELECT file#,
       name,
       status,
       enabled
FROM   v$datafile;

SELECT *
FROM   v$backup;

SELECT *
FROM   v$recovery_status;

SELECT *
FROM   v$recover_file;

SELECT *
FROM   v$recovery_file_status;

SELECT *
FROM   v$recovery_log;

SELECT username,
       command,
       status,
       module
FROM   v$session;

Howto check the Locked objects?

Howto check the Locked objects?

SELECT b.session_id AS sid,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name
FROM   dba_objects a,
       v$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

How to check the Invalid ,database info status

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

How to check the database info?

SELECT *
FROM   v$version;

SELECT a.name,
       a.value
FROM   v$sga a;

SELECT Substr(c.name,1,60) "Controlfile",
       NVL(c.status,'UNKNOWN') "Status"
FROM   v$controlfile c
ORDER BY 1;

SELECT Substr(d.name,1,60) "Datafile",
       NVL(d.status,'UNKNOWN') "Status",
       d.enabled "Enabled",
       LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM   v$datafile d
ORDER BY 1;

SELECT l.group# "Group",
       Substr(l.member,1,60) "Logfile",
       NVL(l.status,'UNKNOWN') "Status"
FROM   v$logfile l
ORDER BY 1,2;