Find the Tablespace size in putty

select nvl(b.tablespace_name,nvl(a.tablespace_name,'NA')) " Tablespace" 
         , kbytes_alloc/1024 "Allocated" 
         , (kbytes_alloc-nvl(kbytes_free,0))/1024 "used" 
         , nvl(kbytes_free,0) /1024 "free" 
from (select sum(bytes)/1024 Kbytes_free 
                  , max(bytes)/1024 largest 
                  , tablespace_name 
         from  dba_free_space 
         group by tablespace_name) a ,
       (select sum(bytes)/1024 Kbytes_alloc 
                 ,tablespace_name 
        from  dba_data_files 
        group by tablespace_name) b 
where a.tablespace_name (+) = b.tablespace_name 
and b.tablespace_name like '%' 
order by 4 ;

Find the Database Growth by Monthly Basis

select to_char(creation_time, 'YYYY Month') "Month",
    sum(bytes)/1024/1024 "Growth in MB"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'YYYY Month');

Backup the Database Links

set lines 130
set pages 200
select  'DB link for '||b.username||chr(10)||'  create  database  link '||c.name||' connect to '||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select  'DB link for Public' ||chr(10)|| '  create public  database  link '||c.name||' connect to
'||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'
from link$ c
where owner#=1;

Find the Concurrent Program Completed with Error

SELECT b.request_id, a.user_concurrent_program_name,
       b.phase_code AS completed, b.status_code AS error,
       u.user_name requestor,
       TO_CHAR (b.actual_start_date, 'MM/DD/YY HH24:MI:SS') starttime,
       ROUND ((b.actual_completion_date - b.actual_start_date) * (60 * 24),
              2
             ) runtime,
       b.completion_text
  FROM fnd_concurrent_programs_tl a, fnd_concurrent_requests b, fnd_user u
 WHERE a.concurrent_program_id = b.concurrent_program_id
   AND b.phase_code = 'C'
   AND b.status_code = 'E'
   AND b.actual_start_date > SYSDATE - 1  
   AND b.requested_by = u.user_id
   AND a.LANGUAGE = 'US';

Note :  SYSDATE - 1 --> It will list out the concurrent program completed with error for 1 day.

Find the Table Lock and Unlock the Table

To check table has been locked or not:

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and

   a.object_id = c.object_id;

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

Unlock the table:

Exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');

To check the Index status:

select INDEX_NAME,INDEX_TYPE,BLEVEL, STATUS, LAST_ANALYZED, OWNER from dba_indexes where
upper(index_name) like'%PA_PROJ_EVENTS%'

Rebuilding the particular Index:

ALTER INDEX PJI.PJI_PA_PROJ_EVENTS_LOG_N1 REBUILD ONLINE;

Change Password Expire Policy for Database Users

To find the database profile values

select * from dba_profiles where resource_name like 'PASSWORD_LIFE_TIME';

It will provide the output like below, By default users password will expire in 180 days.

DEFAULT              PASSWORD_LIFE_TIME        PASSWORD 180

Change the password life time as unlimited, so that password will not expire for all users.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

commit;


select * from dba_profiles where resource_name like 'PASSWORD_LIFE_TIME';

Find the Recently Changed Profiles in R12 EBS

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE  
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null 
order by last_update_date desc, short_name, level_set;

Run SCP command as background process using nohup

Follow the below steps to run the scp command as background process using nohup option 

Step 1: nohup scp -rC rman_backup root@172.16.1.1.:/d03/SUPPORT > nohup.out 2>&1

Step 2: Enter the password

Step 3: Press ctl + z

Step 4: Type bg

Find the Scheduled Concurrent Program lists with Parameters

SELECT   r.request_id,
            p.user_concurrent_program_name
         || NVL2 (r.description, ' (' || r.description || ')', NULL)
                                                                    conc_prog,
         s.user_name requestor, r.argument_text arguments,
         r.requested_start_date next_run, r.last_update_date last_run,
         r.hold_flag on_hold, r.increment_dates,
         DECODE (c.class_type,
                 'P', 'Periodic',
                 'S', 'On Specific Days',
                 'X', 'Advanced',
                 c.class_type
                ) schedule_type,
         CASE
            WHEN c.class_type = 'P'
               THEN    'Repeat every '
                    || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
                    || DECODE (SUBSTR (c.class_info,
                                       INSTR (c.class_info, ':', 1, 1) + 1,
                                       1
                                      ),
                               'N', ' minutes',
                               'M', ' months',
                               'H', ' hours',
                               'D', ' days'
                              )
                    || DECODE (SUBSTR (c.class_info,
                                       INSTR (c.class_info, ':', 1, 2) + 1,
                                       1
                                      ),
                               'S', ' from the start of the prior run',
                               'C', ' from the completion of the prior run'
                              )
            WHEN c.class_type = 'S'
               THEN    NVL2 (dates.dates,
                             'Dates: ' || dates.dates || '. ',
                             NULL
                            )
                    || DECODE (SUBSTR (c.class_info, 32, 1),
                               '1', 'Last day of month '
                              )
                    || DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),
                               '1', 'Days of week: '
                                || DECODE (SUBSTR (c.class_info, 33, 1),
                                           '1', 'Su '
                                          )
                                || DECODE (SUBSTR (c.class_info, 34, 1),
                                           '1', 'Mo '
                                          )
                                || DECODE (SUBSTR (c.class_info, 35, 1),
                                           '1', 'Tu '
                                          )
                                || DECODE (SUBSTR (c.class_info, 36, 1),
                                           '1', 'We '
                                          )
                                || DECODE (SUBSTR (c.class_info, 37, 1),
                                           '1', 'Th '
                                          )
                                || DECODE (SUBSTR (c.class_info, 38, 1),
                                           '1', 'Fr '
                                          )
                                || DECODE (SUBSTR (c.class_info, 39, 1),
                                           '1', 'Sa '
                                          )
                              )
         END AS schedule,
         c.date1 start_date, c.date2 end_date, c.class_info
    FROM fnd_concurrent_requests r,
         fnd_conc_release_classes c,
         fnd_concurrent_programs_tl p,
         fnd_user s,
         (WITH date_schedules AS
               (SELECT release_class_id,
                       RANK () OVER (PARTITION BY release_class_id ORDER BY s)
                                                                            a,
                       s
                  FROM (SELECT c.class_info, l, c.release_class_id,
                               DECODE (SUBSTR (c.class_info, l, 1),
                                       '1', TO_CHAR (l)
                                      ) s
                          FROM (SELECT     LEVEL l
                                      FROM DUAL
                                CONNECT BY LEVEL <= 31),
                               fnd_conc_release_classes c
                         WHERE c.class_type = 'S'
                           AND INSTR (SUBSTR (c.class_info, 1, 31), '1') > 0)
                 WHERE s IS NOT NULL)
          SELECT     release_class_id,
                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
                FROM date_schedules
          START WITH a = 1
          CONNECT BY NOCYCLE PRIOR a = a - 1
            GROUP BY release_class_id) dates
   WHERE r.phase_code = 'P'
     AND c.application_id = r.release_class_app_id
     AND c.release_class_id = r.release_class_id
     AND NVL (c.date2, SYSDATE + 1) > SYSDATE
     AND c.class_type IS NOT NULL
     AND p.concurrent_program_id = r.concurrent_program_id
     AND p.LANGUAGE = 'US'
     AND dates.release_class_id(+) = r.release_class_id
     AND r.requested_by = s.user_id
ORDER BY conc_prog, on_hold, next_run;

Find the Completed Concurrent Program for more than 1 Hour

SELECT b.request_id, a.user_concurrent_program_name, b.cancel_or_hold,
           TO_CHAR (b.actual_start_date, 'MM/DD/YY HH24:MI:SS') starttime,
           TO_CHAR (b.actual_completion_date, 'MM/DD/YY HH24:MI:SS') endtime,
           ROUND ((b.actual_completion_date - b.actual_start_date) * (60 * 24),
                  2
                 ) runtime,
           b.outcome_code, b.completion_text
      FROM fnd_concurrent_programs_tl a, fnd_concurrent_requests b
     WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
   AND B.ACTUAL_START_DATE > SYSDATE - 1
   AND ROUND ((actual_completion_date - actual_start_date) * (60 * 24), 2) > 60
      AND a.LANGUAGE = 'US';

Find the Archive log Generation by Hourly Basis

Using this below query you can find the archive log file generation history by hourly basis.

set linesize 1000
clear columns computes breaks
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999

break on report

compute sum of "MidN" "1AM" "2AM" "3AM" "4AM" "5AM" "6AM" "7AM" "8AM" "9AM" "10AM" "11AM" "Noon" "1PM" "2PM" "3PM" "4PM" "5PM" "6PM" "7PM" "8PM" "9PM" "10PM" "11PM" on report

select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1;

Add System Administrator Responsibility through Back End

Using this below sql query, you can add the system administrator responsibility to fnd_users

BEGIN
fnd_user_pkg.addresp ('&Enter_User_Name','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
        WHEN others THEN
                dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
                ROLLBACK;
END;