Home » SQL & PL/SQL » SQL & PL/SQL » Help : Execute IMMEDIATE
Help : Execute IMMEDIATE [message #8426] Wed, 20 August 2003 06:23 Go to next message
Gerald
Messages: 54
Registered: January 2001
Member
help my dear friends,

I'm working on an evaluation of the size of a table.
So here is my first WORKING script.
It gives the Real size, the allocated size, and the maximum size , For one TABLE.

SET LINESIZE 120
COLUMN Table_name HEADING "Nom de la&#124Table" FORMAT A15
COLUMN "Espace Alloué" HEADING "Espace&#124Alloué" FORMAT A15
COLUMN "Espace utilisé" HEADING "Espace&#124utilisé" FORMAT A15
COLUMN "Pourcentage" HEADING "Pourcentage&#124d'utilisation" FORMAT A15
COLUMN "Taille Maximum" HEADING "Taille&#124Maximum prévue" FORMAT A15
Select distinct Table_name ,
round(((Blocks + empty_blocks)*value)/1024,2)||' Ko' "Espace Alloué",
round(((value - avg_space)* blocks)/1024,2)||' Ko' "Espace utilisé",
round((((value - avg_space)* blocks)/1024)/((((Blocks + empty_blocks)*value)/1024))*100,2)||' %' "Pourcentage",
round((Max_EXTENTS*4*value)/1024,2)||' Ko' "Taille Maximum"
From user_tables, v$parameter
Where value = (select value from v$parameter
where name='db_block_size')
And Table_name='LANGUE';

ok, that's cool and wonderfull.
But as you've noticed the name of the table is given directly.
I upgrade the script by creating a For...Loop that take all the Tables names (from a SELECT) and apply for each table the first script ...
and here come the fun !

i've written that:

SET LINESIZE 120
COLUMN Table_name HEADING "Nom de la&#124Table" FORMAT A15
COLUMN "Espace Alloué" HEADING "Espace&#124Alloué" FORMAT A15
COLUMN "Espace utilisé" HEADING "Espace&#124utilisé" FORMAT A15
COLUMN "Pourcentage" HEADING "Pourcentage&#124d'utilisation" FORMAT A15
COLUMN "Taille Maximum" HEADING "Taille&#124Maximum prévue" FORMAT A15

BEGIN

For i in (Select Table_name from user_tables)
Loop

EXECUTE IMMEDIATE('ANALYSE TABLE i.table_name COMPUTE STATISTICS');
Execute IMMEDIATE ('Select distinct Table_name,round(((Blocks + empty_blocks)*value)/1024,2)||'' Ko'' "Espace Alloué",round(((value - avg_space)* blocks)/1024,2)||'' Ko'' "Espace utilisé",round((((value - avg_space)* blocks)/1024)/((((Blocks + empty_blocks)*value)/1024))*100,2)||'' %'' "Pourcentage",round((Max_EXTENTS*4*value)/1024,2)||'' Ko'' "Taille Maximum" From user_tables, v$parameter Where value = (select value from v$parameter where name=''db_block_size'') And Table_name=i.table_name');

End Loop;
End;
/

You guess ...

error in SQL instruction
ORA00-900
ORA06-512
On the first EXECUTE IMMEDIATE LINE ...

So what's up docs ?

RQ: as i try to do that on 8.0.4 version of Oracle too
I've try with that:
SET LINESIZE 120
COLUMN Table_name HEADING "Nom de la&#124Table" FORMAT A15
COLUMN "Espace Alloué" HEADING "Espace&#124Alloué" FORMAT A15
COLUMN "Espace utilisé" HEADING "Espace&#124utilisé" FORMAT A15
COLUMN "Pourcentage" HEADING "Pourcentage&#124d'utilisation" FORMAT A15
COLUMN "Taille Maximum" HEADING "Taille&#124Maximum prévue" FORMAT A15

DECLARE
str varchar2(50):='ANALYSE TABLE i.table_name COMPUTE STATISTICS';
str2 VARCHAR2(1000):='Select distinct Table_name,round(((Blocks + empty_blocks)*value)/1024,2)||'' Ko'' "Espace Alloué",round(((value - avg_space)* blocks)/1024,2)||'' Ko'' "Espace utilisé",round((((value - avg_space)* blocks)/1024)/((((Blocks + empty_blocks)*value)/1024))*100,2)||'' %'' "Pourcentage",round((Max_EXTENTS*4*value)/1024,2)||'' Ko'' "Taille Maximum" From user_tables, v$parameter Where value = (select value from v$parameter where name=''db_block_size'') And Table_name=i.table_name';
cur integer;
cur2 integer;
status integer;
status2 integer;

BEGIN

For i in (Select Table_name from user_tables)
Loop
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, str, dbms_sql.native);
status := dbms_sql.execute(cur);
cur2 := dbms_sql.open_cursor;
dbms_sql.parse(cur2, str2, dbms_sql.native);
status2 := dbms_sql.execute(cur2);
dbms_sql.close_cursor (cur);
dbms_sql.close_cursor (cur2);
End Loop;
End;
/

but i got an:
ERREUR à la ligne 1 :
ORA-00900: invalid SQL statement
ORA-06512: at line 20 (the dbms_sql.close_cursor (cur2); line)

******************

If someone can help me with that damn EXECUTE SQL or DBMS_SQL ... would be happy !!!
Re: Help : Execute IMMEDIATE [message #8430 is a reply to message #8426] Wed, 20 August 2003 07:35 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Gerald,

Although this should work,
BEGIN
    FOR i IN (SELECT table_name FROM user_tables)
    LOOP
        EXECUTE IMMEDIATE 'ANALYZE TABLE ' || i.table_name || ' COMPUTE STATISTICS';
        EXECUTE IMMEDIATE 'SELECT DISTINCT ut.table_name'
                          || ', TO_CHAR(ROUND(((ut.blocks + ut.empty_blocks) * vp.value) / 1024, 2)) || '' Ko'' "Espace Alloué" '
                          || ', TO_CHAR(ROUND(((vp.value - ut.avg_space) * ut.blocks) / 1024, 2)) || '' Ko'' "Espace utilisé" '
                          || ', TO_CHAR(ROUND((((vp.value - ut.avg_space) * ut.blocks) / 1024)/((((ut.blocks + ut.empty_blocks) * vp.value) / 1024)) * 100, 2)) || '' %'' "Pourcentage" '
                          || ', TO_CHAR(ROUND((ut.max_extents * 4 * vp.value) / 1024, 2)) || '' Ko'' "Taille Maximum" '
                          || 'FROM sys.user_tables     ut'
                          || ',    v$parameter         vp '
                          || 'WHERE vp.name = ''db_block_size'' '
                          || 'AND ut.table_name = '''
                          || UPPER(i.table_name)
                          || '''';
    END LOOP;
END;
/
....why do you need dynamic SQL, since you're processing every table in sys.user_tables anyway? Why not just
DECLARE
    CURSOR c_table_stats IS
        SELECT DISTINCT ut.table_name
        ,      TO_CHAR(ROUND(((ut.blocks + ut.empty_blocks)
                               *
                               vp.value) / 1024, 2))
               || ' Ko'                                     "Espace Alloue" 
        ,      TO_CHAR(ROUND(((vp.value - ut.avg_space)
                               *
                               ut.blocks) / 1024, 2))
               || ' Ko'                                     "Espace utilise"
        ,      TO_CHAR(ROUND((((vp.value - ut.avg_space)
                                *
                                ut.blocks) / 1024)
                              /
                              ((((ut.blocks + ut.empty_blocks)
                                  *
                                  vp.value) / 1024))
                                * 100, 2))
               || ' %'                                      "Pourcentage" 
        ,      TO_CHAR(ROUND((ut.max_extents
                              *
                              4
                              *
                              vp.value) / 1024, 2))
               || ' Ko'                                     "Taille Maximum"
        FROM   sys.user_tables     ut
        ,      v$parameter         vp
        WHERE  vp.name = 'db_block_size'
        AND    (ut.blocks + ut.empty_blocks) != 0
        ORDER BY ut.table_name
    ;    
BEGIN
    FOR i IN c_table_stats LOOP
        <i>(do something here)</i>
    END LOOP;
END;
Let us know how it goes,

A.
Re: Help : Execute IMMEDIATE [message #8433 is a reply to message #8426] Wed, 20 August 2003 07:45 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Actually, what I meant to say, is why do you need PL/SQL at all?
SQL> SELECT DISTINCT ut.table_name
  2  ,      TO_CHAR(ROUND(((ut.blocks + ut.empty_blocks)
  3                         *
  4                         vp.value) / 1024, 2))
  5         || ' Ko'                                     "Espace Alloue" 
  6  ,      TO_CHAR(ROUND(((vp.value - ut.avg_space)
  7                         *
  8                         ut.blocks) / 1024, 2))
  9         || ' Ko'                                     "Espace utilise"
 10  ,      TO_CHAR(ROUND((((vp.value - ut.avg_space)
 11                          *
 12                          ut.blocks) / 1024)
 13                        /
 14                        ((((ut.blocks + ut.empty_blocks)
 15                            *
 16                            vp.value) / 1024))
 17                          * 100, 2))
 18         || ' %'                                      "Pourcentage" 
 19  ,      TO_CHAR(ROUND((ut.max_extents
 20                        *
 21                        4
 22                        *
 23                        vp.value) / 1024, 2))
 24         || ' Ko'                                     "Taille Maximum"
 25  FROM   sys.user_tables     ut
 26  ,      v$parameter         vp
 27  WHERE  vp.name = 'db_block_size'
 28  AND    (ut.blocks + ut.empty_blocks) != 0
 29  ORDER BY ut.table_name
 30  /
  
TABLE_NAME   Espace Alloue   Espace utilise   Pourcentage  Taille Maximum
------------ --------------- ---------------- ------------ ---------------
DEPT         8 Ko            8 Ko             100 %        16160 Ko
EMP          8 Ko            8 Ko             100 %        16160 Ko
  
SQL>
A.
Re: Help : Execute IMMEDIATE [message #8437 is a reply to message #8430] Wed, 20 August 2003 09:55 Go to previous messageGo to next message
Gerald
Messages: 54
Registered: January 2001
Member
Thank you for this good help,

Well do I need Dynamic, just to improve the FOR...LOOP
and it seems to be kind of difficult to handle.

Anyway I'm checking your version, but be carefull
with this mistake i've done,
it's not:
round((Max_EXTENTS* 4 *value)/1024,2)
^
but
round((Max_EXTENTS* 8 *value)/1024,2)
^

because : 1 SEGMENT = 4 EXTENSIONS = 8 BLOCKS

Another difficulty is to ANALYSE all the table BEFORE doing this script if not, all value will be NULL

Thanks for your help
Take Care
Gerald
Re: Help : Execute IMMEDIATE [message #8448 is a reply to message #8426] Wed, 20 August 2003 15:41 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The proper command is ANALYZE with Z, not ANALYSE with S.
Previous Topic: SQL gurus - Can you solve this?
Next Topic: pl/sql error
Goto Forum:
  


Current Time: Thu Mar 28 03:20:56 CDT 2024