Solved

catproc.sql and chained rows

Posted on 2002-05-04
7
1,087 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

830 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