| Ref :"Understanding Indexes" from Home Page Link [message #221137] |
Sat, 24 February 2007 05:37  |
josh_snehal
Messages: 15
Registered: May 2006
Location: BANGALORE
|
Junior Member |
|
|
Hello ALL,
I was trying to simulate the commands given in the article at home page regarding Understanding Indexes by Mr Rleishman.
http://www.orafaq.com/node/1403
I find the material quite helpful in understanding fundamentals of indexes.Thanks a lot to Mr Rleishman for the same.
But while I was trying to practice myself of the commands on my PC in Scott.emp table using SQL*Plus, I was not able to be successful in executing commands.Here is the sequence of commands I followed as per suggested and the errors I received.
SQL> ACCEPT index_name PROMPT "Index Name:"
Index Name:PK_EMP
SQL>
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name';
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PK_EMP'
Session altered.
SQL>
SQL> COLUMN object_id NEW_VALUE object_id
SQL>
SQL> SELECT object_id
2 FROM user_objects
3 WHERE object_type='INDEX'
4 AND object_name=upper('&index_name');
old 4: AND object_name=upper('&index_name')
new 4: AND object_name=upper('PK_EMP')
OBJECT_ID
----------
30140
SQL>
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id';
old 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id'
new 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 30140'
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER=" "
2 ;
Session altered.
SQL> SHOW PARAMETER user_dump_test
ORA-00942: table or view does not exist
SQL>
I am not very clear from the sequence above what I am trying to accomplish , I believe if done properly above sequence will prepare a trace file.
Can someone plz explain what I am doing wrong here, or what is stopping here from getting desirable output.
Why I am getting Numeric Error Flow & Ora-00942.
Also it will be good if someone can explain or point to links/docs which explain the stuff what's going here.
It was requested not to post questions or comments on Blog but on to Forums,so I am posting the question here.
Sincere thanks to all of you to take a look at this message.
Thanks & Cheers,
[Updated on: Sat, 24 February 2007 05:41] Report message to a moderator |
|
|
|
|
|
| Re: Ref :"Understanding Indexes" from Home Page Link [message #221183 is a reply to message #221154] |
Sat, 24 February 2007 20:44  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Well, I can help you with the last one: the DBA has not granted you SELECT privs on the V$_PARAMETER view - SHOW PARAMETER requires that privilege. This is clearly documented in the SQL*Plus manual.
As for the TREEDUMP problem, I don't know what's causing that, sorry. It is failing on a recursive SQL (ie. a background SQL that Oracle runs). If you were to start a SQL Trace, you would be able to find which SQL - you might not be able to fix it though. I doubt you'd get much help from Oracle either - I don't know whether that is a supported command.
Look in the Performance Tuning manual for instructions on starting SQL Trace, and using TK*Prof to view the trace file.
Ross Leishman
|
|
|
|
| Re: Ref :"Understanding Indexes" from Home Page Link [message #221201 is a reply to message #221154] |
Sun, 25 February 2007 05:43 |
josh_snehal
Messages: 15
Registered: May 2006
Location: BANGALORE
|
Junior Member |
|
|
Thanks for your concerns.
I am using
OS : Microsoft Windows 2000 Service Pack 4
Database :Oracle 9i Enterprise Edition Release 9.2.0.1.0
Can you plz point me towards links/online docs where I can understand what's going in the commands which I followed as shown in previous post.
Thanks..
|
|
|
|
|
|
| Re: Ref :"Understanding Indexes" from Home Page Link [message #221856 is a reply to message #221137] |
Wed, 28 February 2007 12:54 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Not that I can help with the error you are receiving, but there really should be no reason for using 9.2.0.1. Ever.
Not only is it quite old, but it had numerous bugs which have since been corrected.
If you are using 9iR2 in production, then you should patch yourself to 9.2.0.8 or whatever the current release is.
If you are just using this as a test/sandbox database to work on, then download and use a 10g version.
|
|
|
|
| Re: Ref :"Understanding Indexes" from Home Page Link [message #221859 is a reply to message #221137] |
Wed, 28 February 2007 13:07 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
For what it is worth, when I am in:
MYDBA@orcl > select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
And I run:
create table emp2 as select * from emp;
alter table emp2 add constraint emp_pk primary key(empno);
ACCEPT index_name PROMPT "Index Name:"
ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name';
COLUMN object_id NEW_VALUE object_id
SELECT object_id FROM user_objects
WHERE object_type='INDEX' AND object_name=upper('&index_name');
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id';
drop table emp2;
My result is:
MYDBA@orcl > start index_trace;
Table created.
Table altered.
Index Name:emp_pk
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='emp_pk'
Session altered.
old 2: WHERE object_type='INDEX' AND object_name=upper('&index_name')
new 2: WHERE object_type='INDEX' AND object_name=upper('emp_pk')
OBJECT_ID
----------
54921
old 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id'
new 1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 54921'
Session altered.
Table dropped.
And I get a file called "orcl_ora_540_emp_pk.trc":
*** TRACE DUMP CONTINUED FROM FILE ***
Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_540_emp_pk.trc
Wed Feb 28 14:00:38 2007
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:287M/1014M, Ph+PgF:793M/1429M, VA:1672M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Windows thread id: 540, image: ORACLE.EXE (SHAD)
*** 2007-02-28 14:00:38.946
*** ACTION NAME:() 2007-02-28 14:00:38.946
*** MODULE NAME:(SQL*Plus) 2007-02-28 14:00:38.946
*** SERVICE NAME:(SYS$USERS) 2007-02-28 14:00:38.946
*** SESSION ID:(143.1492) 2007-02-28 14:00:38.946
----- begin tree dump
leaf: 0x1000234 16777780 (0: nrow: 14 rrow: 14)
----- end tree dump
Now I'm off to read the article you referenced that Ross wrote, which I'm sure (based on his forum responses I've seen) is quite good.
[Updated on: Wed, 28 February 2007 13:10] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
