Running pupbld.sql?

[eseabrook2008]

I've been asked (by one of our consultants) to run pupbld.sql as we don't seem to have the PRODUCT_USER_PROFILE table. The question I have, and have not had answered yet, is will this break any currently setup in Oracle? I don't have a test system to run this on and can't afford to have our system go down because of this.

The reason this came up was I am not able to run:

begin
dbms_stats.gather_schema_stats(
ownname => 'Administrator',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true,
degree => 15);
end;

When I log on, I get:

Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM.

I've been advised to run the PUPBLD.SQL and then "modify grants for user"? What am I suppose to modify and for who? If anyone has any answers to my questions, I'd greatly appreciate hearing them!!

 

[SantaMufasa]

Brook,

Here are the contents of Oracle's 'PUPBLD.SQL' script:

-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
-- NAME
--   pupbld.sql
--
-- DESCRIPTION
--   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These
--   tables allow SQL*Plus to disable commands per user.  The tables
--   are used only by SQL*Plus and do not affect other client tools
--   that access the database.  Refer to the SQL*Plus manual for table
--   usage information.
--
--   This script should be run on every database that SQL*Plus connects
--   to, even if the tables are not used to restrict commands.
-- USAGE
--   sqlplus system/<system_password> @pupbld
--
--   Connect as SYSTEM before running this script
-- If PRODUCT_USER_PROFILE exists, use its values and drop it
DROP SYNONYM PRODUCT_USER_PROFILE;
CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS
  SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE,
  DATE_VALUE FROM PRODUCT_USER_PROFILE;
DROP TABLE PRODUCT_USER_PROFILE;
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG);
-- Create SQLPLUS_PRODUCT_PROFILE from scratch
CREATE TABLE SQLPLUS_PRODUCT_PROFILE
(
  PRODUCT        VARCHAR2 (30) NOT NULL,
  USERID         VARCHAR2 (30),
  ATTRIBUTE      VARCHAR2 (240),
  SCOPE          VARCHAR2 (240),
  NUMERIC_VALUE  DECIMAL (15,2),
  CHAR_VALUE     VARCHAR2 (240),
  DATE_VALUE     DATE,
  LONG_VALUE     LONG
);
-- Remove SQL*Plus V3 name for sqlplus_product_profile
DROP TABLE PRODUCT_PROFILE;
-- Create the view PRODUCT_PRIVS and grant access to that
DROP VIEW PRODUCT_PRIVS;
CREATE VIEW PRODUCT_PRIVS AS
  SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,
         NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE
  FROM SQLPLUS_PRODUCT_PROFILE
  WHERE USERID = 'PUBLIC' OR USER LIKE USERID;
GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;
DROP PUBLIC SYNONYM PRODUCT_PROFILE;
CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
DROP SYNONYM PRODUCT_USER_PROFILE;
CREATE SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.SQLPLUS_PRODUCT_PROFILE;
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE;
CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
-- End of pupbld.sql

As you can see from the code, Oracle assures that running the script does not impact any other environment besides SQL*Plus. Further, note that you must run the script as Oracle user, "SYSTEM".

If you would like an excellent explanation and tutorial of the purpose and usage of PUPBLD.sql-related features, click The Product User Profile from Jonathan Gennick's Oracle SQL*Plus: The Definitive Guide, published by O'Reilly.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

Is there some way of finding out why the below doesn't seem to be doing anything?

begin
dbms_stats.gather_schema_stats(
ownname => 'Administrator',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true,
degree => 15);
end;

When I run it, it immediately tells me command completed successfully. I've been advised that it should take about 30 - 45 mins to complete?

 

[SantaMufasa]

The reason that "it immediately tells me command completed successfully." is because it "successfully" tried to gather statistics for a schema named 'Administrator', but since there is no schema named 'Administrator', it instantly completed successfully, without doing anything worthwhile. The schema for which you want to gather statistics is probably named 'ADMINISTRATOR', instead.

Let us know the outcome of changing the "spelling" to 'ADMINISTRATOR'.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

Would I use this to get a list of all the schema's (and spelling?)

select distinct owner from dba_objects

 

[eseabrook2008]

Better yet, schema names are case sensitive?!? Our consultant has been racking his brain trying to figure out why this doesn't work at our end but it does at his end...

 

[SantaMufasa]

schema names are case sensitive?!?

By default, Oracle stores schema names (in its Data Dictionary) in upper-case characters. Oracle, however, allows you to use schema names as object qualifiers in a case-insensitive fashion.


If, however, you refer to any Oracle-named object (e.g., table, column, index, sequence, et cetera) as a literal (i.e., within a quoted string), then you must be aware of case-sensitivities.

For example:

select count(*) from dba_objects where owner = 'dhunt';

  COUNT(*)
----------
         0

select count(*) from dba_objects where owner = 'DHUNT';

  COUNT(*)
----------
       345

(but)

select count(*) from dhunt.s_emp;

  COUNT(*)
----------
        25

Notice in the last query, that I can use a lower-case "dhunt" to qualify the owner of the "s_emp" table, but if I am comparing a string that appears in the Data Dictionary, then I must use the same case that the Data Dictionary uses to store strings.

Is that as clear as mud?

Would I use this to get a list of all the schema's (and spelling?)

select distinct owner from dba_objects;

You could use that query, but that code:[ul][li]...will not print out a schema name that owns no objects (which is a "distinct" <grin> possibility), and,[/li][li]...is very inefficient. It is far more efficient to use this code:

select username from dba_users;

[/li][/ul]Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

Well, I ran that script but substituted uppercase for the "ownname" but it still just comes back as completed, immediately. Is there anything else I can do to see what the problem might be, or do to verify that anything happened at all?

 

[SantaMufasa]

A query that I use to clarify the timeliness of the my tables' statistics is:

select trunc(last_analyzed),count(*)
from user_tables
group by trunc(last_analyzed)

The output gives shows the dates that your tables were last analyzed, grouped by date.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

This is what I get when I run that.

TRUNC(LAS COUNT(*)
03-JUN-08 2534
1

 

[SantaMufasa]

Your results tell us that for all but one of your 2,535 tables, they all had their statistics regathered today. You can find out which table was not part of the statistics gathering with the query:

SELECT table_name FROM user_tables WHERE last_analyzed IS NULL;

I also infer from the results that since the statistics gathered today that your running of "dbms_stats.gather_schema_stats" worked faster than others led you to believe.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

Okay, so the table that didn't have its stats gathered today is the one table that we've been having problems with (AR_OVERDUEBALANCE). We have a report that queries this table but there have been problems with the report recently. Other than doing a SELECT *, is there something else I can to do check the health of the table? Or find out why the stats are not gathered. It seems to me that this table is a temp table and only populated when a sub-query runs. Thanks for all your help!!

 

[SantaMufasa]

It is logical to me that a TEMPORARY table could not/would not have gatherable statistics. You can confirm whether or not a table is TEMPORARY by querying the "TEMPORARY" (Y/N) characteristic of user_tables.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

That's kind of what I thought, but I wanted to see with my own eyes that the table was a temp one. Sure enough, it is....so, in your opinion, would running the first script I posted have "sped" up the db? I'm with the understanding that this should have done something to make our queries much faster. Is there some way to check all the dates that the stats were collected?

 

[SantaMufasa]

Is there some way to check all the dates that the stats were collected?

First of all, my presumption is that when you ran the query, above, against "USER_TABLES", that you were connected to Oracle as user "ADMINISTRATOR". If you were connected as "ADMINISTRATOR", then that query tells you that your statistics are sufficiently up to date (since they were gathered today).


If you are still battling a slow-running query, perhaps you could post the query here and we could evaluate if for performance-improvement opportunities.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[eseabrook2008]

Well, in all honesty I don't think running this has anything to do with our problems. We have a report that runs and for some reason it stopped working for us (not returning the records it should). We sent our DB to our consultant and he said, "Oh, I just ran this script on your DB and it runs 10x's faster and runs correctly at our end". So, he's been getting me to run this script to "fix" our problem. Obviously it's not fixing anything (nor would it, really!). I'll have to track down the actual SQL that the report is generated from...once I find it, I'll post it and see what you all can see that our "consultant" can't! Thanks again!