Home » SQL & PL/SQL » SQL & PL/SQL » Help : Execute IMMEDIATE
Help : Execute IMMEDIATE [message #8426] |
Wed, 20 August 2003 06:23 |
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|Table" FORMAT A15
COLUMN "Espace Alloué" HEADING "Espace|Alloué" FORMAT A15
COLUMN "Espace utilisé" HEADING "Espace|utilisé" FORMAT A15
COLUMN "Pourcentage" HEADING "Pourcentage|d'utilisation" FORMAT A15
COLUMN "Taille Maximum" HEADING "Taille|Maximum 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|Table" FORMAT A15
COLUMN "Espace Alloué" HEADING "Espace|Alloué" FORMAT A15
COLUMN "Espace utilisé" HEADING "Espace|utilisé" FORMAT A15
COLUMN "Pourcentage" HEADING "Pourcentage|d'utilisation" FORMAT A15
COLUMN "Taille Maximum" HEADING "Taille|Maximum 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|Table" FORMAT A15
COLUMN "Espace Alloué" HEADING "Espace|Alloué" FORMAT A15
COLUMN "Espace utilisé" HEADING "Espace|utilisé" FORMAT A15
COLUMN "Pourcentage" HEADING "Pourcentage|d'utilisation" FORMAT A15
COLUMN "Taille Maximum" HEADING "Taille|Maximum 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 |
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 justDECLARE
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 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 03:20:56 CDT 2024
|