catproc.sql and chained rows

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


LVL 17
k_murli_krishnaAsked:
Who is Participating?
 
pennnnConnect With a Mentor Commented:
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
 
pennnnCommented:
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
 
k_murli_krishnaAuthor Commented:
pennnn:

You have not covered the catproc.sql part.

--- k_murli_krishna
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
asimkovskyCommented:
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
 
asimkovskyCommented:
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
 
k_murli_krishnaAuthor Commented:
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
 
k_murli_krishnaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.