GENERAL-DAILY USE SQL QUERIES.....

1.select * from gv$license

2.select sid,serial#,status,program,terminal,machine,username from v$session where sql_hash_value='&value';

3.ALTER SYSTEM KILL SESSION 'sid,serial#'

4.cat alert_bvsbi1.log |grep ORA

5. col program for a25
col machine for a20
col terminal for a20
select program,machine,terminal,username,serial#,status from v$session where sid='&sid';

7.select * from v$lock where request>0 or block=1

8.select * from dba_blockers

9.select * from dba_waiters

10.select * from v$access where object='&tblename';

11.select SID,SERIAL#,MACHINE,program,TERMINAL,USERNAME from v$session where status='ACTIVE';

12.select p1 "file#",p2 "block#",p3 "reason code" from v$session_wait where event = 'buffer busy waits';
select sid from v$session where audsid=userenv('SESSIONID');

13.select sid,message from v$session_longops where sid='&sid' order by start_time;


14.alter session set nls_date_format = 'DD-MM-YY HH24:MI';


15.select a.sid,a.serial#,a.status,a.schemaname,a.osuser,a.username,a.machine,a.program,to_char(logon_time,'DD-HH24:MI:SS')
from v$session a,v$process b where a.paddr=b.addr and b.spid=&spid

16.select sequence#,applied,status,registrar from v$archived_log where thread#='&1'

select tablespace_name,sum(bytes/1024/1024) from sm$ts_used group by tablespace_name


select sid,username,machine,terminal,LOGON_TIME from v$session where username not in ('BVSBI','SYS','WAC') and status='ACTIVE';

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

SPACE & TABLESPACE:

1.alter tablespace add datafile '/disk01/oradata01/bvsbi/ycs_data_01_03.dbf' size 1920

2.alter database datafile '/disk01/oradata02/bvsbi/sbi_filedata_hist_part_01_04.dbf' resize 1500m

3.select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='&tbs' group by tablespace_name;

4.select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='&tbs'

select tablespace_name,bytes_free/1024/1024,file_id from v$temp_space_header;

SELECT INDEX_NAME,TABLESPACE_NAME FROM USER_INDEXES WHERE TABLE_NAME='&TBL';



---------------------------------------------------------------------------------------------------------------------------------
DATAGUARD:

1.alter database recover managed standby database cancel

2.alter database recover managed standby database disconnect from session

3.select * from v$archive_gap

4.desc v$dataguard_status;
5.desc v$archive_dest;
6.desc v$archive_dest_status;
7.desc v$standby_log;
8.desc v$managed_standby;
9.select standby_logfile_count,standby_logfile_active from v$archive_dest_status;
10.select activation#,archivelog_change#,database_role,remote_archive,switchover_status from v$database;
select type from v$logfile;
select status from v$log;
select applied,backup_count,completion_time,creator,deleted,dest_id,dictionary_begin,dictionary_end,registrar,standby_dest,status,
end_of_redo,archival_thread# from v$archived_log;
select protection_mode from v$database;
select manifest,affirm,alternate,archiver,async_blocks,delay_mins,dependency,failure_count,max_failure,mountid,process,quota_size,quota_used,
register,schedule,transmit_mode,type from v$archive_dest;
select net_timeout,type from v$archive_dest;
select protection_mode,srl from v$archive_dset_status;
select guard_status,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,FORCE_LOGGING,PROTECTION_LEVEL from v$database;


SELECT max(sequence#) from v$archived_log where applied='YES';

SELECT min(sequence#) FROM v$archived_log WHERE ( (sequence#+1) NOT IN
(SELECT sequence# FROM v$archived_log) ) AND (sequence# > laseq)

select * from v$archive_gap

select message from v$dataguard_status;

ALTER DATABASE RECOVER managed standby database ;
or
ALTER DATABASE RECOVER managed standby database nodelay disconnect;

SELECT PROTECTION_MODE FROM V$DATABASE;

ALTER DATABASE RECOVER automatic standby database;



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

SEGMENT MANAGEMENT

1.select last_analyzed,TABLE_NAME FROM dba_tables where owner='&own' ORDER BY LAST_ANALYZED DESC;

2.select object_name,last_ddl_time from dba_objects where owner='&OWN' AND OBJECT_TYPE='TABLE' ORDER BY LAST_DDL_TIME DESC;

3.col owner for a20
col index_name for a20
select segment_name,segment_type,owner,sum(bytes/1024/1024)
from dba_segments
where
owner='&own'
order by bytes desc

select segment_name,segment_type,owner,bytes/1024/1024 from dba_segments
where segment_type='TABLE' and segment_name='SBICORP_ECHEQUE_MASTER'

4.SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

SELECT SEGMENT_NAME,BYTES/1024/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME='&TBL';
select name from sys.obj$ where obj# = 4145

select a.segment_name,a.bytes/1024/1024/1024,b.created from user_segments a,user_objects b where a.segment_name=b.object_name and b.last_ddl_time>='01-AUG-07' and b.object_type='TABLE'


--------------------------------------------------------------------------------------------------------------------------------------
PARTITIONS:

1.DESC user_tab_partitions;

2.user_part_tables;

select count(1) from SBI_FILE_DATA_HISTORY partition(DEC2006);

select table_name,PARTITION_NAME from user_tab_partitions where table_name='SBI_FILE_DATA_HISTORY';





WEBSITES:
http://www.orsweb.com/.
------------------------------------------------------------------------------------------------------------------------------------
EXPORT & IMPORT

1.imp backup/backup file=/disk02/backup01/expdump/Oct31_full_morn.dmp fromuser=YCSSTEP touser=YCSSTEP TABLES=REVERSAL ignore=y

2.exp backup/backup file=BVSBI.SBICORP_ECHEQUE_MASTER.dmp log=SBICORP_ECHEQUE_MASTER.log

tables=SBICORP_ECHEQUE_MASTER
exp bvsbi/ FILE=sbi_bp_biller_master.dmp log=sbi_bp_biller_master.log tables=bvsbi.sbi_bp_biller_master GRANTS=y INDEXES=y statistics=none buffer=60000000


----------------------------------------------------------------------------------------------------------------------------------------
USERS & PREVILEGES
1.grant all on sbi_txn_history_irctc to INTSBI
select GRANTEE,TABLE_NAMe,PRIVILEGE,GRANTABLE from dba_tab_privs where owner = 'WAC';
select grantee,table_name,grantor,PRIVILEGE,grantable from dba_tab_privs where owner='YCSSTEP';


---------------------------------------------------------------------------------------------------------------------------------------
INDEX:

1.col table_name for a20
select owner,index_name,table_name from dba_indexes where tablespace_name='&tbs';

2.analyze index SBICORP_RULE_MASTER_ACCNO_IDX compute statistics;

3.create index SBICORP_RULE_MASTER_STATUS_IDX on SBICORP_RULE_MASTER(STATUS)
TABLESPACE SMALL_MED_INDEX

4.select index_name,table_name,column_name from user_ind_columns where table_name='&tablename';

5.select index_name,TABLESPACE_NAME from dba_indexes where table_name='&tbname' and owner='&own';

6.select index_name,table_name,owner from dba_indexes where tablespace_name='&tbs';

SELECT index_name, column_name FROM user_ind_columns WHERE table_name ='&tb';


select table_name from all_indexes where index_name='&indname';

select * from all_ind_columns where index_name='&indname';

create index var_area_45_5_indx_name on inbs(substr(var_area,45,5)) tablespace USERS(function based index)
---------------------------------------------------------------------------------------------------------------------------------------
Jobs:

1. where job=746;


2. select sid from dba_jobs_running where job='&no';


ALTER SYSTEM SET job_queue_processes=10 SCOPE=BOTH
exec dbms_job.enable()
exec dbms_job.disable()

select job,LAST_DATE,NEXT_DATE, BROKEN,INTERVAL,FAILURES,INSTANCE,WHAT from dba_jobs where job=746;

select text from dba_source where name like 'OFFLINE%' and owner = 'BVSBI';

--------------------------------------------------------------------------------------------------
constraints

SELECT constraint_name FROM user_constraints WHERE table_name = '&tb' AND constraint_type = 'P'

select table_name, column_name from all_cons_columns where owner='&own' and constraint_name='&consname' order by position;


Audit:

1.desc sbi_user_login;

scp arch_1_7867.arc.Z oracle@10.192.24.66:/disk02/arch02/standby_archive/


exec dbms_stats.create_stat_table (ownname=>'BVSBI',stattab=>'STAGEDBBVSBISTATS');

DBA_VIEWS
1.DBA_ROLES
DBA_ROLE_PRIVS
DBA_SYS_PRIVS
ROLE_ROLE_PRIVS
ROLE_TAB_PRIVS
SESSION_ROLES
ROLE_SYS_PRIVS
DBA_SYS_PRIVS
SESSION_PRIVS
DBA_TAB_PRIVS
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB)PRIVS_RECD
ALL_COL_PRIVS
DBA_COL_PRIVS
ALL_COL_PRIVS_MADE_RECD
DBA_PROFILES
USER_PASSWORD_LIMITS
USER_RESOURCE_LIMITS
RESOURCE_COST
DBA_TS_QUOTAS
AUDIT_ACTIONS
DBA_AUDIT_OBJECT
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRIAL
DBA_AUDIT_POLICIES


create table INBS
tablespace SBI_TXN_HISTORY_ARCHIVE_TS as select * from ayyu.INBS


select dbms_metadata.get_ddl('TABLE','EMP') FROM dual

SELECT * FROM v$sqlAREA WHERE sql_text LIKE 'INSERT%SBI_FILE_DATA_HISTORY%' ;