REGULAR LIST OF ACTIVITIES PERFORMED BY THE LAMBENT ON SITE REGULAR LIST OF ACTIVITIES PERFORMED BY THE LAMBENT ON SITE
CONSULTANT IN WHEELS INDIA CONSULTANT IN WHEELS INDIA DATABASE MONITORING AND TUNING (DAILY)
DATABASE MONITORING AND TUNING (DAILY)
Database Growth MonitoringDatabase Growth Monitoring
Archival Space MonitoringArchival Space Monitoring
Rollback segment MonitoringRollback segment Monitoring
Lock MonitoringLock Monitoring
Monitoring Latch ContentionMonitoring Latch Contention Performance Monitoring
Performance Monitoring
Monitoring Memory PerformanceMonitoring Memory Performance
Parameter Tuning (whenever required)Parameter Tuning (whenever required)
I/0 MonitoringI/0 Monitoring
Resolving ContentionResolving Contention
Database object status validation and re-compilation Database object status validation and re-compilation
Checking Alert Files And Trace FilesChecking Alert Files And Trace Files
Index Usage MonitoringIndex Usage Monitoring
Sort Performance MonitoringSort Performance Monitoring
Analyzing tables for performance (monthly)Analyzing tables for performance (monthly)
Re-building indexes (monthly)Re-building indexes (monthly)
Table re-organization (whenever required)Table re-organization (whenever required) BACKUP STRATEGY IMPLEMENTATION BACKUP STRATEGY IMPLEMENTATION Implementing Backups
Implementing Backups
Recovery Contingency TestingRecovery Contingency Testing
Maintaining Standby DatabasesMaintaining Standby Databases
DailyDaily
•
• Export backupExport backup •
• Applying Archive logs on Standby DatabaseApplying Archive logs on Standby Database
WeeklyWeekly
•
• Hot Back Hot Back •
• Archive Log BackupArchive Log Backup
MonthlyMonthly
•
SECURITY (ON DEMAND) SECURITY (ON DEMAND)
Managing Users, Privileges And RolesManaging Users, Privileges And Roles
Implementing Database AuditingImplementing Database Auditing
CUSTOMER INITIATED ACTIVITIES (ON DEMAND) CUSTOMER INITIATED ACTIVITIES (ON DEMAND) Data Loading
Data Loading
Exp / Imp Of Tables / Users For TestingExp / Imp Of Tables / Users For Testing
Creation Of Objects Based On User RequestCreation Of Objects Based On User Request Installation of oracle server and clients
Installation of oracle server and clients TROUBLESHOOTING
TROUBLESHOOTING (ON (ON DEMAND)DEMAND)
Oracle Connectivity IssuesOracle Connectivity Issues
Solving Errors Reported By Users.Solving Errors Reported By Users.
Oracle internal errorsOracle internal errors
Database recovery after failuresDatabase recovery after failures
à
à Daily Daily Activities:Activities:
Database
Database > > MIS MIS -à -à MAIN MAIN à à Production Production DatabaseDatabase ACCTS:
ACCTS: Accounts Accounts DatabaseDatabase Wirewheel Database
Wirewheel Database Mis_bk
Mis_bk Database (Test Database (Test Database)Database) Standby Database
Standby Database
Daily Activities Daily Activities
1)
1) ChChececk ak all ll ththe e DaDatatababase se opopen en anand rd reaead wd wririte te momode de alall l 5 5 DaDatatababasese 2
2)) CChheecck k mmaaiill.... 3
3)) CChheecck bk bdduummp p ffrreeqquueennttllyy 4)
4) chchececk ak all ll tthe he v$v$sesesssision on , l, lococksks, v, v$a$actctiivevexx 5)
5) spspacace e alall l dadatatababase se frfrom om f: f: (R(Reqequiuirered d MoMore re ththan an 4G4GB)B)
Before leaving remove archive
Before leaving remove archive files in f drive: of every databases should be files in f drive: of every databases should be deleteddeleted (delete the oldest archive files) for space
Go to
Go to remote remote server lserver login to ogin to Acer Acer will will and pwd and pwd click alt click alt del ctdel ctl l adminstrator adminstrator <pwd><pwd> go
go to to c: c: drive drive select select folder folder export_backup export_backup . . Change Change the the date date -> -> controlpannel->controlpannel-> shcudled.
shcudled. Go
Go to to remote remote server server login login to to commwill commwill and and pwd pwd click click alt alt del del ctl ctl adminstrator adminstrator <pwd><pwd> go
go to to c: c: dirve dirve select select folder folder export_backup export_backup . . Change Change the the date date -> -> controlpannel->controlpannel-> shcudled.
shcudled. Go
Go to to remote remote server server login login to to wirewill wirewill and and pwd pwd click click alt alt del del ctl ctl adminstrator adminstrator <pwd> <pwd> gogo to
to E: E: dirve dirve select select folder folder export_backup export_backup . . change change the the date date -> -> controlpannel-> controlpannel-> shcudled.shcudled. From
From e: e: drive drive copy copy to to local local backup area.backup area. Tape backup: Tape backup: Ø Ø nnttbbaacckkuupp Ø Ø cclliicck k bbaacckkuupp Ø
Ø sselelecect mt medediia à < a à < TTAPAPE NE NAAMEME> <> <EXEXAAMPMPLLE : 2E : 2404040406 B6 BACACKKUPUP>> Ø
Ø GO GO TO TO BBACACKUKUP FP FOLOLDEDER - R - SESELELECT CT DADATTE OE OF DF DUMUMP FP FILILE AE AND ND LOLOGG FILE.
FILE. Ø
Ø GIGIVE VE THTHE NE NAMAME EE EXXP_P_MIMIS_S_ACACT_T_WWW_W_<D<DATATE OE OF BF BACACKUKUP> P> FOFOR R EXAMPLE <280406>
EXAMPLE <280406>
Scripts: Scripts:
@active; - to check the
@active; - to check the users are activeusers are active @block - to check any lock has occured @block - to check any lock has occured @sid - to check for sid
@sid - to check for sid @hash - to check which
@hash - to check which sql stmt is runningsql stmt is running @lock - to check for deadlock
@lock - to check for deadlock
@lock_ob - to check which user locked the table @lock_ob - to check which user locked the table @ts - to check the tablespace
@ts - to check the tablespace free detailsfree details
@rollback - to shrink the rollback if extents reach
@rollback - to shrink the rollback if extents reach max_extentsmax_extents @rollstat - to check weather to shrink or n
@rollstat - to check weather to shrink or notot @buffhit - to check the buffer hit ratio
@buffhit - to check the buffer hit ratio @loghit - to check the log hit ratio @loghit - to check the log hit ratio @ddhit - to check the
@ddhit - to check the data dictionary hit ratiodata dictionary hit ratio @lockwait - who has locked an
@lockwait - who has locked and who is waitingd who is waiting @hwm - to check high water mark
@hwm - to check high water mark @active - to check the active for session @active - to check the active for session @temp - to check for sorting
@temp - to check for sorting @temp1 - to check full details abo
@temp1 - to check full details about temp spaceut temp space @cnt - to count the active and inactive sessions. @cnt - to count the active and inactive sessions.
@sessions - to check inactive sessions having more than 3 sessions @sessions - to check inactive sessions having more than 3 sessions @sysstat - to tune the database
@sysstat - to tune the database @alz - to analyze the table @alz - to analyze the table
@chkpt - to check when the last checkpoint has occurred @chkpt - to check when the last checkpoint has occurred
@time - to check the p
@time - to check the particular os user who is inactivearticular os user who is inactive @tgrow - to check for table growth.
@tgrow - to check for table growth. @igrow - to check for index growth. @igrow - to check for index growth. @idle - to check for users are
@idle - to check for users are in idle statein idle state @privs - to check for object privilege @privs - to check for object privilege
@user - to check how much mb users are using @user - to check how much mb users are using @rsql - to check current sql for particular rollback
@rsql - to check current sql for particular rollback segmentsegment 1. @active; - to check the users are active
1. @active; - to check the users are active Script:
Script:
Select LPAD (SID, 4) SID, SERIAL#, username, RPAD (t.start_time, 18) Select LPAD (SID, 4) SID, SERIAL#, username, RPAD (t.start_time, 18) "START_TIME", r.name, t.used_ublk "RBS BLKS",
"START_TIME", r.name, t.used_ublk "RBS BLKS",
2 decode (t.space, 'YES','SPACE TX', DECODE (T.RECURSIVE,'YES','RECURSIVE 2 decode (t.space, 'YES','SPACE TX', DECODE (T.RECURSIVE,'YES','RECURSIVE TX',
TX',
3 DECODE (T.NOUNDO, 'YES', 'NO UNDO TX',
3 DECODE (T.NOUNDO, 'YES', 'NO UNDO TX', T.STATUS)T.STATUS) 4)) STATUS, terminal, osuser
4)) STATUS, terminal, osuser
5 FROM SYS.V$TRANSACTION T, SYS.V$ROLLNAME R, SYS.V$SESSIONS 6 5 FROM SYS.V$TRANSACTION T, SYS.V$ROLLNAME R, SYS.V$SESSIONS 6 WHERE T.XIDUSN = R.USN
WHERE T.XIDUSN = R.USN
7 AND T.SES_ADDR = S.SADDR 7 AND T.SES_ADDR = S.SADDR 8* ORDER BY SID
8* ORDER BY SID
Example: http://www.orafaq.com/wiki/SQL_FAQ Example: http://www.orafaq.com/wiki/SQL_FAQ
SERIAL# USERNAME START_TIME NAME RBS BLKS STATUS TERMINAL SERIAL# USERNAME START_TIME NAME RBS BLKS STATUS TERMINAL OSUSER
OSUSER
---- --- - - - --- --- ---- --- - - - --- ---
---7
7 14644 14644 TP TP 03/02/07 03/02/07 08:18:41 08:18:41 RBS10 RBS10 1 1 ACTIVE ACTIVE PC400 PC400 manimani 9
9 53685 53685 DS DS 03/02/07 03/02/07 08:02:18 08:02:18 RBS07 RBS07 1 1 ACTIVE ACTIVE PC100 PC100 internalinternal 18
18 3944 3944 MIS MIS 03/02/07 03/02/07 07:56:19 07:56:19 RBS10 RBS10 1 1 ACTIVE ACTIVE PC406 PC406 ravianandravianand 30
30 7718 7718 TP TP 03/02/07 03/02/07 07:36:48 07:36:48 RBS09 RBS09 1 1 ACTIVE ACTIVE PC368 PC368 parasuramparasuram 34
34 7177 7177 PE PE 03/02/07 03/02/07 07:42:43 07:42:43 RBS01 RBS01 1 1 ACTIVE ACTIVE PC431 PC431 senthilsenthil 39 6655
39 6655 PE PE 03/02/07 03/02/07 08:20:03 08:20:03 RBS08 RBS08 1 1 ACTIVE ACTIVE PC425 PC425 lpcmlpcm 41
41 29751 29751 PE PE 03/02/07 03/02/07 01:01:05 01:01:05 RBS09 RBS09 1 1 ACTIVE ACTIVE PC528 PC528 storesstores 44 6322
44 6322 PE PE 03/01/07 03/01/07 14:14:46 14:14:46 RBS03 RBS03 1 1 ACTIVE ACTIVE PC297 PC297 pdpd 49
49 7207 7207 QLTY QLTY 03/02/07 03/02/07 08:13:19 08:13:19 RBS01 RBS01 1 1 ACTIVE ACTIVE PCLP PCLP lpsundarelpsundare 51
51 4093 4093 MISQ MISQ 03/02/07 03/02/07 08:06:31 08:06:31 RBS06 RBS06 1 1 ACTIVE ACTIVE PC516 PC516 emrimemrim 53 5398
53 5398 TS TS 03/02/07 03/02/07 07:17:19 07:17:19 RBS04 RBS04 2 2 ACTIVE ACTIVE PC484 PC484 tooltool 69 7077
69 7077 DS DS 03/02/07 03/02/07 07:23:10 07:23:10 RBS05 RBS05 2 2 ACTIVE ACTIVE PCAS PCAS subramanisubramani 2. @block - to check any lock has occurred
2. @block - to check any lock has occurred Script:
Script:
select * from v$lock WHERE LMODE=6 and
Example: Example:
No rows selected (No problem but any lock is occur then kill the session use sid script to No rows selected (No problem but any lock is occur then kill the session use sid script to
find the sid no) find the sid no) SQL> @l1 SQL> @l1 ADDR
ADDR KADDR KADDR SID SID TY TY ID1 ID1 ID2 ID2 LMODE LMODE REQUEST REQUEST CTIMECTIME --- --- --
--- --- -- ---616970C8
616970C8 61697194 61697194 363 363 TX TX 524313 524313 557 557 6 6 0 0 28122812 3. @sid - to check
3. @sid - to check for sidfor sid Script:
Script:
Select * from v$session where sid=&sid Select * from v$session where sid=&sid Example:
Example:
Enter value for sid: 376 Enter value for sid: 376 SADDR
SADDR SID SID SERIAL# SERIAL# AUDSID AUDSID PADDR PADDR USER# USER# USERNAME USERNAME COMMANDCOMMAND OWNERID TADDR
OWNERID TADDR
--- --- -- -- -- -- -- --- --- -- -- -- -- -- ---342AC098 376
342AC098 376 1473 1473 47366 47366 341C327C 341C327C 36 36 PE PE 0 0 2147483644 2147483644 34B20CA834B20CA8 Solution:
Solution:
Alter system kill session'376, 1473'; Alter system kill session'376, 1473'; 4. @hash - to check
4. @hash - to check which sql stmt is runningwhich sql stmt is running Script:
Script:
Select sql_text from v$sqlarea where hash_value=&hash_value Select sql_text from v$sqlarea where hash_value=&hash_value Example:
Example: mis>@hash mis>@hash
Enter value for hash_value: 824065089 Enter value for hash_value: 824065089 SQL_TEXT
SQL_TEXT
---Select rowid, day1, day2 from tampr.weekhol Select rowid, day1, day2 from tampr.weekhol 5. @lock - to check for deadlock
5. @lock - to check for deadlock Script:
Script:
Select * from v$lock WHERE
Example: Example: ADDR
ADDR KADDR KADDR SID SID TY TY ID1 ID1 ID2 ID2 LMODE LMODE REQUEST REQUEST CTIME CTIME BLOCK BLOCK -- -- --- -- ---- ---- ---- ---- -- -- --- -- ---- ---- ---- ---- ---34379768 34379768 34379778 34379778 3 3 RT RT 1 1 0 0 6 6 0 0 0 0 00 61699A5C 61699A5C 61699B28 61699B28 7 7 TX TX 786432 786432 1785 1785 6 6 0 0 3701 3701 00 61699A5C 61699A5C 61699B28 61699B28 9 9 TX TX 589871 589871 386 386 6 6 0 0 4684 4684 00 61699A5C 61699A5C 61699B28 61699B28 16 16 TX TX 524308 524308 556 556 6 6 0 0 1596 1596 00 61699A5C 61699A5C 61699B28 61699B28 18 18 TX TX 655438 655438 451 451 6 6 0 0 2098 2098 00 61699A5C 61699A5C 61699B28 61699B28 23 23 TX TX 786497 786497 1781 1781 6 6 0 0 236 236 00 61699A5C 61699A5C 61699B28 61699B28 30 30 TX TX 720928 720928 846 846 6 6 0 0 6214 6214 00 61699A5C 61699A5C 61699B28 61699B28 34 34 TX TX 196695 196695 461 461 6 6 0 0 5859 5859 00 6. @lock_ob - to check which user locked the table
6. @lock_ob - to check which user locked the table Script:
Script:
Select s.sid, s.serial#, s.username, Select s.sid, s.serial#, s.username,
decode (l.type,'TM','Table Lock','TX','Row Lock', Null) aa, decode (l.type,'TM','Table Lock','TX','Row Lock', Null) aa, o.owner,o.object_type,o.object_NAME
o.owner,o.object_type,o.object_NAME
from v$session s, v$lock l, dba_objects o where from v$session s, v$lock l, dba_objects o where
s.sid=l.sid and o.object_id=l.id1 and s.username is not s.sid=l.sid and o.object_id=l.id1 and s.username is not nullnull Example:
Example:
mis>@lock_ob mis>@lock_ob SID
SID SERIAL# SERIAL# USERNAME USERNAME Lock Lock Level OWLevel OWNER NER OBJECT_TYPE OBJECT_TYPE OBJECT_NAMEOBJECT_NAME --- --- --- --- --- ---
--- --- --- --- --- --- ---340 1583
340 1583 ES ES Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 343
343 889 889 DS DS Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 344
344 968 968 ES ES Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 346
346 1315 1315 MIS MIS Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 349 2203
349 2203 PE PE Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 360
360 2219 2219 PUR PUR Table Table Lock Lock WILDBA WILDBA TABLE TABLE PC_ORDER_MASTPC_ORDER_MAST 360
360 2219 2219 PUR PUR Table Table Lock Lock WILDBA WILDBA TABLE TABLE PC_PURCHASE_REQNPC_PURCHASE_REQN 361 1077
361 1077 GS GS Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 367
367 727 727 PE PE Table Table Lock Lock WILDBA WILDBA TABLE TABLE WIL_MENU_USER WIL_MENU_USER 7. @ts - to check the tablespace free details
7. @ts - to check the tablespace free details Script:
Script:
select tablespace_name, sum(MB) "TOTAL BYTES (MB)", round(sum(FREE),2) select tablespace_name, sum(MB) "TOTAL BYTES (MB)", round(sum(FREE),2)
"FREE BYTES (MB)", round(sum(free)*100/sum(mb),2) "FREE PERCENTAGE (MB)" "FREE BYTES (MB)", round(sum(free)*100/sum(mb),2) "FREE PERCENTAGE (MB)" from
from ((
select tablespace_name,0 MB, sum(bytes)/1048576 FREE from dba_free_space select tablespace_name,0 MB, sum(bytes)/1048576 FREE from dba_free_space
group by tablespace_name group by tablespace_name union all
union all
select tablespace_name,sum(bytes)/1048576 MB, 0 FREE from sys.dba_data_files select tablespace_name,sum(bytes)/1048576 MB, 0 FREE from sys.dba_data_files group by tablespace_name group by tablespace_name )) group by tablespace_name group by tablespace_name Example: Example: mis>@ts mis>@ts TABLESPACE_NAME
TABLESPACE_NAME TOTAL BYTES (MB) TOTAL BYTES (MB) FREE BYTES (MB) FREE BYTES (MB) FREEFREE PERCENTAGE (MB) PERCENTAGE (MB) --- --- --- --- --- --- ---INDEX_DATA INDEX_DATA 12900 12900 1843.32 1843.32 14.2914.29 ROLLBACK_DATA ROLLBACK_DATA 2600 2600 602.18 602.18 23.1623.16 SYSTEM SYSTEM 2000 2000 1787.86 1787.86 89.3989.39 TAMPR TAMPR 2800 2800 508.23 508.23 18.1518.15 USER_DATA USER_DATA 800 800 528.74 528.74 66.0966.09 WILDATA WILDATA 9600 9600 2427.04 2427.04 25.2825.28 8. @rollback - to shrink the rollback
8. @rollback - to shrink the rollback if extents reach max_extentsif extents reach max_extents Script:
Script:
alter rollback segment SYS_RBS shrink to alter rollback segment SYS_RBS shrink to 10m;10m; alter rollback segment RBS01 shrink to 10m; alter rollback segment RBS01 shrink to 10m; alter rollback segment RBS02 shrink to 10m; alter rollback segment RBS02 shrink to 10m; alter rollback segment RBS03 shrink to 10m; alter rollback segment RBS03 shrink to 10m; alter rollback segment RBS04 shrink to 10m; alter rollback segment RBS04 shrink to 10m; alter rollback segment RBS05 shrink to 10m; alter rollback segment RBS05 shrink to 10m; alter rollback segment RBS06 shrink to 10m; alter rollback segment RBS06 shrink to 10m; alter rollback segment RBS07 shrink to 10m; alter rollback segment RBS07 shrink to 10m; alter rollback segment RBS08 shrink to 10m; alter rollback segment RBS08 shrink to 10m; alter rollback segment RBS09 shrink to 10m; alter rollback segment RBS09 shrink to 10m; alter rollback segment RBS10 shrink to 10m; alter rollback segment RBS10 shrink to 10m; 9. @rollstat - to check weather to shrink or not 9. @rollstat - to check weather to shrink or not Script:
Script:
Select * from v$rollstat; Select * from v$rollstat; Example:
Example: mis>@rollstat mis>@rollstat
USN
USN EXTENTS EXTENTS RSSIZE RSSIZE WRITES WRITES XACTS XACTS GETS GETS WAITS WAITS OPTSIZEOPTSIZE HWMSIZE
HWMSIZE
---0 61 0 61 4988928 4988928 4796 4796 0 0 2580 2580 0 0 49889284988928 1 1821 1 1821 59695104 59695104 122958038 122958038 7 7 363608 363608 8 8 892575744892575744 3 3 256 256 10469376 10469376 135484906 135484906 7 7 481338 481338 3 3 156467200156467200 4 4 286 286 11689984 11689984 131723198 131723198 6 6 875702 875702 0 0 119775232119775232 5 5655 5 5655 231571456 231571456 154230310 154230310 8 8 1099711 1099711 0 0 648863744648863744 6 6105 6 6105 250003456 250003456 298364090 298364090 8 8 2607550 2607550 2 2 250003456250003456 7 7 3752 3752 153640960 153640960 49896784 49896784 8 8 35719 35719 1 1 153640960153640960 10. @buffhit - to check
10. @buffhit - to check the buffer hit ratiothe buffer hit ratio Script:
Script:
select (1-(pr.value/(dbg.value+cg.value)))*100 "Buffer Hit Raio" select (1-(pr.value/(dbg.value+cg.value)))*100 "Buffer Hit Raio" from v$sysstat pr, v$sysstat dbg, v$sysstat cg
from v$sysstat pr, v$sysstat dbg, v$sysstat cg where pr.name = 'physical reads'
where pr.name = 'physical reads' and dbg.name = 'db block gets' and dbg.name = 'db block gets' and cg.name = 'consistent gets' and cg.name = 'consistent gets' Example:
Example: mis>@buffhit mis>@buffhit Buffer
Buffer Hit Hit Raio Raio 90.079836390.0798363 11. @loghit - to check
11. @loghit - to check the log hit ratiothe log hit ratio Script:
Script:
select rbar.name , rbar.value , re.name , re.value , (rbar.value*100)/re.value "ratio" select rbar.name , rbar.value , re.name , re.value , (rbar.value*100)/re.value "ratio" from v$sysstat rbar , v$sysstat re where rbar.name = 'redo buffer allocation retries' and from v$sysstat rbar , v$sysstat re where rbar.name = 'redo buffer allocation retries' and re.name = 'redo entries'
re.name = 'redo entries' Example:
Example: mis>@loghit mis>@loghit NAME
NAME VALUE NAME VALUE NAME VALUE VALUE ratioratio
---redo
redo buffe buffe 750 750 redo redo entri entri 18693284 18693284 .004012136.004012136 r
r allocati allocati eses on retries
on retries
12. @hwm - to check high water mark 12. @hwm - to check high water mark Script:
Script:
select a.owner, a.table_name, b.blocks allocblks, a.blocks usedblks, select a.owner, a.table_name, b.blocks allocblks, a.blocks usedblks, (b.blocks-a.empty_blocks-1) highwtr
(b.blocks-a.empty_blocks-1) highwtr from dba_tables a, dba_segments b from dba_tables a, dba_segments b where a.table_name=b.segment_name where a.table_name=b.segment_name and a.owner=b.owner
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM') and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1) and a.blocks <> (b.blocks-a.empty_blocks-1) and a.owner like upper('&owner')||'%'
order by 1,2 order by 1,2 Example: Example: OWNER
OWNER TABLE_NAME TABLE_NAME ALLOCBLKS ALLOCBLKS USEDBLKS USEDBLKS HIGHWTR HIGHWTR ---TAMPR
TAMPR WAGES_AB WAGES_AB 574 574 759 759 528528 TAMPR
TAMPR WG_OT WG_OT 14685 14685 12204 12204 1460114601 TAMPR
TAMPR WG_OT WG_OT 5229 5229 12204 12204 51455145 WILDBA
WILDBA CUSTOMER CUSTOMER 105 105 89 89 104104 WILDBA
WILDBA ES_SHIPMENT_DTLS ES_SHIPMENT_DTLS 272 272 209 209 271271 WILDBA
WILDBA ITM_CODE ITM_CODE 535 535 3 3 523523 WILDBA
WILDBA PC_ORDER_MAST_MN PC_ORDER_MAST_MN 667 667 604 604 666666 WILDBA
WILDBA PE_WO_ADD PE_WO_ADD 91 91 61 61 9090 WILDBA
WILDBA SC_TIMEOFFICE SC_TIMEOFFICE 8 8 1 1 -41-41 WILDBA
WILDBA TR_TOOL_MAST TR_TOOL_MAST 2084 2084 1988 1988 20832083 WILDBA
WILDBA WIL_MENU_USER WIL_MENU_USER 882 882 586 586 796796
13. @active - to check the active for session 13. @active - to check the active for session SELECT s.sid,
SELECT s.sid,
s.serial#, s.username, s.osuser,i.consistent_gets Cgets,i.physical_reads phy_rds, s.serial#, s.username, s.osuser,i.consistent_gets Cgets,i.physical_reads phy_rds, ROUND(100 - ( (I.PHYSICAL_READS * 100) / decode((I.CONSISTENT_GETS + ROUND(100 - ( (I.PHYSICAL_READS * 100) / decode((I.CONSISTENT_GETS +
I.BLOCK_GETS),0, 1,(I.CONSISTENT_GETS + I.BLOCK_GETS) )), 2) "Hit I.BLOCK_GETS),0, 1,(I.CONSISTENT_GETS + I.BLOCK_GETS) )), 2) "Hit Rate", Rate", s.machine "Machine", s.machine "Machine", s.program "Program", s.program "Program",
to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time", to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time", w.wait_time, w.wait_time, NVL(w.seconds_in_wait,0) wait_in_secs, NVL(w.seconds_in_wait,0) wait_in_secs, w.state, w.state, s.last_call_et s.last_call_et FROM
FROM v$session v$session s,s, v$sess_io i, v$sess_io i,
v$session_wait w v$session_wait w WHERE
WHERE s.sid s.sid = = i.sidi.sid AND
AND s.sid s.sid = = w.sid w.sid (+)(+) AND
AND 'SQL*Net 'SQL*Net message message from from client' client' = = w.event w.event (+)(+) AND
AND s.osuser s.osuser is is not not nullnull AND
AND s.username s.username is is not not nullnull AND
AND s.status s.status = = 'ACTIVE''ACTIVE' ORDER
ORDER BY 6 BY 6 DESC, 11 DESC, 11 DESCDESC Example:
Example: mis>@active mis>@active
SID
SID SERIAL# SERIAL# USERNAME USERNAME OSUSER OSUSER CGETS PHY_RDS CGETS PHY_RDS Hit Hit RateRate Machine Program Machine Program --- --- --- --- --- --- ---43 43 4344 4344 MP MP pch pch 7853106 7853106 37307 37307 99.52 DOMAIN\PC299.52 DOMAIN\PC2 28
28 17907 17907 SYSTEM SYSTEM radiant radiant 147370 147370 987 987 99.33 99.33 DOMAIN\PC3 DOMAIN\PC3 SQLPLUSW.ESQLPLUSW.E 14. @temp - to check
14. @temp - to check for sortingfor sorting Script:
Script:
select s.username, u."USER",s.OSUSER "osuser", u.tablespace, u.contents, u.extents, select s.username, u."USER",s.OSUSER "osuser", u.tablespace, u.contents, u.extents, u.blocks
u.blocks from from sys.v_$session sys.v_$session s, s, sys.v_$sort_usage sys.v_$sort_usage u u where where s.saddr s.saddr = = u.session_addr u.session_addr Example:
Example: mis>@temp mis>@temp USERNAME
USERNAME USER USER OSUSER OSUSER TABLESPACETABLESPACE ---
--- ---DS
DS SYSTEM SYSTEM rskumar rskumar TEMPORARY_DATATEMPORARY_DATA 15. @temp1 - to check full details abt temp space
15. @temp1 - to check full details abt temp space Script:
Script:
select s.osuser, s.process, s.username, s.serial#, select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size,U.EXTENTS,U.BLOCKS sum(u.blocks)*vp.value/1024 sort_size,U.EXTENTS,U.BLOCKS from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp where
where s.saddr s.saddr = = u.session_addr u.session_addr and
and vp.name vp.name = = 'db_block_size''db_block_size' group
group by s.osuser, by s.osuser, s.process, s.uss.process, s.username, s.serial#, ername, s.serial#, vp.value,U.EXTENTS,U.BLOCKSvp.value,U.EXTENTS,U.BLOCKS Example:
Example: mis>@temp1 mis>@temp1 OSUSER
OSUSER PROCESS PROCESS USERNAME USERNAME SERIAL# SERIAL# SORT_SIZE SORT_SIZE EXTENTS EXTENTS BLOCKSBLOCKS --- --- ---
--- --- --- ---rskumar
rskumar 1328:1780 1328:1780 DS DS 10272 10272 1024 1024 1 1 128128 16. @cnt - to count the active and inactive sessions.
16. @cnt - to count the active and inactive sessions. Script:
Script:
SELECT COUNT (*) "TOTAL"FROM v$session SELECT COUNT (*) "TOTAL"FROM v$session Example: Example: mis>@cnt mis>@cnt TOTAL 338 TOTAL 338
17.@sessions - to check inactive sessions having more
Script: Script:
SELECT OSUSER,USERNAME,COUNT(*) "INACTIVE" FROM V$SESSION SELECT OSUSER,USERNAME,COUNT(*) "INACTIVE" FROM V$SESSION WHERE STATUS ='INACTIVE' AND
WHERE STATUS ='INACTIVE' AND USERNAME ='CST' USERNAME ='CST' OR USERNAME='DS' OR USERNAME='DS' OR USERNAME='ES' OR USERNAME='ES' OR USERNAME='FASAL' OR USERNAME='FASAL' OR USERNAME='GS' OR USERNAME='GS' OR USERNAME='MFG' OR USERNAME='MFG' OR USERNAME='MISQRY' OR USERNAME='MISQRY' OR USERNAME='MISREAD' OR USERNAME='MISREAD' OR USERNAME='MP' OR USERNAME='MP' OR USERNAME='NEW_INCENTIVE' OR USERNAME='NEW_INCENTIVE' OR USERNAME='PD' OR USERNAME='PD' OR USERNAME='PE' OR USERNAME='PE' OR USERNAME='PLE' OR USERNAME='PLE' OR USERNAME='PUR' OR USERNAME='PUR' OR USERNAME='QLTY' OR USERNAME='QLTY' OR USERNAME='READMIS' OR USERNAME='READMIS' OR USERNAME='RMS' OR USERNAME='RMS' OR USERNAME='TAMPR' OR USERNAME='TAMPR' OR USERNAME='TD' OR USERNAME='TD' OR USERNAME='TP' OR USERNAME='TP' OR USERNAME='TR' OR USERNAME='TR' OR USERNAME='TS' OR USERNAME='TS' OR USERNAME='KAROMI' OR USERNAME='KAROMI' OR USERNAME='WILCDA' OR USERNAME='WILCDA'
OR USERNAME='WILDBA' GROUP BY OSUSER,USERNAME HAVING OR USERNAME='WILDBA' GROUP BY OSUSER,USERNAME HAVING COUNT(*) > 3 order by count(*);
COUNT(*) > 3 order by count(*); Example:
Example: OSUSER
OSUSER USERNAME USERNAME INACTIVEINACTIVE ---- ---
---- --- ---toolstores
toolstores MISQRY MISQRY 66 wwashok
wwashok WILDBA WILDBA 77 rmstores rmstores RMS RMS 99 stores stores GS GS 1111 toolstores toolstores TS TS 1111 wwstores
wwstores WILDBA WILDBA 2020 18.@sysstat - to tune the database
18.@sysstat - to tune the database Script:
Script:
SELECT a.name,b.value SELECT a.name,b.value FROM
FROM v$statname v$statname a,a, v$sysstat b
WHERE
WHERE a.statistic# a.statistic# = = b.statistic#b.statistic# AND
AND UPPER(a.name) UPPER(a.name) LIKE LIKE '%' '%' || || UPPER('&statname') UPPER('&statname') || || '%''%' Example:
Example: mis>@sysstat mis>@sysstat
Enter value for statname: PX local messages sent Enter value for statname: PX local messages sent NAME
NAME VALUEVALUE ---PX
PX local local messages messages sent sent 182182 19.@alz - to analyze the table
19.@alz - to analyze the table Script:
Script:
select owner,table_name,blocks,empty_blocks,num_rows from dba_tables where select owner,table_name,blocks,empty_blocks,num_rows from dba_tables where owner=upper('&owner') and table_name=upper('&table_name');
owner=upper('&owner') and table_name=upper('&table_name'); Example:
Example: mis>@alz mis>@alz
Enter value for owner: wildba Enter value for owner: wildba
Enter value for table_name: ds_invoice Enter value for table_name: ds_invoice OWNER
OWNER TABLE_NAME TABLE_NAME BLOCKS BLOCKS EMPTY_BLOCKS EMPTY_BLOCKS NUM_ROWSNUM_ROWS -- -- ---WILDBA
WILDBA DS_INVOICE DS_INVOICE 8684 8684 430 430 192255192255 20.@chkpt - t0 check when the last checkpoint has occurred
20.@chkpt - t0 check when the last checkpoint has occurred Script:
Script:
select name,checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY select name,checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header;
HH24:MI:SS') from v$datafile_header; Example:
Example: NAME
NAME CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_ CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_ --- --- --- --- ---E:\MIS\DATA\INDEX_DATA01.DBF E:\MIS\DATA\INDEX_DATA01.DBF 200965067 200965067 02.03.2007 02.03.2007 13:11:3913:11:39 E:\MIS\DATA\INDEX_DATA02.DBF E:\MIS\DATA\INDEX_DATA02.DBF 200965067 200965067 02.03.2007 02.03.2007 13:11:3913:11:39 E:\MIS\DATA\INDEX_DATA03.DBF E:\MIS\DATA\INDEX_DATA03.DBF 200965067 200965067 02.03.2007 02.03.2007 13:11:3913:11:39 E:\MIS\DATA\INDEX_DATA04.DBF E:\MIS\DATA\INDEX_DATA04.DBF 200965067 200965067 02.03.2007 02.03.2007 13:11:3913:11:39 E:\MIS\DATA\INDEX_DATA05.DBF E:\MIS\DATA\INDEX_DATA05.DBF 200965067 200965067 02.03.2007 02.03.2007 13:11:3913:11:39 E:\MIS\DATA\INDEX_DATA06.DBF E:\MIS\DATA\INDEX_DATA06.DBF 200965067 200965067 02.03.2007 02.03.2007 13:11:3913:11:39 21.@time - to check the
21.@time - to check the particular os user who is inactiveparticular os user who is inactive Script:
Script:
ELECT LPAD(SID,4) SID,LPAD(SERIAL#,7) SERIAL#, ELECT LPAD(SID,4) SID,LPAD(SERIAL#,7) SERIAL#,
USERNAME, RPAD(OSUSER,12) OSUSER, LPAD(COMMAND,7) USERNAME, RPAD(OSUSER,12) OSUSER, LPAD(COMMAND,7) COMMAND,STATUS,MACHINE,
COMMAND,STATUS,MACHINE,
RPAD(TO_CHAR(LOGON_TIME,'DD-MON-YY:HH:
RPAD(TO_CHAR(LOGON_TIME,'DD-MON-YY:HH:MI:SS-AM'),23) MI:SS-AM'),23) LOGIN_TIMELOGIN_TIME FROM V$SESSION WHERE USERNAME IS NOT NULL AND OSUSER like
FROM V$SESSION WHERE USERNAME IS NOT NULL AND OSUSER like '&OSUSER%' AND STATUS NOT IN ('KILLED') order by LOGON_TIME; '&OSUSER%' AND STATUS NOT IN ('KILLED') order by LOGON_TIME; Example:
Example: mis>@time mis>@time
Enter value for osuser: apa Enter value for osuser: apa SID
SID SERIAL# SERIAL# USERNAME USERNAME OSUSER OSUSER COMMAND COMMAND STATUS STATUS MACHINEMACHINE ---- --- -- -- --- ---
---- --- -- -- --- --- ---58
58 14854 14854 TS TS aparajita aparajita 0 0 INACTIVE INACTIVE DOMAIN\PC447DOMAIN\PC447 227
227 4382 4382 RMS RMS aparajita aparajita 0 0 INACTIVE INACTIVE DOMAIN\PC447DOMAIN\PC447 310
310 2514 2514 GS GS aparajita aparajita 0 0 INACTIVE INACTIVE DOMAIN\PC280DOMAIN\PC280 110
110 14289 14289 GS GS aparajita aparajita 0 0 INACTIVE INACTIVE DOMAIN\PC280DOMAIN\PC280
22.@tgrow - to check for table growth. 22.@tgrow - to check for table growth. Script:
Script:
select /*+ index */ table_name,(blocks*8192)/(1024*1024) "GROWTH" from dba_tables select /*+ index */ table_name,(blocks*8192)/(1024*1024) "GROWTH" from dba_tables where owner='WILDBA' AND (blocks*8192)/(1024*1024) > 100 order by
where owner='WILDBA' AND (blocks*8192)/(1024*1024) > 100 order by (blocks*8192)/(1024*1024); (blocks*8192)/(1024*1024); Example: Example: mis>@tgrow mis>@tgrow TABLE_NAME GROWTH TABLE_NAME GROWTH --- --- ---PERD_TRANS_AUDIT 115.46 PERD_TRANS_AUDIT 115.46 ISS_ACCP_DETL_GS 214.45 ISS_ACCP_DETL_GS 214.45 PERD_TRANS 367.06 PERD_TRANS 367.06 CONSUMP_TABLE_TMP 504.56 CONSUMP_TABLE_TMP 504.56 PC_ORDER_DETAIL 953.05 PC_ORDER_DETAIL 953.05 23.@igrow - to check for index growth. 23.@igrow - to check for index growth. Script:
Script:
select TABLE_NAME,index_name,(leaf_blocks*8192)/(1024*1024) "GROWTH" from select TABLE_NAME,index_name,(leaf_blocks*8192)/(1024*1024) "GROWTH" from dba_indexes where owner='WILDBA' AND (leaf_blocks*8192)/(1024*1024)> 100 order dba_indexes where owner='WILDBA' AND (leaf_blocks*8192)/(1024*1024)> 100 order by LEAF_BLOCKS;
by LEAF_BLOCKS; Example:
Example:
TABLE_NAME
TABLE_NAME INDEX_NAME INDEX_NAME GROWTHGROWTH --- ---
---PC_ORDER_DETAIL
PC_ORDER_DETAIL PO_DTL_IND PO_DTL_IND 212.38212.38 PC_ORDER_DETAIL
PC_ORDER_DETAIL IND_FOR_GIN_UPD_3 IND_FOR_GIN_UPD_3 464.77464.77 PC_ORDER_DETAIL
PC_ORDER_DETAIL PK_PC_ORDER_DETAIL PK_PC_ORDER_DETAIL 633.80633.80 24.@idle - to check for users are
24.@idle - to check for users are in idle statein idle state Script:
Script:
select sid,serial#,osuser,username,trunc select sid,serial#,osuser,username,trunc
(last_call_et/3600,2)||' hr' last_call_et from V$session where (last_call_et/3600,2)||' hr' last_call_et from V$session where last_call_et > 3600 and username is not null;
last_call_et > 3600 and username is not null; Example:
Example: SID
SID SERIAL# SERIAL# OSUSER OSUSER USERNAME USERNAME LAST_CALL_ETLAST_CALL_ET --- --- -- --
--- --- -- -- ---370
370 1188 1188 ple ple MISQRY MISQRY 4.48 4.48 hr hr 371
371 7592 7592 psr psr TAMPR TAMPR 2.98 2.98 hr hr 372
372 558 558 radmin radmin MISQRY MISQRY 4.92 hr 4.92 hr 375 652
375 652 wwashok wwashok WILDBA WILDBA 2.23 2.23 hr hr 376
376 1580 1580 TOOLSTORES TOOLSTORES TD TD 2.42 2.42 hr hr 379
379 1339 1339 rbharathi rbharathi PUR PUR 1.36 1.36 hr hr 25.@privs - to check for object privilege 25.@privs - to check for object privilege Script:
Script:
SELECT * FROM DBA_TAB_PRIVS WHERE SELECT * FROM DBA_TAB_PRIVS WHERE
TABLE_NAME=upper('&TABLE_NAME') and owner='WILDBA'; TABLE_NAME=upper('&TABLE_NAME') and owner='WILDBA'; Example:
Example:
GRANTEE OWNER
GRANTEE OWNER TABLE_NAME GRTABLE_NAME GRANTOR PRIANTOR PRIVILEGEVILEGE RWH
RWH WILDBA WILDBA DS_INVOICE DS_INVOICE WILDBA SELEWILDBA SELECTCT RWH
RWH WILDBA WILDBA DS_INVOICE DS_INVOICE WILDBA UWILDBA UPDATEPDATE RTS
RTS WILDBA WILDBA DS_INVOICE DS_INVOICE WILDBA WILDBA DELETEDELETE RTS
RTS WILDBA WILDBA DS_INVOICE DS_INVOICE WILDBA IWILDBA INSERTNSERT RTS
RTS WILDBA WILDBA DS_INVOICE DS_INVOICE WILDBA WILDBA SELECTSELECT RTS
RTS WILDBA WILDBA DS_INVOICE DS_INVOICE WILDBA UPDATWILDBA UPDATEE 26.@user - to check how much mb users are using
26.@user - to check how much mb users are using Script: Script: select owner, select owner, sum(bytes)/1024/1024 user_space_usage_mb, sum(bytes)/1024/1024 user_space_usage_mb, sum(bytes)/ts.tot_si
sum(bytes)/ts.tot_size*100 ze*100 pct_usagepct_usage from
from dba_segments dba_segments s,s,
(select sum(bytes) tot_size (select sum(bytes) tot_size
from dba_segments from dba_segments where
where tablespace_name tablespace_name ='WILDATA') ='WILDATA') tsts where
where tablespace_name tablespace_name = = 'WILDATA''WILDATA' group by owner,ts.tot_size group by owner,ts.tot_size having sum(bytes) > 1024*1024 having sum(bytes) > 1024*1024 order order by by 22 Example: Example:
OWNER USER_SPACE_USAGE_MB PCT_USAGE OWNER USER_SPACE_USAGE_MB PCT_USAGE --- - --- - ---MP MP 1.06 1.06 .01.01 TSCST TSCST 3.08 3.08 .04.04 RMS RMS 3.40 3.40 .05.05 AIRSUS AIRSUS 3.43 3.43 .05.05 JVC JVC 5.45 5.45 .08.08 PLE PLE 5.60 5.60 .08.08 CST CST 15.20 15.20 .21.21 27.@rsql - to check current sql for p
27.@rsql - to check current sql for particular rollback segmentarticular rollback segment Script:
Script:
SELECT Y.SQL_TEXT, R.NAME, S.OSUSER, S.USERNAME, X.EXTENTS, SELECT Y.SQL_TEXT, R.NAME, S.OSUSER, S.USERNAME, X.EXTENTS, X.EXTENDS FROM SYS.V_$ROLLSTAT X, SYS.V_$ROLLNAME
X.EXTENDS FROM SYS.V_$ROLLSTAT X, SYS.V_$ROLLNAME
R,SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$SQLAREA Y R,SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$SQLAREA Y
WHERE s.sql_hash_value = y.hash_value(+) AND s.sql_address = y.address(+) AND WHERE s.sql_hash_value = y.hash_value(+) AND s.sql_address = y.address(+) AND t.addr = s.taddr(+) AND r.usn = x.usn(+) AND
t.addr = s.taddr(+) AND r.usn = x.usn(+) AND
r.usn = t.xidusn(+) AND S.USERNAME NOT IN ('SYS','SYSTEM') and r.usn = t.xidusn(+) AND S.USERNAME NOT IN ('SYS','SYSTEM') and r.name=upper('&name') r.name=upper('&name') ORDER BY r.usn; ORDER BY r.usn; Example: Example: mis>@rsql mis>@rsql USN NAME USN NAME --- --- ---0 SYSTEM 0 SYSTEM 1 SYS_RBS 1 SYS_RBS 3 RBS01 3 RBS01 4 RBS02 4 RBS02 5 RBS03 5 RBS03 6 RBS04 6 RBS04 7 RBS05 7 RBS05 8 RBS06 8 RBS06 9 RBS07 9 RBS07 10 RBS08 10 RBS08 11 RBS09 11 RBS09 12 RBS10 12 RBS10
12 rows selected. 12 rows selected.
Enter value for name: rbs01 Enter value for name: rbs01 SQL_TEXT
SQL_TEXT
--- NAME
NAME OSUSER OSUSER USERNAME USERNAME EXTENTS EXTENTS EXTENDSEXTENDS --- --- --- --- ---SELECT SELECT ROWID,RND_NOTE_TYPE,RND_NOTE_YEAR,RND_NOTE_NO,RND_DRAW_NO ROWID,RND_NOTE_TYPE,RND_NOTE_YEAR,RND_NOTE_NO,RND_DRAW_NO ,RND_SHEET_NO,RND_FROM_DTL_NO,RND_ ,RND_SHEET_NO,RND_FROM_DTL_NO,RND_ NO,RND_COMP_DRAWNO,RND_DESC,RND_ISSUE,RND_ISSUE_DATE,RND_T NO,RND_COMP_DRAWNO,RND_DESC,RND_ISSUE,RND_ISSUE_DATE,RND_T YPE,RND_STATUS,RND_TYPE_DESC,RND_STATUS_ YPE,RND_STATUS,RND_TYPE_DESC,RND_STATUS_
OM REL_NOTE_DETAIL WHERE RND_NOTE_TYPE=:1 AND OM REL_NOTE_DETAIL WHERE RND_NOTE_TYPE=:1 AND RND_NOTE_YEAR=:2 AND
RND_NOTE_YEAR=:2 AND RND_NOTE_NO=:3 RND_NOTE_NO=:3 order by order by rnd_nornd_no , rnd_note_year, rnd_note_no
, rnd_note_year, rnd_note_no RBS01
RBS01 asaravanan asaravanan TD TD 289 289 3333 28.@uga-display the session uga memory
28.@uga-display the session uga memory Script:
Script:
SELECT username, value || 'b
SELECT username, value || 'bytes' "Current UGA memory"ytes' "Current UGA memory" FROM v$session sess, v$sesstat stat, v$statname name FROM v$session sess, v$sesstat stat, v$statname name WHERE sess.sid = stat.sid
WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic# AND stat.statistic# = name.statistic# AND name.name = 'session uga memory'; AND name.name = 'session uga memory'; Example:
Example: USERNAME
USERNAME Current Current UGA UGA memorymemory --- --- ---PE 69928bytes PE 69928bytes WILDBA 29356bytes WILDBA 29356bytes PUR 47320bytes PUR 47320bytes MISQRY 47320bytes MISQRY 47320bytes TAMPR 25120bytes TAMPR 25120bytes MISQRY 20884bytes MISQRY 20884bytes TP 69924bytes TP 69924bytes
29.@tune-display statistics of the background processes 29.@tune-display statistics of the background processes Script: Script: select select event c1, event c1, total_waits c2, total_waits c2, time_waited time_waited / / 100 100 c3,c3, total_timeouts c4, total_timeouts c4, average_wait average_wait /100 /100 c5c5 from from
sys.v_$system_event sys.v_$system_event where where event not in ( event not in ( 'dispatcher timer', 'dispatcher timer', 'lock element cleanup', 'lock element cleanup', 'Null event',
'Null event',
'parallel query dequeue wait', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'parallel query idle wait - Slaves', 'pipe get', 'pipe get', 'PL/SQL lock timer', 'PL/SQL lock timer', 'pmon timer', 'pmon timer', 'rdbms ipc message', 'rdbms ipc message', 'slave wait', 'slave wait', 'smon timer', 'smon timer',
'SQL*Net break/reset to client', 'SQL*Net break/reset to client', 'SQL*Net message from client', 'SQL*Net message from client', 'SQL*Net message to client', 'SQL*Net message to client', 'SQL*Net more data to client', 'SQL*Net more data to client', 'virtual circuit status',
'virtual circuit status', 'WMON goes to sleep' 'WMON goes to sleep' ))
AND AND
event not like 'DFS%' event not like 'DFS%' and
and
event not like '%done%' event not like '%done%' and
and
event not like '%Idle%' event not like '%Idle%' AND
AND
event not like 'KXFX%' event not like 'KXFX%' order by order by c2 desc; c2 desc; Example; Example; Event
Event Total Total Seconds Seconds Total Total WaitWait Name
Name Waits Waits Waiting Waiting Timeouts (in secs)Timeouts (in secs) --- --- --- --- --- --- --- --- ---library
library cache cache lock lock 4 4 0 0 2 2 .000.000 buffer deadlock
buffer deadlock 3 3 0 0 3 3 .000.000 PX
PX qref qref latch latch 3 3 0 0 3 3 .000.000 SQL*Net
SQL*Net more more data data to to dblink dblink 2 2 0 0 0 0 .000.000 inactive
inactive session session 1 1 0 0 1 1 .000.000 29.@TSIZE-display
29.@TSIZE-display the the tablespace tablespace sizesize Script:
Script:
SET VERIFY OFF SET VERIFY OFF
COLUMN SEGMENT_NAME FORMAT A20 COLUMN SEGMENT_NAME FORMAT A20 SELECT
SELECT
SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,SUM(BYTES)/ SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,SUM(BYTES)/ (1024*1024) "MB",SUM(BLOCKS) "BLOCKS" FROM DBA_SEGMENTS WHERE (1024*1024) "MB",SUM(BLOCKS) "BLOCKS" FROM DBA_SEGMENTS WHERE owner=upper('&owner') and SEGMENT_NAME like upper('&SEGMENT_NAME%') owner=upper('&owner') and SEGMENT_NAME like upper('&SEGMENT_NAME%') GROUP BY SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME; GROUP BY SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME; Example: Example: mis>@tsize mis>@tsize
Enter value for owner: wildba Enter value for owner: wildba
Enter value for segment_name: IND_TOOL_OBS Enter value for segment_name: IND_TOOL_OBS SEGMENT_NAME SEGMENT
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME _TYPE TABLESPACE_NAME MB MB BLOCKSBLOCKS ---
--- ---IND_TOOL_OBS
IND_TOOL_OBS INDEX INDEX INDEX_DATA INDEX_DATA 1.8671875 1.8671875 239239
30. @idx-to check for how much indexes in the table 30. @idx-to check for how much indexes in the table
Script: Script: select select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS,STATUS,LA index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS,STATUS,LA ST_ANALYZED from dba_indexes where OWNER=UPPER('&OWNER') AND
ST_ANALYZED from dba_indexes where OWNER=UPPER('&OWNER') AND table_name=UPPER('&TABLE_NAME'); table_name=UPPER('&TABLE_NAME'); Example: Example: mis>@idx mis>@idx
Enter value for owner: wildba Enter value for owner: wildba
Enter value for table_name: ds_invoice Enter value for table_name: ds_invoice INDEX_NAME
INDEX_NAME BLEVEL BLEVEL LEAF_BLOCKS LEAF_BLOCKS DISTINCT_KEYSDISTINCT_KEYS NUM_ROWS STATUS
NUM_ROWS STATUS
-- --- ---- --- --- -- --- ---- --- --- ---CANBEDELETED
CANBEDELETED 1 1 330 330 2760 2760 124738 124738 VALIDVALID DS_INVOICE_IDX1
DS_INVOICE_IDX1 2 2 509 509 2988 2988 192255 192255 VALIDVALID DS_INV_ENTRDTTM
DS_INV_ENTRDTTM 1 1 260 260 72714 72714 98169 VALID98169 VALID GATEPASS_IND
GATEPASS_IND 0 0 1 1 6 6 6 6 VALIDVALID INVSDNOCANCELLED
INVSDNOCANCELLED 1 1 420 420 21263 21263 192255 192255 VALIDVALID PACKING_LIST_INVOICE_FK
PACKING_LIST_INVOICE_FK 2 2 705 705 192247 192247 192255 192255 VALIDVALID PK_DS_INVOICE
PK_DS_INVOICE 2 2 724 724 192255 192255 192255 192255 VALIDVALID SALES_TAX_CODES_INVOICE_FK
SALES_TAX_CODES_INVOICE_FK 1 1 375 375 30 30 191979 191979 VALIDVALID 31. @csql- to display user executing the sql statement
Script: Script:
SELECT osuser, username, sql_text from v$session a, v$sqltext SELECT osuser, username, sql_text from v$session a, v$sqltext bb where a.sql_address =b.address AND OSUSER like '&osuser' where a.sql_address =b.address AND OSUSER like '&osuser' order by address, piece;
order by address, piece; Example:
Example: mis>@csql mis>@csql
Enter value for osuser: balaji Enter value for osuser: balaji OSUSER
OSUSER USERNAME USERNAME SQL_TEXTSQL_TEXT
--- --- --- --- --- balaji
balaji FORMCONTRO SELECT ROWID,ACCHEAD,SUBACC,NAME FROMFORMCONTRO SELECT ROWID,ACCHEAD,SUBACC,NAME FROM subacc WHERE (ACCHEAD=:1)
subacc WHERE (ACCHEAD=:1) L
L balaji
balaji PLE PLE UPDATE "PE_WO_DETAIL" "A1" SET "ACCOUNTED_AMT" =UPDATE "PE_WO_DETAIL" "A1" SET "ACCOUNTED_AMT" = NVL("A1"."ACCOU
NVL("A1"."ACCOU balaji
balaji PLE PLE NTED_AMT",0)+NVL(:B1,0) WHERENTED_AMT",0)+NVL(:B1,0) WHERE "A1"."WO_NO"=DECODE(SUBSTR(:B2,1,2
"A1"."WO_NO"=DECODE(SUBSTR(:B2,1,2 balaji
balaji PLE PLE ),'FC','FC/','CC/')||SUBSTR(:B2,3) AND "A1"."WO_SLNO"=:B4),'FC','FC/','CC/')||SUBSTR(:B2,3) AND "A1"."WO_SLNO"=:B4 balaji
balaji PLE PLE SELECT /*+ */ COUNT(*) FROM "PE_WO_DETAIL" "A1" WHERESELECT /*+ */ COUNT(*) FROM "PE_WO_DETAIL" "A1" WHERE "A1"."WO_N
"A1"."WO_N balaji
balaji PLE PLE O"=DECODE(SUBSTR(:B1,1,2),'FC','FC/','CC/')||SUBSTR(:B1,3)O"=DECODE(SUBSTR(:B1,1,2),'FC','FC/','CC/')||SUBSTR(:B1,3) AND "
AND " balaji
balaji PLE PLE A1"."WO_AMOUNT"-NVL("A1"."ACCOUNTED_AMT",0)>0A1"."WO_AMOUNT"-NVL("A1"."ACCOUNTED_AMT",0)>0 balaji
balaji TAMPR TAMPR SELECT "A1"."NAME" FROM "CCMAS" "A1" WHERESELECT "A1"."NAME" FROM "CCMAS" "A1" WHERE "A1"."CCCODE"=:B1
"A1"."CCCODE"=:B1 balaji
balaji PUR PUR UPDATE "PC_SUPP_MAST" "A1" SET "SM_NAME" =UPDATE "PC_SUPP_MAST" "A1" SET "SM_NAME" = SUBSTR(:B1,1,40) WHER
SUBSTR(:B1,1,40) WHER 32. @alter- to check invalid ob
32. @alter- to check invalid objects of the table or whole databasejects of the table or whole database Script:
Script:
SET VERIFY OFF SET VERIFY OFF
select distinct 'Alter ' || a.type || ' '||
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' a.owner || '.' || a.name || ' compile;'compile;' from dba_dependencies a, dba_objects b
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='&TABLE_NAME' and where a.name=b.object_name and a. referenced_name ='&TABLE_NAME' and b.status='INVALID'; b.status='INVALID'; 33. 33. @to_pin-Script: Script: select OWNER, select OWNER,
NAME||' - '||TYPE object, NAME||' - '||TYPE object,
SHARABLE_MEM SHARABLE_MEM
from v$db_object_cache from v$db_object_cache where
where SHARABLE_MEM SHARABLE_MEM > > 1000010000 and
and tytype pe in in ('P('PACKACKAGEAGE','','PACPACKAGKAGE E BODBODY',Y','FU'FUNCTNCTIONION','P','PROCROCEDUEDURE'RE')) and owner !='SYS'
and owner !='SYS' order
order by by SHARABLE_MEM SHARABLE_MEM desc;desc; Example: Example: OWNER OBJECT OWNER OBJECT --- --- ---WILDBA
WILDBA SUM_MFG_DETAIL SUM_MFG_DETAIL - - PROCEDUREPROCEDURE WILDBA
WILDBA SUM_MFG_DTLS SUM_MFG_DTLS - - PROCEDUREPROCEDURE WILDBA
WILDBA SUM_MFG_DETL SUM_MFG_DETL - - PROCEDUREPROCEDURE WILDBA
WILDBA ANNLPLAN ANNLPLAN - - PROCEDUREPROCEDURE WILDBA
WILDBA PEMONTH PEMONTH - - PROCEDUREPROCEDURE WILDBA
WILDBA POPULATE_COMP_DETAILS POPULATE_COMP_DETAILS - - PROCEDUREPROCEDURE WILDBA
WILDBA INS_PERD_TRANS_NEW_TS INS_PERD_TRANS_NEW_TS - - PROCEDUREPROCEDURE WILDBA
WILDBA CST_GIN_VAL_RM_TS CST_GIN_VAL_RM_TS - - PROCEDUREPROCEDURE WILDBA
WILDBA INS_PERD_TRANS_LOAN_TS INS_PERD_TRANS_LOAN_TS - - PROCEDUREPROCEDURE WILDBA
WILDBA INS_PERD_TRANS INS_PERD_TRANS - - PROCEDUREPROCEDURE WILDBA
WILDBA INS_PERD_TRANS_SUPP INS_PERD_TRANS_SUPP - - PROCEDUREPROCEDURE WILDBA
WILDBA POPULATE_WHEEL_SUMMARY_EXP POPULATE_WHEEL_SUMMARY_EXP - - PROCEDUR PROCEDUR WILDBA
WILDBA GET_RATE_RM_TS GET_RATE_RM_TS - - FUNCTIONFUNCTION WILDBA
WILDBA CST_GIN_VAL_TOOL_TS CST_GIN_VAL_TOOL_TS - - PROCEDUREPROCEDURE WILDBA
WILDBA INVNOCHG INVNOCHG - - PROCEDUREPROCEDURE WILDBA
WILDBA GET_AVAILABLE_STOCK GET_AVAILABLE_STOCK - - PROCEDUREPROCEDURE WILDBA
WILDBA INS_PERD_TRANS_RM_TS INS_PERD_TRANS_RM_TS - - PROCEDUREPROCEDURE WILDBA
WILDBA GENERATEDEBITNOTE GENERATEDEBITNOTE - - PROCEDUREPROCEDURE 34. 34. Script: Script: SELECT a.tablespace_name,b.file_name,x.free_in_mb, SELECT a.tablespace_name,b.file_name,x.free_in_mb, SUM(CEIL(a.bytes/1024/1024)) used_in_mb SUM(CEIL(a.bytes/1024/1024)) used_in_mb FROM
FROM dba_extents dba_extents a,a, dba_data_files b, dba_data_files b,
(select y.tablespace_name,y.file_id,SUM(y.bytes/1024/1024) free_in_mb (select y.tablespace_name,y.file_id,SUM(y.bytes/1024/1024) free_in_mb
from
from dba_free_space dba_free_space yy group
group by by y.tablespace_name,y.file_id) y.tablespace_name,y.file_id) xx WHERE
WHERE a.owner a.owner LIKE LIKE UPPER('&&owner')UPPER('&&owner') AND
AND a.segment_name a.segment_name LIKE LIKE UPPER('&&tname')UPPER('&&tname') AND
AND a.tablespace_name a.tablespace_name = = b.tablespace_nameb.tablespace_name AND
AND a.file_id a.file_id = = b.file_idb.file_id AND
AND x.tablespace_name(+) x.tablespace_name(+) = = b.tablespace_nameb.tablespace_name AND
AND x.file_id(+) x.file_id(+) = = b.file_idb.file_id GROUP
GROUP BY BY a.tablespace_name,b.file_name,x.free_in_mb;a.tablespace_name,b.file_name,x.free_in_mb; Example:
TABLESPACE_NAME
TABLESPACE_NAME FILE_NAME FILE_NAME FREE_IN_MBFREE_IN_MB USED_IN_MB
USED_IN_MB
--- --- ---WILDATA
WILDATA D:\MIS\DATA\WILDATA01.DBF D:\MIS\DATA\WILDATA01.DBF 973 973 33 WILDATA
WILDATA D:\MIS\DATA\WILDATA03.DBF D:\MIS\DATA\WILDATA03.DBF 814 814 44 WILDATA
WILDATA E:\MIS\DATA\WILDATA02.DBF E:\MIS\DATA\WILDATA02.DBF 3939 WILDATA
WILDATA E:\MIS\DATA\WILDATA04.DBF E:\MIS\DATA\WILDATA04.DBF 88 WILDATA
WILDATA E:\MIS\DATA\WILDATA05.DBF E:\MIS\DATA\WILDATA05.DBF 1111 WILDATA
WILDATA E:\MIS\DATA\WILDATA06.DBF E:\MIS\DATA\WILDATA06.DBF 639 639 1616
Create
Createtable & synonymtable & synonym permissions:permissions: create table wildba.medi_item_m
create table wildba.medi_item_master as select aster as select * from * from trainee.medi_item_master;trainee.medi_item_master; create synonym
create synonym misqry.medi_item_master misqry.medi_item_master for wilfor wildba.medi_item_master;dba.medi_item_master; grant
grant all all on on wildba.medi_item_master wildba.medi_item_master to to misqry;misqry; Move table:
Move table:
select distinct 'Alter ' || a.type || ' '||
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' a.owner || '.' || a.name || ' compile;'compile;' from dba_dependencies a, dba_objects b
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='WILDBA.GET_RATE_TL_TS' where a.name=b.object_name and a. referenced_name ='WILDBA.GET_RATE_TL_TS' and b.status='INVALID';
and b.status='INVALID'; CONN
CONN WILDBA@MISWILDBA@MIS
Create db link already is there no problem Create db link already is there no problem CREATE TABLE
CREATE TABLE INWARD_CORRECTION AS INWARD_CORRECTION AS SELECT * SELECT * FROMFROM INWARD_CORRECTION@MISBK2MIS INWARD_CORRECTION@MISBK2MIS;; Fmotrainee Fmotrainee ============== ==============
create table wildba.medi_item_m
create table wildba.medi_item_master as select aster as select * from * from trainee.medi_item_master;trainee.medi_item_master; create synonym
create synonym misqry.medi_item_master misqry.medi_item_master for wilfor wildba.medi_item_master;dba.medi_item_master; grant
grant all all on on wildba.medi_item_master wildba.medi_item_master to to misqry;misqry; create table wildba.medical_stock
create table wildba.medical_stock as select * as select * from from trainee.medical_stock;trainee.medical_stock; create table wi
create table wildba.medical_purchase as sldba.medical_purchase as select * felect * from rom trainee.medical_purchase;trainee.medical_purchase; create table wildba.medical_sales
create table wildba.medical_sales as select * as select * from from trainee.medical_sales;trainee.medical_sales;
create synonym
create synonym misqry.medi_item_master misqry.medi_item_master for wilfor wildba.medi_item_master;dba.medi_item_master; create
create synonym synonym misqry.medical_stock misqry.medical_stock for for wildba.medical_stock;wildba.medical_stock; create sy
create synonym nonym misqry.medical_purchase misqry.medical_purchase for wifor wildba.medical_purchase;ldba.medical_purchase; create
create synonym misqry.patient_mast for wildba.patient_mast; create synonym misqry.patient_mast for wildba.patient_mast; grant
grant all all on on wildba.medi_item_master wildba.medi_item_master to to misqry;misqry; grant
grant all all on on wildba.medical_stock wildba.medical_stock to to misqry;misqry; grant all on wildba.medical_purchase to misqry; grant all on wildba.medical_purchase to misqry; grant all on wildba.medical_sales to misqry; grant all on wildba.medical_sales to misqry; create table wildba.patient_mast
create table wildba.patient_mast as select * as select * from from trainee.patient_mast;trainee.patient_mast; create table wildba.patient_detl
create table wildba.patient_detl as select * as select * from from trainee.patient_detl;trainee.patient_detl; create table wildba.complain as select * from trainee.complain;
create table wildba.complain as select * from trainee.complain;
create table wildba.cost_centre_mast as select * from trainee.cost_centre_mast; create table wildba.cost_centre_mast as select * from trainee.cost_centre_mast; create table wildba.groupmas as select *
create table wildba.groupmas as select * from trainee.groupmas;from trainee.groupmas;
create
create synonym synonym misqry.patient_mast misqry.patient_mast FOR FOR WILDBA.patient_mast;WILDBA.patient_mast; create
create synonym synonym misqry.patient_detl misqry.patient_detl FOR FOR WILDBA.patient_detl;WILDBA.patient_detl; create
create synonym synonym misqry.COMPLAIN misqry.COMPLAIN FOR FOR WILDBA.COMPLAIN;WILDBA.COMPLAIN; create
create synonym synonym misqry.cost_centre_mast misqry.cost_centre_mast FOR FOR WILDBA.cost_centre_mast;WILDBA.cost_centre_mast; create
create synonym misqry.groupmas synonym misqry.groupmas FOR FOR WILDBA.groupmas;WILDBA.groupmas; create synonym misqry.empmas for wildba.empmas;
create synonym misqry.empmas for wildba.empmas;
GRANT ALL O
GRANT ALL ON N WILDBA.patient_mast TO WILDBA.patient_mast TO MISQRY;MISQRY; GRANT ALL O
GRANT ALL ON N WILDBA.patient_detl TO WILDBA.patient_detl TO MISQRY;MISQRY; GRANT
GRANT ALL ALL ON ON WILDBA.COMPLAIN WILDBA.COMPLAIN TO TO MISQRY;MISQRY; GRANT
GRANT ALL ALL ON ON WILDBA.cost_centre_mast WILDBA.cost_centre_mast TO TO MISQRY;MISQRY; GRANT ALL ON WILDBA.GROUPMAS TO MISQRY; GRANT ALL ON WILDBA.GROUPMAS TO MISQRY; GRANT ALL ON WILDBA.EMPMAS TO MISQRY; GRANT ALL ON WILDBA.EMPMAS TO MISQRY;
=============================================================== =============================================================== ====================
====================
create sy
create synonym nonym TS.DS_INVOICE_POP TS.DS_INVOICE_POP FOR WFOR WILDBA.DS_INVOICE_POP;ILDBA.DS_INVOICE_POP; GRANT ALL ON WILDBA.DS_INVOICE_POP TO TS;
GRANT ALL ON WILDBA.DS_INVOICE_POP TO TS;
CREATE
CREATE SYNONYM SYNONYM TS.USER_DEPT_LINK TS.USER_DEPT_LINK FOR FOR WILDBA.USER_DEPT_LINK;WILDBA.USER_DEPT_LINK; GRANT SELECT,UPDATE
1.Please Create the synonym for table “Messages” ,user “Airsus” @mis database. 1.Please Create the synonym for table “Messages” ,user “Airsus” @mis database.
CONNECT WILDBA @MIS CONNECT WILDBA @MIS
CREATE
CREATE SYNONYM SYNONYM TRAINEE.STD_CODE TRAINEE.STD_CODE FOR FOR WILDBA.STD_CODE;WILDBA.STD_CODE; GRANT SELECT
GRANT SELECT ON ON WILDBA.STD_CODE WILDBA.STD_CODE TO TRAINEE;TO TRAINEE;
CREATE
CREATE SYNONYM SYNONYM TP.DS_PACKING_LIST_DETAIL TP.DS_PACKING_LIST_DETAIL FOR FOR WILDBA.DS_PACKING_LIST_DETAIL ;
WILDBA.DS_PACKING_LIST_DETAIL ; CREATE
CREATE SYNONYM SYNONYM TS.DS_PACKING_LIST_DETAIL TS.DS_PACKING_LIST_DETAIL FOR FOR WILDBA.DS_PACKING_LIST_DETAIL ;
WILDBA.DS_PACKING_LIST_DETAIL ;
CREATE SYNONYM TP.DS_PACKING_LIST FOR CREATE SYNONYM TP.DS_PACKING_LIST FOR WILDBA.DS_PACKING_LIST;
WILDBA.DS_PACKING_LIST;
CREATE SYNONYM TS.DS_PACKING_LIST FOR CREATE SYNONYM TS.DS_PACKING_LIST FOR WILDBA.DS_PACKING_LIST;
WILDBA.DS_PACKING_LIST;
GRANT SELECT, I
GRANT SELECT, INSERT ON NSERT ON WILDBA.DS_PACKING_LIST_DETAIL WILDBA.DS_PACKING_LIST_DETAIL TO TO TP;TP; GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST TO TP;
GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST TO TP; GRANT SELECT, I
GRANT SELECT, INSERT ON NSERT ON WILDBA.DS_PACKING_LIST_DETAIL WILDBA.DS_PACKING_LIST_DETAIL TO TO TS;TS; GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST TO TS;
GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST TO TS;
GRANT SELECT ON WILDBA.GIN_RECP_MAST_CG TO MISQRY; GRANT SELECT ON WILDBA.GIN_RECP_MAST_CG TO MISQRY;
CREATE
CREATE SYNONYM SYNONYM MISQRY.GIN_RECP_DETL_CG MISQRY.GIN_RECP_DETL_CG FOR FOR WILDBA.GIN_RECP_DETL_CG;
WILDBA.GIN_RECP_DETL_CG;
GRANT SELECT ON WILDBA.GIN_RECP_DETL_CG TO MISQRY; GRANT SELECT ON WILDBA.GIN_RECP_DETL_CG TO MISQRY;
CREATE S
CREATE SYNONYM YNONYM MISQRY.GATE_BILL_DETAIL MISQRY.GATE_BILL_DETAIL FOR FOR WILDBA.GATE_BILL_DETAIL;
WILDBA.GATE_BILL_DETAIL;
GRANT ALL ON WILDBA.GATE_BILL_DETAIL TO MISQRY; GRANT ALL ON WILDBA.GATE_BILL_DETAIL TO MISQRY;
CREATE
CREATE SYNONYM SYNONYM DS.TR_RAW_MATL_MAST DS.TR_RAW_MATL_MAST FOR FOR WILDBA.TR_RAW_MATL_MAST;
WILDBA.TR_RAW_MATL_MAST;
GRANT SELECT ON WILDBA.TR_RAW_MATL_MAST TO DS; GRANT SELECT ON WILDBA.TR_RAW_MATL_MAST TO DS;
CREATE S
CREATE SYNONYM YNONYM MISQRY.FORMCONTROLMASTER MISQRY.FORMCONTROLMASTER FOR FOR FORMCONTROL.FORMCONTROLMASTER;
FORMCONTROL.FORMCONTROLMASTER;
GRANT SELECT ON FORMCONTROL.FORMCONTROLMASTER TO MISQRY; GRANT SELECT ON FORMCONTROL.FORMCONTROLMASTER TO MISQRY; DROP SYNONYM MISQRY.FORMCONTROLMASTER
DROP SYNONYM MISQRY.FORMCONTROLMASTER
CREATE SYNONYM
CREATE SYNONYM MISQRY.WILHELP MISQRY.WILHELP FOR WIL.WFOR WIL.WILHELP;ILHELP; CREATE SYNONYM
CREATE SYNONYM MISQRY.WIL_DIR MISQRY.WIL_DIR FOR WIFOR WIL.WIL_DIR;L.WIL_DIR; CREATE SYNONYM
CREATE SYNONYM MISQRY.WIL_INI_DETAILS MISQRY.WIL_INI_DETAILS FOR FOR WIL.WIL_INI_DETAILS;WIL.WIL_INI_DETAILS; CREATE SYNONYM
CREATE SYNONYM MISQRY.WIL_MENU_CST MISQRY.WIL_MENU_CST FOR FOR WIL.WIL_MENU_CST;WIL.WIL_MENU_CST;
CREATE SYNONYM
CREATE SYNONYM MISQRY.WIL_MENU MISQRY.WIL_MENU FOR WIL.WIFOR WIL.WIL_MENUL_MENU CREATE
CREATE SYNONYM SYNONYM MISQRY.WIL_MENU_ORG MISQRY.WIL_MENU_ORG FOR FOR WIL.WIL_MENU_ORG;WIL.WIL_MENU_ORG; CREATE
CREATE SYNONYM SYNONYM MISQRY.WIL_MENU_USER MISQRY.WIL_MENU_USER FOR FOR WIL.WIL_MENU_USER;WIL.WIL_MENU_USER; CREATE
CREATE SYNONYM SYNONYM MISQRY.WIL_OBJECTS MISQRY.WIL_OBJECTS FOR FOR WIL.WIL_OBJECTS;WIL.WIL_OBJECTS; CREATE
CREATE
CREATE SYNONYM SYNONYM MISQRY.WIL_SYNONYMS FOR MISQRY.WIL_SYNONYMS FOR WIL.WIL_SYNONYMS;WIL.WIL_SYNONYMS; CREATE S
CREATE SYNONYM YNONYM MISQRY.WIL_TAB_COLUMNS MISQRY.WIL_TAB_COLUMNS FOR FOR WIL.WIL_TAB_COLUMNS;
WIL.WIL_TAB_COLUMNS; CREATE
CREATE SYNONYM SYNONYM MISQRY.WIL_USERS MISQRY.WIL_USERS FOR FOR WIL.WIL_USERS;WIL.WIL_USERS; CREATE SYNONYM
CREATE SYNONYM MISQRY.WIL_USER_MOD MISQRY.WIL_USER_MOD FOR WILFOR WIL.WIL_USER_MOD;.WIL_USER_MOD;
GRANT
GRANT SELECT SELECT ON ON WIL.WILHELP WIL.WILHELP TO TO MISQRY;MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_DIR WIL.WIL_DIR TO TO MISQRY;MISQRY; GRANT SELECT O
GRANT SELECT ON N WIL.WIL_INI_DETAILS WIL.WIL_INI_DETAILS TO MISQRY;TO MISQRY; GRANT SELECT
GRANT SELECT ON ON WIL.WIL_MENU_CST WIL.WIL_MENU_CST TO MISQRY;TO MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_MENU_ORG WIL.WIL_MENU_ORG TO TO MISQRY;MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_MENU_USER WIL.WIL_MENU_USER TO TO MISQRY;MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_OBJECTS WIL.WIL_OBJECTS TO TO MISQRYMISQRY GRANT SELECT O
GRANT SELECT ON N WIL.WIL_PERMISSION WIL.WIL_PERMISSION TO MISQRY;TO MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_SYNONYMS WIL.WIL_SYNONYMS TO TO MISQRY;MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_TAB_COLUMNS WIL.WIL_TAB_COLUMNS TO TO MISQRY;MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_USERS WIL.WIL_USERS TO TO MISQRY;MISQRY; GRANT
GRANT SELECT SELECT ON ON WIL.WIL_USER_MOD WIL.WIL_USER_MOD TO TO MISQRY;MISQRY; GRANT SELECT
GRANT SELECT ON WIL.WION WIL.WIL_MENU TO L_MENU TO MISQRY;MISQRY; CREATE
CREATE SYNONYM SYNONYM PUR.GIN_RECP_MAST_PKG PUR.GIN_RECP_MAST_PKG FOR FOR WILDBA.GIN_RECP_MAST_PKG
WILDBA.GIN_RECP_MAST_PKG GRANT
GRANT EXECUTE ON WILDBA.GIN_RECP_MAST_PKG EXECUTE ON WILDBA.GIN_RECP_MAST_PKG TO PUR;TO PUR;
CREATE SYNONYM MISQRY.PATIENT_SEQMAST FOR CREATE SYNONYM MISQRY.PATIENT_SEQMAST FOR WILDBA.PATIENT_MAST;
WILDBA.PATIENT_MAST;
CREATE SYNONYM MISQRY.PAD_SEQUENCE_PS FOR CREATE SYNONYM MISQRY.PAD_SEQUENCE_PS FOR WILDBA.PAD_SEQUENCE_PS
WILDBA.PAD_SEQUENCE_PS CREATE
CREATE SYNONYM SYNONYM MISQRY.PATIENT_DETL MISQRY.PATIENT_DETL FOR FOR WILDBA.PATIENT_DETL;WILDBA.PATIENT_DETL; Create
Create synonym synonym GS.GATE_BILL_DETAIL_HISTORY GS.GATE_BILL_DETAIL_HISTORY for for WILDBA.gate_bill_detail_history;
WILDBA.gate_bill_detail_history; create synonym
create synonym pur.gate_bill_detail_history pur.gate_bill_detail_history for wilfor wildba.gate_bill_detail_history;dba.gate_bill_detail_history; create sy
create synonym nonym pur.gate_bill_detail pur.gate_bill_detail for wifor wildba.gate_bill_detail;ldba.gate_bill_detail; CREATE SYNONYM QLTY.WARRANTY_MASTER FOR CREATE SYNONYM QLTY.WARRANTY_MASTER FOR WARRANTY.WARRANTY_MASTER
WARRANTY.WARRANTY_MASTER
CREATE SYNONYM QLTY.CUSTOMER_NAME FOR CREATE SYNONYM QLTY.CUSTOMER_NAME FOR WARRANTY.CUSTOMER_NAME
GRANT SELECT
GRANT SELECT ON PATIEON PATIENT_MAST NT_MAST TO MITO MISQRY;SQRY; GRANT SELECT
GRANT SELECT ON PATIEON PATIENT_DETL NT_DETL TO MISQRY;TO MISQRY; GRANT SELECT
GRANT SELECT ON MESSAGES ON MESSAGES TO RAIRSUSTO RAIRSUS
GRANT SELECT ON WARRANTY_MASTER TO QLTY GRANT SELECT ON WARRANTY_MASTER TO QLTY GRANT SELECT ON CUSTOMER_NAME TO QLTY GRANT SELECT ON CUSTOMER_NAME TO QLTY CREATE
CREATE SEQUENCE SEQUENCE AIRSUS.MESSAGES FAIRSUS.MESSAGES FOR OR WILDBA.MESSAGESWILDBA.MESSAGES 2.Please create the synonym
2.Please create the synonym for the following objects for the user airsus @ Mis Databasefor the following objects for the user airsus @ Mis Database 1.Sequence
1.Sequence - - Gs_Doc_seqGs_Doc_seq 2.Table
2.Table - - EmpmasEmpmas
CONNECT WILDBA @MIS CONNECT WILDBA @MIS CREATE SYNONYM
CREATE SYNONYM AIRSUS.Gs_Doc_deq AIRSUS.Gs_Doc_deq FOR FOR WILDBA.GS_DOC_DEQWILDBA.GS_DOC_DEQ GRANT SELECT ON GS_DOC_DEQ TO RAIRSUS.
GRANT SELECT ON GS_DOC_DEQ TO RAIRSUS.
CREATE S
CREATE SYNONYM YNONYM WARRANTY.WIL_INI_DETAILS WARRANTY.WIL_INI_DETAILS FOR FOR WILDBA.WIL_INI_DETAILS
WILDBA.WIL_INI_DETAILS GRANT
GRANT SELECT SELECT ON ON WIL_INI_DETAILS WIL_INI_DETAILS TO TO WARRANTYWARRANTY CREATE
CREATE SYNONYM SYNONYM AIRSUS.EMPMAS AIRSUS.EMPMAS FOR FOR WILDBA.EMPMASWILDBA.EMPMAS GRANT SELECT ON EMPMAS TO RAIRSUS
GRANT SELECT ON EMPMAS TO RAIRSUS
---2.Pls move the following table from wildba@mis_bk to wildba@mis and wildba@wwdb 2.Pls move the following table from wildba@mis_bk to wildba@mis and wildba@wwdb and give a synonyms to mp@mis and mp@wwdb.
and give a synonyms to mp@mis and mp@wwdb.
Inward_correction Inward_correction Inward_correction_mast Inward_correction_mast Inward_correction_detl Inward_correction_detl PATIENT_MAST PATIENT_MAST PATIENT_DETL PATIENT_DETL
select object_name,object_type,status from dba_objects where select object_name,object_type,status from dba_objects where object_name='PATIENT_MAST';
object_name='PATIENT_MAST'; CONN WILDBA@MIS
CONN WILDBA@MIS CREATE TABLE
CREATE TABLE INWARD_CORRECTION AS INWARD_CORRECTION AS SELECT * SELECT * FROMFROM INWARD_CORRECTION@MISBK2MIS;
INWARD_CORRECTION@MISBK2MIS; create table
create table patient_mast as selpatient_mast as select * from ect * from patient_mast@misbk2mis;patient_mast@misbk2mis; create table
create table patient_detl as selpatient_detl as select * from ect * from patient_detl@misbk2mispatient_detl@misbk2mis CREATE TABLE
CREATE TABLE INWARD_CORRECTION_MAST AS INWARD_CORRECTION_MAST AS SELECT * FROMSELECT * FROM INWARD_CORRECTION_MAST @MISBK2MIS;
INWARD_CORRECTION_MAST @MISBK2MIS; CREATE
CREATE TABLE TABLE INWARD_CORRECTION_DET1 INWARD_CORRECTION_DET1 AS AS SELECT SELECT * * FROMFROM INWARD_CORRECTION_DET1 @MISBK2MIS;
INWARD_CORRECTION_DET1 @MISBK2MIS; CONN WILDBA@WWDB
CONN WILDBA@WWDB CREATE
CREATE TABLE TABLE INWARD_CORRECTION INWARD_CORRECTION AS AS SELECT SELECT * * FROMFROM INWARD_CORRECTION @MISBKTOWWDB;
INWARD_CORRECTION @MISBKTOWWDB; CREATE
CREATE TABLE TABLE INWARD_CORRECTION_MAST INWARD_CORRECTION_MAST AS AS SELECT SELECT * * FROMFROM INWARD_CORRECTION_MAST
INWARD_CORRECTION_MAST @MISBKTOWWDB @MISBKTOWWDB ;; CREATE
CREATE TABLE TABLE INWARD_CORRECTION_DET1 INWARD_CORRECTION_DET1 AS AS SELECT SELECT * * FROMFROM INWARD_CORRECTION_DET1
INWARD_CORRECTION_DET1 @MISBKTOWWDB @MISBKTOWWDB ;;
CREATE TABLE ES_PROD_MAST_WW AS SELECT * FROM CREATE TABLE ES_PROD_MAST_WW AS SELECT * FROM ES_PROD_MAST_WW @MIS2MISBK
ES_PROD_MAST_WW @MIS2MISBK CREATING SYNONYMS TO MP@MIS CREATING SYNONYMS TO MP@MIS
---CONN WILDBA@MIS CONN WILDBA@MIS CREATE S
CREATE SYNONYM YNONYM MP.INWARD_CORRECTION MP.INWARD_CORRECTION FOR FOR WILDBA.INWARD_CORRECTION
WILDBA.INWARD_CORRECTION
GRANT SELECT ON INWARD_CORRECTION TO RMP GRANT SELECT ON INWARD_CORRECTION TO RMP
CREATE S
CREATE SYNONYM YNONYM MP.INWARD_CORRECTION_MAST MP.INWARD_CORRECTION_MAST FOR FOR WILDBA.INWARD_CORRECTION_MAST
WILDBA.INWARD_CORRECTION_MAST
GRANT SELECT ON INWARD_CORRECTION_MAST TO RMP GRANT SELECT ON INWARD_CORRECTION_MAST TO RMP
CREATE S
CREATE SYNONYM YNONYM MP.INWARD_CORRECTION_DET1 MP.INWARD_CORRECTION_DET1 FOR FOR WILDBA.INWARD_CORRECTION_DET1
WILDBA.INWARD_CORRECTION_DET1
GRANT SELECT ON MP.INWARD_CORRECTION_DET1 TO RMP GRANT SELECT ON MP.INWARD_CORRECTION_DET1 TO RMP
CREATING SYNONYMS TO MP@WWDB CREATING SYNONYMS TO MP@WWDB ---CONNECT WILDBA@WWDB; CONNECT WILDBA@WWDB; CREATE SYNONYM
CREATE SYNONYM MP.INWARD_CORRECTION(NEWNAME) MP.INWARD_CORRECTION(NEWNAME) FOR FOR WILDBA.INWARD_CORRECTION
WILDBA.INWARD_CORRECTION
GRANT SELECT ON MP.INWARD_CORRECTION TO RMP GRANT SELECT ON MP.INWARD_CORRECTION TO RMP
create table NEW_Loadtrace tablespace usr01 ... create table NEW_Loadtrace tablespace usr01 ... as select * from loadTrack;
as select * from loadTrack;
CREATE S
CREATE SYNONYM YNONYM MP.INWARD_CORRECTION_MAST MP.INWARD_CORRECTION_MAST FOR FOR WILDBA.INWARD_CORRECTION_MAST
WILDBA.INWARD_CORRECTION_MAST
GRANT SELECT ON MP.INWARD_CORRECTION_MAST TO RMP GRANT SELECT ON MP.INWARD_CORRECTION_MAST TO RMP
CREATE S
CREATE SYNONYM YNONYM MP.INWARD_CORRECTION_DET1 MP.INWARD_CORRECTION_DET1 FOR FOR WILDBA.INWARD_CORRECTION_DET1
WILDBA.INWARD_CORRECTION_DET1
GRANT SELECT ON MP.INWARD_CORRECTION_DET1 TO RMP GRANT SELECT ON MP.INWARD_CORRECTION_DET1 TO RMP
---3.Please take the table “GIN_RECP_DETL_GS” of MIS DB from backup of 21-jun-06… 3.Please take the table “GIN_RECP_DETL_GS” of MIS DB from backup of 21-jun-06… BACKUP 190606 BACKUP 190606 RESTORE RESTORE ---4.Pls add following two fil
gmm_gin_cancel varchar2(1) gmm_gin_cancel varchar2(1) gmm_gin_cancel_date date gmm_gin_cancel_date date
CONECT AS
CONECT AS WILDBA@MIS,@WIL,@WWILDBA@MIS,@WIL,@WWDBWDB
Alter table GIN_MAST_MP ADD GMM_GIN_CANCEL Alter table GIN_MAST_MP ADD GMM_GIN_CANCEL VARCHAR2(1),GMM_GIN_CANCEL_DATE;
VARCHAR2(1),GMM_GIN_CANCEL_DATE;
select distinct 'Alter ' || a.type || ' '||
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' a.owner || '.' || a.name || ' compile;'compile;' from dba_dependencies a, dba_objects b
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='WILDBA.GET_RATE_TL_TS' where a.name=b.object_name and a. referenced_name ='WILDBA.GET_RATE_TL_TS' and b.status='INVALID'; and b.status='INVALID'; INV_CESS_PER_ONE INV_CESS_PER_ONE INV_CESS_AMOUNT_ONE INV_CESS_AMOUNT_ONE
COMPILE THE PROCEDURES,TRIGGERS,PACKAGE COMPILE THE PROCEDURES,TRIGGERS,PACKAGE ---5.Please alter the below tables.
5.Please alter the below tables. pc_order_mast pc_order_mast pc_order_mast_mn pc_order_mast_mn pc_order_mast_imp pc_order_mast_imp
modify fc_number number(4) to fc_number number(10) modify fc_number number(4) to fc_number number(10)
ALTER TABLE PATIENT_MAST MODIFY TOKEN_NO VARCHAR2(5); ALTER TABLE PATIENT_MAST MODIFY TOKEN_NO VARCHAR2(5); alter table pc_order_mast modify fc_number number(10);
alter table pc_order_mast modify fc_number number(10);
alter table pc_order_mast_mn modify fc_number number(10); alter table pc_order_mast_mn modify fc_number number(10); alter table pc_order_mast_imp modify fc_number number(10); alter table pc_order_mast_imp modify fc_number number(10); select distinct 'Alter ' || a.type || ' '||
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' a.owner || '.' || a.name || ' compile;'compile;' from dba_dependencies a, dba_objects b
from dba_dependencies a, dba_objects b where a.name=b.object_name and
