Solved

catproc.sql and chained rows

Posted on 2002-05-04
7
1,096 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
Industry Leaders: 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

724 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