Tips For DBA part 3

Tnsnames.ora Path :: =====================================>
/etc/tnsnames.ora
DB Objects Queries:: =====================================>
select * from user_objects where status='INVALID' order by object_type;
select * from user_constraints;
select * from dba_objects where object_name = 'obj_name'
select * from dba_directories
select * from user_objects where trunc(last_ddl_time) > trunc(sysdate - 3) and object_type NOT IN ( 'TABLE', 'INDEX','VIEW','FUNCTION')
select * from user_objects where object_type = 'TABLE';
select * from v$session where status='ACTIVE' and type!='BACKGROUND' and schemaname='vivek'
select * from v$sql where sql_id = '81q8p3wcy6jjh'
select * from v$sql_plan where sql_id = '81q8p3wcy6jjh' and operation = 'HASH JOIN' order by bytes desc
select * from dba_objects where data_object_id = 2337498
select * from v$session_longops where sofar<totalwork
select INSTANCE_NUMBER,INSTANCE_NAME,STATUS,STARTUP_TIME,DATABASE_STATUS,ACTIVE_STATE from v$instance;
select * from dict where table_name like '%SORT%'
select * from V$TEMPSTAT
select * from V$TEMPSEG_USAGE
select * from V$TEMP_EXTENT_POOL
select * from V$TEMP_SPACE_HEADER
select * from V$TEMPSTATXS
select * from V$TEMP_HISTOGRAM
select * from V$SORT_SEGMENT
select * from V$SORT_USAGE
select * from v$parameter where name like 'pga%'
select * from v$instance@publish
select * from user_db_links
select * from dba_directories
select * from v$session where status = 'ACTIVE'
select * from dba_mview_refresh_times;
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects;
select segment_type , count(1) from dba_segments where tablespace_name = 'V_INDEX' group by segment_type;
select * from recyclebin;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='&tbname' / 2 Enter value for tbname: V_Index
select segment_type , count(1) from dba_segments where tablespace_name = 'V_Index' group by segment_type;
select * from recyclebin;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='&tbname'
select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin
flashback table tst to before drop;


Utilities:: =====================================>
exec sys.alter_user ('alter user vivread account unlock');
exec dbms_utility.compile_schema(schema => '`echo $USERNAME | tr [:lower:] [:upper:]`',compile_all=>FALSE);
exec dbms_utility.compile_schema('VIVEK',FALSE);
exec alter_user('alter user username identified by passwd')
exec sys.kill_session(30,'52981');
exec sys.kill_session(sid,'serialno');
exec alter_user('alter user a2 account unlock');
exec alter_user('alter user a2 identified by nji90okm');
exec dbms_mview.refresh('MVIEW_NAME','C')
C -> complete
exec flush_sga('SHARED_POOL');
exec flush_sga('BUFFER_CACHE');
select * from dual@im_tns
select * from user_objects where object_type = 'TABLE';
select * from gv$session where type <> 'BACKGROUND' order by last_call_et desc;