Benny’s Weblog

Database Reverse Engineer - Use Visio XP
SQL Editor - Toad
Change Management - Use Oracle Change Management Pack

To see rows processed by a query:
SELECT ROWS_PROCESSED RFOM V$SQLAREA WHERE UPPER(SQL_TEXT) like ‘%%’;

To see if something is rolling back:
SELECT USED_UBLK FROM V$TRANSACTION;

To drop Undo tablespace:
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘d:\oradata\undotbs02.dbf’ SIZE 2M REUSE AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;
DROP TABLESPACE undotbs1;

To monitor Undo tablespace:
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,
MAXCONCURRENCY AS “MAXCON”
FROM V$UNDOSTAT;

To switch Temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE ‘temp02.dbf’ SIZE 5M AUTOEXTEND ON;
ALTER USER PFP_NEW TEMPORARY TABLESPACE TEMP2
DROP TABLESPACE TEMP;

To fix questionable statistics during an export:
set the NLS_LANG variable to the same as the database being exported.

To Flush Oracles Buffer Cache
Oracle 10g: alter system flush buffer_cache;
Oracle 9i: alter session set events = ‘immediate trace name flush_cache’;

To Import and skip tables.
If you have to use ignore=y to load into existing tables, would
be to make it “non-insertable” — eg: if the table is named “T”, rename T to
something else if it exists, and create a new table T that has the wrong
columns. imp will fail on it. afterwards, drop t and rename the other table
back.

To gather stats:
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname=>’XXX’,
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
block_sample=>FALSE,
method_opt=>’REPEAT’,
degree=>12,
granularity=>’DEFAULT’,
cascade=>TRUE,
options=>’GATHER STALE’,
no_invalidate=>TRUE,
gather_temp=>FALSE);

NOTE Gather stale requires you to put monitoring on for those tables which are to be analyzed. It ensures you only do stats if the table changes a lot so you can run stats more often.

Another point to remember is to monitor the results of stats especially with large tables (watch out for full table scans). We found dbms_stats would often get column cardinality (dba_tab_columns) wrong depending on sampling size and especially if we didnt use histograms.

ASKTOM.COM
the recommendation going forward is “dbms_stats” instead of analyze table

it is more flexible (you’ll love gather stale and alter table monitoring — you
only gather stats on that which you need). can run in parallel. is more
automated/automatable.

as with ANY big change — testing is important.

in general,

dbms_stats.gather_table_stats( user, ‘T’, method_opt => ‘for all indexed
columns’, cascade => true )

is more or less the same as

analyze table t compute statistics for table for all indexes for all indexed
columns;

method opt gets histograms
cascade gets indexes
gather_table_stats does the table

From AskTom:
But remember, keep saying

“full scans are not evil, indexes are not good”
“full scans are not evil, indexes are not good”
“full scans are not evil, indexes are not good”
“full scans are not evil, indexes are not good”

until you believe it.

AskTom On Perf Tuning Ratios:
ratios STINK ( i have stronger words but this is a public forum after all ).
There is one ratio I use — soft parse ratio (the ratio of soft to hard parses).
It should be near 100 for most systems. All other ratios — forget about them.
There is no magic “good number”. It is like the stupidest ratio of them all -
cache hit. I have a theory that systems with high cache hit ratios, over 95,
96% — are among the most poorly tuned systems.

Project-DBA:
Good or much experience, focuses on database technology, supports
the designers, involved in all design decisions, implements the data model,
coaches the developers, maintains all development databases, responsible for
versioning and deployment, is aware of all database related activities

Lookup Table Pros
http://asktom.oracle.com/pls/ask/f?p=4950:8:5639879985208498345::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:897311189461

Contract Contact
Kha Sang Phan kphan88@hotmail.com

Execute a script and not hang up
nohup sqlplus user/pass @compute.sql &

Schedule a Job
DECLARE
job_num NUMBER;
BEGIN
dbms_job.submit(
job_num,
‘p_checkqueue;’,
–start
SYSDATE,
–nextrun
’sysdate+1/24′
);
COMMIT;
END;
/

Send An Email
CREATE OR REPLACE PROCEDURE P_Checkqueue AS
c utl_smtp.connection;
v_cnt INT;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ‘: ‘ || header || utl_tcp.CRLF);
END;

BEGIN
SELECT COUNT(*) INTO v_cnt FROM ACC_TRANSACTION;
IF ( v_cnt > 10 ) THEN
c := utl_smtp.open_connection(’smtp.sensis.com.au’);
utl_smtp.helo(c, ‘foo.com’);
utl_smtp.mail(c, ‘Ben.Smith@sensis.com.au’);
utl_smtp.rcpt(c, ‘Ben.Smith@sensis.com.au’);
utl_smtp.open_data(c);
send_header(’From’, ‘”Sender” ‘);
send_header(’To’, ‘”Recipient” ‘);
send_header(’Subject’, ‘Queue limit exceeded.’);
utl_smtp.write_data(c, utl_tcp.CRLF || ‘Queue is too big! Length: ‘ || v_cnt);
utl_smtp.close_data(c);
utl_smtp.quit(c);
END IF;
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; — When the SMTP server is down or unavailable, we don’t have
— a connection to the server. The quit call will raise an
— exception that we can ignore.
END;
RAISE_APPLICATION_ERROR(-20000,
‘Failed to send mail due to the following error: ‘ || SQLERRM);
END;
/

PDF

- All ASL references to Indirect Fire apply to both OBA and mortars.
- Ordnance is anything using the To Hit procedure.
- An OBA or bomb hit of 250mm is resolved on the 36 FP column with a -1 bonus DRM for each multiple of 50mm in excess of 200mm (FRD).

OBA
- Radio contact may be attempted at the start of the PFPh and DFPh.
- A HE/WP FFE:½/C also attacks each unit/stack that enters a hex of the Blast Area during the MPh/RtPh/APh/CCPh.
- Each non-heroic, non-berserk unit [EXC: an Observer within that Blast Area due to his Accurate placement/Correction of that FFE; units in Locations immune to OBA (1.5)] has its Morale Level, or that of its Inherent Personnel, lowered by one while within the Blast Area of a friendly HE/WP FFE or Bombardment.
- A HE Concentration (1.7) FFE of 70mm is considered a two level +1 LOS Hindrance to any LOS traced into, through, within, or from its Blast Area hex(es).
- This Hindrance DRM never exceeds +1 per FFE, regardless of the number of HE Blast hexes of that concentration it is traced through. (See Barrage)

Bombardment
- Bombardment begins after setup (inclusive of units set up offboard) but prior to the starty of play.

To Hit
- Underscored Caliber means cannot fire HE.
- Overscored Caliber means cannot fire AP.
- A vehicle may attempt to Bounding First Fire its MA (/other-FP, including Passenger FP/SW) at that DEFENDER first by declaring a Gun Duel provided it does not need to change its CA and is not OVRing.
- IFE is used straight on the IFT. A Gun using IFE has its Multiple ROF reduced by one for that shot.
- IFE has restrictions. C2.29. i.e. pays TH for CA changes, no cowering etc.
- The multiple ROF of a non-vehicular NT Gun [EXC: 76-82mm mortar] is lowered by one for its next shot in the current phase if it changes its CA for that shot.
- If that Gun has no Multiple ROF to lower, it is instead covered with an Intensive Fire counter.
- Only mortars, AA Guns (2.22) and Guns capable of using AA fire may fire-at/affect a higher-level target if the range to that target is < the elevation difference between the firer’s and the target’s Location.
- A Gun in a building hex may not fire at a lower-level target in its own hex, and may fire at a higher-level target in its hex only if it is an AA Gun of 40mm.
- These restrictions also apply to non-AA vehicular MG and to vehicular FT.
- A Gun can fire during the same Player Turn after it has entered a new Location only if it is vehicular-mounted.
- A Gun may change its CA without firing only at the end of a friendly fire phase (not MPh), and only if at that time its crew is still able to fire it without using Intensive/Sustained Fire.
- Such a change in the PFPh cancels any movement possibilities for that Gun (even a vehicular Gun) and its crew for the rest of that Player Turn, but does not prevent that Gun from attacking in the AFPh - presumably now without any Case A DRM.
-

When getting ORA-01031: insufficient privileges errors check:

- the user is part of the ORA_DBA group.

- Check that sqlnet.ora contains the line:
SQLNET.AUTHENTICATION_SERVICES = (NTS)

- Check that other sqlnet.ora files are not being accessed in another location! Especially check c:/apps/OraTNS!!

PDF

- Good Order for “?” loss; unbroken (and Dummies) for denial of “?” gain.
- A vehicle that has neither an inherent crew, nor Passenger(s), nor Rider(s), is considered “broken” for “?” gain/loss purposes.
- A single “?” dummy counter cannot be placed beneath unconcealed units.
- Before announcing any mine attacks exposed by the movement of a stack topped by a “?”, the DEFENDER may force the ATTACKER to momentarily reveal a non-Dummy unit in that stack to show that an actual force exists there.
- A Dummy stack out of the LOS of all enemy ground units uses a Morale Level of 7 when attacked, or when taking a PAATC, or a Bombardment MC.
Any K/KIA result eliminates the stack. Otherwise, the owner declares how many DRs (at least one) he will make; any failed MC or Pin result eliminates the entire stack.
- A Snap Shot attack opportunity is not sufficient to cause the moving unit to lost its concealment unless the attack generates a “PTC” or better result.
- Whenever a non-berserk enemy infantry/non-charging Cavalry unit attempts to move into a Location containing a concealed unit during the Mph [EXC: Bypass; Human Wave], the DEFENDER must immediately reveal at least one concealed unit in that Location and thereby force the moving unit back to the last Location occupied before entering his Location where it will end its Mph[EXC: units allowed to enter an enemy Location during the MPh; 4.14].
- If the ATTACKER is concealed, the DEFENDER can (before he reveals any unit) force him to momentarily reveal a non-Dummy unit in the stack; if the ATTACKER cannot, his Dummy stack is removed.
- Already-existing Residual FP in the returned-to Location attacks the returning unit possibly again.
- Similarly, a unit forced back into a FFE or minefield Location is subject to FFE/minefield attack as it re-enters that Location.
- A unit forced back into a Wire Location is placed beneath the Wire counter.
- A unit forced back to a Depression Location is placed IN it - not in Crest status.
- A unit forced back to an entrenchment/shellhole Location can derive no TEM benefit there from vs an ensuing Defensive First Fire.
- Random Selection is used to determine which of multiple concealed units must lose their concealment, but all hidden Units in the Location must be placed on board beneath a “?” prior to that Random Selection.

Searching
- A MMC may attempt to reveal concealed enemy units (/Minefields; 12.33) in Accessible hexes (including its own) by expending one additional MF in its present hex and making a Search dr, provided that all units making the attempt are neither Pinned nor using Assault Movement.
- Regardless of the outcome, that unit or moving stack is TI for the remainder of that Player Turn.
- The Final dr indicates the number of Accessible hexes other than its own of the ATTACKER’s choice which the unit/stack may not Search.
- All Searched hexes (including all above-ground) Locations in those hexes) automatically revel their contents, including the presence of minefields (but not their type and strength) and Fortified Buildings. All enemy concealed units revealed lost their “?” (or if hidden are placed on board with a “?”).

Mopping Up
- During its PFPh, an armed unpinned Good Order Infantry MMC in a multi-hex/multi-level building that contains no unconcealed unbroken enemy unit, may declare that it is becoming TI so that it may secure the building provided it is within two hexes of every ground level Location of that building which it or a friendly unit does not actually Control.
- If necessary, two or more units in different hexes may declare Mopping Up simultaneously (e.g., so that between them they can be within two hexes of every ground level Location of that building which their side does not Control).
- Any hidden enemy unit(s) in the building is immediately placed in view beneath a “?”, and all enemy Dummies are removed. Other concealed units remain concealed.
- If no concealed enemy unit remains, the building is considered secured, all its Locations Controlled, and all broken enemy units therein immediately surrender to units of the Attacker’s choice inside that building regardless of their proximity.
- All Fortified Building Locations so Controlled are revealed.
- Mopping Up can be attempted only once per building per Player Turn but can be attempted even if no stairwell is available to reach upper levels.
- Once a side employs No Quarter/Massacre (20.3-.4), it may no longer use Mopping Up.
- If a Location that is Searched / Mopped Up contains an armed Good Order unit/minefield/Residual FP/HE FFE Blast Area, or if the DEFENDER has any Booby Trap capability (B28.9), the DEFENDER may make one Casualty DR per Search dr in an effort to cause casualties to the searching / Mopping Up unit(s).
-