-
i WANT to use the dbms_stats.gather_schema_stats
..
Is sprd is the schema name ..IS this method correct ..
exec dbms_stats.gather_schema_stats('SPRD', cascade => true);
-
my schema name is sprd ...
-
Code:
DECLARE
errmsg ctl_error_proceso.des_error%type;
BEGIN
execute immediate 'alter session set sort_area_size=10485760';
for i in (select username from dba_users where username in ('LSC'))
LOOP
dbms_stats.gather_schema_stats(OWNNAME => i.username,
ESTIMATE_PERCENT => 20,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 75',
DEGREE => 1,
CASCADE => TRUE);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
errmsg := 'Error during analyze: '||sqlerrm;
insert into ctl_error_proceso values(seq_cod_error_proceso.nextval, sysdate, errmsg);
commit;
END;
/
check the dbms_stats part
-
-
First time use sorry
What is LSC ??
IF WE ARE saying username in dba_users will it not analyze
sys objects which should not be the case ???
how to create this table ctl_error_proceso
-
LSC is me! my username in the database
if you see in dbms_stats I have i.username that will be 'LSC' so the syntax to excute in sql*plus
Code:
execute dbms_stats.gather_schema_stats(OWNNAME => 'LSC',-
ESTIMATE_PERCENT => 20,-
METHOD_OPT => 'FOR ALL COLUMNS SIZE 75',-
DEGREE => 1,-
CASCADE => TRUE);
table ctl_error_proceso is a process exception control table, you dont need it. I use it to store exceptions just in case any process fails
CREATE TABLE CTL_ERROR_PROCESO (
COD_ERROR_PROCESO NUMBER,
FEC_ERROR DATE,
DES_ERROR VARCHAR2 (256))
since my implict cursor contains 'LSC' only the pl/sql procedure will analyze for this user only, if I need more user I just add more username in the cursor
you can ignore METHOD_OPT => 'FOR ALL COLUMNS SIZE 75' if you dont want to use histograms
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|