Jennifer Lin’s Weblog

March 29, 2008

Turning Challenges into Opportunities

Filed under: English — jennyca @ 3:17 pm

By Linda Thompson

Back in 1990, if anyone had told me I’d be speaking to audiences of 100 and more, I would have told them they were crazy and then run for the nearest exit. While I’ve always been outspoken, I had never sought attention. I always preferred to be in the background with a certain degree of anonymity. That was before I found a method of turning a challenge into an opportunity, and doing it more than once.

I used to say I had made a profession of being laid off. I was in marketing – always the first to go – in the electronics industry – on a 3-5 year cycle, and could pretty well count on a severance package about every 4 years. If I positioned myself right, I’d be one of the first out the door and those checks were very healthy.When the big 50 came along, it was obvious to me that the gig was up. I had to find a way to make a living on a more permanent basis. At the same time, I decided I really didn’t like working 8 to 5. My internal clock didn’t work that way and I really resented waking up to the very irritating sound of an alarm clock every day.In 1997 a friend went into the financial services industry, and convinced me that was where I needed to be. Insurance and securities licenses in hand, I was set to make millions. That was the first challenge – I did not like selling insurance. There was nothing fun about it. I can’t add 2 and 2 without a calculator and the paperwork was intimidating, overwhelming and downright boring.The other side of this coin is that my mother relocated to Phoenix in 1994 and we built a home together. At that time she was a very healthy 78 year old who could work circles around me. As time went by, Mom began the journey toward her last days and things got increasingly difficult for her to do around the house. She stopped driving, had an aneurism in one eye, her hearing was going and I was faced with some difficult decisions. I had to find a way to keep her healthy and happy at home because I had promised her she would take her last breath in her own home. However, I also had to make a living to pay the bills.As I worked with clients on their financial needs, the thing that kept coming up were questions about caring for Mom and Dad. How do I, what do I, who do I and so forth. Since I was doing this research for Mom, I had some of the answers. Those that I did not have, I knew people who would. Networking is a great thing. Before long, I had a box full of business cards from all areas of the elder-care industry, had formed relationships with a lot of them, and was talking with my insurance clients about their challenges.At a networking lunch, I met a gal who was trying to put together a series of educational, elder-care workshops for her employees. When she heard my story, she invited me to take over the project. That was my entrée into public speaking.

Scared witless the first time, clutching the lectern for dear life, and with raspy, shaky voice, I began my first workshop to an audience of about 50. What I learned very quickly was that I had no problem talking with five and that 50 was only 5 times 10. So, I picked five people in the back of the room, focused on them and muddled my way through my first speaking gig. My evaluation forms were almost all positive.

This company kept me coming back for six years and I’ve expanded my horizons considerably. I’ve spoken to groups of 100+, have written four books, have breezed through radio and TV interviews and now feel like a pro. But if I hadn’t had my back to the wall, faced with 8 to 5 for the rest of my life and rebelling with every ounce of my being, I don’t know if I would have taken that leap.

Here’s some advice from someone who has been there – done that. Don’t just quit your job and go into business without thinking it through from every angle. Make darn sure you have some money squirreled away, and I don’t mean petty cash. I have cashed in my 401(k)s, my IRAs, had spent one inheritance from an aunt and was well into the second before I finally turned a profit. For someone with less confidence in themselves, or less determined to never hold a JOB again, this jump may not be the wisest choice you’ll ever make.

On the other hand, if you have the financial backing, the confidence that your service or product is needed, and are not afraid to toot your own horn to anyone and everyone, then do it. It’s never too late. To paraphrase an old cliché, “It’s much better to have tried and failed than never to have tried at all.”

http://www.success.bz/articles/1911/turning_challenges_into_opportunities

March 28, 2008

ORA-01031

Filed under: ORA Errors — jennyca @ 5:45 pm

ORA-01031: insufficient privileges

In most cases, the user receiving this error lacks a privilege to create an object (such as a table, view, procedure and the like).
Grant the required privilege like so:
grant create table to user_lacking_privilege;

Startup

If someone receives this error while trying to startup the instance, the logged on user must belong to the ora_dba group on Windows or dba group on Unix.
To add a user to the ora_dba group on Windows, net localgroup should help:
C:\> net localgroup ora_dba rene /add

http://www.adp-gmbh.ch/ora/err/ora_01031.html

According to our DBA, I have the permission to create views. However, when I am creating one, it gives me error ‘ORA-01031: insufficient privileges’. Could you kindly give some expert advice on what might be the cause. I am currently using TOAD to connect to Oracle DB.

Your first idea that you need the CREATE VIEW system privilege is correct. If you do not have this system privilege, then you will receive the ORA-1031 error when you try to create the view. If you have been granted CREATE VIEW and still cannot create your view, then you need to dig further. When you create the view, you rely on one or more underlying objects of that view. These objects can be other tables, views, synonyms, functions, etc. And sometimes, these objects depend on other objects. For instance, the synonym your view is trying to reference points to a view in another schema that uses a table in that schema. You need to make sure that you have the appropriate permissions on the underlying objects of your view, and any objects that those underlying objects depend on. If you do not have permissions on any of these objects, then you will receive the ORA-1031 error.

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054070,00.html

Oracle Diagnostic Events

Filed under: Oracle DBA — jennyca @ 5:47 am
SQL> SET linesize 120
SQL> SET feedback off
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  err_msg VARCHAR2(120);
  3  BEGIN
  4  dbms_output.enable (1000000);
  5  FOR err_num IN 10000..10999
  6  LOOP
  7  err_msg := SQLERRM (-err_num);
  8  IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
  9  dbms_output.put_line (err_msg);
 10  END IF;
 11  END LOOP;
 12  END;
 13  /
ORA-10000: control file debug event, name 'control_file'
ORA-10001: control file crash event1
ORA-10002: control file crash event2
ORA-10003: control file crash event3
ORA-10004: block recovery testing - internal error
ORA-10005: trace latch operations for debugging
ORA-10006: block recovery testing - external error
ORA-10007: log switch debug crash after new log select, thread
ORA-10008: log switch debug crash after new log header write, thread
ORA-10009: log switch debug crash after old log header write, thread
ORA-10010: Begin Transaction
ORA-10011: End   Transaction
ORA-10012: Abort Transaction
ORA-10013: Instance Recovery
ORA-10014: Roll Back to Save Point
ORA-10015: Undo Segment Recovery
ORA-10016: Undo Segment extend
ORA-10017: Undo Segment Wrap
ORA-10018: Data Segment Create
ORA-10020: partial link restored to linked list (KSG)
ORA-10021: KST event to trace control file header writes and reads
ORA-10022: trace ktsgsp
ORA-10023: Create Save Undo Segment
ORA-10024: Write to Save Undo
ORA-10026: Apply Save Undo
ORA-10027: Specify Deadlock Trace Information to be Dumped
ORA-10028: Dump trace information during lock / resource latch cleanup
ORA-10029: session logon (KSU)
ORA-10030: session logoff (KSU)
ORA-10031: sort debug event (S*)
ORA-10032: sort statistics (SOR*)
ORA-10033: sort run information (SRD*/SRS*)
ORA-10035: Write parse failures to alert log file
ORA-10036: create remote row source (QKANET)
ORA-10037: allocate remote row source (QKARWS)
ORA-10038: dump row source tree (QBADRV)
ORA-10039: type checking (OPITCA)
ORA-10041: dump undo records skipped
ORA-10043: check consistency of owner/waiter/converter lists in KSQ
ORA-10044: free list undo operations
ORA-10045: free list update operations - ktsrsp, ktsunl
ORA-10046: enable SQL statement timing
ORA-10047: trace switching of sessions
ORA-10048: Undo segment shrink
ORA-10049: protect library cache memory heaps
ORA-10050: sniper trace
ORA-10051: trace OPI calls
ORA-10052: don't clean up obj$
ORA-10053: CBO Enable optimizer trace
ORA-10056: dump analyze stats (kdg)
ORA-10057: suppress file names in error messages
ORA-10058: use table scan cost in tab$.spare1
ORA-10059: simulate error in logfile create/clear
ORA-10060: CBO Enable predicate dump
ORA-10061: disable SMON from cleaning temp segment
ORA-10062: disable usage of OS Roles in osds
ORA-10063: disable usage of DBA and OPER privileges in osds
ORA-10064: thread enable debug crash level , thread
ORA-10065: limit library cache dump information for state object dump
ORA-10066: simulate failure to verify file
ORA-10067: force redo log checksum errors - block number
ORA-10068: force redo log checksum errors - file number
ORA-10070: force datafile checksum errors - block number
ORA-10071: force datafile checksum errors - file number
ORA-10072: protect latch recovery memory
ORA-10073: latch cleanup tracing
ORA-10074: default trace function mask for kst
ORA-10075: CBO Disable outer-join to regular join conversion
ORA-10076: CBO Enable cartesian product join costing
ORA-10077: CBO Disable view-merging optimization for outer-joins
ORA-10078: CBO Disable constant predicate elimination optimization
ORA-10079: trace data sent/received via SQL*Net
ORA-10080: dump a block on a segment list which cannot be exchanged
ORA-10081: segment High Water Mark has been advanced
ORA-10082: free list head block is the same as the last block
ORA-10084: free list becomes empty
ORA-10085: free lists have been merged
ORA-10086: CBO Enable error if kko and qka disagree on oby sort
ORA-10087: disable repair of media corrupt data blocks
ORA-10088: CBO Disable new NOT IN optimization
ORA-10089: CBO Disable index sorting
ORA-10090: invoke other events before crash recovery
ORA-10091: CBO Disable constant predicate merging
ORA-10092: CBO Disable hash join
ORA-10093: CBO Enable force hash joins
ORA-10094: before resizing a data file
ORA-10095: dump debugger commands to trace file
ORA-10096: after the cross instance call when resizing a data file
ORA-10097: after generating redo when resizing a data file
ORA-10098: after the OS has increased the size of a data file
ORA-10099: after updating the file header with the new file size
ORA-10100: after the OS has decreased the size of a data file
ORA-10102: switch off anti-joins
ORA-10103: CBO Disable hash join swapping
ORA-10104: dump hash join statistics to trace file
ORA-10105: CBO Enable constant pred trans and MPs w WHERE-clause
ORA-10106: CBO Disable evaluating correlation pred last for NOT IN
ORA-10107: CBO Always use bitmap index
ORA-10108: CBO Don't use bitmap index
ORA-10109: CBO Disable move of negated predicates
ORA-10110: CBO Try index rowid range scans
ORA-10111: Bitmap index creation switch
ORA-10112: Bitmap index creation switch
ORA-10113: Bitmap index creation switch
ORA-10114: Bitmap index creation switch
ORA-10115: CBO Bitmap optimization use maximal expression
ORA-10116: CBO Bitmap optimization switch
ORA-10117: CBO Disable new parallel cost model
ORA-10118: CBO Enable hash join costing
ORA-10119: QKA Disable GBY sort elimination
ORA-10120: generate relative file # different from absolute
ORA-10121: CBO Don't sort bitmap chains
ORA-10122: Disable transformation of count(col) to count(*)
ORA-10124: Force creation of segmented arrays by kscsAllocate
ORA-10125: Disable remote sort elimination
ORA-10126: Debug oracle java xa
ORA-10127: Disable remote query block operation
ORA-10128: Dump Partition Pruning Information
ORA-10129: Alter histogram lookup for remote queries
ORA-10130: sort disable readaheads
ORA-10132: dump plan after compilation
ORA-10133: testing for SQL Memory Management
ORA-10134: tracing for SQL Memory Management for session
ORA-10135: CBO do not count 0 rows partitions
ORA-10136: CBO turn off fix for bug 1089848
ORA-10137: CBO turn off fix for bug 1344111
ORA-10138: CBO turn off fix for bug 1577003
ORA-10139: CBO turn off fix for bug 1386119
ORA-10140: CBO turn off fix for bug 1332980
ORA-10141: CBO disable additional keys for inlist in bitmap optimization
ORA-10142: CBO turn off advanced OR-expansion checks
ORA-10143: CBO turn off hints
ORA-10144: CBO turn off cost based selection of bji over bsj subquery
ORA-10145: test auditing network errors
ORA-10146: enable Oracle TRACE collection
ORA-10147: enable join push through UNION view
ORA-10149: allow the creation of constraints with illegal date constants
ORA-10150: import exceptions
ORA-10152: CBO don't consider function costs in plans
ORA-10153: Switch to use public synonym if private one does not translate
ORA-10154: Switch to disallow synonyms in DDL statements
ORA-10155: CBO disable generation of transitive OR-chains
ORA-10156: CBO disable index fast full scan
ORA-10157: CBO disable index access path for in-list
ORA-10158: CBO preserve predicate order in post-filters
ORA-10159: CBO disable order-by sort pushdown into domain indexes
ORA-10160: CBO disable use of join index
ORA-10161: CBO recursive semi-join on/off-switch
ORA-10162: CBO join-back elimination on/off-switch
ORA-10163: CBO join-back elimination on/off-switch
ORA-10164: CBO disable subquery-adjusted cardinality fix
ORA-10165: mark session to be aborted during shutdown normal
ORA-10166: trace long operation statistics updates
ORA-10167: CBO use old index MIN/MAX optimization
ORA-10168: CBO disable single-table predicate predicate generation
ORA-10169: CBO disable histograms for multi partitions
ORA-10170: CBO use old bitmap costing
ORA-10171: CBO disable transitive join predicates
ORA-10172: CBO force hash join back
ORA-10173: Dynamic Sampling time-out error
ORA-10174: view join-back elimination switch
ORA-10175: CBO star transformation switch
ORA-10176: CBO colocated join switch
ORA-10177: CBO colocated join switch
ORA-10178: CBO turn off hash cluster filtering through memcmp
ORA-10179: CBO turn off transitive predicate replacement
ORA-10180: temp table transformation print error messages
ORA-10181: CBO disable multi-column in-list processing
ORA-10182: CBO disable generation of implied predicates
ORA-10183: CBO disable cost rounding
ORA-10184: CBO disable OR-exp if long inlist on bitmap column
ORA-10185: CBO force index joins
ORA-10186: CBO disable index join
ORA-10187: CBO additional index join switch
ORA-10188: CBO additional index join switch
ORA-10189: CBO turn off FFS null fix
ORA-10190: Analyze use old frequency histogram collection and density
ORA-10191: Avoid conversion of in-lists back to OR-expanded form
ORA-10192: nopushdown when number of groups exceed number of rows
ORA-10193: Force repeatable sampling with specified seed
ORA-10194: CBO disable new LIKE selectivity heuristic
ORA-10195: CBO don't use check constraints for transitive predicates
ORA-10196: CBO disable index skip scan
ORA-10197: CBO force index skip scan
ORA-10199: set parameter in session
ORA-10200: consistent read buffer status
ORA-10201: consistent read undo application
ORA-10202: consistent read block header
ORA-10203: block cleanout
ORA-10204: signal recursive extend
ORA-10205: row cache debugging
ORA-10206: transaction table consistent read
ORA-10207: consistent read transactions' status report
ORA-10208: consistent read loop check
ORA-10209: enable simulated error on control file
ORA-10210: check data block integrity
ORA-10212: check cluster integrity
ORA-10213: crash after control file write
ORA-10214: simulate write errors on control file
ORA-10215: simulate read errors on control file
ORA-10216: dump control file header
ORA-10217: debug sequence numbers
ORA-10218: dump uba of applied undo
ORA-10219: monitor multi-pass row locking
ORA-10220: show updates to the transaction table
ORA-10221: show changes done with undo
ORA-10222: row cache
ORA-10223: transaction layer - turn on verification codes
ORA-10224: index block split/delete trace
ORA-10225: free/used extent row cache
ORA-10226: trace CR applications of undo for data operations
ORA-10227: verify (multi-piece) row structure
ORA-10228: trace application of redo by kcocbk
ORA-10229: simulate I/O error against datafiles
ORA-10231: skip corrupted blocks on _table_scans_
ORA-10232: dump corrupted blocks symbolically when kcbgotten
ORA-10233: skip corrupted blocks on index operations
ORA-10234: trigger event after calling kcrapc to do redo N times
ORA-10235: check memory manager internal structures
ORA-10236: dump redo on object no. or block type mismatch errors 1410/8103
ORA-10237: simulate ^C (for testing purposes)
ORA-10238: instantiation manager
ORA-10239: multi-instance library cache manager
ORA-10240: dump dba's of blocks that we wait for
ORA-10241: remote SQL execution tracing/validation
ORA-10242: suppress OER 2063 (for testing distrib w/o different error log)
ORA-10243: simulated error for test  of K2GTAB latch cleanup
ORA-10244: make tranids in error msgs print as 0.0.0 (for testing)
ORA-10246: print trace of PMON actions to trace file
ORA-10247: Turn on scgcmn tracing. (VMS ONLY)
ORA-10248: turn on tracing for dispatchers
ORA-10249: turn on tracing for multi-stated servers
ORA-10250: Trace all allocate and free calls to the topmost SGA heap
ORA-10251: check consistency of transaction table and undo block
ORA-10254: trace cross-instance calls
ORA-10255: pl/sql parse checking
ORA-10257: trace shared server load balancing
ORA-10258: force shared servers to be chosen round-robin
ORA-10259: get error message text from remote using explicit call
ORA-10261: Limit the size of the PGA heap
ORA-10262: Don't check for memory leaks
ORA-10263: Don't free empty PGA heap extents
ORA-10265: Keep random system generated output out of error messages
ORA-10266: Trace OSD stack usage
ORA-10267: Inhibit KSEDMP for testing
ORA-10268: Don't do forward coalesce when deleting extents
ORA-10269: Don't do coalesces of free space in SMON
ORA-10270: Debug shared cursors
ORA-10271: distributed transaction after COLLECT
ORA-10272: distributed transaction before PREPARE
ORA-10273: distributed transaction after PREPARE
ORA-10274: distributed transaction before COMMIT
ORA-10275: distributed transaction after COMMIT
ORA-10276: distributed transaction before FORGET
ORA-10277: Cursor sharing (or not) related event (used for testing)
ORA-10278: Internal testing
ORA-10279: Simulate block corruption in kdb4chk
ORA-10280: Internal testing - segmentation fault during crash recovery
ORA-10281: maximum time to wait for process creation
ORA-10282: Inhibit signalling of other backgrounds when one dies
ORA-10284: simulate zero/infinite asynch I/O buffering
ORA-10285: Simulate control file header corruption
ORA-10286: Simulate control file open error
ORA-10287: Simulate archiver error
ORA-10288: Do not check block type in ktrget
ORA-10289: Do block dumps to trace file in hex rather than fromatted
ORA-10290: kdnchk - checkvalid event - not for general purpose use.
ORA-10291: die in tbsdrv to test control file undo
ORA-10293: trace log switch media recovery checkpoints
ORA-10295: die after file header update durning cf xact
ORA-10296: disable ORA-379
ORA-10297: customize dictionary object number cache
ORA-10298: ksfd i/o tracing
ORA-10299: Trace prefetch tracking decisions made by CKPT
ORA-10301: Enable LCK timeout table consistency check
ORA-10302: trace create or drop internal trigger
ORA-10303: trace loading of library cache for internal triggers
ORA-10304: trace replication trigger
ORA-10305: trace updatable materialized view trigger
ORA-10306: trace materialized view log trigger
ORA-10307: trace RepCat execution
ORA-10308: replication testing event
ORA-10309: Trigger Debug event
ORA-10310: trace synchronous change table trigger
ORA-10311: Disable Flashback Table Timestamp checking
ORA-10312: Allow disable to log rows into the mapping table
ORA-10314: Enable extra stats gathering for CR
ORA-10316: Events for extensible txn header, non zero ext header size
ORA-10317: Events for extensible txn header, zero ext header size
ORA-10318: Trace extensible txn header movements
ORA-10319: Trace PGA statistics maintenance
ORA-10320: Enable data layer (kdtgrs) tracing of space management calls
ORA-10321: Datafile header verification debug failure.
ORA-10322: CBO don't simplify inlist predicates
ORA-10323: before committing an add datafile command
ORA-10324: Enable better checking of redo logs errors
ORA-10325: Trace control file record section expand and shrink operations
ORA-10326: clear logfile debug crash at , log
ORA-10327: simulate ORA-00235 error for testing
ORA-10328: disable first-to-mount split-brain error, for testing
ORA-10329: simulate out-of-memory error during first pass of recovery
ORA-10330: clear MTTR statistics in checkpoint progress record
ORA-10331: simulate resilvering during recovery
ORA-10332: force ALTER SYSTEM QUIESCE RESTRICTED command to fail
ORA-10333: dump MTTR statistics each time it is updated
ORA-10334: force FG to wait to be killed during MTTR advisory simulation
ORA-10335: reserved for recovery layer for testing purpose
ORA-10336: Do remote object transfer using remote SQL
ORA-10337: enable padding owner name in slave sql
ORA-10338: CBO don't use inlist iterator with function-based indexes
ORA-10339: CBO disable DECODE simplification
ORA-10340: Buffer queues sanity check for corrupted buffers
ORA-10341: Simulate out of PGA memory in DBWR during object reuse
ORA-10342: Raise unknown exception in ACQ_ADD when checkpointing
ORA-10343: Raise an out of memory exception-OER 4031 in ACQ_ADD
ORA-10345: validate queue when linking or unlinking a buffer
ORA-10346: check that all buffers for checkpoint have been written
ORA-10347: dump active checkpoint entries and checkpoint buffers
ORA-10348: test abnormal termination of process initiating file checkpoint
ORA-10349: do not allow ckpt to complete
ORA-10350: Simulate more than one object & tsn id in object reuse
ORA-10351: size of slots
ORA-10352: report direct path statistics
ORA-10353: number of slots
ORA-10354: turn on direct read path for parallel query
ORA-10355: turn on direct read path for scans
ORA-10356: turn on hint usage for direct read
ORA-10357: turn on debug information for direct path
ORA-10358: Simulate out of PGA memory in cache advisory reset
ORA-10359: turn off updates to control file for direct writes
ORA-10360: enable dbwr consistency checking
ORA-10361: check buffer change vector count consistency
ORA-10362: simulate a write error to take a file offline
ORA-10364: Do not clear GIMH_STC_SHUT_BEGIN state during shutdown
ORA-10365: turn on debug information for adaptive direct reads
ORA-10368: maximum number of internal errors a process will tolerate
ORA-10370: parallel query server kill event
ORA-10371: disable TQ hint
ORA-10372: parallel query server kill event proc
ORA-10373: parallel query server kill event
ORA-10375: turn on checks for statistics rollups
ORA-10378: force hard process/range affinity
ORA-10380: kxfp latch cleanup testing event
ORA-10381: kxfp latch cleanup testing event
ORA-10382: parallel query server interrupt (reset)
ORA-10383: auto parallelization testing event
ORA-10384: parallel dataflow scheduler tracing
ORA-10385: parallel table scan range sampling method
ORA-10386: parallel SQL hash and range statistics
ORA-10387: parallel query server interrupt (normal)
ORA-10388: parallel query server interrupt (failure)
ORA-10389: parallel query server interrupt (cleanup)
ORA-10390: Trace parallel query slave execution
ORA-10391: trace PX granule allocation/assignment
ORA-10392: parallel query debugging bits
ORA-10393: print parallel query statistics
ORA-10394: generate a fake load to test adaptive and load balancing
ORA-10395: adjust sample size for range table queues
ORA-10397: suppress verbose parallel coordinator error reporting
ORA-10398: enable timeouts in parallel query threads
ORA-10399: trace buffer allocation
ORA-10400: turn on system state dumps for shutdown debugging
ORA-10401: turn on IPC (ksxp) debugging
ORA-10402: turn on IPC (skgxp) debugging
ORA-10403: fake CPU number for default degree of parallelism
ORA-10404: crash dbwr after write
ORA-10405: emulate broken mirrors
ORA-10406: enable datetime TIMESTAMP, INTERVAL datatype creation
ORA-10407: enable datetime TIME datatype creation
ORA-10408: disable OLAP builtin window function usage
ORA-10409: enable granule memset and block invalidation at startup
ORA-10410: trigger simulated communications errors in KSXP
ORA-10413: force simulated error for testing purposes
ORA-10414: simulated error from event  level
ORA-10415: parallel degree specified is too large, max value allowed
ORA-10416: disable fix for 2736734
ORA-10417: limit 1 file per sbtinfo2() validation call
ORA-10418: disable re-creating tempfile
ORA-10419: create tempfile without create_scn and time
ORA-10420: trace KSO OS-process operations
ORA-10421: enable dump from ksbwco if there is no reply
ORA-10422: KSU debugging
ORA-10423: dump the call stack if the specified error is cleared
ORA-10424: KGE debugging
ORA-10425: enable global enqueue operations event trace
ORA-10426: enable ges/gcs reconfiguration event trace
ORA-10427: enable global enqueue service traffic controller event trace
ORA-10428: enable tracing of global enqueue service cached resource
ORA-10429: enable tracing of global enqueue service IPC calls
ORA-10430: enable ges/gcs dynamic remastering event trace
ORA-10431: enable verification messages on pi consistency
ORA-10432: enable tracing of global cache service fusion calls
ORA-10433: global enqueue service testing event
ORA-10434: enable tracing of global enqueue service multiple LMS
ORA-10435: enable tracing of global enqueue service deadlock detetction
ORA-10436: enable global cache service duplicate ping checking
ORA-10437: enable trace of global enqueue service S optimized resources
ORA-10438: force lowest node to be master of all gcs resources
ORA-10439: enable tracing of global cache service fusion calls - part 2
ORA-10440: enable global enqueue service inquire resource modes trace
ORA-10442: enable trace of kst for ORA-01555 diagnostics
ORA-10449: enable trace of kst for undo manageability features diagnostics
ORA-10450: signal ctrl-c in kdddca (drop column) after n rows
ORA-10451: Force heap segment compression bypassing compatibility checks
ORA-10453: Dump compression statistics to trace file
ORA-10454: Disable column reordering during compression
ORA-10460: Perform backward tablescans for consistent read reduction
ORA-10461: Simulate control file corruption during write operation
ORA-10462: enable recovery debug module
ORA-10463: enable controlfile test
ORA-10464: enable incremental checkpoint debug for split brain check
ORA-10465: force slave death during parallel crash recovery
ORA-10466: enable HARD check for block write
ORA-10467: amplify control file record expansion for testing
ORA-10468: log writer debug module
ORA-10490: Trace OSM misc. events
ORA-10491: Trace OSM messaging (KFN) events
ORA-10492: Trace OSM metadata events
ORA-10493: Return empty define buffers on 1422
ORA-10494: Trace OSM metadata events
ORA-10495: Trace OSM metadata events
ORA-10496: Turn off fix for bug 2554178
ORA-10497: Trace OSM metadata events
ORA-10498: Trim blank characters including contol characters
ORA-10499: Trace OSM metadata events
ORA-10500: turn on traces for SMON
ORA-10501: periodically check selected heap
ORA-10502: CBO disable the fix for bug 2098120
ORA-10503: enable user-specified graduated bind lengths
ORA-10504: CBO disable the fix for bug 2607029
ORA-10505: CBO enable dynamic sampling dump to table
ORA-10506: Disable fix for bug 2588217
ORA-10507: Trace bind equivalence logic
ORA-10509: Check kghu subheaps at call boundaries
ORA-10510: turn off SMON check to offline pending offline rollback segment
ORA-10511: turn off SMON check to cleanup undo dictionary
ORA-10512: turn off SMON check to shrink rollback segments
ORA-10515: turn on event to use physical cleanout
ORA-10520: recreate view only if definition has changed
ORA-10522: turn off wrap source compression
ORA-10523: force recreate package even if definition is unchanged
ORA-10525: Disable automatic object validation for describe
ORA-10550: signal error during create as select/create index after n rows
ORA-10551: Internal testing for ORA-1551 error handling
ORA-10560: block type ''
ORA-10561: block type '', data object#
ORA-10562: Error occurred while applying redo to data block (file# , block# )
ORA-10563: Test recovery had to corrupt data block (file# , block# ) in order to proceed
ORA-10564: tablespace
ORA-10565: Another test recovery session is active
ORA-10566: Test recovery has used all the memory it can use
ORA-10567: Redo is inconsistent with data block (file# , block# )
ORA-10568: Failed to allocate recovery state object: out of SGA memory
ORA-10570: Test recovery complete
ORA-10571: Test recovery canceled
ORA-10572: Test recovery canceled due to errors
ORA-10573: Test recovery tested redo from change  to
ORA-10574: Test recovery did not corrupt any data block
ORA-10575: Give up restoring recovered datafiles to consistent state: out of memory
ORA-10576: Give up restoring recovered datafiles to consistent state: some error occurred
ORA-10577: Can not invoke test recovery for managed standby database recovery
ORA-10578: Can not allow corruption for managed standby database recovery
ORA-10579: Can not modify control file during test recovery
ORA-10580: Can not modify datafile header during test recovery
ORA-10581: Can not modify redo log header during test recovery
ORA-10582: The control file is not a backup control file
ORA-10583: Can not recovery file  renamed as missing during test recovery
ORA-10584: Can not invoke parallel recovery for test recovery
ORA-10585: Test recovery can not apply redo that may modify control file
ORA-10586: Test recovery had to corrupt 1 data block in order to proceed
ORA-10587: Invalid count for ALLOW n CORRUPTION option
ORA-10588: Can only allow 1 corruption for normal media/standby recovery
ORA-10589: Test recovery had to corrupt  data blocks in order to proceed
ORA-10590: kga (argus debugger) test flags
ORA-10591: kga (argus debugger) test flags
ORA-10592: kga (argus debugger) test flags
ORA-10593: kga (argus debugger) test flags
ORA-10594: kga (argus debugger) test flags
ORA-10595: kga (argus debugger) test flags
ORA-10596: kga (argus debugger) test flags
ORA-10597: kga (argus debugger) test flags
ORA-10598: kga (argus debugger) test flags
ORA-10599: kga (argus debugger) test flags
ORA-10600: check cursor frame allocation
ORA-10601: turn on debugging for cursor_sharing (literal replacement)
ORA-10603: cause an error to occur during truncate (for testing purposes)
ORA-10604: trace parallel create index
ORA-10605: enable parallel create index by default
ORA-10606: trace parallel create index
ORA-10607: trace index rowid partition scan
ORA-10608: trace create bitmap index
ORA-10609: trace for array index insertion
ORA-10610: trace create index pseudo optimizer
ORA-10611: causes migration to fail - testing only
ORA-10612: prints debug information for auto-space managed segments
ORA-10613: prints debug information for auto-space managed segments
ORA-10614: Operation not allowed on this segment
ORA-10615: Invalid tablespace type for temporary tablespace
ORA-10616: Operation not allowed on this tablespace
ORA-10617: Cannot create rollback segment in dictionary managed tablespace
ORA-10618: Operation not allowed on this segment
ORA-10619: Avoid assertions when possible
ORA-10620: Operation not allowed on this segment
ORA-10621: specify retry count for online index build cleanup DML lock get
ORA-10622: test/trace online index (re)build
ORA-10623: Enable Index range scan Prefetch - testing only
ORA-10624: Disable UJV invalidation on drop index
ORA-10625: Turn off redo log dump for the index when OERI 12700
ORA-10626: specify timeout for online index rebuild to wait for DML
ORA-10627: Dump the content of the index leaf block
ORA-10628: Turn on sanity check for kdiss index skip scan state
ORA-10629: force online index build to backoff and retry DML lock upgrade
ORA-10630: Illegal syntax specified with SHRINK clause
ORA-10631: SHRINK clause should not be specified for this object
ORA-10632: Invalid rowid
ORA-10633: No space found in the segment
ORA-10634: Segment is already being shrunk
ORA-10635: Invalid segment or tablespace type
ORA-10636: ROW MOVEMENT is not enabled
ORA-10637: The segment does not exist
ORA-10638: Index status is invalid
ORA-10639: Dump library cache during kksfbc-reparse-infinite-loop error
ORA-10640: Operation not permitted during SYSTEM tablespace migration
ORA-10641: Cannot find a rollback segment to bind to
ORA-10642: Found rollback segments in dictionary managed tablespaces
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-10645: Recursive Extension in SYSTEM tablespace during migration
ORA-10646: Too many recursive extensions during SYSTEM tablespace migration
ORA-10647: Tablespace other than SYSTEM, ,  not found in read only mode
ORA-10648: Tablespace SYSAUX is not offline
ORA-10649: Turn off/trace lob index freelist coalesce
ORA-10650: disable cache-callback optimisation
ORA-10651: incorrect file number block number specified
ORA-10652: Object has on-commit materialized views
ORA-10653: Table is in a cluster
ORA-10654: Table is of type temporary or external
ORA-10655: Segment can be shrunk
ORA-10656: Table is in unusable state due to incomplete operation
ORA-10657: Lob column to be shrunk does not exist
ORA-10658: Lob column to be shrunk is marked unused
ORA-10659: Segment being shrunk is not a lob
ORA-10660: Segment is a shared lob segment
ORA-10661: Invalid option specified
ORA-10662: Segment has long columns
ORA-10663: Object has rowid based materialized views
ORA-10664: Table has bitmap join indexes
ORA-10665: Inject Evil Literals
ORA-10666: Do not get database enqueue name
ORA-10667: Cause sppst to check for valid process ids
ORA-10668: Inject Evil Identifiers
ORA-10690: Set shadow process core file dump type (Unix only)
ORA-10691: Set background process core file type (Unix only)
ORA-10700: Alter access violation exception handler
ORA-10701: Dump direct loader index keys
ORA-10704: Print out information about what enqueues are being obtained
ORA-10706: Print out information about global enqueue manipulation
ORA-10707: Simulate process death for instance registration
ORA-10708: print out trace information from the RAC buffer cache
ORA-10709: enable parallel instances in create index by default
ORA-10710: trace bitmap index access
ORA-10711: trace bitmap index merge
ORA-10712: trace bitmap index or
ORA-10713: trace bitmap index and
ORA-10714: trace bitmap index minus
ORA-10715: trace bitmap index conversion to rowids
ORA-10716: trace bitmap index compress/decompress
ORA-10717: trace bitmap index compaction trace for index creation
ORA-10718: event to disable automatic compaction after index creation
ORA-10719: trace bitmap index dml
ORA-10720: trace db scheduling
ORA-10721: Internal testing - temp table transformation
ORA-10722: set parameters for CPU frequency calculation (debug)
ORA-10723: Internal testing - release buffer for buffer cache shrink
ORA-10724: trace cross-instance broadcast
ORA-10725: bitmap index version control
ORA-10726: frequent itemset counting
ORA-10730: trace row level security policy predicates
ORA-10731: dump SQL for CURSOR expressions
ORA-10732: honor pctfree during insert into AQ IOTs
ORA-10733: test transient-IOT metadata during PMO cleanup
ORA-10734: reroute external procedures
ORA-10735: debug ksws operations
ORA-10736: buffer cache pin history dump
ORA-10737: test block checking
ORA-10738: internal block testing
ORA-10740: disables fix for bug 598861
ORA-10750: test rollback segment blksize guessing for index array insert
ORA-10780: LogMiner API trace event
ORA-10781: LogMiner reader trace event
ORA-10782: LogMiner preparer trace event
ORA-10783: LogMiner builder trace event
ORA-10784: LogMiner dictionary trace event
ORA-10785: LogMiner trace event
ORA-10786: call push/pop (KSU)
ORA-10787: trace intra-instance broadcast
ORA-10788: trace call stacks
ORA-10789: LogMiner test event
ORA-10790: LogMiner trace event
ORA-10791: Logical Standby swithover/failover trace event
ORA-10792: Logical Standby XDAT trace event
ORA-10793: Logical Standby trace event
ORA-10794: Logical Standby trace event
ORA-10800: disable Smart Disk scan
ORA-10804: reserved for ksxb
ORA-10806: Switch to 7.3 mode when detaching sessions
ORA-10807: Disable user id check when switching to a global transaction
ORA-10808: Enable assert when waiting without a reason
ORA-10809: Trace state object allocate / free history
ORA-10810: Trace snapshot too old
ORA-10811: Trace block cleanouts
ORA-10812: Trace Consistent Reads
ORA-10826: enable upgrade/downgrade error message trace
ORA-10827: enable upgrade/downgrade diagnostics
ORA-10830: Trace group by sort row source
ORA-10831: Trace group by rollup row source
ORA-10839: trace / debug caching module (qesca.c)
ORA-10840: trace / debug pl/sql caching module (kkxmInitCache)
ORA-10841: Default un-inintialized charact set form to SQLCS_IMPLICIT
ORA-10842: Event for OCI Tracing and Statistics Info
ORA-10844: turn on Native Net IPC debugging (skgxp)
ORA-10845: Enable Director tracing
ORA-10846: Enable Director Single Node Testing
ORA-10850: Enable time manager tracing
ORA-10851: Allow Drop command to drop queue tables
ORA-10852: Enable tracing for Enqueue Dequeue Operations
ORA-10854: Sets poll count used for AQ listen code under RAC
ORA-10856: Disable AQ propagator from using streaming
ORA-10857: Force AQ propagator to use two-phase commit
ORA-10858: Crash the AQ propagator at different stages of commit
ORA-10859: Disable updates of message retry count
ORA-10860: event for AQ admin disable new name parser
ORA-10861: disable storing extended message properties
ORA-10862: resolve default queue owner to current user in enqueue/dequeue
ORA-10863: Control behavior of buffered background operations
ORA-10864: event to enable AQ dedicated propagation
ORA-10865: Control tracing of notification operations
ORA-10871: dump file open/close timestamp during media recovery
ORA-10872: Flashback Database fault insertion event #.
ORA-10880: trace Java VM execution
ORA-10891: disable column pruning in ANSI join transformation
ORA-10900: extent manager fault insertion event #
ORA-10901: disable the fix for bug 1230798
ORA-10902: disable seghdr conversion for ro operation
ORA-10903: Force tablespaces to become locally managed
ORA-10904: Allow locally managed tablespaces to have user allocation
ORA-10905: Do cache verification (kcbcxx) on extent allocation
ORA-10906: Unable to extend segment after insert direct load
ORA-10907: Trace extent management events
ORA-10908: Trace temp tablespace events
ORA-10909: Trace free list events
ORA-10910: inject corner case events into the RAC buffer cache
ORA-10911: Locally managed SYSTEM tablespace bitmaps can be modified only under the supervision
ORA-10912: Used to perform admin operations on locally managed SYSTEM tablespace
ORA-10913: Create locally managed database if compatible > 920 by default
ORA-10914: invalid TABLESPACE GROUP clause
ORA-10915: TABLESPACE GROUP cannot be specified for this type of tablespace
ORA-10916: TABLESPACE GROUP already specified
ORA-10917: TABLESPACE GROUP cannot be specified
ORA-10918: TABLESPACE GROUP name cannot be the same as tablespace name
ORA-10919: Default temporary tablespace group must have at least one tablespace
ORA-10920: Cannot offline tablespace belonging to default temporary tablespace group
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group
ORA-10922: Temporary tablespace group is empty
ORA-10923: prints debug information for object space server manageability
ORA-10924: import storage parse error ignore event
ORA-10925: trace name context forever
ORA-10926: trace name context forever
ORA-10927: trace name context forever
ORA-10928: trace name context forever
ORA-10929: trace name context forever
ORA-10930: trace name context forever
ORA-10931: trace name context forever
ORA-10932: trace name context forever
ORA-10933: trace name context forever
ORA-10936: trace name context forever
ORA-10938: trace name context forever
ORA-10939: trace name context forever
ORA-10940: trace name context forever
ORA-10941: trace name context forever
ORA-10943: trace name context forever
ORA-10944: trace name context forever
ORA-10945: trace name context forever
ORA-10946: trace name context forever
ORA-10947: trace name context forever
ORA-10948: trace name context forever
ORA-10960: AQ tracing event
ORA-10970: backout event for bug 2133357
ORA-10971: prints debugging information for LOBs
ORA-10972: raise a 1551 exception in kdu_array_flush
ORA-10973: backout evet for 2619509
ORA-10974: Turn on LOB integrity verification
ORA-10975: trace execution of parallel propagation
ORA-10976: internal package related tracing
ORA-10977: trace event for RepAPI
ORA-10979: trace flags for join index implementation
ORA-10980: prevent sharing of parsed query during Materialized View query generation
ORA-10981: dscn computation-related event in replication
ORA-10982: event to turn off CDC-format MV Logs
ORA-10983: event to enable Create_Change_Table debugging
ORA-10984: subquery materialized view-related event
ORA-10985: event for NULL refresh of materialized views
ORA-10986: donot use HASH_AJ in refresh
ORA-10987: event for the support of caching table with object feature
ORA-10988: event to get exclusive lock during materialized view refresh in IAS
ORA-10989: event to internally create statistics MV
ORA-10990: dump spreadsheet info
ORA-10991: event for optimizing the online redefinition instantiation
ORA-10992: event to enable dbms_job instead of dbms_scheduler
ORA-10995: general event for materialized views
ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-10998: event to enable short stack dumps in system state dumps
ORA-10999: do not get database enqueue name

$ORACLE_HOME/rdbms/mesg/oraus.msg

http://www.eygle.com/internal/Oracle.Diagnostics.Events.list.htm

Oracle 10g Scheduler

Filed under: Oracle DBA — jennyca @ 5:36 am

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it’s only provided for backward compatibility.

Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the ‘CREATE JOB’ privilege.

If you want to user resource plans and/or consumer groups you need to set a system parameter:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

You can build a schedule using components like program, schedule, job, job class and window.

A schedule specifies when and how many times a job is executed. Similar to programs, schedules are database entities and can be saved in the database. The same schedule can be used by multiple jobs.
A program is a collection of metadata about what will be run by the scheduler. This includes information such as the program name, the type of program, and information about arguments passed to the program.
A job specifies what needs to executed and when. For example, the “what” could be a PL/SQL procedure, an executable C program, a java application, a shell script, or client-side PL/SQL. You can specify the program (what) and schedule (when) as part of the job definition, or you can use an existing program or schedule instead.

Getting started quickly

To quickly get a job running, you can use code like this:

begin
  dbms_scheduler.create_job(
      job_name => 'DEMO_JOB_SCHEDULE'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin package.procedure(''param_value''); end; '
     ,start_date => '01/01/2006 02:00 AM'
     ,repeat_interval => 'FREQ=DAILY'
     ,enabled => TRUE
     ,comments => 'Demo for job schedule.');
end;
/

Program

The program component represents program-code that can be executed. This program code can have parameters. Code example

begin
   dbms_scheduler.create_program (
       program_name => 'DEMO_JOB_SCHEDULE'
      ,program_type => 'STORED_PROCEDURE'
      ,program_action => 'package.procedure'
      ,number_of_arguments => 1
      ,enabled => FALSE
      ,comments => 'Demo for job schedule.');

   dbms_scheduler.define_program_argument (
       program_name => 'DEMO_JOB_SCHEDULE'
      ,argument_position => 1
      ,argument_name => 'kol1'
      ,argument_type => 'VARCHAR2'
      ,default_value => 'default'
    );
    dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');
end;
/

The parameter program_type can have one of the following values: ‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’,'EXECUTABLE’.
dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables.

Schedule

A schedule defines the frequence and date/time specifics of the start-time for the job.
example code

begin
  dbms_scheduler.create_schedule(
      schedule_name => 'DEMO_SCHEDULE'
    , start_date =>  '01/01/2006 22:00:00'
    , repeat_interval => 'FREQ=WEEKLY'
    , comments => 'Weekly at 22:00');
END;
/

To drop the schedule:
begin
  dbms_scheduler.drop_schedule(
     schedule_name => 'DEMO_SCHEDULE'
     , force => TRUE );
end;
/

Calendar expresions can have one of these values:

FREQ=[YEARLY | MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY] ;

Job

A job defines when a specific task will be started. This can be done by assigning a program to one or more schedules (or to a specific date/time). A job can belong to only 1 job class. Code example

begin
  dbms_scheduler.create_job(
       job_name => 'DEMO_JOB1'
     , program_name =>'DEMO_JOB_SCHEDULE'
     , schedule_name =>'DEMO_SCHEDULE'
     , enabled => FALSE
     , comments => 'Run demo program every week at 22:00');

   dbms_scheduler.set_job_argument_value(
        job_name => 'DEMO_JOB1'
      , argument_position => 1
      , argument_value => 'param1');

   dbms_scheduler.enable('DEMO_JOB1');

   commit;
end;
/

Or start shell script

begin
   dbms_scheduler.create_job
   (
      job_name      => 'RUN_SHELL1',
      schedule_name => 'DEMO_SCHEDULE',
      job_type      => 'EXECUTABLE',
      job_action    => '/home/test/run_script.sh',
      enabled       => true,
      comments      => 'Run shell-script'
   );
end;
/

job_type job_action

PLSQL_BLOCK Entire PL/SQL code

STORED_PROCEDURE Stored procedure name
EXECUTABLE Shell script or OS command

Monitoring job-scheduling

dba_scheduler_job_run_details

dba_scheduler_running_jobs

dba_scheduler_job_log

dba_scheduler_schedules

dba_scheduler_jobs

dba_scheduler_programs

dba_scheduler_program_args

Estimate job running time

SQL> set serveroutput on size 999999 SQL> declare
  2  L_start_date TIMESTAMP;
  3  l_next_date TIMESTAMP;
  4  l_return_date TIMESTAMP;
  5  begin
  6  l_start_date := trunc(SYSTIMESTAMP);
  7  l_return_date := l_start_date;
  8  for ctr in 1..10 loop
  9  dbms_scheduler.evaluate_calendar_string(
 10  'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',
 11  l_start_date, l_return_date, l_next_date
 12  );
 13  dbms_output.put_line('Next Run on: ' ||
 14  to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
 15  );
 16  l_return_date := l_next_date;
 17  end loop;
 18* end;
SQL> /
Next Run on: 04/01/2008 07:00:00
Next Run on: 04/01/2008 15:00:00
Next Run on: 04/02/2008 07:00:00
Next Run on: 04/02/2008 15:00:00
Next Run on: 04/03/2008 07:00:00
Next Run on: 04/03/2008 15:00:00
Next Run on: 04/04/2008 07:00:00
Next Run on: 04/04/2008 15:00:00
Next Run on: 04/07/2008 07:00:00
Next Run on: 04/07/2008 15:00:00

PL/SQL procedure successfully completed.

One of the best new features in BMS_SCHEDULER is its tight integration with Resource Manager.

Resource Manager is the part of Oracle that enables you to divide up resources, such as CPU time, among the various sessions logged on to Oracle. Sessions can be assigned to a Consumer Resource Group, and a Resource Plan divides available resources among Consumer Resource Groups.

Two DBMS_SCHEDULER features are used to integrate with Resource Manager: job classes and scheduler windows.

Job classes

A job class is a category of scheduler job. A job class can have multiple jobs assigned to it, but each job can be associated with only one job class at a time. One of the attributes of a job class is that it can include the name of a Consumer Resource Group to associate with all jobs in the class. This means that jobs in the job class get the resources given to the specified Consumer Resource Group but no more.

Scheduler windows


Scheduler windows are pre-defined time periods that have a name, a start time, and a duration. A scheduler window “opens” when its start time arrives and “closes” after its duration is reached. Scheduler windows have a Resource Plan as an attribute: When the window opens, Oracle switches the current Resource Plan automatically to start using the one specified in the window.

http://articles.techrepublic.com.com/5100-9592-6174617.html

Generic Issues Applicable to UNIX and Windows

- The job action (script or executable) must return 0 or the job run will be
marked as failed.
- Always use the full pathname to executables and scripts.
- Do not count on environment variables being set in your job. Make sure that
the script or executable that your jobs runs sets all required environment
variables including ORACLE_HOME, ORACLE_SID, PATH etc.
- It is not recommended to pass in a complete command line including arguments
as the action. Instead it is recommended to pass in only the path to and
name of the executable and to pass in arguments as job argument values.
- Scripts with special characters in the execution path or script name may
give problems.
- Ensure that the OS user your job runs as has the required
privileges/permissions to run your job.
- External job actions cannot contain redirection operators e.g. > < >> | && ||
- In general try getting a simple external job working first e.g. /bin/echo or
ipconfig.exe on Windows. Also try running the job action directly from the
commandline as the OS user that the job will run as.

Windows-specific Issues

- The OracleJobScheduler Windows service must be started before external jobs
will run (except for jobs in the SYS schema and jobs with credentials).
- The user that the OracleJobScheduler Windows service runs as must have the
“Log on as batch job” Windows privilege.
- A batch file (ending in .bat) cannot be called directly by the Scheduler.
Instead cmd.exe must be used and the name of the batch file passed in as an
argument.
For example,
begin
dbms_scheduler.create_job(‘myjob’,
job_action=>’C:\WINDOWS\SYSTEM32\CMD.EXE’,
number_of_arguments=>3,
job_type=>’executable’, enabled=>false);
dbms_scheduler.set_job_argument_value(‘myjob’,1,’/q’);
dbms_scheduler.set_job_argument_value(‘myjob’,2,’/c’);
dbms_scheduler.set_job_argument_value(‘myjob’,3,’c:\temp\test.bat’);
dbms_scheduler.enable(‘myjob’);
end;
/

If a job fails, the first place to look for diagnostic information is the
*_SCHEDULER_JOB_RUN_DETAILS set of views

Details see

http://www.oracle-base.com/articles/10g/Scheduler10g.php

http://forums.oracle.com/forums/thread.jspa?messageID=2065003

Oracle alter table

Filed under: Schema Objects mgt — jennyca @ 5:19 am

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

 

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
 RENAME TO vendors;

This will rename the suppliers table to vendors.

 

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 ADD column_name column-definition;

For example:

ALTER TABLE supplier
 ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

 

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD ( column_1 column-definition,
  column_2 column-definition,
   
  column_n column_definition );

For example:

ALTER TABLE supplier
ADD ( supplier_name varchar2(50),
  city varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

 

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 MODIFY column_name column_type;

For example:

ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

 

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY ( column_1 column_type,
  column_2 column_type,
   
  column_n column_type );

For example:

ALTER TABLE supplier
MODIFY ( supplier_name varchar2(100) not null,
  city varchar2(75)   );

This will modify both the supplier_name and city columns.

 

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 DROP COLUMN column_name;

For example:

ALTER TABLE supplier
 DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

 

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1

Starting in Oracle 9i Release 2, you can now rename a column.

To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
 RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

http://www.techonthenet.com/sql/tables/alter_table.php

Using utl_file, how file permissions are determined, working sample

Filed under: Oracle DBA — jennyca @ 5:15 am

Note:74268.1

PURPOSE
=======

1. Step-by-step instructions for using the UTL_FILE package.
2. Explanation on how output file permissions are set for output files.
3. Working example.

SCOPE & APPLICATION
===================

Support analysts and customers, detail=medium

IMPLEMENTATION
==============

To implement UTL_FILE, make the following changes to the init<SID>.ora file:

Add following to init<SID>.ora file:

   UTL_FILE_DIR = dir, dir1, dir2
or
   UTL_FILE_DIR = /tmp, /usr/od3000/admin

Verify that you do not have a "/" at the end of the directory path in
either the init<SID>.ora file or your code.  Also, confirm that paths
match exactly, unless the wildcard (*) is being used.

To maintain file security, all valid directories must be explicitly
mentioned in the init<SID>.ora file.   The directories that UTL_FILE
can read from and write to need to have permissions of the Oracle instance
owner and the user running the package.

The easiest way to do this is to do a "chmod 777 <directory name>" at the
unix prompt (omit the double quotes) which will give all users read/write
permissions to the directory).

UTL_FILE creates files with default permissions of rw-r--r--.  These can be
overriden by the Oracle instance owner's Unix umask.  For example, if the Oracle
instance owner's umask is 077, files created by UTL_FILE will be rw-------.
This happens because files created in Unix are created with the process
owner's umask.

Default permissions are  rw-r--r--,  which can be overridden by the
following factors:

1) The Oracle instance owner's Unix umask since Oracle is the owner of all the
   server processes.  It is the server process that writes the UTL_FILE
   output file.

2) For network connections, permissions are set by umask of the user that starts
   the sqlnet listener.  The Listener spawns a server process which inherits the
   listener's userid.

3) On HP, BEQ server processes are owned by the Unix user that connects to the
   database. Therefore, files are created according to the umask of the Unix
   user, which may not be the Oracle instance owner.
   (This was observed on HPUX 10.20 and 11.0)

Using the UTL_FILE package
==========================

First, create a table in the database for use with the UTL_FILE package.

create table testtab (c1 number, c2 number);
insert into testtab values (10,25);
insert into testtab values (20,50);
commit;

Sample UTL_FILE procedure - file output to /tmp/myfile.txt

SQL> CREATE OR REPLACE PROCEDURE test1 IS

           file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
           col1  NUMBER;                 -- C1 retrieved from testtab table
           retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file
         BEGIN
           -- Open file to write into  and get it's file_handle
           file_handle :=
             UTL_FILE.FOPEN('/tmp','myfile.txt','W');

           -- Write a line of text out to the file.
           UTL_FILE.PUT_LINE(file_handle, 'this is line 1 as a test');

           -- Select the c1 from the testtab table where empno = 7900
           SELECT c1 INTO col1 FROM testtab
              WHERE c2 = 25;

           -- Using PUTF write text with the col1 argument out to the file.
           UTL_FILE.PUTF (file_handle,
              'This is the c1 %s when the c2 is %s.\n',
              col1,'25');

           -- Close the file.
           UTL_FILE.FCLOSE(file_handle);

           -- Open the same file to read from
           file_handle :=
              UTL_FILE.FOPEN('/tmp','myfile.txt','R');

           -- Read a line from the file.
           UTL_FILE.GET_LINE (file_handle, retrieved_buffer);

           -- Print fetched line out to the SQL*PLUS prompt.
           DBMS_OUTPUT.PUT_LINE(retrieved_buffer);

           -- CLose the file.
           UTL_FILE.FCLOSE(file_handle);
        EXCEPTION

           WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE('no_data_found');
              UTL_FILE.FCLOSE(file_handle);
           WHEN UTL_FILE.INVALID_PATH THEN
              DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
              UTL_FILE.FCLOSE(file_handle);
           WHEN UTL_FILE.READ_ERROR THEN
              DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
              UTL_FILE.FCLOSE(file_handle);
           WHEN UTL_FILE.WRITE_ERROR THEN
              DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
              UTL_FILE.FCLOSE(file_handle);
           WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('other stuff');
              UTL_FILE.FCLOSE(file_handle);
         END;
/

Procedure created.

SQL> set serveroutput on
SQL> execute test1
this is line 1 as a test

PL/SQL procedure successfully completed.

Check the contents of the output file.

SQL> !more /tmp/myfile.txt
this is line 1 as a test
This is the c1 10 when the c2 is 25.

Schedule using Windows ‘At’ command

Filed under: Windows — jennyca @ 4:48 am

The ‘At’ command in Windows allows you to schedule any program or script to run at a specified time. You can schedule the program to run on specified days of the week at the specified time. This works with the Scheduling service and hence the Schedule service must be running to use the ‘At’ command.

Setting Up At command

  • Open Service panel and ensure ‘Task Scheduler’ service is running. If not, start the ‘Task Scheduler’ and proceed with the below mentioned steps.
  • Select Start > Run and enter cmd in the dialog box.
  • Enter parameters to schedule a program. In the below mentioned example, running the Disk Clean utility every day of the week (Mon thru Fri at 11:45 A.M) is shown. Enter the following at the command prompt: at 11:45 /interactive /every:M,T,W,Th,F “cleanmgr.exe”
C:\Documents and Settings>at 11:45 /interactive /every:M,T,W,Th,F "cleanmgr.exe"
  • In the above box, 11:45 represents the time program is run, interactive allows the job to interact with the user, every:date runs the job on specified day(s). If this parameter is omitted, the job is run at the current day of the month.
  • Enter at /? at the command prompt to get a list of arguments for the ‘At’ command
C:\Documents and Settings>at /?The AT command schedules commands and programs
to run on a computer ata specified time and date. The Schedule service must be
running to usethe AT command.

AT [\\computername] [ [id] [/DELETE] | /DELETE [/YES]]
AT [\\computername] time [/INTERACTIVE]
[ /EVERY:date[,...] | /NEXT:date[,...]] "command"
\\computername     Specifies a remote computer. Commands are scheduled on the
local computer if this parameter is omitted.
id                 Is an identification number assigned to a scheduled
command.
/delete            Cancels a scheduled command. If id is omitted, all the
scheduled commands on the computer are canceled.
/yes               Used with cancel all jobs command when no further
confirmation is desired.
time               Specifies the time when command is to run.
/interactive       Allows the job to interact with the desktop of the user
who is logged on at the time the job runs.
/every:date[,...]  Runs the command on each specified day(s) of the week or
month. If date is omitted, the current day of the month
is assumed.
/next:date[,...]   Runs the specified command on the next occurrence of the
day (for example, next Thursday).  If date is omitted, the
current day of the month is assumed.

"command"          Is the Windows NT command, or batch program to be run.

View Scheduled Programs

Enter at to view the scheduled programs as shown below.

C:\Documents and Settings>at

Status ID   Day             Time          Command Line
---------------------------------------------------------------------------------
1   Each M T W Th F         11:15 AM      notepad.exe
2   Each M T W Th F         11:45 AM      cleanmgr.exe

Deleting Schedule

Enter <id> /delete to delete schedule relating to the specified id. If id is omitted, it deletes all the scheduled programs.

C:\Documents and Settings>at 1 /delete

http://www.referpages.com/reference/computing/35-windows/99-at.html

Question: What is the equivalent from a cron in Windows? I want something like a crontab to schedule Oracle DBA jobs. What options do I have for job scheduling in Windows?

Answer: Within Oracle, you can use the dbms_job and dbms_scheduler packages.

In Windows, you can invoke SQL*Plus in a bat file. The trick is to put the whole sqlplus command on a single line:

c:> type run_oracle.bat

@echo OFF
set MYDIR=C:\oracle\scripts
sqlplus -s fred/flintstone @%MYDIR%\rpt_dba.sql
exit

You can also use the Windows “AT” command to schedule Oracle Windows jobs. The Windows Scheduler can be programmed to run Oracle Windows batch scripts either from the Windows Control Panel interface or via command line execution using the AT command.

The Windows Scheduler needs to know a few basic facts in order to run an Oracle job:

  • Time of day to run (military HH:MM) the Windows job
  • Day to run the Windows job … either the day of week (M,T,W,Th,F,S,Su) or day of month (1-31) with multiple values separated by commas
  • What Windows job to run … in our case the CMD processor followed by the batch command to be executed
  •   @echo off
    
    REM +-----------------------------------------------
    REM | Set up client specific variables
    REM +-----------------------------------------------
    
    set BC_DIR=C:\BC
    
    REM +-----------------------------------------------
    REM | Schedule BAT jobs to run at appropriate
    REM | times.
    REM |                 ** Caution **
    REM |  The following /delete switch clears all
    REM |  jobs from the Windows Scheduler
    REM +-----------------------------------------------
    
    AT /delete /yes
    
    REM +-----------------------------------------------
    REM | Schedule Monday Table & Index Reports
    REM +-----------------------------------------------
    
    AT  6:30 /every:M  cmd /c %BC_DIR%\script\get_obj_stats.bat
    AT  7:30 /every:M  cmd /c %BC_DIR%\script\weekly_rpt_dba.bat
    AT  7:40 /every:M  cmd /c %BC_DIR%\script\weekly_rpt_mgt.bat
    
    REM +-----------------------------------------------
    REM |  A daily STATSPACK alert
    REM +-----------------------------------------------
    
    AT  7:00 /every:M,T,W,Th,F,S,Su cmd /c %BC_DIR%\script\statspack_alert_9i.bat
    
    REM +-----------------------------------------------
    REM | Disk Free Space Alert every 6 hours
    REM +-----------------------------------------------
    
    AT  6:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
    AT 12:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
    AT 18:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
    AT 00:30 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\disk_space.bat
    
    REM +-----------------------------------------------
    REM | Schedule a Monday STATSPACK snapshot removal
    REM +-----------------------------------------------
    
    AT  7:45 /every:M  cmd /c %BC_DIR%\script\sprem.bat
    
    REM +-----------------------------------------------
    REM | Schedule a periodic Trace File alert
    REM +-----------------------------------------------
    
    AT  0:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
    AT  6:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
    AT 12:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
    AT 18:00 /every:M,T,W,Th,F,S,Su  cmd /c %BC_DIR%\script\trace_alert.bat
    
    REM +-----------------------------------------------
    REM | Schedule a daily Alert Log check
    REM +-----------------------------------------------
    
    AT  7:15 /every:M,T,W,Th,F,S,Su cmd /c %BC_DIR%\script\alert_log.bat

    http://www.dba-oracle.com/t_windows_job_scheduling.htm

    March 25, 2008

    How to Have a Happy Life

    Filed under: English — jennyca @ 5:44 pm

    By Carol Giannantonio

    Happiness is a choice you make and you are the only person who can make that choice.However, in order to be happy, you must feel that you DESERVE to be happy.

    Feeling undeserving of happiness is a common roadblock that shuts the door on happiness for many people. Remember, you have the right to be happy and you deserve happiness. Once you believe this. The choice is yours.

    Are your ready to open the door and catch some happiness? Here are 12 steps to steps to get you started.

    1. Create a blueprint for a life you love.
    Identify what you want from life-set goals, develop a plan and take ACTION! Monitor your actions, measure your success and celebrate!

    2. Practice Awareness, Acceptance, Action
    Be Aware of what you don’t like. Accept it. Take Action to change it.

    3. Give up on being perfect.
    Be proud of who you are. Live your values. No one is perfect.

    4. Stay out of the drama.
    Life is for living. Drama is for TV. Stay out of the drama- focus on solutions to resolve problems.

    5. Balance your life.
    Balance work and life. Make time to play.

    6. Develop your skills and talents.
    Invest in yourself personally & professionally. Be all your can be.

    7. Let go of anger and negativity.
    Free yourself from “empty” emotions. They drain you. They hurt you more than those you feel them towards. Fuel yourself with positive energy. The world will look a whole lot nicer.

    8. Take care of you.
    You deserve the best. Take care of YOU!

    9. Receive and reduce expectations.
    Allow yourself to receive. Don’t expect or make assumptions.

    10. Replace need with fulfillment.
    Practice self reliance. Only you can meet your emotional needs. Reconnect with your soul and find fulfillment.

    11. Use your energy wisely.
    Identify your energy zones. They are sources of motivation leading to action and success.

    12. Live your dreams.
    Take risks to follow your dreams. Dreams come true are happy moments.

    Remember-we all deserve to be happy. Happiness is a process that requires change for many of us and change is never easy since it takes us out of our comfort zone.

    However with committment and perserverance you will be catching more happiness each day. Your world will be a much nicer place to live.

    http://www.success.bz/articles/1901/how_to_have_a_happy_life

    March 24, 2008

    让Mplayer成为Linux系统下的万能播放器

    Filed under: UNIX/LINUX — jennyca @ 12:58 am

    1.下载相关codes ( http://www.mplayerhq.hu/MPlayer/ ) 主要都在这里

    主程序 :http://www.mplayerhq.hu/MPlayer/releases/MPlayer-1.0rc2.tar.bz2
    皮肤:http://www.mplayerhq.hu/MPlayer/skins/Abyss-1.6.tar.bz2
    all-20071007.tar.bz2:http://www.mplayerhq.hu/MPlayer/releases/codecs/all-20071007.tar.bz2
    windows-all-20071007.zip:http://www.mplayerhq.hu/MPlayer/releases/codecs/windows-all-20071007.zip
    皮肤可以自己选择啦

    2 安装过程(打开终端)
    a 安装相应的编码
    进入主目录,以root身份安装.
    mkdir /usr/lib/codecs
    mkdir /usr/lib/wincodecs
    tar jvxf all-20071007.tar.bz2
    mv all-20071007/* /usr/lib/codecs
    unzip windows-all-20071007.zip
    mv windows-all-20071007/* /usr/lib/wincodecs
    b 安装mplayer
    tar jxvf MPlayer-1.0rc2.tar.bz2
    cd MPlayer-1.0rc2
    ./configure –prefix=/usr/local/mplayer –enable-gui –enable-freetype –codecsdir=/usr/lib/codecs/ -win32codecsdir=/usr/lib/wincodecs/ –language=zh_CN
    注意:
    –prefix=/usr/local/mplayer 是安装路径
    –enable-gui 安装图形化用户界面
    –enable-freetype 调节字体
    –with-codecsdir=/usr/lib/codecs/
    –with-win32libdir=/usr/lib/wincodecs 指定解码位置
    –language=zh_CN 中文
    接着make   make install
    c.安装皮肤
    tar jxvf Abyss-1.6.tar.bz2
    mv Abyss /usr/local/mplayer/share/mplayer/skins/
    cd /usr/local/mplayer/share/mplayer/skins/
    mv Abyss default

    你一定会发现我们还没有装字体,运行mplayer后它也会提示没有字体
    你就从自己电脑中拷个字体到主目录下的.mplayer文件夹下,并把你的字体改名为subfont.ttf,问题就解决了,或者做个链接也行,只要你喜欢
    字体你可以上网下载,mplayer网站也有,你电脑里也有字体,在 /usr/share/fonts/chinese下(/usr/share/fonts/chinese/TrueType/uming.ttf就可以用)
    你就直接cp /usr/share/fonts/chinese/TrueType/uming.ttf $HOME/.mplayer下
    每个用户都有自己的环境变量HOME,再cd $HOME/.mplayer 接着

    mv umingsh.ttf subfont.ttf 结束!

    http://blog.chinaunix.net/u1/43090/showart_491050.html

    Check system information of UNIX/LINUX

    Filed under: UNIX/LINUX — jennyca @ 12:55 am

    RHEL

    Check CPU: cat /proc/cpuinfo

    Check memory: cat /proc/meminfo

    Check hard disk partition: df -k

    Check HD:

    IDE/ATA: hdparm -i /dev/xxx

    SCSI: cat /proc/scsi/scsi

    dmesg,lspci

    UNIX (Solaris)

    Check CPU: psrinfo -v and /usr/platform/sun4u/sbin/prtdiag –v

    Check memory: /usr/platform/sun4u/sbin/prtdiag –v and prtconf -v

    prtconf | grep ‘Memory’

    Check hard disk partition: df -k

    Check HD: iostat -E

    Next Page »

    Blog at WordPress.com.