Friday, October 24, 2008

* DBA Roles and Responsibilities *


* DBA Roles and Responsibilities *

REGULAR LIST OF ACTIVITIES PERFORMED BY THE LAMBENT ON SITE CONSULTANT IN WHEELS INDIA

DATABASE MONITORING AND TUNING (DAILY)

 Database Growth Monitoring
 Archival Space Monitoring
 Rollback segment Monitoring
 Lock Monitoring
 Monitoring Latch Contention
 Performance Monitoring
 Monitoring Memory Performance
 Parameter Tuning (whenever required)
 I/0 Monitoring
 Resolving Contention
 Database object status validation and re-compilation
 Checking Alert Files And Trace Files
 Index Usage Monitoring
 Sort Performance Monitoring
 Analyzing tables for performance (monthly)
 Re-building indexes (monthly)
 Table re-organization (whenever required)

BACKUP STRATEGY IMPLEMENTATION

 Implementing Backups
 Recovery Contingency Testing
 Maintaining Standby Databases

 Daily
• Export backup
• Applying Archive logs on Standby Database

 Weekly
• Hot Back
• Archive Log Backup

 Monthly
• Cold Backup



SECURITY (ON DEMAND)

 Managing Users, Privileges And Roles
 Implementing Database Auditing

CUSTOMER INITIATED ACTIVITIES (ON DEMAND)

 Data Loading
 Exp / Imp Of Tables / Users For Testing
 Creation Of Objects Based On User Request
 Installation of oracle server and clients

TROUBLESHOOTING (ON DEMAND)

 Oracle Connectivity Issues
 Solving Errors Reported By Users.
 Oracle internal errors
 Database recovery after failures



à Daily Activities:


Database > MIS -à MAIN à Production Database
ACCTS: Accounts Database
Wirewheel Database
Mis_bk Database (Test Database)
Standby Database


Daily Activities


1) Check all the Database open and read write mode all 5 Database
2) Check mail..
3) Check bdump frequently
4) check all the v$session , locks, v$activex
5) space all database from f: (Required More than 4GB)


Before leaving remove archive files in f drive: of every databases should be deleted (delete the oldest archive files) for space

Go to remote server login to Acer will and pwd click alt del ctl adminstrator go to c: drive select folder export_backup . Change the date -> controlpannel-> shcudled.

Go to remote server login to commwill and pwd click alt del ctl adminstrator go to c: dirve select folder export_backup . Change the date -> controlpannel-> shcudled.

Go to remote server login to wirewill and pwd click alt del ctl adminstrator go to E: dirve select folder export_backup . change the date -> controlpannel-> shcudled.
From e: drive copy to local backup area.
Tape backup:

Ø ntbackup
Ø click backup
Ø select media à < TAPE NAME>
Ø GO TO BACKUP FOLDER - SELECT DATE OF DUMP FILE AND LOG FILE.
Ø GIVE THE NAME EXP_MIS_ACT_WW_ FOR EXAMPLE <280406>



Scripts:
@active; - to check the users are active
@block - to check any lock has occured
@sid - to check for sid
@hash - to check which sql stmt is running
@lock - to check for deadlock
@lock_ob - to check which user locked the table
@ts - to check the tablespace free details
@rollback - to shrink the rollback if extents reach max_extents
@rollstat - to check weather to shrink or not
@buffhit - to check the buffer hit ratio
@loghit - to check the log hit ratio
@ddhit - to check the data dictionart hit ratio
@lockwait - who has locked and who is waiting
@hwm - to check high water mark
@active - to check the active for session
@temp - to check for sorting
@temp1 - to check full details abt temp space
@cnt - to count the active and inactive sessions.
@sessions - to check inactive sessions having more than 3 sessions
@sysstat - to tune the database
@alz - to analyze the table
@chkpt - t0 check when the last checkpoint has occured
@time - to check the particular os user who is inactive
@tgrow - to check for table growth.
@igrow - to check for index growth.
@idle - to check for users are in idle state
@privs - to check for object privilege
@user - to check how much mb users are using
@rsql - to check current sql for particular rollback segment

1. @active; - to check the users are active

Script:

Select LPAD (SID, 4) SID, SERIAL#, username, RPAD (t.start_time, 18) "START_TIME", r.name, t.used_ublk "RBS BLKS",
2 decode (t.space, 'YES','SPACE TX', DECODE (T.RECURSIVE,'YES','RECURSIVE TX',
3 DECODE (T.NOUNDO, 'YES', 'NO UNDO TX', T.STATUS)
4)) STATUS, terminal, osuser
5 FROM SYS.V$TRANSACTION T, SYS.V$ROLLNAME R, SYS.V$SESSIONS 6 WHERE T.XIDUSN = R.USN
7 AND T.SES_ADDR = S.SADDR
8* ORDER BY SID

Example: http://www.orafaq.com/wiki/SQL_FAQ

SERIAL# USERNAME START_TIME NAME RBS BLKS STATUS TERMINAL OSUSER
---- ------- ---------- ------------------ ---------- ---------- -------- -------------- ---------
7 14644 TP 03/02/07 08:18:41 RBS10 1 ACTIVE PC400 mani
9 53685 DS 03/02/07 08:02:18 RBS07 1 ACTIVE PC100 internal
18 3944 MIS 03/02/07 07:56:19 RBS10 1 ACTIVE PC406 ravianand
30 7718 TP 03/02/07 07:36:48 RBS09 1 ACTIVE PC368 parasuram
34 7177 PE 03/02/07 07:42:43 RBS01 1 ACTIVE PC431 senthil
39 6655 PE 03/02/07 08:20:03 RBS08 1 ACTIVE PC425 lpcm
41 29751 PE 03/02/07 01:01:05 RBS09 1 ACTIVE PC528 stores
44 6322 PE 03/01/07 14:14:46 RBS03 1 ACTIVE PC297 pd
49 7207 QLTY 03/02/07 08:13:19 RBS01 1 ACTIVE PCLP lpsundare
51 4093 MISQ 03/02/07 08:06:31 RBS06 1 ACTIVE PC516 emrim
53 5398 TS 03/02/07 07:17:19 RBS04 2 ACTIVE PC484 tool
69 7077 DS 03/02/07 07:23:10 RBS05 2 ACTIVE PCAS subramani

2. @block - to check any lock has occurred

Script:

select * from v$lock WHERE LMODE=6 and block=1 ORDER BY SID

Example:

No rows selected (No problem but any lock is occur then kill the session use sid script to find the sid no)
SQL> @l1

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ----------
616970C8 61697194 363 TX 524313 557 6 0 2812

3. @sid - to check for sid

Script:

Select * from v$session where sid=&sid

Example:
Enter value for sid: 376

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR
-------- ----- ------- ---------- -------- ---------- ---------- ---------- ---------- --------
342AC098 376 1473 47366 341C327C 36 PE 0 2147483644 34B20CA8

Solution:

Alter system kill session'376, 1473';

4. @hash - to check which sql stmt is running

Script:
Select sql_text from v$sqlarea where hash_value=&hash_value

Example:
mis>@hash
Enter value for hash_value: 824065089

SQL_TEXT
----------------------------------------------
Select rowid, day1, day2 from tampr.weekhol

5. @lock - to check for deadlock

Script:
Select * from v$lock WHERE LMODE=6 ORDER BY SID

Example:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ----- -- ---------- ---------- ------ ---------- ---------- ------
34379768 34379778 3 RT 1 0 6 0 0 0
61699A5C 61699B28 7 TX 786432 1785 6 0 3701 0
61699A5C 61699B28 9 TX 589871 386 6 0 4684 0
61699A5C 61699B28 16 TX 524308 556 6 0 1596 0
61699A5C 61699B28 18 TX 655438 451 6 0 2098 0
61699A5C 61699B28 23 TX 786497 1781 6 0 236 0
61699A5C 61699B28 30 TX 720928 846 6 0 6214 0
61699A5C 61699B28 34 TX 196695 461 6 0 5859 0

6. @lock_ob - to check which user locked the table

Script:

Select s.sid, s.serial#, s.username,
decode (l.type,'TM','Table Lock','TX','Row Lock', Null) aa,
o.owner,o.object_type,o.object_NAME
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 null

Example:
mis>@lock_ob

SID SERIAL# USERNAME Lock Level OWNER OBJECT_TYPE OBJECT_NAME
--- ------- ---------- ------------ ---------- ------------ ------------------------
340 1583 ES Table Lock WILDBA TABLE WIL_MENU_USER
343 889 DS Table Lock WILDBA TABLE WIL_MENU_USER
344 968 ES Table Lock WILDBA TABLE WIL_MENU_USER
346 1315 MIS Table Lock WILDBA TABLE WIL_MENU_USER
349 2203 PE Table Lock WILDBA TABLE WIL_MENU_USER
360 2219 PUR Table Lock WILDBA TABLE PC_ORDER_MAST
360 2219 PUR Table Lock WILDBA TABLE PC_PURCHASE_REQN
361 1077 GS Table Lock WILDBA TABLE WIL_MENU_USER
367 727 PE Table Lock WILDBA TABLE WIL_MENU_USER

7. @ts - to check the tablespace free details

Script:
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)"
from
(
select tablespace_name,0 MB, sum(bytes)/1048576 FREE from dba_free_space
group by tablespace_name
union all
select tablespace_name,sum(bytes)/1048576 MB, 0 FREE from sys.dba_data_files
group by tablespace_name
)
group by tablespace_name

Example:

mis>@ts

TABLESPACE_NAME TOTAL BYTES (MB) FREE BYTES (MB) FREE PERCENTAGE (MB)
------------------------------ ---------------- --------------- --------------------
INDEX_DATA 12900 1843.32 14.29
ROLLBACK_DATA 2600 602.18 23.16
SYSTEM 2000 1787.86 89.39
TAMPR 2800 508.23 18.15
USER_DATA 800 528.74 66.09
WILDATA 9600 2427.04 25.28
8. @rollback - to shrink the rollback if extents reach max_extents

Script:
alter rollback segment SYS_RBS shrink to 10m;
alter rollback segment RBS01 shrink to 10m;
alter rollback segment RBS02 shrink to 10m;
alter rollback segment RBS03 shrink to 10m;
alter rollback segment RBS04 shrink to 10m;
alter rollback segment RBS05 shrink to 10m;
alter rollback segment RBS06 shrink to 10m;
alter rollback segment RBS07 shrink to 10m;
alter rollback segment RBS08 shrink to 10m;
alter rollback segment RBS09 shrink to 10m;
alter rollback segment RBS10 shrink to 10m;

9. @rollstat - to check weather to shrink or not

Script:
Select * from v$rollstat;

Example:
mis>@rollstat

USN EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 61 4988928 4796 0 2580 0 4988928
1 1821 59695104 122958038 7 363608 8 892575744
3 256 10469376 135484906 7 481338 3 156467200
4 286 11689984 131723198 6 875702 0 119775232
5 5655 231571456 154230310 8 1099711 0 648863744
6 6105 250003456 298364090 8 2607550 2 250003456
7 3752 153640960 49896784 8 35719 1 153640960

10. @buffhit - to check the buffer hit ratio
Script:
select (1-(pr.value/(dbg.value+cg.value)))*100 "Buffer Hit Raio"
from v$sysstat pr, v$sysstat dbg, v$sysstat cg
where pr.name = 'physical reads'
and dbg.name = 'db block gets'
and cg.name = 'consistent gets'

Example:
mis>@buffhit
Buffer Hit Raio 90.0798363

11. @loghit - to check the log hit ratio

Script:
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
re.name = 'redo entries'

Example:
mis>@loghit

NAME VALUE NAME VALUE ratio
---------- ---------- ---------- ---------- ----------
redo buffe 750 redo entri 18693284 .004012136
r allocati es
on retries

12. @hwm - to check high water mark
Script:
select a.owner, a.table_name, b.blocks allocblks, a.blocks usedblks,
(b.blocks-a.empty_blocks-1) highwtr
from dba_tables a, dba_segments b
where a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'
order by 1,2

Example:
OWNER TABLE_NAME ALLOCBLKS USEDBLKS HIGHWTR
------------------------------ ------------------------------ ---------- ---------- ----------
TAMPR WAGES_AB 574 759 528
TAMPR WG_OT 14685 12204 14601
TAMPR WG_OT 5229 12204 5145
WILDBA CUSTOMER 105 89 104
WILDBA ES_SHIPMENT_DTLS 272 209 271
WILDBA ITM_CODE 535 3 523
WILDBA PC_ORDER_MAST_MN 667 604 666
WILDBA PE_WO_ADD 91 61 90
WILDBA SC_TIMEOFFICE 8 1 -41
WILDBA TR_TOOL_MAST 2084 1988 2083
WILDBA WIL_MENU_USER 882 586 796


13. @active - to check the active for session
SELECT s.sid,
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 +
I.BLOCK_GETS),0, 1,(I.CONSISTENT_GETS + I.BLOCK_GETS) )), 2) "Hit Rate",
s.machine "Machine",
s.program "Program",
to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.wait_time,
NVL(w.seconds_in_wait,0) wait_in_secs,
w.state,
s.last_call_et
FROM v$session s,
v$sess_io i,
v$session_wait w
WHERE s.sid = i.sid
AND s.sid = w.sid (+)
AND 'SQL*Net message from client' = w.event (+)
AND s.osuser is not null
AND s.username is not null
AND s.status = 'ACTIVE'
ORDER BY 6 DESC, 11 DESC

Example:
mis>@active

SID SERIAL# USERNAME OSUSER CGETS PHY_RDS Hit Rate Machine Program
----- ------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
43 4344 MP pch 7853106 37307 99.52 DOMAIN\PC2
28 17907 SYSTEM radiant 147370 987 99.33 DOMAIN\PC3 SQLPLUSW.E

14. @temp - to check for sorting

Script:
select s.username, u."USER",s.OSUSER "osuser", u.tablespace, u.contents, u.extents, u.blocks from sys.v_$session s, sys.v_$sort_usage u where s.saddr = u.session_addr

Example:
mis>@temp

USERNAME USER OSUSER TABLESPACE
--------------- ------------------------------ ---------- ---------------
DS SYSTEM rskumar TEMPORARY_DATA
15. @temp1 - to check full details abt temp space

Script:
select s.osuser, s.process, s.username, s.serial#,
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
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
group by s.osuser, s.process, s.username, s.serial#, vp.value,U.EXTENTS,U.BLOCKS

Example:
mis>@temp1

OSUSER PROCESS USERNAME SERIAL# SORT_SIZE EXTENTS BLOCKS
-------------------- --------- --------------- ------- ---------- ---------- ----------
rskumar 1328:1780 DS 10272 1024 1 128

16. @cnt - to count the active and inactive sessions.

Script:
SELECT COUNT (*) "TOTAL"FROM v$session

Example:
mis>@cnt

TOTAL 338

17.@sessions - to check inactive sessions having more than 3 sessions

Script:
SELECT OSUSER,USERNAME,COUNT(*) "INACTIVE" FROM V$SESSION WHERE STATUS ='INACTIVE' AND
USERNAME ='CST'
OR USERNAME='DS'
OR USERNAME='ES'
OR USERNAME='FASAL'
OR USERNAME='GS'
OR USERNAME='MFG'
OR USERNAME='MISQRY'
OR USERNAME='MISREAD'
OR USERNAME='MP'
OR USERNAME='NEW_INCENTIVE'
OR USERNAME='PD'
OR USERNAME='PE'
OR USERNAME='PLE'
OR USERNAME='PUR'
OR USERNAME='QLTY'
OR USERNAME='READMIS'
OR USERNAME='RMS'
OR USERNAME='TAMPR'
OR USERNAME='TD'
OR USERNAME='TP'
OR USERNAME='TR'
OR USERNAME='TS'
OR USERNAME='KAROMI'
OR USERNAME='WILCDA'
OR USERNAME='WILDBA' GROUP BY OSUSER,USERNAME HAVING COUNT(*) > 3 order by count(*);
Example:
OSUSER USERNAME INACTIVE
-------------------- --------------- --------
toolstores MISQRY 6
wwashok WILDBA 7
rmstores RMS 9
stores GS 11
toolstores TS 11
wwstores WILDBA 20

18.@sysstat - to tune the database

Script:
SELECT a.name,b.value
FROM v$statname a,
v$sysstat b
WHERE a.statistic# = b.statistic#
AND UPPER(a.name) LIKE '%' || UPPER('&statname') || '%'

Example:
mis>@sysstat
Enter value for statname: PX local messages sent

NAME VALUE
------------------------------ ----------
PX local messages sent 182

19.@alz - to analyze the table

Script:
select owner,table_name,blocks,empty_blocks,num_rows from dba_tables where owner=upper('&owner') and table_name=upper('&table_name');

Example:
mis>@alz
Enter value for owner: wildba
Enter value for table_name: ds_invoice

OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ------------------------------ ---------- ------------ ----------
WILDBA DS_INVOICE 8684 430 192255

20.@chkpt - t0 check when the last checkpoint has occurred

Script:
select name,checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header;
Example:
NAME CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_
------------------------------ ------------------ -------------------
E:\MIS\DATA\INDEX_DATA01.DBF 200965067 02.03.2007 13:11:39
E:\MIS\DATA\INDEX_DATA02.DBF 200965067 02.03.2007 13:11:39
E:\MIS\DATA\INDEX_DATA03.DBF 200965067 02.03.2007 13:11:39
E:\MIS\DATA\INDEX_DATA04.DBF 200965067 02.03.2007 13:11:39
E:\MIS\DATA\INDEX_DATA05.DBF 200965067 02.03.2007 13:11:39
E:\MIS\DATA\INDEX_DATA06.DBF 200965067 02.03.2007 13:11:39

21.@time - to check the particular os user who is inactive

Script:
ELECT LPAD(SID,4) SID,LPAD(SERIAL#,7) SERIAL#,
USERNAME, RPAD(OSUSER,12) OSUSER, LPAD(COMMAND,7) COMMAND,STATUS,MACHINE,
RPAD(TO_CHAR(LOGON_TIME,'DD-MON-YY:HH:MI:SS-AM'),23) LOGIN_TIME
FROM V$SESSION WHERE USERNAME IS NOT NULL AND OSUSER like '&OSUSER%' AND STATUS NOT IN ('KILLED') order by LOGON_TIME;

Example:
mis>@time
Enter value for osuser: apa

SID SERIAL# USERNAME OSUSER COMMAND STATUS MACHINE
---- ------- --------------- --------------- ------- -------- -------------
58 14854 TS aparajita 0 INACTIVE DOMAIN\PC447
227 4382 RMS aparajita 0 INACTIVE DOMAIN\PC447
310 2514 GS aparajita 0 INACTIVE DOMAIN\PC280
110 14289 GS aparajita 0 INACTIVE DOMAIN\PC280


22.@tgrow - to check for table growth.

Script:
select /*+ index */ table_name,(blocks*8192)/(1024*1024) "GROWTH" from dba_tables where owner='WILDBA' AND (blocks*8192)/(1024*1024) > 100 order by (blocks*8192)/(1024*1024);

Example:
mis>@tgrow

TABLE_NAME GROWTH
------------------------------ ------------
PERD_TRANS_AUDIT 115.46
ISS_ACCP_DETL_GS 214.45
PERD_TRANS 367.06
CONSUMP_TABLE_TMP 504.56
PC_ORDER_DETAIL 953.05

23.@igrow - to check for index growth.

Script:
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 by LEAF_BLOCKS;

Example:
TABLE_NAME INDEX_NAME GROWTH
------------------------------ ------------------------------ ------------
PC_ORDER_DETAIL PO_DTL_IND 212.38
PC_ORDER_DETAIL IND_FOR_GIN_UPD_3 464.77
PC_ORDER_DETAIL PK_PC_ORDER_DETAIL 633.80

24.@idle - to check for users are in idle state

Script:
select sid,serial#,osuser,username,trunc
(last_call_et/3600,2)||' hr' last_call_et from V$session where
last_call_et > 3600 and username is not null;

Example:

SID SERIAL# OSUSER USERNAME LAST_CALL_ET
--- ------- --------------- --------------- -------------
370 1188 ple MISQRY 4.48 hr
371 7592 psr TAMPR 2.98 hr
372 558 radmin MISQRY 4.92 hr
375 652 wwashok WILDBA 2.23 hr
376 1580 TOOLSTORES TD 2.42 hr
379 1339 rbharathi PUR 1.36 hr

25.@privs - to check for object privilege

Script:
SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME=upper('&TABLE_NAME') and owner='WILDBA';

Example:

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
RWH WILDBA DS_INVOICE WILDBA SELECT
RWH WILDBA DS_INVOICE WILDBA UPDATE
RTS WILDBA DS_INVOICE WILDBA DELETE
RTS WILDBA DS_INVOICE WILDBA INSERT
RTS WILDBA DS_INVOICE WILDBA SELECT
RTS WILDBA DS_INVOICE WILDBA UPDATE

26.@user - to check how much mb users are using

Script:
select owner,
sum(bytes)/1024/1024 user_space_usage_mb,
sum(bytes)/ts.tot_size*100 pct_usage
from dba_segments s,
(select sum(bytes) tot_size
from dba_segments
where tablespace_name ='WILDATA') ts
where tablespace_name = 'WILDATA'
group by owner,ts.tot_size
having sum(bytes) > 1024*1024
order by 2

Example:
OWNER USER_SPACE_USAGE_MB PCT_USAGE
------------------------------ ------------------- ---------
MP 1.06 .01
TSCST 3.08 .04
RMS 3.40 .05
AIRSUS 3.43 .05
JVC 5.45 .08
PLE 5.60 .08
CST 15.20 .21
27.@rsql - to check current sql for particular rollback segment

Script:
SELECT Y.SQL_TEXT, R.NAME, S.OSUSER, S.USERNAME, X.EXTENTS, X.EXTENDS FROM SYS.V_$ROLLSTAT X, SYS.V_$ROLLNAME 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 t.addr = s.taddr(+) AND r.usn = x.usn(+) AND
r.usn = t.xidusn(+) AND S.USERNAME NOT IN ('SYS','SYSTEM') and r.name=upper('&name')
ORDER BY r.usn;

Example:
mis>@rsql

USN NAME
---------- -----------------
0 SYSTEM
1 SYS_RBS
3 RBS01
4 RBS02
5 RBS03
6 RBS04
7 RBS05
8 RBS06
9 RBS07
10 RBS08
11 RBS09
12 RBS10

12 rows selected.

Enter value for name: rbs01
SQL_TEXT
--------------------------------------------------------------------------------------------------
NAME OSUSER USERNAME EXTENTS EXTENDS
------------------------------ --------------- --------------- ---------- ----------
SELECT ROWID,RND_NOTE_TYPE,RND_NOTE_YEAR,RND_NOTE_NO,RND_DRAW_NO,RND_SHEET_NO,RND_FROM_DTL_NO,RND_
NO,RND_COMP_DRAWNO,RND_DESC,RND_ISSUE,RND_ISSUE_DATE,RND_TYPE,RND_STATUS,RND_TYPE_DESC,RND_STATUS_
OM REL_NOTE_DETAIL WHERE RND_NOTE_TYPE=:1 AND RND_NOTE_YEAR=:2 AND RND_NOTE_NO=:3 order by rnd_no
, rnd_note_year, rnd_note_no
RBS01 asaravanan TD 289 33
28.@uga-display the session uga memory

Script:
SELECT username, value || 'bytes' "Current UGA memory"
FROM v$session sess, v$sesstat stat, v$statname name
WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name = 'session uga memory';

Example:
USERNAME Current UGA memory
------------------------------ -------------------
PE 69928bytes
WILDBA 29356bytes
PUR 47320bytes
MISQRY 47320bytes
TAMPR 25120bytes
MISQRY 20884bytes
TP 69924bytes

29.@tune-display statistics of the background processes
Script:
select
event c1,
total_waits c2,
time_waited / 100 c3,
total_timeouts c4,
average_wait /100 c5
from
sys.v_$system_event
where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net break/reset to client',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'virtual circuit status',
'WMON goes to sleep'
)
AND
event not like 'DFS%'
and
event not like '%done%'
and
event not like '%Idle%'
AND
event not like 'KXFX%'
order by
c2 desc;

Example;
Event Total Seconds Total Wait
Name Waits Waiting Timeouts (in secs)
------------------------------ ------------ -------- ------------ ---------
library cache lock 4 0 2 .000
buffer deadlock 3 0 3 .000
PX qref latch 3 0 3 .000
SQL*Net more data to dblink 2 0 0 .000
inactive session 1 0 1 .000

29.@TSIZE-display the tablespace size

Script:
SET VERIFY OFF
COLUMN SEGMENT_NAME FORMAT A20
SELECT SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,SUM(BYTES)/(1024*1024) "MB",SUM(BLOCKS) "BLOCKS" FROM DBA_SEGMENTS WHERE owner=upper('&owner') and SEGMENT_NAME like upper('&SEGMENT_NAME%') GROUP BY SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME;

Example:
mis>@tsize
Enter value for owner: wildba
Enter value for segment_name: IND_TOOL_OBS

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
-------------------- ------------------ ------------------------------ ---------- ----------
IND_TOOL_OBS INDEX INDEX_DATA 1.8671875 239



30. @idx-to check for how much indexes in the table



Script:
select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS,STATUS,LAST_ANALYZED from dba_indexes where OWNER=UPPER('&OWNER') AND table_name=UPPER('&TABLE_NAME');

Example:
mis>@idx
Enter value for owner: wildba
Enter value for table_name: ds_invoice

INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS STATUS
------------------------------ ---------- ----------- ------------- ---------- -------
CANBEDELETED 1 330 2760 124738 VALID
DS_INVOICE_IDX1 2 509 2988 192255 VALID
DS_INV_ENTRDTTM 1 260 72714 98169 VALID
GATEPASS_IND 0 1 6 6 VALID
INVSDNOCANCELLED 1 420 21263 192255 VALID
PACKING_LIST_INVOICE_FK 2 705 192247 192255 VALID
PK_DS_INVOICE 2 724 192255 192255 VALID
SALES_TAX_CODES_INVOICE_FK 1 375 30 191979 VALID

31. @csql- to display user executing the sql statement

Script:
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address AND OSUSER like '&osuser'
order by address, piece;

Example:
mis>@csql
Enter value for osuser: balaji

OSUSER USERNAME SQL_TEXT
--------- ---------- ----------------------------------------------------------------
balaji FORMCONTRO SELECT ROWID,ACCHEAD,SUBACC,NAME FROM subacc WHERE (ACCHEAD=:1)
L

balaji PLE UPDATE "PE_WO_DETAIL" "A1" SET "ACCOUNTED_AMT" = NVL("A1"."ACCOU
balaji PLE NTED_AMT",0)+NVL(:B1,0) WHERE "A1"."WO_NO"=DECODE(SUBSTR(:B2,1,2
balaji PLE ),'FC','FC/','CC/')||SUBSTR(:B2,3) AND "A1"."WO_SLNO"=:B4
balaji PLE SELECT /*+ */ COUNT(*) FROM "PE_WO_DETAIL" "A1" WHERE "A1"."WO_N
balaji PLE O"=DECODE(SUBSTR(:B1,1,2),'FC','FC/','CC/')||SUBSTR(:B1,3) AND "
balaji PLE A1"."WO_AMOUNT"-NVL("A1"."ACCOUNTED_AMT",0)>0
balaji TAMPR SELECT "A1"."NAME" FROM "CCMAS" "A1" WHERE "A1"."CCCODE"=:B1
balaji PUR UPDATE "PC_SUPP_MAST" "A1" SET "SM_NAME" = SUBSTR(:B1,1,40) WHER

32. @alter- to check invalid objects of the table or whole database

Script:
SET VERIFY OFF
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='&TABLE_NAME' and b.status='INVALID';

33. @to_pin-

Script:
select OWNER,
NAME||' - '||TYPE object,
SHARABLE_MEM
from v$db_object_cache
where SHARABLE_MEM > 10000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
and owner !='SYS'
order by SHARABLE_MEM desc;

Example:
OWNER OBJECT
------------ -------------------------------------
WILDBA SUM_MFG_DETAIL - PROCEDURE
WILDBA SUM_MFG_DTLS - PROCEDURE
WILDBA SUM_MFG_DETL - PROCEDURE
WILDBA ANNLPLAN - PROCEDURE
WILDBA PEMONTH - PROCEDURE
WILDBA POPULATE_COMP_DETAILS - PROCEDURE
WILDBA INS_PERD_TRANS_NEW_TS - PROCEDURE
WILDBA CST_GIN_VAL_RM_TS - PROCEDURE
WILDBA INS_PERD_TRANS_LOAN_TS - PROCEDURE
WILDBA INS_PERD_TRANS - PROCEDURE
WILDBA INS_PERD_TRANS_SUPP - PROCEDURE
WILDBA POPULATE_WHEEL_SUMMARY_EXP - PROCEDUR
WILDBA GET_RATE_RM_TS - FUNCTION
WILDBA CST_GIN_VAL_TOOL_TS - PROCEDURE
WILDBA INVNOCHG - PROCEDURE
WILDBA GET_AVAILABLE_STOCK - PROCEDURE
WILDBA INS_PERD_TRANS_RM_TS - PROCEDURE
WILDBA GENERATEDEBITNOTE - PROCEDURE

34.
Script:
SELECT a.tablespace_name,b.file_name,x.free_in_mb,
SUM(CEIL(a.bytes/1024/1024)) used_in_mb
FROM dba_extents a,
dba_data_files b,
(select y.tablespace_name,y.file_id,SUM(y.bytes/1024/1024) free_in_mb
from dba_free_space y
group by y.tablespace_name,y.file_id) x
WHERE a.owner LIKE UPPER('&&owner')
AND a.segment_name LIKE UPPER('&&tname')
AND a.tablespace_name = b.tablespace_name
AND a.file_id = b.file_id
AND x.tablespace_name(+) = b.tablespace_name
AND x.file_id(+) = b.file_id
GROUP BY a.tablespace_name,b.file_name,x.free_in_mb;

Example:
TABLESPACE_NAME FILE_NAME FREE_IN_MB USED_IN_MB
--------------- ---------------------------------------- ---------- ----------
WILDATA D:\MIS\DATA\WILDATA01.DBF 973 3
WILDATA D:\MIS\DATA\WILDATA03.DBF 814 4
WILDATA E:\MIS\DATA\WILDATA02.DBF 39
WILDATA E:\MIS\DATA\WILDATA04.DBF 8
WILDATA E:\MIS\DATA\WILDATA05.DBF 11
WILDATA E:\MIS\DATA\WILDATA06.DBF 639 16


Create table & synonym permissions:

create table wildba.medi_item_master as select * from trainee.medi_item_master;
create synonym misqry.medi_item_master for wildba.medi_item_master;
grant all on wildba.medi_item_master to misqry;

Move table:

select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='WILDBA.GET_RATE_TL_TS' and b.status='INVALID';

CONN WILDBA@MIS

Create db link already is there no problem

CREATE TABLE INWARD_CORRECTION AS SELECT * FROM INWARD_CORRECTION@MISBK2MIS;

Fmotrainee
==============
create table wildba.medi_item_master as select * from trainee.medi_item_master;
create synonym misqry.medi_item_master for wildba.medi_item_master;
grant all on wildba.medi_item_master to misqry;

create table wildba.medical_stock as select * from trainee.medical_stock;
create table wildba.medical_purchase as select * from trainee.medical_purchase;
create table wildba.medical_sales as select * from trainee.medical_sales;


create synonym misqry.medi_item_master for wildba.medi_item_master;
create synonym misqry.medical_stock for wildba.medical_stock;
create synonym misqry.medical_purchase for wildba.medical_purchase;
create synonym misqry.medical_sales for wildba.medical_sales;
create synonym misqry.patient_mast for wildba.patient_mast;

grant all on wildba.medi_item_master to misqry;
grant all on wildba.medical_stock to misqry;
grant all on wildba.medical_purchase to misqry;
grant all on wildba.medical_sales to misqry;

create table wildba.patient_mast as select * from trainee.patient_mast;
create table wildba.patient_detl as select * from trainee.patient_detl;
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.groupmas as select * from trainee.groupmas;


create synonym misqry.patient_mast FOR WILDBA.patient_mast;
create synonym misqry.patient_detl FOR WILDBA.patient_detl;
create synonym misqry.COMPLAIN FOR WILDBA.COMPLAIN;
create synonym misqry.cost_centre_mast FOR WILDBA.cost_centre_mast;
create synonym misqry.groupmas FOR WILDBA.groupmas;
create synonym misqry.empmas for wildba.empmas;


GRANT ALL ON WILDBA.patient_mast TO MISQRY;
GRANT ALL ON WILDBA.patient_detl TO MISQRY;
GRANT ALL ON WILDBA.COMPLAIN TO MISQRY;

GRANT ALL ON WILDBA.cost_centre_mast TO MISQRY;
GRANT ALL ON WILDBA.GROUPMAS TO MISQRY;
GRANT ALL ON WILDBA.EMPMAS TO MISQRY;
===================================================================================


create synonym TS.DS_INVOICE_POP FOR WILDBA.DS_INVOICE_POP;

GRANT ALL ON WILDBA.DS_INVOICE_POP TO TS;



CREATE SYNONYM TS.USER_DEPT_LINK FOR WILDBA.USER_DEPT_LINK;

GRANT SELECT,UPDATE ON WILDBA.USER_DEPT_LINK TO RTS;








1.Please Create the synonym for table “Messages” ,user “Airsus” @mis database.


CONNECT WILDBA @MIS


CREATE SYNONYM TRAINEE.STD_CODE FOR WILDBA.STD_CODE;
GRANT SELECT ON WILDBA.STD_CODE TO TRAINEE;



CREATE SYNONYM TP.DS_PACKING_LIST_DETAIL FOR WILDBA.DS_PACKING_LIST_DETAIL ;

CREATE SYNONYM TS.DS_PACKING_LIST_DETAIL FOR WILDBA.DS_PACKING_LIST_DETAIL ;


CREATE SYNONYM TP.DS_PACKING_LIST FOR WILDBA.DS_PACKING_LIST;
CREATE SYNONYM TS.DS_PACKING_LIST FOR WILDBA.DS_PACKING_LIST;



GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST_DETAIL TO TP;
GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST TO TP;

GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST_DETAIL TO TS;
GRANT SELECT, INSERT ON WILDBA.DS_PACKING_LIST TO TS;









GRANT SELECT ON WILDBA.GIN_RECP_MAST_CG TO MISQRY;


CREATE SYNONYM MISQRY.GIN_RECP_DETL_CG FOR WILDBA.GIN_RECP_DETL_CG;
GRANT SELECT ON WILDBA.GIN_RECP_DETL_CG TO MISQRY;


CREATE SYNONYM MISQRY.GATE_BILL_DETAIL FOR WILDBA.GATE_BILL_DETAIL;
GRANT ALL ON WILDBA.GATE_BILL_DETAIL TO MISQRY;



CREATE SYNONYM DS.TR_RAW_MATL_MAST FOR WILDBA.TR_RAW_MATL_MAST;
GRANT SELECT ON WILDBA.TR_RAW_MATL_MAST TO DS;



CREATE SYNONYM MISQRY.FORMCONTROLMASTER FOR FORMCONTROL.FORMCONTROLMASTER;

GRANT SELECT ON FORMCONTROL.FORMCONTROLMASTER TO MISQRY;

DROP SYNONYM MISQRY.FORMCONTROLMASTER




CREATE SYNONYM MISQRY.WILHELP FOR WIL.WILHELP;

CREATE SYNONYM MISQRY.WIL_DIR FOR WIL.WIL_DIR;

CREATE SYNONYM MISQRY.WIL_INI_DETAILS FOR WIL.WIL_INI_DETAILS;

CREATE SYNONYM MISQRY.WIL_MENU_CST FOR WIL.WIL_MENU_CST;



CREATE SYNONYM MISQRY.WIL_MENU FOR WIL.WIL_MENU

CREATE SYNONYM MISQRY.WIL_MENU_ORG FOR WIL.WIL_MENU_ORG;

CREATE SYNONYM MISQRY.WIL_MENU_USER FOR WIL.WIL_MENU_USER;

CREATE SYNONYM MISQRY.WIL_OBJECTS FOR WIL.WIL_OBJECTS;

CREATE SYNONYM MISQRY.WIL_PERMISSION FOR WIL.WIL_PERMISSION;

CREATE SYNONYM MISQRY.WIL_SYNONYMS FOR WIL.WIL_SYNONYMS;

CREATE SYNONYM MISQRY.WIL_TAB_COLUMNS FOR WIL.WIL_TAB_COLUMNS;

CREATE SYNONYM MISQRY.WIL_USERS FOR WIL.WIL_USERS;

CREATE SYNONYM MISQRY.WIL_USER_MOD FOR WIL.WIL_USER_MOD;


GRANT SELECT ON WIL.WILHELP TO MISQRY;
GRANT SELECT ON WIL.WIL_DIR TO MISQRY;
GRANT SELECT ON WIL.WIL_INI_DETAILS TO MISQRY;
GRANT SELECT ON WIL.WIL_MENU_CST TO MISQRY;
GRANT SELECT ON WIL.WIL_MENU_ORG TO MISQRY;
GRANT SELECT ON WIL.WIL_MENU_USER TO MISQRY;
GRANT SELECT ON WIL.WIL_OBJECTS TO MISQRY
GRANT SELECT ON WIL.WIL_PERMISSION TO MISQRY;
GRANT SELECT ON WIL.WIL_SYNONYMS TO MISQRY;
GRANT SELECT ON WIL.WIL_TAB_COLUMNS TO MISQRY;
GRANT SELECT ON WIL.WIL_USERS TO MISQRY;
GRANT SELECT ON WIL.WIL_USER_MOD TO MISQRY;

GRANT SELECT ON WIL.WIL_MENU TO MISQRY;

CREATE SYNONYM PUR.GIN_RECP_MAST_PKG FOR WILDBA.GIN_RECP_MAST_PKG

GRANT EXECUTE ON WILDBA.GIN_RECP_MAST_PKG TO PUR;


CREATE SYNONYM MISQRY.PATIENT_SEQMAST FOR WILDBA.PATIENT_MAST;
CREATE SYNONYM MISQRY.PAD_SEQUENCE_PS FOR WILDBA.PAD_SEQUENCE_PS
CREATE SYNONYM MISQRY.PATIENT_DETL FOR WILDBA.PATIENT_DETL;
Create synonym GS.GATE_BILL_DETAIL_HISTORY for WILDBA.gate_bill_detail_history;
create synonym pur.gate_bill_detail_history for wildba.gate_bill_detail_history;
create synonym pur.gate_bill_detail for wildba.gate_bill_detail;
CREATE SYNONYM QLTY.WARRANTY_MASTER FOR WARRANTY.WARRANTY_MASTER
CREATE SYNONYM QLTY.CUSTOMER_NAME FOR WARRANTY.CUSTOMER_NAME

GRANT SELECT ON PATIENT_MAST TO MISQRY;
GRANT SELECT ON PATIENT_DETL TO MISQRY;
GRANT SELECT ON MESSAGES TO RAIRSUS
GRANT SELECT ON WARRANTY_MASTER TO QLTY
GRANT SELECT ON CUSTOMER_NAME TO QLTY

CREATE SEQUENCE AIRSUS.MESSAGES FOR WILDBA.MESSAGES

2.Please create the synonym for the following objects for the user airsus @ Mis Database

1.Sequence - Gs_Doc_seq

2.Table - Empmas

CONNECT WILDBA @MIS

CREATE SYNONYM AIRSUS.Gs_Doc_deq FOR WILDBA.GS_DOC_DEQ

GRANT SELECT ON GS_DOC_DEQ TO RAIRSUS.


CREATE SYNONYM WARRANTY.WIL_INI_DETAILS FOR WILDBA.WIL_INI_DETAILS

GRANT SELECT ON WIL_INI_DETAILS TO WARRANTY

CREATE SYNONYM AIRSUS.EMPMAS FOR WILDBA.EMPMAS

GRANT SELECT ON EMPMAS TO RAIRSUS
----------------------------------------------------------------------------

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.



Inward_correction

Inward_correction_mast

Inward_correction_detl

PATIENT_MAST
PATIENT_DETL

select object_name,object_type,status from dba_objects where object_name='PATIENT_MAST';

CONN WILDBA@MIS

CREATE TABLE INWARD_CORRECTION AS SELECT * FROM INWARD_CORRECTION@MISBK2MIS;

create table patient_mast as select * from patient_mast@misbk2mis;
create table patient_detl as select * from patient_detl@misbk2mis

CREATE TABLE INWARD_CORRECTION_MAST AS SELECT * FROM INWARD_CORRECTION_MAST @MISBK2MIS;

CREATE TABLE INWARD_CORRECTION_DET1 AS SELECT * FROM INWARD_CORRECTION_DET1 @MISBK2MIS;

CONN WILDBA@WWDB

CREATE TABLE INWARD_CORRECTION AS SELECT * FROM INWARD_CORRECTION @MISBKTOWWDB;

CREATE TABLE INWARD_CORRECTION_MAST AS SELECT * FROM INWARD_CORRECTION_MAST @MISBKTOWWDB ;
CREATE TABLE INWARD_CORRECTION_DET1 AS SELECT * FROM INWARD_CORRECTION_DET1 @MISBKTOWWDB ;


CREATE TABLE ES_PROD_MAST_WW AS SELECT * FROM ES_PROD_MAST_WW @MIS2MISBK

CREATING SYNONYMS TO MP@MIS
---------------------------
CONN WILDBA@MIS

CREATE SYNONYM MP.INWARD_CORRECTION FOR WILDBA.INWARD_CORRECTION

GRANT SELECT ON INWARD_CORRECTION TO RMP


CREATE SYNONYM MP.INWARD_CORRECTION_MAST FOR WILDBA.INWARD_CORRECTION_MAST

GRANT SELECT ON INWARD_CORRECTION_MAST TO RMP


CREATE SYNONYM MP.INWARD_CORRECTION_DET1 FOR WILDBA.INWARD_CORRECTION_DET1

GRANT SELECT ON MP.INWARD_CORRECTION_DET1 TO RMP


CREATING SYNONYMS TO MP@WWDB
----------------------------

CONNECT WILDBA@WWDB;


CREATE SYNONYM MP.INWARD_CORRECTION(NEWNAME) FOR WILDBA.INWARD_CORRECTION

GRANT SELECT ON MP.INWARD_CORRECTION TO RMP


create table NEW_Loadtrace tablespace usr01 .....
as select * from loadTrack;



CREATE SYNONYM MP.INWARD_CORRECTION_MAST FOR WILDBA.INWARD_CORRECTION_MAST

GRANT SELECT ON MP.INWARD_CORRECTION_MAST TO RMP


CREATE SYNONYM MP.INWARD_CORRECTION_DET1 FOR WILDBA.INWARD_CORRECTION_DET1

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…

BACKUP 190606

RESTORE
--------------------------------------------------------------------------

4.Pls add following two fileds in GIN_mast_mp table in all database.



gmm_gin_cancel varchar2(1)

gmm_gin_cancel_date date


CONECT AS WILDBA@MIS,@WIL,@WWDB

Alter table GIN_MAST_MP ADD GMM_GIN_CANCEL VARCHAR2(1),GMM_GIN_CANCEL_DATE;



select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='WILDBA.GET_RATE_TL_TS' and b.status='INVALID';

INV_CESS_PER_ONE
INV_CESS_AMOUNT_ONE

COMPILE THE PROCEDURES,TRIGGERS,PACKAGE

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

5.Please alter the below tables.

pc_order_mast
pc_order_mast_mn
pc_order_mast_imp


modify fc_number number(4) to fc_number number(10)

ALTER TABLE PATIENT_MAST MODIFY TOKEN_NO VARCHAR2(5);

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_imp modify fc_number number(10);

select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and owner='RMS' and b.status='INVALID';



select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='PATIENT_MAST' and b.status='INVALID';


select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='TEST_TAB' and b.status='INVALID';

compile trig,pro,body.


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

CREATE DATABASE LINK MISBKTOWWDBWILDBA CONNECT TO WILDBA IDENTIFIED BY DBAWIL F 'WWDB';

CREATE DATABASE LINK WILDBAMISTOWILWIL CONNECT TO WIL IDENTIFIED BY P25THIBY2 USING 'WIL'

CREATE VIEW EMP_MAST AS SELECT * FROM WIL.EMP_MAST@WILDBAMISTOWILWIL
create view patient_mast as select * from mis_bk.patient_mast@@misbk2mis;
CREATE SYNONYM MISQRY.EMP_MAST FOR WILDBA.EMP_MAST;
create synonym misqry.patient_mast for wildba.patient_mast;
GRANT SELECT ON WILDBA.EMP_MAST TO MISQRY;


CREATE VIEW EMPMAS AS SELECT * FROM WIL.EMPMAS@WILDBAMISTOWILWIL;

CREATE SYNONYM MISQRY.EMPMAS FOR WILDBA.EMPMAS;
GRANT SELECT ON WILDBA.EMPMAS TO MISQRY;


CREATE VIEW EMP_ERNDED AS SELECT * FROM WIL.EMP_ERNDED@WILDBAMISTOWILWIL;

CREATE SYNONYM MISQRY.EMP_ERNDED FOR WILDBA.EMP_ERNDED;

GRANT SELECT ON WILDBA.EMP_ERNDED TO MISQRY;



CREATE VIEW ERNDED_HEADMAST AS SELECT * FROM WIL.ERNDED_HEADMAST@WILDBAMISTOWILWIL;

CREATE SYNONYM MISQRY.ERNDED_HEADMAST FOR WILDBA.ERNDED_HEADMAST;

GRANT SELECT ON WILDBA.ERNDED_HEADMAST TO MISQRY;


CREATE VIEW PRS_GEN AS EXECUTE ON WIL.PRS_GEN@WILDBAMISTOWILWIL;








Alter table EMP ADD EMPSTATUS1 VARCHAR2(15) DEFAULT 'NULL'
Alter table EMP MODIFY COLUMN EMPSTATUS DEFAULT " ";

ALTER TABLE MODIFY COLUMN DROP DEFAULT;






8.SET THE DEFAULT VALUE FOR A COLUMN
-----------------------------------
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DEFAULT 'GK'

ALTER TABLE PE_PRODLOSS_MAST MODIFY PE_WHEEL_CODE DEFAULT NULL;

9i :ALTER TABLE
RENAME COLUMN TO ;
ex : ALTER TABLE test RENAME COLUMN third_col TO date_col

USER_TAB_COLUMNS

ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT defaultvalue | DROP DEFAULT }

ALTER TABLE PE_PRODLOSS_MAST ADD CONSTRAINT GKCONSTRAINT NULL PE_WHEEL_CODE;



ALTER TABLE TABLE_NAME

ADD CONSTRAINT CONSTRAINT_NAME PRIMARYKEY EMPNO;




SELECT * FROM all_tab_columns
WHERE table_name='PE_PRODLOSS_MAST'



ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME CASCADE DEPENDENT




ALTER TABLE DS_PACKING_LIST
ENABLE CONSTRAINT FK_PACKING_LIST_INVOICE CASCADE DEPENDENT



ALTER TABLE TABLE_NAME
ENABLE CONSTRAINT CONS_NAME

ALTER TABLE TABLE_NAME
ADD COLUMN_NAME DATATYPE DEFAULT VALUE

ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME

MOVING TABLE:
=============

USING DB LINK CREATE TABLE TABLE_NAME AS SELECT *

SET UNUSED OPTION
================

ALTER TABLE JOBS SET UNUSED COLUMN EMPVAL3
ALTER TABLE JOBS DROP UNUSED columns


GRANT SELECT ON EMP TO USER


GRANT UPDATE(DEPTNO, LOCATION_ID) ON DEPARTMENTS TO SCOTT



alter table ES_PROD_MAST ADD PRM_PRODUCT_TYPE VARCHAR2(6);
alter table MED_CLAIM_DETL add pay_date date;


select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a.referenced_name ='TR_PACK_SLIP_DETAIL' AND and b.status='INVALID';


select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a.referenced_name ='PATIENT_MAST' and b.status='INVALID';


CREATE SYNONYM ES. FOR WILDBA.ES_PROD_MAST_WW

GRANT SELECT ON MESSAGES TO RAIRSUS

GRANT SELECT ON ES_PROD_MAST_WW TO RES

CREATE SYNONYM ES.DS_PRODUCT_MAST FOR WILDBA.DS_PRODUCT_MAST

GRANT SELECT ON DS_PRODUCT_MAST TO RES

alter user lambent quota unlimited on WIL_DATA


CREATE PUBLIC DATABASE LINK AA CONNECT TO LAMBENT IDENTIFIED BY LAMBENT USING 'MIS';


CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
TABLESPACE INDEX_DATA;


CREATE UNIQUE INDEX LAMBENT.EMP_INDEX ON LAMBENT.EMP (
DEPT)
TABLESPACE INDEX_DATA PCTFREE 10
STORAGE(INITIAL 10240 NEXT 10240 PCTINCREASE 50 )
NO LOGGING

ALTER TABLE GIN_RECP_DETL_GS
ADD(grd_total_amt Number (15,2),
grd_excise_amt Number (15,2),
grd_cess_amt Number (15,2),
grd_ST Number (15,2),
grd_sc_amt Number (15,2),
grd_asc_amt Number (15,2),
grd_sed_amt Number (15,2));


select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a.referenced_name ='GIN_RECP_DETL_GS' and b.status='INVALID';

Alter PACKAGE WILDBA.GIN_RECP_ADJM_PKG compile BODY;
Alter PACKAGE WILDBA.GIN_RECP_MAST_PKG compile BODY;
Alter PACKAGE WILDBA.GS_SHELF_LIFE_PKG compile BODY;
Alter PACKAGE WILDBA.ITM_RMR_DETL_PKG compile BODY;
Alter PACKAGE WILDBA.PUR_REQN_DETL_PKG compile BODY;


UPDATE PACCOUNTS.PAY_STAT SET USED=’N’ WHERE TYPE=’PJBULK’;COMMIT;
==========================================================
Triggers
==========================================================
ALTER TRIGGER TR_GIN_DETAIL_DEL_UPD_NEW DISABLE

ALTER TRIGGER DS_EXCISE_CHAP_NO DISABLE;

SELECT OWNER,TABLE_NAME,TRIGGER_NAME,STATUS FROM DBA_TRIGGERS
WHERE TRIGGER_NAME='TR_GIN_DETAIL_DEL_UPD_NEW';

SELECT OWNER,TABLE_NAME,TRIGGER_NAME,STATUS FROM DBA_TRIGGERS
WHERE TRIGGER_NAME='TR_GIN_DETAIL_DEL_UPD_TRIG';




CREATE VIEW WILDBA.EMP_MAST AS SELECT * FROM EMP_MAST@


select 'ANALYZE INDEX ' || OWNER || '.'|| INDEX_NAME || ' VALIDATE STRUCTURE;' FROM DBA_INDEXES;



COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status,
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

PE_WORK_ORDER
PE_WO_ADD
PE_WO_DETAIL
PE_WO_LESS
PW_WO_RECON_DETAILS


select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a.referenced_name ='MSGPITEM' and b.status='INVALID';



CREATE SEQUENCE PATIENT_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999
CYCLE
CACHE 20



CREATE SEQUENCE PATIENT_SEQ
MIN_VALUE 1
MAX_VALUE 99999
INCREMENT_BY 1
CYCLE
cache 20
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 20
LAST_NUMBER

541;



ALTER SEQUENCE Emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;


CREATE unique INDEX GS_ISS_DETL_RS_IND11 ON
GS_ISSU_MAST( GS_DOC_NO, GS_DOC_DATE)
TABLESPACE INDEX_DATA ;



CREATE INDEX GIN_ISSU_MAST_IND ON
GIN_ISSU_MAST(GRD_ITM_CODE,GRD_GIN_NO)
TABLESPACE INDEX_DATA ;

CREATE VIEW PACCOUNTS.FC AS SELECT * FROM PACCOUNTS.FC@MISTOPACCOUNTS;



ALTER TABLE MEDICAL_STOCK
add CONSTRAINT MEDICAL_STOCK_CONSTRAINT UNIQUE (CODE,BATCH1);

26-07-2007 increase the size
=============================
ALTER TABLESPACE INDEX_DATA
ADD DATAFILE 'E:\MIS\DATA\INDEX_DATA09.DBF'
SIZE 1500M;

ALTER TABLESPACE WILDATA
ADD DATAFILE 'E:\MIS\DATA\WILDATA06.DBF'
SIZE 1500M;



ALTER TABLE ds_order_mast ADD orm_comm_code varchar2(30)

ALTER TABLE ds_product_mast ADD pdm_comm_code varchar2(30);


Please alter the table ds_product_mast @mis , wwdb and mis_bk



Add new column pdm_comm_code varchar2(30)





Alter PACKAGE WILDBA.MONTHLY_BUDGET_PKG compilE BODY;
Alter PACKAGE WILDBA.WH_GATE_PASS_PKG compile BODY;

Alter PACKAGE BODY WILDBA.MONTHLY_BUDGET_PKG compile;
Alter PACKAGE BODY WILDBA.WH_GATE_PASS_PKG compile;





CREATE SYNONYM MISQRY.EMPMAS FOR WILDBA.EMPMAS;
GRANT SELECT ON WILDBA.EMPMAS TO MISQRY;

CREATE SYNONYM MISQRY.MED_CLAIM_DETL FOR WILDBA.MED_CLAIM_DETL;
GRANT ALL ON WILDBA.MED_CLAIM_DETL TO MISQRY;

CREATE SYNONYM MISQRY.FAMDET FOR TAMPR.FAMDET
GRANT SELECT ON TAMPR.FAMDET TO MISQRY;

CREATE SYNONYM MISQRY.GRADE FOR WILDBA.GRADE;
GRANT SELECT ON WILDBA.GRADE TO MISQRY;






create table wildba.gin_recp_mast_cg as select * from gin_recp_mast_cg@misbk2mis where 1=2;

create table wildba.gin_recp_detl_cg as select * from gin_recp_detl_cg@misbk2mis where 1=2;



CREATE SYNONYM gs.gin_recp_mast_cg for wildba.gin_recp_mast_cg;

CREATE SYNONYM gs.gin_recp_detl_cg for wildba.gin_recp_detl_cg;

grant all on wildba.gin_recp_mast_cg to gs;

grant all on wildba.gin_recp_detl_cg to gs;

create synonym gs.pc_order_mast_mn for wildba.pc_order_mast_mn;

create synonym gs.pc_order_detail_mn for wildba.pc_order_detail_mn;

create synonym gs.formcontrolmaster for formcontrol.formcontrolmaster;

grant all on wildba.pc_order_mast_mn to gs;

grant all on wildba.pc_order_detail_mn to gs;

grant all on formcontrol.formcontrolmaster to gs;
(know password is possible)


select s.sid,s.serial#,s.username,o.owner,s.osuser,s.terminal,o.object_type,s.status,
o.object_name 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 null and object_name='JOB_PACK_SLIP_MAST_MP';



CREATE SYNONYM DS.DS_PACKING_LIST_DETAIL_TR FOR WILDBA.DS_PACKING_LIST_DETAIL_TR;
CREATE SYNONYM TS.DS_PACKING_LIST_DETAIL_TR FOR WILDBA.DS_PACKING_LIST_DETAIL_TR;
CREATE SYNONYM TP.DS_PACKING_LIST_DETAIL_TR FOR WILDBA.DS_PACKING_LIST_DETAIL_TR;





CREATE SYNONYM DS.DS_PACKING_LIST_TR FOR WILDBA.DS_PACKING_LIST_TR;
CREATE SYNONYM TS.DS_PACKING_LIST_TR FOR WILDBA.DS_PACKING_LIST_TR;
CREATE SYNONYM TP.DS_PACKING_LIST_TR FOR WILDBA.DS_PACKING_LIST_TR;




ALTER TABLE DS_PACKING_LIST_TR DISABLE CONSTRAINT SYS_C009851;

ALTER TABLE DS_PACKING_LIST_TR DISABLE CONSTRAINT SYS_C009852;
ALTER TABLE DS_PACKING_LIST_TR DISABLE CONSTRAINT PK_DS_PACKING_LIST_TR;
ALTER TABLE DS_PACKING_LIST_TR DISABLE CONSTRAINT FK_CUSTOMER_PACKING_LIST_TR;
ALTER TABLE DS_PACKING_LIST_TR DISABLE CONSTRAINT FK_PACKING_LIST_INVOICE_TR;
ALTER TABLE DS_PACKING_LIST_TR DISABLE CONSTRAINT FK_SALES_TYPE_PACKING_LIST_TR;


ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT SYS_C009857;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT SYS_C009858;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT SYS_C009859;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT SYS_C009860;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT PKD_QTY_NOTNULL_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT PKD_PRICE_NOTNULL_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT PKD_QTY_CHK_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT PKD_PRICE_CHK_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT PKD_ALLOT_QTY_DS_PACKING_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT PK_DS_PACKING_LIST_DETAIL_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT FK_PACK_LIST_DETL_PRODUCT_TR;
ALTER TABLE DS_PACKING_LIST_DETAIL_TR DISABLE CONSTRAINT FK_PACK_LIST_MAST_DETAIL_TR;

create synonym karomi.TR_TOOL_MAST for wildba.TR_TOOL_MAST;
grant select on wildba.TR_TOOL_MAST to karomi;

create synonym karomi.TR_PUR_ORDER_DETAIL for wildba.TR_PUR_ORDER_DETAIL;
grant select on wildba.TR_PUR_ORDER_DETAIL to karomi;

create synonym karomi.TR_PUR_ORDER_MAST for wildba.TR_PUR_ORDER_MAST;
grant select on wildba.TR_PUR_ORDER_MAST to karomi;

create synonym karomi.TR_WORK_ORDER for wildba.TR_WORK_ORDER;
grant select on wildba.TR_WORK_ORDER to karomi;

create synonym karomi.TR_GIN_RECP_MAST for wildba.TR_GIN_RECP_MAST;
grant select on wildba.TR_GIN_RECP_MAST to karomi;


create synonym karomi.TR_GIN_RECP_DETAIL for wildba.TR_GIN_RECP_DETAIL;
grant select on wildba.TR_GIN_RECP_DETAIL to karomi;



create synonym karomi.TR_TOOL_ISSUE_DETAIL for wildba.TR_TOOL_ISSUE_DETAIL;
grant select on wildba.TR_TOOL_ISSUE_DETAIL to karomi;


create synonym karomi.TR_TOOL_ISSUE_MAST for wildba.TR_TOOL_ISSUE_MAST;
grant select on wildba.TR_TOOL_ISSUE_MAST to karomi;


create synonym karomi.PC_SUPP_MAST for wildba.PC_SUPP_MAST;
grant select on wildba.PC_SUPP_MAST to karomi;


FK_PACKING_LIST_INVOICE_TR
FK_SALES_TYPE_PACKING_LIST_TR


msgp
msgpitem
asubcontractors

the above table in wwproject ,our task is the table move into wildba user then create the synonym of the above table wwproject,mp then give the privileges all in wwproject, select on to mp.so owner of the table is wildba


create table msgp as(select * from wwproject.msgp);
create table msgpitem as(select * from wwproject.msgpitem);
create table asubcontractors as(select * from wwproject.asubcontractors);

create synonym wwproject.msgp for wildba.msgp;
create synonym wwproject.msgpitem for wildba.msgpitem;
create synonym wwproject.asubcontractors for wildba.asubcontractors;

create synonym mp.msgp for wildba.msgp;
create synonym mp.msgpitem for wildba.msgpitem;
create synonym mp.asubcontractors for wildba.asubcontractors;

grant all on wildba.msgp to wwproject;
grant all on wildba.msgpitem to wwproject;
grant all on wildba.asubcontractors to wwproject;

grant select on wildba.msgp to mp;
grant select on wildba.msgpitem to mp;
grant select on wildba.asubcontractors to mp;
=============================================================
28-02-07 :ora-01555 too snapshot old error
========
Solution :
There are three possible solutions to prevent this error:

1.Increase the size of the rollback segment.
Depending on the application, this can take a significant amount of disk space on the Oracle server.The size of the rollback segment can be estimated by monitoring the V$ROLLSTAT view, which has two columns that can point the way to an answer. The RSSIZE column represents the current total space used by a rollback segment, while the HWMSIZE column represents the high-water mark, the highest point reached by RSSIZE since the last time the database instance was started.


2.Modify the application to reduce the time to run long-running queries.


3.For Oracle 9i and later, set the value for the UNDO_MANAGEMENT parameter to AUTO.
When the parameter UNDO_MANAGEMENT is set to MANUAL (the default setting), the rollback segments are used, much as with the previous versions of Oracle. When the parameter UNDO_MANAGEMENT is set to AUTO the role of rollback segments are handled by undo tablespaces, which are locally-managed tablespaces containing rollback segments completely managed by the RDBMS.

ALTER database datafile 'E:\MIS\DATA\INDEX_DATA09.DBF' resize 1000m;privious 500m

ALTER database datafile 'E:\MIS\DATA\RBS02.DBF' resize 1300m; privious 800m

ALTER PUBLIC ROLLBACK SEGMENT RBS08 STORAGE (MAXEXTENTS unlimited);

SELECT segment_name, tablespace_name, bytes, blocks, extents
FROM sys.dba_segments
WHERE segment_type = 'ROLLBACK';
select * from v$rollstat;
select * from dba_rollback_segs;
select * from v$rollname;



solution:
Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.
-Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance. Code long running processes as a series of restartable steps.- Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation. - Use a large optimal value on all rollback segments, to delay extent reuse. - Don't fetch across commits. That is, don't fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.- Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.- Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.- If necessary, add extra rollback segments (undo logs) to make more transaction slots available.


1/03/07
=======
Check the objects are invalid

select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='DS_INVOICE' and b.status='INVALID';

INV_CESS_PER_ONE NUMBER(5,2)




alter table ds_invoice add INV_CESS_PER_ONE NUMBER(5,2);

alter table ds_invoice add INV_CESS_AMOUNT_ONE NUMBER(12,2);

Alter PACKAGE WILDBA.WH_GATE_PASS_PKG compile BODY;

select s.owner,s.synonym_name,d.db_link,d.username,d.host from dba_db_links d, dba_synonyms s
where d.db_link=s.db_link AND S.OWNER='WIL'

GRANT ISNERT,UPDATE ON TR_TOOL_MAST TO KAROMI;

select * from dba_tab_privs where table_name='TR_TOOL_MAST' AND GRANTEE='KAROMI'

MIS,WWDB,MIS_BK


alter table TR_PUR_ORDER_DETAIL add POD_CESS_ADDL_PER NUMBER(5,2);
alter table TR_PUR_ORDER_DETAIL add POD_CESS_ADDL_AMT NUMBER(12,2);

alter table PC_ORDER_DETAIL add POD_CESS_ADDL_PER NUMBER(5,2);
alter table PC_ORDER_DETAIL add POD_CESS_ADDL_AMT NUMBER(12,2);

alter table PC_ORDER_DETAIL_MN add POD_CESS_ADDL_PER NUMBER(5,2);
alter table PC_ORDER_DETAIL_MN add POD_CESS_ADDL_AMT NUMBER(12,2);


select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a.referenced_name ='TOOL_DETAIL_LDR' and b.status='INVALID';

Alter PACKAGE BODY GUEST.PACKAGEGIN compile;
Alter PACKAGE BODY WILDBA.GIN_RECP_MAST_PKG compile;
Alter PACKAGE BODY WILDBA.PUR_REQN_DETL_PKG compile;

Alter PACKAGE GUEST.PACKAGEGIN compile BODY ;
Alter PACKAGE WILDBA.GIN_RECP_MAST_PKG compile BODY;
Alter PACKAGE WILDBA.PUR_REQN_DETL_PKG compile BODY;

Alter FUNCTION WILDBA.GET_PPO_QTY compile;
Alter PACKAGE WILDBA.GIN_RECP_MAST_PKG compile BODY;
Alter PACKAGE WWPROJECT.GINCLOSE compile BODY;
Alter PACKAGE WWPROJECT.PACKAGEGIN compile BODY;
Alter PACKAGE WWPROJECT.PENDGIN compile BODY;



select *from dba_objects
where object_type in (’TABLE’,'VIEW’)
and owner='VKR';

http://sharinc@vsnl.com/
suresh@shartechnologies.in


move table wildba@mis_bk to wildba@mis

contractors

ALTER TABLE contractors
add CONSTRAINT contractors_CONSTRAINT UNIQUE (CNAME);


complain
ALTER TABLE complain
add CONSTRAINT complain_CONSTRAINT UNIQUE (COMPNAME);

conn wildba@mis
==============
create table contractors as select * from contractors@misbk2mis;


create table complain as select * from complain@misbk2mis;

alter table tr_tool_mast add TLM_LOGIN_USER VARCHAR2(2000);

select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='PC_ORDER_MAST_MN' and b.status='INVALID';

Kindly create below mentioned new column in the table PC_ORDER_MAST_MN table in ALL DATABASES.



POM_RAISED_BY VARCHAR2(5)


alter table PC_ORDER_MAST_MN add POM_RAISED_BY VARCHAR2(5);
desc PC_ORDER_MAST_MN

select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='GIN_RECP_DETL_GS ' and b.status='INVALID';

14/03/07
=========
Please create additional column in the following tables in MIS ,WWDB ,MIS_BK databases.

Table Name Column Name

GIN_RECP_DETL_GS GRD_ADDL_CESS_AMT Number(15,2)



CST_INDIGENEOUS_GIN CSG_ADDL_CESS_PER Number(5,2) ,CSG_ADDL_CESS_AMOUNT Number(15,2)



CST_INDIGENEOUS_GIN_RMS CSG_ADDL_CESS_PER Number(5,2) ,CSG_ADDL_CESS_AMOUNT Number(15,2)



CST_INDIGENEOUS_GIN_RMSCHECK CSG_ADDL_CESS_PER Number(5,2) ,


Please ensure that all the realted objects are valid.

1.select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a.owner='RMS' and b.status='INVALID';

desc GIN_RECP_DETL_GS
alter table GIN_RECP_DETL_GS add GRD_ADDL_CESS_AMT Number(15,2);
desc GIN_RECP_DETL_GS


2.desc CST_INDIGENEOUS_GIN

alter table CST_INDIGENEOUS_GIN add CSG_ADDL_CESS_PER Number(5,2);
alter table CST_INDIGENEOUS_GIN add CSG_ADDL_CESS_AMOUNT Number(15,2);

desc CST_INDIGENEOUS_GIN

3.
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='CST_INDIGENEOUS_GIN_RMSCHECK' and b.status='INVALID';

desc CST_INDIGENEOUS_GIN_RMS
alter table CST_INDIGENEOUS_GIN_RMS add CSG_ADDL_CESS_PER Number(5,2);
alter table CST_INDIGENEOUS_GIN_RMS add CSG_ADDL_CESS_AMOUNT Number(15,2);
desc CST_INDIGENEOUS_GIN_RMS

4.
select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;'
from dba_dependencies a, dba_objects b
where a.name=b.object_name and a. referenced_name ='GIN_RECP_DETL_GS' and b.status='INVALID';

desc CST_INDIGENEOUS_GIN_RMSCHECK
alter table CST_INDIGENEOUS_GIN_RMSCHECK add CSG_ADDL_CESS_PER Number(5,2);
alter table CST_INDIGENEOUS_GIN_RMSCHECK add CSG_ADDL_CESS_AMOUNT number(15,2);
desc CST_INDIGENEOUS_GIN_RMSCHECK

move mis table to mis_bk wildba\dbawil
mis_bk db
wildba/dbawil

create database link wildbamistok connect to wildba identified by dbawil using 'MIS';

create table tr_gin_recp_mast as select * from tr_gin_recp_mast@MIS2MISBKWIL;

create table tr_gin_recp_detail as select * from tr_gin_recp_detail@wildbamistok;

REVOKE INSERT ON films FROM PUBLIC;

REVOKE ALL PRIVILEGES ON kinds FROM manuel;


put hte parameter in pfile:
_allow_restlogs_corruption=true

steps:

1.shutdown normal
2.set the parameter in pfile
3.until cancel type recovery
4.alter database open restlogs;
5.wait couple of minutes for oracle to start itself then
6.shutdown normal
7.remove the above parameter
8.startup then check alert logfile for more info



Normal User Login:
-----------------

http://localhost:80/isqlplus

To Login as SYSDBA :
---------------------------------

http://localhost:80/isqlplus/dba

http://localhost:80/isqlplusdba

you need to configure as shown below or it will throw error insufficient previleges...

Go to the htpasswd.exe location in the command prompt.

general syntax :
-------------------------

htpasswd %ORACLE_HOME%\sqlplus\admin\iplusdba.pw username

example:
-------------

D:\oracle9i\Apache\Apache\bin>htpasswd D:\oracle9i\sqlplus\admin\iplusdba.pw dba

-- in this case i have specified username as dba

Automatically using MD5 format on Windows.
New password: *** ( i have given the password to be 'dba')
Re-type new password: ***
Adding password for user dba

D:\oracle9i\Apache\Apache\bin>


when you edit the iplusdba.pw file now you can see the entries in that file related to new user 'dba' created.


now try to connect...

http://localhost:80/isqlplus/dba

it will ask for username and password enter dba(username) password (dba)

then enter sys and password in the isqlplus screen and connect as sysdba..

==================================================
Alter FUNCTION CST.DT_ADDUSEROBJECT compile;
Alter FUNCTION CST.GET_WHEEL_CODES compile;
Alter FUNCTION GUEST.DT_ADDUSEROBJECT compile;
Alter FUNCTION JVC.DT_ADDUSEROBJECT compile;
Alter FUNCTION MFG.DT_ADDUSEROBJECT compile;
Alter FUNCTION MFG.TEST compile;
Alter FUNCTION MISQRY.DT_ADDUSEROBJECT compile;
Alter FUNCTION MISQRY.FU2 compile;
Alter FUNCTION MISQRY.FU3 compile;
Alter FUNCTION MP.GET_VALUE_IND_TL_TS compile;
Alter FUNCTION PACCOUNTS.NM compile;
Alter FUNCTION PACCOUNTS.TEST1 compile;
Alter FUNCTION PUR.DT_ADDUSEROBJECT compile;
Alter FUNCTION SCOTT.DT_ADDUSEROBJECT compile;
Alter FUNCTION SYSTEM.CHKUSER compile;
Alter FUNCTION TAMPR.DT_ADDUSEROBJECT compile;
Alter FUNCTION TAMPR.NM compile;
Alter FUNCTION TAMPR.NOLV compile;
Alter FUNCTION WILDBA.GET_ISS_GROSS compile;
Alter FUNCTION WILDBA.GET_ISS_NEG_ADJ compile;
Alter FUNCTION WILDBA.GET_ISS_POS_ADJ compile;
Alter FUNCTION WILDBA.GET_VALUE_IND_TL_TS compile;
Alter FUNCTION WILDBA.GET_WHEEL_CODES compile;
Alter FUNCTION WILDBA.TEST compile;
Alter PACKAGE BODY DGN.DEMOKIT compile;
Alter PACKAGE BODY SCOTT.DEMOKIT compile;
Alter PACKAGE BODY SCOTT.EMPDEPTP compile;
Alter PACKAGE BODY SCOTT.EMPHTML compile;
Alter PACKAGE BODY SYSTEM.PERFORMANCE compile;
Alter PACKAGE BODY SYSTEM.SQLAB_SCHEDULED_COLLECTOR compile;
Alter PACKAGE BODY TAMPR.DEMOKIT compile;
Alter PACKAGE BODY WILDBA.ABCD compile;
Alter PACKAGE BODY WILDBA.CST_ISSUE_PCC_GRP_PKG compile;
Alter PACKAGE BODY WILDBA.PERFORMANCE compile;
Alter PACKAGE DGN.DEMOKIT compile;
Alter PACKAGE SCOTT.DEMOKIT compile;
Alter PACKAGE SCOTT.EMPDEPTP compile;
Alter PACKAGE SCOTT.EMPHTML compile;
Alter PACKAGE SYSTEM.PERFORMANCE compile;
Alter PACKAGE SYSTEM.SQLAB_SCHEDULED_COLLECTOR compile;
Alter PACKAGE TAMPR.DEMOKIT compile;
Alter PACKAGE WILDBA.ABCD compile;
Alter PACKAGE WILDBA.CST_ISSUE_PCC_GRP_PKG compile;
Alter PACKAGE WILDBA.PERFORMANCE compile;
Alter PROCEDURE BS.FIND_ITEM_ASSESABLE_VALUE compile;
Alter PROCEDURE CST.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE CST.DT_SETPROPERTYBYID compile;
Alter PROCEDURE CST.P1 compile;
Alter PROCEDURE CST.TEST compile;
Alter PROCEDURE CST.TEST1 compile;
Alter PROCEDURE CST.TST compile;
Alter PROCEDURE DGN.SUB_QRY_1 compile;
Alter PROCEDURE DS.OPEN compile;
Alter PROCEDURE ES.ABCD compile;
Alter PROCEDURE GS.ISSADJ_INSERT compile;
Alter PROCEDURE GS.ITEM_CURR_QTY_UPD compile;
Alter PROCEDURE GS.LATEST_PUR compile;
Alter PROCEDURE GS.MISPLEITEM compile;
Alter PROCEDURE GS.NEG_CHANGE compile;
Alter PROCEDURE GS.PR_FIFO compile;
Alter PROCEDURE GS.RECADJ_INSERT compile;
Alter PROCEDURE GS.RMR_INSERT compile;
Alter PROCEDURE GS.VERI_INSERT compile;
Alter PROCEDURE GUEST.ABCD compile;
Alter PROCEDURE GUEST.ANU compile;
Alter PROCEDURE GUEST.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE GUEST.DT_SETPROPERTYBYID compile;
Alter PROCEDURE GUEST.DUP compile;
Alter PROCEDURE GUEST.GININS compile;
Alter PROCEDURE GUEST.MRDETAILS_IN_ISSUE compile;
Alter PROCEDURE GUEST.NM1 compile;
Alter PROCEDURE GUEST.P1 compile;
Alter PROCEDURE GUEST.PDINSERT compile;
Alter PROCEDURE GUEST.PROC1 compile;
Alter PROCEDURE GUEST.SP_ISSUE_FETCH compile;
Alter PROCEDURE GUEST.SP_RATECALCULATION compile;
Alter PROCEDURE GUEST.SP_ZEROSTK compile;
Alter PROCEDURE GUEST.VW compile;
Alter PROCEDURE GUEST.WWPR_TO_WILPR_DUMP compile;
Alter PROCEDURE JVC.AA compile;
Alter PROCEDURE JVC.ABC123 compile;
Alter PROCEDURE JVC.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE JVC.DT_SETPROPERTYBYID compile;
Alter PROCEDURE LAMBENT.PRO_OBJECT_STORAGE compile;
Alter PROCEDURE MFG.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE MFG.DT_SETPROPERTYBYID compile;
Alter PROCEDURE MISQRY.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE MISQRY.DT_SETPROPERTYBYID compile;
Alter PROCEDURE MRP.UPDATE_DETAILS compile;
Alter PROCEDURE PACCOUNTS.ITAX_EST_RECO compile;
Alter PROCEDURE PUR.CHECK_PO compile;
Alter PROCEDURE PUR.CHK_PO compile;
Alter PROCEDURE PUR.DETAIL_LOAD compile;
Alter PROCEDURE PUR.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE PUR.DT_SETPROPERTYBYID compile;
Alter PROCEDURE PUR.MASTER_LOAD compile;
Alter PROCEDURE PUR.PRO_PO_CORRECT compile;
Alter PROCEDURE PUR.TMP_AMD_INST compile;
Alter PROCEDURE PUR.TMP_INST compile;
Alter PROCEDURE PUR.TOT_VALUE compile;
Alter PROCEDURE QLTY.JCD compile;
Alter PROCEDURE RMCST.GIN_PROC compile;
Alter PROCEDURE RMCST.GIN_PROC166 compile;
Alter PROCEDURE RMS.OPEN compile;
Alter PROCEDURE SCOTT.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE SCOTT.DT_SETPROPERTYBYID compile;
Alter PROCEDURE SCOTT.QQ compile;
Alter PROCEDURE SCOTT.TT compile;
Alter PROCEDURE SCOTT.WWW compile;
Alter PROCEDURE SCOTT.YY compile;
Alter PROCEDURE SYSTEM.COMPILE_OBJECTS compile;
Alter PROCEDURE SYSTEM.TEMP_07062000GK compile;
Alter PROCEDURE TAMPR.DETPOST compile;
Alter PROCEDURE TAMPR.DT_DROPUSEROBJECTBYID compile;
Alter PROCEDURE TAMPR.DT_SETPROPERTYBYID compile;
Alter PROCEDURE TAMPR.NM1 compile;
Alter PROCEDURE TAMPR.PROCESS_WAGE_ARREARS compile;
Alter PROCEDURE TOOL_ROOM.BRK_SPACE compile;
Alter PROCEDURE TOOL_ROOM.POP_DIFF_ISS_DISC compile;
Alter PROCEDURE TOOL_ROOM.POP_DIFF_ISS_RIM compile;
Alter PROCEDURE WILDBA.BACK_TRACK compile;
Alter PROCEDURE WILDBA.BLR_OBSOLETE_KNOCK compile;
Alter PROCEDURE WILDBA.BLR_UPDATE_KNOCK compile;
Alter PROCEDURE WILDBA.BOT_CONV compile;
Alter PROCEDURE WILDBA.BRK_SPACE compile;
Alter PROCEDURE WILDBA.CB_ISS_QTY_UPD_RM_TS compile;
Alter PROCEDURE WILDBA.CB_ISS_QTY_UPD_TS compile;
Alter PROCEDURE WILDBA.CB_REC_ISS_QTY_UPD_RM compile;
Alter PROCEDURE WILDBA.CB_REC_QTY_UPD compile;
Alter PROCEDURE WILDBA.CB_REC_QTY_UPD_RM_TS compile;
Alter PROCEDURE WILDBA.CHECK2 compile;
Alter PROCEDURE WILDBA.CHECK_DIFF compile;
Alter PROCEDURE WILDBA.CLOSE_BALANCE_CUMM_LB_TS compile;
Alter PROCEDURE WILDBA.CLOSE_BALANCE_CUMM_NB_TS1 compile;
Alter PROCEDURE WILDBA.CLOSE_BALANCE_CUMM_NB_TS2 compile;
Alter PROCEDURE WILDBA.CLOSE_BALANCE_CUMM_RM_TS1 compile;
Alter PROCEDURE WILDBA.CLOSE_BALANCE_CUMM_RM_TS2 compile;
Alter PROCEDURE WILDBA.CLOSE_TEMP_POP compile;
Alter PROCEDURE WILDBA.CLOS_UPD_ISS compile;
Alter PROCEDURE WILDBA.CLSUPD compile;
Alter PROCEDURE WILDBA.CODES compile;
Alter PROCEDURE WILDBA.COMPARE_VALUE compile;
Alter PROCEDURE WILDBA.COMPILE_OBJECTS compile;
Alter PROCEDURE WILDBA.COMP_CODE_UPD compile;
Alter PROCEDURE WILDBA.COMP_UPD compile;
Alter PROCEDURE WILDBA.CRE_AUG_OPEN compile;
Alter PROCEDURE WILDBA.DEC_CLOS_UPD compile;
Alter PROCEDURE WILDBA.DSINVUPDATE compile;
Alter PROCEDURE WILDBA.EDCORRECTION compile;
Alter PROCEDURE WILDBA.FIND_ITEM_ASSESABLE_VALUE compile;
Alter PROCEDURE WILDBA.GETVA compile;
Alter PROCEDURE WILDBA.GET_MODVAT compile;
Alter PROCEDURE WILDBA.GIN_VALUE_CORRECTION compile;
Alter PROCEDURE WILDBA.GS_GIN_CORR1 compile;
Alter PROCEDURE WILDBA.GS_GIN_CORR3 compile;
Alter PROCEDURE WILDBA.INREPORT compile;
Alter PROCEDURE WILDBA.INREPORTPRO compile;
Alter PROCEDURE WILDBA.INVOICE_UPDATE compile;
Alter PROCEDURE WILDBA.ISS_CLOSE compile;
Alter PROCEDURE WILDBA.ITMMAST compile;
Alter PROCEDURE WILDBA.KK1 compile;
Alter PROCEDURE WILDBA.KNOCK1 compile;
Alter PROCEDURE WILDBA.MARCH compile;
Alter PROCEDURE WILDBA.NETBAL compile;
Alter PROCEDURE WILDBA.OPEN compile;
Alter PROCEDURE WILDBA.OPWIP compile;
Alter PROCEDURE WILDBA.P1 compile;
Alter PROCEDURE WILDBA.PACK_LIST_NO_UPDATE compile;
Alter PROCEDURE WILDBA.PDCHECK compile;
Alter PROCEDURE WILDBA.PERD_CORR compile;
Alter PROCEDURE WILDBA.PKDUPDATE compile;
Alter PROCEDURE WILDBA.POPINV compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_DISC compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_DISC_TD compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_FLG compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_FLG_TD compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_LCK compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_LCK_TD compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_OTC compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_OTC_TD compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_PSH compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_RIM compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_RIM_TD compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_SFLG compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_SFLG_TD compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_WHEEL compile;
Alter PROCEDURE WILDBA.POP_DIFF_ISS_WITS compile;
Alter PROCEDURE WILDBA.POTLDETCV compile;
Alter PROCEDURE WILDBA.PO_AMD_UPD compile;
Alter PROCEDURE WILDBA.PO_GIN_UP compile;
Alter PROCEDURE WILDBA.PRODUMP compile;
Alter PROCEDURE WILDBA.PRO_OPEN_CUR compile;
Alter PROCEDURE WILDBA.PRO_WARE_CODE compile;
Alter PROCEDURE WILDBA.PR_FIFO compile;
Alter PROCEDURE WILDBA.PSNOWISE compile;
Alter PROCEDURE WILDBA.PTLSTK compile;
Alter PROCEDURE WILDBA.PTNSTK compile;
Alter PROCEDURE WILDBA.RECP_CLOSE compile;
Alter PROCEDURE WILDBA.REPTLCONV compile;
Alter PROCEDURE WILDBA.SC_RCT_UPD_TOOL compile;
Alter PROCEDURE WILDBA.SEP_OPEN_UPDATION compile;
Alter PROCEDURE WILDBA.SEP_RATE_FOR_NT compile;
Alter PROCEDURE WILDBA.SP_CHK_CBOB compile;
Alter PROCEDURE WILDBA.SP_CHK_LAN compile;
Alter PROCEDURE WILDBA.SP_COR_RATE compile;
Alter PROCEDURE WILDBA.SP_CST_CHECK_CONV compile;
Alter PROCEDURE WILDBA.SP_UPD_ISSUE compile;
Alter PROCEDURE WILDBA.STK_UPD compile;
Alter PROCEDURE WILDBA.STK_VER_TEMP_PROC compile;
Alter PROCEDURE WILDBA.TEST1 compile;
Alter PROCEDURE WILDBA.TLDET_POP compile;
Alter PROCEDURE WILDBA.TOOL_CHK compile;
Alter PROCEDURE WILDBA.TOOL_CONV compile;
Alter PROCEDURE WILDBA.TOOL_DTL_STATUS_UPD compile;
Alter PROCEDURE WILDBA.TOOL_POP compile;
Alter PROCEDURE WILDBA.TOOL_STATUS_UPD compile;
Alter PROCEDURE WILDBA.TOT_VALUE compile;
Alter PROCEDURE WILDBA.TR_TOOLS_ORA00600 compile;
Alter PROCEDURE WILDBA.TSCST_RATE_UPD_RM compile;
Alter PROCEDURE WILDBA.TST compile;
Alter PROCEDURE WILDBA.TST_AUTO_PR compile;
Alter PROCEDURE WILDBA.UPD compile;
Alter PROCEDURE WILDBA.UPDATEDATA compile;
Alter PROCEDURE WILDBA.UPDATEVALUED compile;
Alter PROCEDURE WILDBA.UPDATE_DISC_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_FLG_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_LCK_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_OTC_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_PEND_INSP_QTY compile;
Alter PROCEDURE WILDBA.UPDATE_PERD_RUNN_TRNNO compile;
Alter PROCEDURE WILDBA.UPDATE_PRIM_SECN_CC compile;
Alter PROCEDURE WILDBA.UPDATE_PSH_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_RIM_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_SFLG_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_VAL compile;
Alter PROCEDURE WILDBA.UPDATE_WHEEL_ISS compile;
Alter PROCEDURE WILDBA.UPDATE_WITS_ISS compile;
Alter PROCEDURE WILDBA.UPD_ITEM_MAST compile;
Alter PROCEDURE WILDBA.UPD_ROUT_PLINE compile;
Alter PROCEDURE WILDBA.USERS compile;
Alter PROCEDURE WILDBA.VAL_UPD compile;
Alter PROCEDURE WILDBA.WOTLCONV compile;
Alter PROCEDURE WILDBA.X compile;
Alter TRIGGER GUEST.COMPTYPE compile;
Alter TRIGGER NEWUSER.DEL_DISC compile;
Alter TRIGGER NEWUSER.DEL_FLANGE compile;
Alter TRIGGER NEWUSER.DEL_OTHER_COMP compile;
Alter TRIGGER NEWUSER.DEL_PART compile;
Alter TRIGGER NEWUSER.DEL_RIM compile;
Alter TRIGGER NEWUSER.DEL_SPRING_FLANGE compile;
Alter TRIGGER NEWUSER.DEL_WHEEL compile;
Alter TRIGGER NEWUSER.ITEM_QTY_UPD_TRIG compile;
Alter TRIGGER NEWUSER.MRP_BATCH_ITEM_DEL_TRIG compile;
Alter TRIGGER NEWUSER.MRP_COMP_SUMM_INS_TRIG compile;
Alter TRIGGER NEWUSER.MRP_COMP_SUMM_UPD_TRIG compile;
Alter TRIGGER NEWUSER.MRP_ITEM_SUMM_INS_TRIG compile;
Alter TRIGGER NEWUSER.MRP_ITEM_SUMM_UPD_TRIG compile;
Alter TRIGGER NEWUSER.MRP_WKYCOMP_SUMM_INS_TRIG compile;
Alter TRIGGER NEWUSER.MRP_WKYCOMP_SUMM_UPD_TRIG compile;
Alter TRIGGER NEWUSER.MTH_BUD_REP_TRIG compile;
Alter TRIGGER NEWUSER.TRIG_MFG_SHEAR_OUTPUT_DETL compile;
Alter TRIGGER PCH.ITEM_QTY_UPD_TRIG compile;
Alter TRIGGER TAMPR.LEAVEAPP_DTUTRIG compile;
Alter TRIGGER TAMPR.LEAVEDET_DTUTRIG compile;
Alter TRIGGER TAMPR.LV_APPLY_DTUTRIG compile;
Alter TRIGGER WILDBA.DEL_DISC compile;
Alter TRIGGER WILDBA.DEL_FLANGE compile;
Alter TRIGGER WILDBA.DEL_OTHER_COMP compile;
Alter TRIGGER WILDBA.DEL_PART compile;
Alter TRIGGER WILDBA.DEL_RIM compile;
Alter TRIGGER WILDBA.DEL_SPRING_FLANGE compile;
Alter TRIGGER WILDBA.DEL_WHEEL compile;
Alter TRIGGER WILDBA.ITEM_QTY_UPD_TRIG compile;
Alter TRIGGER WILDBA.MRP_BATCH_ITEM_DEL_TRIG compile;
Alter TRIGGER WILDBA.MRP_COMP_SUMM_INS_TRIG compile;
Alter TRIGGER WILDBA.MRP_COMP_SUMM_UPD_TRIG compile;
Alter TRIGGER WILDBA.MRP_ITEM_SUMM_INS_TRIG compile;
Alter TRIGGER WILDBA.MRP_ITEM_SUMM_UPD_TRIG compile;
Alter TRIGGER WILDBA.MRP_WKYCOMP_SUMM_INS_TRIG compile;
Alter TRIGGER WILDBA.MRP_WKYCOMP_SUMM_UPD_TRIG compile;
Alter TRIGGER WILDBA.MTH_BUD_REP_TRIG compile;
Alter TRIGGER WILDBA.PUR_REQN_DETL_GS_DTUTRIG compile;
Alter TRIGGER WILDBA.TRIG_MFG_SHEAR_OUTPUT_DETL compile;
Alter VIEW FASAL.EX_INV compile;
Alter VIEW FASAL.MIS_INV compile;
Alter VIEW MFG.SL_TEST_VIEW compile;
Alter VIEW MISQRY.V1 compile;
Alter VIEW NEW_INCENTIVE.ELIGIBLE_TECHNICIANS compile;
Alter VIEW NEW_INCENTIVE.T5307 compile;
Alter VIEW PACCOUNTS.COLUMN_LIST compile;
Alter VIEW PACCOUNTS.INDEX_LIST compile;
Alter VIEW PACCOUNTS.RMR_SCRAP compile;
Alter VIEW PACCOUNTS.RM_FRT compile;
Alter VIEW PACCOUNTS.RM_PKG compile;
Alter VIEW PACCOUNTS.TB_DEBIT compile;
Alter VIEW PACCOUNTS.TRANSANALYSIS compile;
Alter VIEW SCOTT.EMPDEPT compile;
Alter VIEW SCOTT.V1 compile;
Alter VIEW TAMPR.COLUMN_LIST compile;
Alter VIEW TAMPR.INDEX_LIST compile;
Alter VIEW TAMPR.LEAVEREG compile;
Alter VIEW WILDBA.BOM_WHEEL compile;
Alter VIEW WILDBA.EX_INV compile;
Alter VIEW WILDBA.MIS_INV compile;
Alter VIEW WILDBA.PS compile;



Database Reorganization Steps

1. Check the status of all the datafiles.

2. Shutdown immediate

3. Take Cold backup of the full database.

a. All datafiles.

b. Controlfiles.

c. Logfiles.

d. Archive files.

e. Network files.

f. Password files.

g. pfile.

4. Keep copy one in tape and one in Disk other than production server.

5. Startup the database in Restricted mode.

6. Take the full export of the MIS database.

7. Check the errors in the export log.

8. Recreate those views,objects after reorganizing the database.

9. Create the script according to Newplan.

a. Size of the datafiles.

b. No.of datafiles and path for the each datafile.

c. Logfile groups and members.

d. No. of Controlfiles.

10. Delete the Database (including files and folders).

11. Create folders according to the script.

12. Alter initfile if required.

13. Create database using the script.

14. Import the export dump file.

15. Check the import dump logfile.

16. Check the Valid and invalid objects.

17. Identify invalid objects and compile.

18. Check the accessing using application.

19. Run the DB status 1 & 2.

20. Prepare the report for the new database Reorganization.

job.sql

Begin
dbms_job.submit(:jobno, 'p1;', sysdate, 'sysdate + 1');
end;

/

____________________________________________________________________

proce.sql

create or replace procedure p1 as
emprec sys.emp%rowtype;
begin
select n into emprec.n from sys.emp where n=100;
insert into sys.emp1 values(emprec.n);
dbms_output.put_line('Value inserted: ' || emprec.n);
end;
/
____________________________________________________________________
archi.txt

Architucture
=============

Instance- mem+process

MEMORY:
======

SGA
PGA
S/W Code area

SGA

DB buffer cache
Log buffer
Shared pool
Dic. cache
lib. cache
shared sql/private sql
pl/sql (shared/private)
large pool
Java-pool

PGA

Dedicate: stack area
Shared: stack area, session info

List: LRU list, LRU algo., Dirty List
Buffers: Dirty, pinned, free
Cache: hit, miss

Sql execution: parse, fetch, execute
parse: parsed code+execution plan + parse count


PROCESS:
========

user
oralce
server
dedicated
shared
background


Background: 12(5 mandatory + 7 optional)

5 Mandatory: SMON,PMON,DBWn,LGWR,CKPT
7 Optional: Arch0, SNPn, LCKn, RECO, Dnnn, Snnn, QMNn




DATABASE CREATION
=================

1. Set the environment (Oradim).
2. Editing the parameter file.
3. Start the tool(SVRMGRL or SQLPLUS).
4. Connect as administrator.
5. Startup in nomount state.
6. Create the Database.
7. Execute catalog, catproc.


DATABASE STARTUP/SHUTDOWN
==========================

1. Set the environment.
2. Start the tool(SVRMGRL or SQLPLUS).
3. Connect as administrator.
4. Start the database.

States of Database
====================

1. Closed
2. Nomount
3. Mount
4. Open

From closed to nomount: Instance is started(memory allocated), control file is identified.
From nomount to mount: Controlfile is opened, location of data,logfile is identified.
From mount to open: Database is opened(Datafile, logfile is opened)


Shutdown normal/transactional/immediate/abort

Normal: Waits for all the users to end their session.

Transactional: Wait for the users current transaction to end(commit or rollback) and shutdown the

database

Immediate: Rollback the transactions of all the user, and shutdown the database.

Abort: abnormal shutdown.
____________________________________________________________________

backup.txt

backup

physical
logical

Physical
hot
cold

hot: Taken when dbf is online.
Cold: Taken when dbf is offline.

Hot backup should be taken when archivelog is enabled. Cold backup can
be taken even when archivelog is disabled.

To take hot backup follow these steps.

1. Enable archivelog mode.
2. Backup tablespaces individually
2.1. alter tablespace ts1 begin backup
copy the datafiles corresponding to ts, physically from OS. follow these for all TS's. Once

this is done end the backup

Alter tablespace ts1 end backup.


3. Backup controlfile
alter database backup controlfile to 'dest'.


To take cold backup follow these steps:

1. Shutdown the database.
2. copy all the data,log,controlfiles.


Logical backup.

Export.

* Exp utility only exports commited data.
* To Export a full database the user must have Exp_full_database privilage.

Export modes: Table, owner, Full.
Export methods: Interactive, command line, parfile.

Export options:

userid: dba/owner of the object. eg: userid=yoga/yoga
file: destination file. file=c:\exp.dmp or file=exp.dmp,exp1.dmp...
tables: tables to be exported

Import:

* User can only import objects to their own schema. Only DBA can import to other user.

* To import a full database user must have import_full_database privilage.

To import full database follow these steps:

1. Create a new database ,run catalog and catproc.
2. create tablespaces with the same structure as the old database.
3. Perform a full import.

____________________________________________________________________
Deferredrollback.sql

To simulate deferred RBS

1. Create a table(tmp) in non-sys TS(TS1).
2. Assign a trans. to a paticular RBS.
3. Insert rec's into that table(tmp).
4. open another session and make TS1 offline.
5. Now enter the followin query;

select * from dba_segments where segment_type like '%DEF%';

6. Make the tablespace(TS1) online, and again enter the above query

____________________________________________________________________
exp imp examples.sql

Export:
=======

NOTE: Always set the instance before export (set oracle_sid=demo)

1. Exp. Full database

exp userid=system/manager file=c:\full_exp.dmp log=c:\log

2. Exp by owner.

exp userid=system/manager file=c:\owner_exp.dmp owner=yoga.

3. Exp by table.

exp userid=yoga/yoga file=c:\tab_exp.dmp tables=(emp, trans, temp) compress=y

4. Specifying different filesize.

exp userid=system/manager filesize=500k file=c:\exp.dmp,c:\exp1.dmp,exp2.dmp full=y

5. Exporting table partitions

exp userid=yoga/yoga file=c:\part.dmp tables=(emp:p1, trans:p2)

6. Transportable tablespaces

exp userid=system/manager transport_tablespace=y tablespaces=TS1 file=c:\trans_table.exp

7. Query option

exp system/manager query='where salad_type=''fruit''' tables=amy.salad_type file=tmp.dmp



Import:
=======

NOTE: Only DBA can import objects of one user to another

1. imp userid=yoga/yoga file=c:\exp.dmp fromuser=yoga touser=yoga

2. imp userid=system/manager file=c:\exp.dmp tables=(yoga.emp) fromuser=yoga touser=dummy

____________________________________________________________________
how to automate backup.txt

TO automate backup using Windows scheduler we need to create two files

1. backup.bat.
2. backup.sql.


Contents of backup.bat:

sqlplus /nolog @c:\backup.sql



Contents of backup.sql:

connect internal
alter tablespace ts1 begin backup;
host copy c:\oracle\demo\ts1.dbf c:\back.dbf
alter tablespace ts1 end backup;

Once this is done schedule backup.bat in Windows scheduler

____________________________________________________________________
how to rename db.txt

steps to rename a database


1.Open the database, and issue the command "alter database backup controlfile to trace".

2. now shutdown the database.

3. open the trace file in udump folder and change the line "CREATE CONTROLFILE REUSE DATABASE

"DEMO" NORESETLOGS ARCHIVELOG", to "CREATE CONTROLFILE SET DATABASE "NEWDEMO" RESETLOGS

ARCHIVELOG".

4. Change db_name in init.ora.

5. Open the database in nomount.

6. Recreate the controlfile.

7. Open the database using resetlogs.

____________________________________________________________________
lock.txt

share lock mode
---------------

when an user lock the table in this mode other user cannot update,insert or delete

but other user can select the table. and other users can lock the table.


share update lock
-----------------

when an user locks the table in this mode other user

other user can update,insert or delete the rows and other users can lock the table.


share exclusive lock
--------------------

when an user lock the table in this mode other user cannot update, insert or delete

other user can only select the table. but other user cannot lock the tables.


row share lock
--------------

when an user locks the table in this mode other user can update, insert or delete and

other user can lock the table.


row exclusive lock
------------------
when an user locks the table in this mode other user can update, insert or delete and

other user cannot lock the table.


row share exclusive lock
------------------------

when an user lock the table in this mode other user cannot update, insert or delete

other user can only select the table. but other user cannot lock the tables.

____________________________________________________________________
logical.txt

LOGICAL STRUCTURES
==================

Tablespace
Segments
Extents
Blocks


Tablespace
Locally managed
Dict. managed

Permenante
Temp.

Creation
Alter
Drop

Backup

Online/offline

Segment
Extents Views
Blocks


Segment
Types: Data, index, Temp., Rollback.

Rollback: Creation, alter, drop, Online/offline.

Extents: Allocate, Deallocate

Block: PCTINC, PCTFREE.
Struc.


Row chaining,migration

____________________________________________________________________

pending offline.txt

To generate "Pending offline" follow these steps:

1. Open two sessions.
2. Assign a trans to a RBS (PRBS1).
3. insert some rows in any of the avail. table.
4. switch to the next session, and make PRBS1 offline.
5. now view V$rollstat, in any of the session.

____________________________________________________________________

snapshottooold.txt

To simulate "Snapshot too old" follow these steps:

NOTE: Before performing the following be sure that only one RBS in online.

1. create a table(t1).
2. insert upto 50,000 records.
3. commit the trans.
4. Open two more sessions.
5. in the first session, update all the records of t1.
6. do not commit the trans.
7. in the second session, select all the rec's of t1.
8. in the third session, delete all the rec's of t1.

Snapshot too old error should appear in the second session.
____________________________________________________________________

sqlloader.txt

SQL Loader

The frist step in using this tool, is creating a control file(text file). The control file has

the complete information regarding the file to be converted.

Format for control file:

================================================================================================
load data
infile 'c:\employee.txt'
bad 'c:\employee.bad'
insert
into table emp
(
emp_no position (1:3) integer external,
emp_name position (5:13) CHAR,
emp_age position (16:17) integer external
)

================================================================================================
load data
infile *
append
into table emp
(
emp_no position (1:3) integer external,
emp_name position (5:13) CHAR,
emp_age position (16:17) integer external
)

begindata
100:abc :34
200:EEE :23
300:PPP :56
400:SSS :78
================================================================================================

load data
infile 'c:\employee.txt'
badfile 'c:\emploee.bad'
discardfile 'c:\employee.dis'
replace
into table yoga.emp
(
emp_no integer external terminated by ':',
emp_name char terminated by ':',
emp_age position (16:17) integer external
)

================================================================================================

SQLLoader syntax:

sqlldr userid= control=

NOTE:
* All the unformatted data will be copied to bad file.
* All the data's which does not meet the table constraint are entered in discard file.

* Other than insert we can give append, replace and truncate.

____________________________________________________________________

TNS error messages.txt

Distributed database error's:

1. ORA-12541: TNS:no listener

Check entry for "Port" in Tnsnames.ora


2. ORA-12545: Connect failed because target host or object does not exist

Check for either IP Address/Host name for the entry "Host" in tnsnames.ora


3. ORA-12505: TNS:listener could not resolve SID given in connect descriptor

This error might occur if the SID(service name) of the traget machine and the "SID" entry in

tnsnames.ora(of source machine) does not match.


4. ORA-01034: ORACLE not available

If Oracle is shutdown in the target machine


5. ORA-02019: connection description for remote database not found

This error message might occur, if the current user is not the owner of the DBlink. Logon as the

user who owns the DB link or create a public link.

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

ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'D:\LOCALTEST\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile

this occurs if controlfile is older than data file, if ur unable to find new control file then do the following:

recover database using backup controlfile;

now specify the current log file, and open the dbf using reset logs;



****************************************************************************************

MAX_EXTENTS in Temporary tablespace is always NULL


********************************************************
For a dictionary-managed temporary tablespace use -
CREATE TABLESPACE ... TEMPORARY;

For a locally-managed temporary tablespace use -
CREATE TEMPORARY TABLESPACE ... ;



while imp. if constraints=n then the indexes for the corrs. constraint (eg- primary key) is not imported, even though the value of "indexes" is "y"

******************************************************************************

if we move a table from one ts to another, then we need to move the indexes manually, using the following command:

alter index rebuild tablespace

Rest of the dependent objects like proce, func,trigger remain in system ts.

***************************************************************************************

partition:
=========

create table emp (empno number, sal number)
partition by range(sal)
(partition p1 values less than (1000) tablespace ts1);

alter table emp add partition p3 values less than (3000) tablespace tools storage(initial 20k next 20k);

alter table emp add partition p4 values less than (maxvalue) tablespace rman;

alter table emp rename partition p4 to maxvalue;

alter table emp drop partition maxvalue;

alter table emp truncate partition p3;

alter table emp merge partitions p5,p3 into partition p8;


*****************************************************************************************

cluster:
========

create cluster department (clus_deptno number(3));

create table emp(empno number(3),deptno number(3))
cluster department(deptno);

create table dept(deptno number(3))
cluster department(deptno);

create index emp_ind_clus on cluster department;

**************************************************************************************************

standby database setup in oracle 8.1.7 EE, in local host:
=========================================================

Let's assume name of the primary db "Localtest" and name of standby db as "standbydb"

1. create a sid for standbydb.

2. create a folder structure similar to primary db.

3. copy the init file from primary to standby folder.

NOTE: The init settings for standby db, is mentioned at end of this doc.

4. start the primary db, and issue the following command:

"alter database create standby control file as 'standby loc' "


5. shutdown the primary db.

6. copy the data files, log files to standby db loc.

7. start the primary db.

8. for standby db:
startup nomount pfile="standby init.ora"
alter datbase mount standby database;

9. to put standby db in manuall recovery mode:

* copy the archive log files from primary to standby loc.

* issue the command "recover standby database;" and enter "auto".

10. to put standby db in auto(managed) recovery mode:

* issue "recover managed standby database;" (optinally specify timeout option)
this will wait infinitely till the recovery is cancelled.

To cancel the recovery follow these steps:

* open a new session
* issue the command "recover managed standby database cancel", this will cancel the recovery.

To open the standby db in read only mode:
=========================================

"alter datbase open read only"

To switch back to recover mode issue the following command:

"recover managed standby database "

(or)

"recover standby database"

To activate the standby db:
===========================

"activate standby database;"
this is unmount the dbf, manually mount and open the dbf.


init settings for primary db:
=============================

log_archive_start = true
log_archive_dest_1 = 'LOCATION=D:\localtest\archive'
log_archive_dest_2='SERVICE=standbydb'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
log_archive_format = misbktest%s.arc
remote_login_passwordfile = NONE


init settings for standby db:
=============================

DB_FILE_NAME_CONVERT=('d:\localtest','d:\standbydb')
LOG_FILE_NAME_CONVERT=('d:\localtest','d:\standbydb')
LOCK_NAME_SPACE=STANDBY
remote_login_passwordfile = NONE

log_archive_start = true
log_archive_dest = D:\standbydb\archive
standby_archive_dest = D:\standbydb\archive
log_archive_format = misbktest%s.arc

*****************************************************************************************************


DBMS_SHARED_POOL
================

This is used to pin pl/sql objects in memory. To create this package execute dbmspool.sql.

To keep a procedure in memory exec. the following:

* dbms_shared_pool.keep('','type');
type: cursor-c, proce,func,pack-p, trigger-r,seq-q.

To remove a proce from memory exec. the following:
* dbms_shared_pool.unkeep('','type');

execute dbms_shared_pool.SIZES(minsize NUMBER);
Show objects in the shared_pool that are larger than the specified size.

To find out if an object is pinned in the Shared Pool, using the following query:

select * from v$db_object_cache where name='DBMS_DESCRIBE';

******************************************************************************************************

CONVERTING FROM DICTIONARY MANAGED TO LOCALLY MANAGED TABLESPACE:
=================================================================

* While converting from dict managed to locally managed, the storage clause is adapted from original ts. The "Allocation type" will be marked as "USER".

SCOPE & APPLICATION
-------------------
Analysts and Customers wishing to remove dependance on the data dictionary for space management operations. Locally Managed tablespaces. From 8.1.5, it is possible to create Locally Managed tablespaces. A locally managed tablespace is one where the space management information (basis for DBA_EXTENTS and DBA_FREE_SPACE) is stored in bitmaps in some blocks of the datafiles, rather than in the data dictionary. It is not however possible to migrate existing tablespaces from Dictionary to Local management. This means that most existing databases which have been migrated from Oracle8 still use the existing Dictionary managed space management model. One of the main reasons for this restriction was that in 8.1.5 the space management code depends on the bitmap control block and the bitmap blocks themselves being at the start of the file. This would not be possible, since the first blocks of any file in an existing tablespace are occupied by existing disk objects. With release 8.1.6, ( Oracle 8i release 2) the restriction is lifted, and therefore allows users to migrate existing tablespaces from Dictionary managed to Locally managed. The bitmap header is located at the location previously occupied by the start of the first free extent in the file. This is achieved via the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.

This takes three arguments:
o Tablespace name - The Tablespace to be Migrated. ( Mandatory )
o Unit Size - The Unit size required for space management.
o Rfno - The relative file number.

Restrictions
============
1. Tablespace cannot be SYSTEM, offline, locally managed or Temporary (ORA-3245). --> to migrate a temporary tablespace to local management, drop and recreate the tablespace with the CREATE TEMPORARY TABLESPACE command. 2. Tablespace must exist (ORA-959) with logged to tidy this up. 3. There must be enough free space in the tablespace to accomodate the bitmaps. If there are any problems with the Locally managed scheme, users can still return to dictionary management using the folowing procedure : DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL


Tablespace Name:
Name of the tablespace to be migrated.

Allocation Unit:
Unit size (which is the size of the smallest possible chunk of space that can be allocated) in the tablespace.

Relative File Number:
Relative File Number of the file where the bitmap blocks should be placed (optional)



Note on Allocation Unit:

Allocation Unit may be specified optionally. The default is calculated by the system based on the highest common divisor of all extents (used or free) for the tablespace. This number is further trimmed based on the MINIMUM EXTENT for the tablespace (5 if MINIMUM EXTENT is not specified). Thus, the calculated value will not be larger than the MINIMUM EXTENT for the tablespace. The last free extent in every file will be ignored for GCD calculation. If you specify the unit size, it has to be a factor of the UNIT size calculated by the system, otherwise an error message is returned.

Note on Relative File Number:

The Relative File Number parameter is used to place the bitmaps in a desired file. If space is not found in the file, an error is issued. The datafile specified should be part of the tablespace being migrated. If the datafile is not specified then the system will choose a datafile in which to place the initial bitmap blocks. If space is not found for the initial bitmaps, an error will be raised.

Usage Notes :

The tablespace must be kept online and read write during migration. Note that temporary tablespaces cannot be migrated. Migration of SYSTEM tablespace is not permitted in this release.

Example:

To migrate a tablespace 'TS1' with minimum extent size 1m, use
execute dbms_space_admin.tablespace_migrate_to_local('TS1', 512, 2);

The bitmaps will be placed in file with relative file number 2.

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

To access TNSNAME from a shared network, map the drive and add the entry in the following
reg value:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\TNS_ADMIN

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

To configure mts(shared server):

Make the following entries in INIT.ORA:
service_names= "localtest"
MTS_DISPATCHERS = "(protocol=tcp)(dispatchers=3)"
mts_max_dispatchers = 6
mts_servers = 3
mts_max_servers = 6

Make the following entries in TNSNAMES.ORA:

LOCALTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = misdba)(PORT = 1521))
(CONNECT_DATA =
(service_name = localtest)
(SERVER = SHARED)
)
)

REBUILD INDEX

select 'alter index' ||' '|| owner ||'.'|| segment_name || ' '|| 'rebuild tablespace index_data;' from dba_segments where
segment_type='INDEX' and owner not in('SYS','SYSTEM') and tablespace_name <> 'Index_data';

Analyze Table:
Select ‘analyze table’|| ‘ ’|| owner ||’.’|| table_name || ’ ’ ||’compute statistics;’
From dba_tables;

SQL LOADER:
Control file:aa.ctl
LOAD DATA
INFILE 'c:\LODER\PERD_050407.csv'
INTO TABLE PERD_TRANS_NEW_TS_050407 Truncate
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '^'
TRAILING NULLCOLS
(PTN_SRL_NO,
PTN_TOOL_SRL_NO,
PTN_TRAN_TYPE,
PTN_SEQ_NO,
PTN_DOCU_NO,
PTN_DOCU_DATE,
PTN_ENTR_DTTM,
PTN_WIL_FIN_TRAN_QTY,
PTN_WIL_SF_TRAN_QTY,
PTN_BOT_FIN_TRAN_QTY,
PTN_BOT_SF_TRAN_QTY,
PTN_IMP_NO,
PTN_SUPP_CODE,
PTN_PORD_NO,
PTN_PORD_DATE,
PTN_VALUE,
PTN_PRIM_CC,
PTN_SECN_CC,
PTN_VALUED_DT,
PTN_VALUED_YN,
PTN_RUNN_TRNNO)

No comments: