Saturday, September 1, 2007

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%' ;

7 comments:

Anonymous said...

Good day !.
You re, I guess , probably very interested to know how one can manage to receive high yields .
There is no initial capital needed You may commense earning with as small sum of money as 20-100 dollars.

AimTrust is what you thought of all the time
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with offices around the world.
Do you want to become really rich in short time?
That`s your choice That`s what you wish in the long run!

I feel good, I started to get income with the help of this company,
and I invite you to do the same. It`s all about how to choose a proper partner who uses your funds in a right way - that`s the AimTrust!.
I take now up to 2G every day, and my first investment was 500 dollars only!
It`s easy to get involved , just click this link http://alalefiz.jamminweb.com/izofiv.html
and lucky you`re! Let`s take our chance together to feel the smell of real money

Anonymous said...

Hello everyone!
I would like to burn a theme at here. There is such a thing, called HYIP, or High Yield Investment Program. It reminds of ponzy-like structure, but in rare cases one may happen to meet a company that really pays up to 2% daily not on invested money, but from real profits.

For quite a long time, I make money with the help of these programs.
I'm with no money problems now, but there are heights that must be conquered . I make 2G daily, and I started with funny 500 bucks.
Right now, I managed to catch a guaranteed variant to make a sharp rise . Turn to my blog to get additional info.

http://theblogmoney.com

Anonymous said...

Hello!
You may probably be very curious to know how one can make real money on investments.
There is no initial capital needed.
You may commense earning with a sum that usually is spent
on daily food, that's 20-100 dollars.
I have been participating in one company's work for several years,
and I'm ready to let you know my secrets at my blog.

Please visit blog and send me private message to get the info.

P.S. I make 1000-2000 per day now.

http://theinvestblog.com [url=http://theinvestblog.com]Online Investment Blog[/url]

Anonymous said...

Good day!

We are not acquainted yet? It’s easy to fix,
friends call me James F. Collins.
Generally I’m a venturesome analyst. for a long time I’m keen on online-casino and poker.
Not long time ago I started my own blog, where I describe my virtual adventures.
Probably, it will be interesting for you to read my notes.
Please visit my diary. http://allbestcasino.com I’ll be glad would you find time to leave your comments.

Anonymous said...

You made some good points there. I did a search on the topic and hardly found any specific details on other sites, but then great to be here, seriously, thanks...

- Andre

Xia Zhang said...

http://www.shopbestgoods.com/
http://www.bestcustomsonline.com/
http://www.nike-jordanshoes.com/
http://www.polo-tshirts.com/
http://www.burberry-factory.com/
http://www.kate-spades.com/
http://www.barbour-factory.com/
http://www.coachlosangeles.com/
http://www.official-coachoutlet.com/
http://www.louisvuittonas.com/
http://www.burberryoutlet2014.com/
http://www.official-mkoutlet.com/
http://www.official-pradaoutlet.com/
http://www.beatsbydreoutlet.net/
http://www.michaelkorsonlineusa.com/
http://www.northsclearance.com/
http://www.ralph-laurensale.com/
http://www.gucci-shoesuk2014.com/
http://www.michael-korsusa.com/
http://www.polo-outlets.com/
http://www.hermes-outletonline.com/
http://www.ralphslauren.co.uk/
http://www.marcjacobsonsale.com/
http://www.mcmworldwides.com/
http://www.warmbootssale.com/
http://www.salongchamppairs.com/
http://www.canada-gooser.com/
http://www.michaelkors.so/
http://www.oakley-sunglassoutlet.com/
http://www.north-faceoutlets.net/
http://www.moncler-clearance.com/
http://www.woolrich-clearance.com/
http://www.barbour-jacketsoutlet.com/
http://www.moncler-jacketsoutletonline.com/
http://www.monsterbeatsbydres.net/
http://www.lv-guccishoesfactory.com/
http://www.cheapdiscountoutlet.com/
http://www.coachsfactoryoutlet.com/
http://www.coach-blackfriday2014.com/
http://www.coach-storeoutletonline.com/
http://www.coach-factorysoutletonline.com/
http://www.coachccoachoutlet.com/
http://www.coach-factories.net/
http://www.coach-pursesoutletonline.com/
http://www.coach-outletsusa.com/
http://www.zxcoachoutlet.com/
http://www.mischristmas.com/
http://www.misblackfriday.com/
http://www.newoutletonlinemall.com/
http://www.ralphlaurenepolo.com/
http://michaelkorsoutlet.mischristmas.com/
http://mcmbackpack.mischristmas.com/
http://monsterbeats.mischristmas.com/
http://northfaceoutlet.mischristmas.com/
http://mk.misblackfriday.com/
http://coachoutlet.misblackfriday.com/
http://coachfactory.misblackfriday.com/
http://uggaustralia.misblackfriday.com/
http://coachpurses.misblackfriday.com/
http://coachusa.misblackfriday.com/
http://coach.misblackfriday.com/
http://michaelkorss.misblackfriday.com/
http://michaelkors.misblackfriday.com/
http://airmax.misblackfriday.com/
http://michael-kors.misblackfriday.com/
https://twitter.com/CoachOutlet2014
https://www.facebook.com/coachoutletstoreonline
https://www.facebook.com/ralphlaurenoutletonline

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.