Routine checks SQL

posted Sep 13, 2010, 7:16 AM by Sachchida Ojha
select job_name,state from dba_scheduler_jobs;

select username, account_status,profile, lock_date,default_tablespace,temporary_tablespace from dba_users order by lock_date desc;
select username, account_status,profile from dba_users where account_status='OPEN' order by lock_date desc;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
select start_time,end_time, status, session_key from v$rman_backup_job_details order by end_time desc;
select * from DBA_DB_LINKS;
select * from dba_data_files where autoextensible!='YES';
exec dbms_stats.gather_schema_stats('SYS');
exec dbms_stats.gather_database_stats (gather_sys=>TRUE);
exec dbms_stats.gather_dictionary_stats;
select table_name,last_analyzed from dba_tables where last_analyzed is null;
select min(last_analyzed) from dba_tables;
select max(last_analyzed) from dba_tables;
select owner,table_name, last_analyzed from dba_tables where last_analyzed<sysdate-30;
select 'alter '||decode(object_type,'PACKAGE BODY','package', object_type) ||' '||owner
||'.'||object_name||' compile' ||decode(object_type, 'PACKAGE BODY', ' body;', ';')
from dba_objects where status='INVALID';
select owner,object_name,object_type from dba_objects where status='INVALID';
ALTER USER sachiaccount unlock;
select username, account_status,profile, lock_date,default_tablespace,temporary_tablespace from dba_users where upper(username) like  'sachi';    

select username, account_status,profile from dba_users where profile<>'profile_name';

select * from dba_data_files;   

Comments