Solved

catproc.sql and chained rows

Posted on 2002-05-04
7
1,094 Views
Last Modified: 2012-06-21
1) I am running oracle 8.1.6 enterprise edition on windows NT.

2) The DBMS_UTILITY package, together with all other built-in packages are created when you run the catproc.sql script on a new database. This is from a book titled 'Oracle8i DBA Bible' by Jonathan Gennick etc.

3) My database is relatively new and I have never ran this script but:
exec dbms_utility.analyze_schema('OFFSHORE','ESTIMATE',NULL,20,'FOR ALL INDEXED COLUMNS');
PL/SQL procedure successfully completed.

4) When I ran the script from my DBA user system which is what I should do I get a number of errors:

grant select on ALL_DEPENDENCIES to public with grant option
                *
ERROR at line 1:
ORA-00942: table or view does not exist

comment on column DBA_DEPENDENCIES.REFERENCED_OWNER is
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

select d.d_obj#, d.p_obj# from dependency$ d
                               *
ERROR at line 4:
ORA-00942: table or view does not exist

grant execute on sys.plitblm to public
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

5) The catproc.sql script itself runs many other scripts. Do I have to run some really other scripts not in catproc.sql and if so which and in what order.

6) Will not my already running catproc.sql with errors, package and package bodies being created affect the already working packages.

7) The second part of my question is I have analyzed in step 3 but when I run query connected to OFFSHORE schema,

select table_name,chain_cnt from user_tables order by chain_cnt desc;

I get no values i.e. blank under chain_cnt column. One table in my schema is having 80000 records and rest average 25 records with 450 tables in all with 200 being indexed especially the big table on 4 columns. Is this not enough to create chained rows.

--- k_murli_krishna


0
Comment
Question by:k_murli_krishna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:pennnn
ID: 6990029
I think you should be happy not to have chained rows. I seems to me that you have the wrong idea about what chained rows are. Here's the description from the Oracle documentation:
"If a row is too large to fit into one data block when it is first inserted, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. When a row is chained, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row."
Hope that helps!
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 6990379
pennnn:

You have not covered the catproc.sql part.

--- k_murli_krishna
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6990758
What user were you logged in as when you ran the catproc.sql script?  You need to be logged in as SYS or INTERNAL. If you ran it as another user, you should also drop the objects that the script created in that user's schema.


Andrew
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:asimkovsky
ID: 6990759
What user were you logged in as when you ran the catproc.sql script?  You need to be logged in as SYS or INTERNAL. If you ran it as another user, you should also drop the objects that the script created in that user's schema.


Andrew
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 6991431
pennn:

Can you highlight on migrated rows which got left out. How they come about, affect performance and how to prevent/cure them.

asimovsky:

Run catproc.sql in SYS/INTERNAL if the database is new else I will not get access or able to use PL/SQL packages. But when the DB was new, then itself some PL/SQL packages working. Can you tell me which all packages are enabled only when catproc.sql is run in SYS/INTERNAL.

--- k_murli_krishna
0
 
LVL 11

Accepted Solution

by:
pennnn earned 125 total points
ID: 6991556
I think the answers to the first two questions about chained rows are in my previous note - they are created if a row is too big to fit in one data block. They have negative impact on the performance, because Oracle needs to scan more than one data block to retrieve the information for the row.
Here's a link to the documentation about how to eliminate migrated or chained rows:
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90117/general.htm#18477
And here's another excerpt from that same documentation:
"You can eliminate chained rows only by increasing your data block size. It might not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns."
Hope that helps!
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 6998933
asimovsky: sorry, i had run catproc.sql and now when I ran in SYS, it was smooth. But why were not all packages included by default on oracle install and why create later.
Are all that are created like this totally new ones or upgradations also there.

--- k_murli_krishna
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question