Solved

catproc.sql and chained rows

Posted on 2002-05-04
7
1,083 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

776 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