Для чего предназначена эта хранимая процедура? Она выдает объем свободного пространства для изменений, верно? Но она не сообщает, сколько свободного пространства осталось в каждом блоке. Информацию о свободном пространстве можно получить из представления dba_free_space. Не могли бы вы показать, для чего мы могли бы использовать эту процедуру?
В этом пакете есть еще одна процедура, unused_space. Если она сообщает, что есть 35 блоков, значит ли это, что в 35 блоках никогда не было данных?
Похоже, что она не сообщает о свободных блоках за отметкой максимального уровня заполнения (high water mark - HWM), не так ли?
Как мы могли бы использовать эту информацию? Не могли бы вы дать несколько примеров использования этих процедур при управлении пространством.
Ответ Тома Кайта
Далее представлен пример, показывающий, как использовать пакет dbms_space и как интерпретировать получаемые результаты. По сути, с помощью этих 2 процедур пакета мы сможем получить следующую информацию:
Free Blocks...... Количество блоков в списке свободных (freelist) Total Blocks..... Общее количество блоков, выделенных таблице Total Bytes...... Общее количество байтов, выделенных таблице Unused Blocks.... Сколько блоков никогда не содержали данные Unused Bytes..... То же, но в байтах
При этом вы не сможете узнать, в скольких блоках есть свободное пространство для изменений. Мы можем определить, сколько блоков являются кандидатами для вставки (они находятся в списке свободных блоков) и все блоки в списке свободных точно содержат пространство для изменений, но в таблице есть еще блоки, в которых достаточно пространства для изменений, но они не входят в список свободных блоков. Информацию о них мы никак не получим.
Предлагаемая процедура не скажет вам, сколько пространства свободно в каждом блоке (и никто не скажет, поскольку в таблице обычно - тысячи или сотни тысяч блоков, и любой поблочный анализ свободного пространства практически неэффективен. Можно получить средний объем свободного пространства, но не по каждому блоку отдельно).
Этот отчет выдает информацию о блоках за отметкой максимального уровня заполнения. Unused Blocks - это как раз блоки "выше" отметки максимального уровня заполнения.
Большую часть информации, выдаваемой пакетом dbms_space, можно получить, анализируя таблицу и выполняя запросы к представлениям user_tables и user_segments. Этот пакет обеспечивает более детальный анализ списков свободных блоков, поскольку каждый список можно анализировать отдельно.
Ниже представлена процедура, с помощью которой можно упростить использование пакета dbms_space. После нее я создаю таблицу и показываю, как используется выделенное ей пространство. В комментариях я объясняю полученные результаты.
ops$tkyte@8i> create or replace 2 procedure show_space 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default 'TABLE' ) 6 as 7 l_free_blks number; 8 9 l_total_blocks number; 10 l_total_bytes number; 11 l_unused_blocks number; 12 l_unused_bytes number; 13 l_LastUsedExtFileId number; 14 l_LastUsedExtBlockId number; 15 l_LAST_USED_BLOCK number; 16 procedure p( p_label in varchar2, p_num in number ) 17 is 18 begin 19 dbms_output.put_line( rpad(p_label,40,'.') // 20 p_num ); 21 end; 22 begin 23 dbms_space.free_blocks 24 ( segment_owner => p_owner, 25 segment_name => p_segname, 26 segment_type => p_type, 27 freelist_group_id => 0, 28 free_blks => l_free_blks ); 29 30 dbms_space.unused_space 31 ( segment_owner => p_owner, 32 segment_name => p_segname, 33 segment_type => p_type, 34 total_blocks => l_total_blocks, 35 total_bytes => l_total_bytes, 36 unused_blocks => l_unused_blocks, 37 unused_bytes => l_unused_bytes, 38 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 39 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 40 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 41 42 p( 'Free Blocks', l_free_blks ); 43 p( 'Total Blocks', l_total_blocks ); 44 p( 'Total Bytes', l_total_bytes ); 45 p( 'Unused Blocks', l_unused_blocks ); 46 p( 'Unused Bytes', l_unused_bytes ); 47 p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 48 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 49 p( 'Last Used Block', l_LAST_USED_BLOCK ); 50 end; 51 / Procedure created. ops$tkyte@8i> create table t ( x int, y char(2000) default '*' ) 2 storage ( initial 40k next 40k minextents 5 ) 3 tablespace system; Table created.
Я создал таблицу с несколькими экстентами, чтобы результаты были интереснее. Я также добавил столбец типа char(2000), чтобы минимальная длина строки составляла 2000 байтов (поля типа char всегда имеют максимальный размер). В результате, строки у меня получаются "большие".
ops$tkyte@8i> insert into t (x) values ( 1 ); 1 row created.
Я создал одну строку, чтобы использовать немного пространства в таблице.
ops$tkyte@8i> analyze table t compute statistics;
Table analyzed.
ops$tkyte@8i> compute sum of blocks on report
ops$tkyte@8i> break on report
ops$tkyte@8i> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 and segment_type = 'TABLE'
5 /
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32
Это показывает, что в пяти экстентах этой таблице выделено 32 блока (что и следовало ожидать)
ops$tkyte@8i> clear breaks
ops$tkyte@8i> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /
BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
1 30 6091 1
Поскольку я проанализировал таблицу, у меня есть доступ к приведенной выше информации. Вы увидите, что она в точности соответствует полученным ниже результатам. Всего таблице выделено 32 блока (как показано ниже и подтверждается запросом к user_extents выше). Есть 30 EMPTY_BLOCKS (выше)/UNUSED_BLOCKS (ниже). Это блоки выше HWM. Остается 2 неучтенных блока - один блок содержит данные, а другой - карту экстентов для таблицы (первый блок каждой таблицы используется системой для своих целей).
ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................1 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................30 Unused Bytes............................245760 Last Used Ext FileId....................1 Last Used Ext BlockId...................64816 Last Used Block.........................2 PL/SQL procedure successfully completed. ops$tkyte@8i> insert into t (x) 2 select rownum 3 from all_users 4 where rownum < 50 5 / 49 rows created. ops$tkyte@8i> commit; Commit complete.
Итак, теперь у нас есть 50 строк по 2 Кбайта каждая. Я использую блоки размером 8 Кбайт, поэтому предполагаю, что в каждом блоке будет по 3 строки. Это означает около 18 блоков данных плюс 1 для системной информации = около 19 "используемых" блоков в результате. Ниже я получаю следующую информацию:
- 3 блока в списке свободных. В них есть еще пространство для вставки новых строк (в них еще не достигнут процент заполнения pctused, поэтому можно вставлять данные)
- 12 неиспользуемых блоков, что дает 20 "используемых". Поскольку в списке свободных три блока, мы, похоже, использовали для данных немного больше 18 блоков - 19 блоков для 50 строк. Еще один блок используется системой, вот все и сошлось.
ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................3 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/SQL procedure successfully completed. ops$tkyte@8i> delete from t; 50 rows deleted. ops$tkyte@8i> commit; Commit complete.
Теперь мы можем увидеть, как используется пространство после удаления.
ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................19 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/SQL procedure successfully completed.
Представленный результат показывает, что при удалении все блоки помещаются в список свободных. У нас получилось 19 блоков в списке свободных + 12 неиспользуемых + 1 системный = 32 блока. Все учтены. Обратите внимание, что позиция HWM осталась той же - у нас не 31 неиспользуемый блок, а 12, как и прежде. Значение HWM для таблицы никогда не уменьшается, если только мы не выполним...
ops$tkyte@8i> truncate table t; Table truncated. ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................0 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................31 Unused Bytes............................253952 Last Used Ext FileId....................1 Last Used Ext BlockId...................64816 Last Used Block.........................1 PL/SQL procedure successfully completed.
ее очистку. В результате, все выделенные блоки оказываются до HWM. Теперь у нас есть 31 неиспользуемых блока + 1 системный = 32 блока. Ни один из блоков не входит в список свободных, потому что ни в одном из них нет никаких данных.
ORA-14107: partition specification is required for a partitioned object
SQL> CREATE TABLE T (X VARCHAR2(20));
Table created.
SQL> EXEC SHOW_SPACE('T');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> EXEC SHOW_SPACE('T');
Free Blocks.............................0
Total Blocks............................10
Total Bytes.............................81920
Unused Blocks...........................9
Unused Bytes............................73728
Last Used Ext FileId....................5
Last Used Ext BlockId...................126659
Last Used Block.........................1
PL/SQL procedure successfully completed.
T_P - секционированная таблица.
SQL> EXEC SHOW_SPACE('T_P');
BEGIN SHOW_SPACE('T_P'); END;
*
ERROR at line 1:
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "myschema.SHOW_SPACE", line 22
ORA-06512: at line 1
Почему я получаю эту ошибку при попытке использования процедуры show_space для секционированной (partitioned) таблицы?
Ответ Тома Кайта:
Да, пришло время обновить эту утилиту! У меня уже есть новая версия, которая дополнительно:
- учитывает секционирование;
- позволяет получать результаты в SQL операторах, возвращая результирующее множество, а не просто печатая их. Распечатать с помощью dbms_output.put_line вы легко сможете сами...
Начнем с типов:
create or replace type show_space_type as object ( owner varchar2(30), segment_name varchar2(30), partition_name varchar2(30), segment_type varchar2(30), free_blocks number, total_blocks number, unused_blocks number, last_used_ext_fileid number, last_used_ext_blockid number, last_used_block number ) / create or replace type show_space_table_type as table of show_space_type /
А затем сама функция:
create or replace
function show_space_for
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
return show_space_table_type
authid CURRENT_USER
as
-- pragma autonomous_transaction; -- ниже выяснится, что это лишнее - прим. В.К.
type rc is ref cursor;
l_cursor rc;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_last_used_block number;
l_sql long;
l_conj varchar2(7) default ' where ';
l_data show_space_table_type := show_space_table_type();
l_owner varchar2(30);
l_segment_name varchar2(30);
l_segment_type varchar2(30);
l_partition_name varchar2(30);
procedure add_predicate( p_name in varchar2, p_value in varchar2 )
as
begin
if ( instr( p_value, '%' ) > 0 )
then
l_sql := l_sql // l_conj // p_name //
' like ''' // upper(p_value) // '''';
l_conj := ' and ';
elsif ( p_value is not null )
then
l_sql := l_sql // l_conj // p_name //
' = ''' // upper(p_value) // '''';
l_conj := ' and ';
end if;
end;
begin
l_sql := 'select owner, segment_name, segment_type, partition_name
from dba_segments ';
add_predicate( 'segment_name', p_segname );
add_predicate( 'owner', p_owner );
add_predicate( 'segment_type', p_type );
add_predicate( 'partition', p_partition );
execute immediate 'alter session set cursor_sharing=force';
open l_cursor for l_sql;
execute immediate 'alter session set cursor_sharing=exact';
loop
fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name;
exit when l_cursor%notfound;
begin
dbms_space.free_blocks
( segment_owner => l_owner,
segment_name => l_segment_name,
segment_type => l_segment_type,
partition_name => l_partition_name,
freelist_group_id => 0,
free_blks => l_free_blks );
dbms_space.unused_space
( segment_owner => l_owner,
segment_name => l_segment_name,
segment_type => l_segment_type,
partition_name => l_partition_name,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
l_data.extend;
l_data(l_data.count) :=
show_space_type( l_owner, l_segment_name, l_partition_name,
l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks,
l_lastUsedExtFileId, l_LastUsedExtBlockId, l_last_used_block );
exception
when others then null;
end;
end loop;
close l_cursor;
return l_data;
end;
/
Теперь мы можем выполнить:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select SEGMENT_NAME, PARTITION_NAME SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS 2 from table( cast( show_space_for( 'HASHED',user,'%' ) as show_space_table_type ) ) 3 / SEGMENT_NA SEGMENT_TYPE FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS ---------- ----------------- ----------- ------------ ------------- HASHED PART_2 1 64 62 HASHED PART_3 1 64 62 HASHED PART_4 1 64 62 HASHED PART_1 1 64 62
А для версии 9i можно изменить функцию на потоковую (pipelined):
ops$tkyte@ORA9I.WORLD> create or replace 2 function show_space_for 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default 'TABLE', 6 p_partition in varchar2 default NULL ) 7 return show_space_table_type 8 authid CURRENT_USER 9 PIPELINED 10 as 11 -- pragma autonomous_transaction; -- ниже выяснится, что это лишнее - прим. В.К. 12 type rc is ref cursor; 13 l_cursor rc; 14 15 l_free_blks number; 16 l_total_blocks number; 17 l_total_bytes number; 18 l_unused_blocks number; 19 l_unused_bytes number; 20 l_LastUsedExtFileId number; 21 l_LastUsedExtBlockId number; 22 l_last_used_block number; 23 l_sql long; 24 l_conj varchar2(7) default ' where '; 25 l_owner varchar2(30); 26 l_segment_name varchar2(30); 27 l_segment_type varchar2(30); 28 l_partition_name varchar2(30); 29 30 procedure add_predicate( p_name in varchar2, p_value in varchar2 ) 31 as 32 begin 33 if ( instr( p_value, '%' ) > 0 ) 34 then 35 l_sql := l_sql // l_conj // p_name // ' like ''' // upper(p_value) // ''''; 36 l_conj := ' and '; 37 elsif ( p_value is not null ) 38 then 39 l_sql := l_sql // l_conj // p_name // ' = ''' // upper(p_value) // ''''; 40 l_conj := ' and '; 41 end if; 42 end; 43 begin 44 l_sql := 'select owner, segment_name, segment_type, partition_name 45 from dba_segments '; 46 47 add_predicate( 'segment_name', p_segname ); 48 add_predicate( 'owner', p_owner ); 49 add_predicate( 'segment_type', p_type ); 50 add_predicate( 'partition', p_partition ); 51 52 execute immediate 'alter session set cursor_sharing=force'; 53 open l_cursor for l_sql; 54 execute immediate 'alter session set cursor_sharing=exact'; 55 56 loop 57 fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name; 58 dbms_output.put_line( l_segment_name // ',' // l_segment_type ); 59 exit when l_cursor%notfound; 60 begin 61 dbms_space.free_blocks 62 ( segment_owner => l_owner, 63 segment_name => l_segment_name, 64 segment_type => l_segment_type, 65 partition_name => l_partition_name, 66 freelist_group_id => 0, 67 free_blks => l_free_blks ); 68 69 dbms_space.unused_space 70 ( segment_owner => l_owner, 71 segment_name => l_segment_name, 72 segment_type => l_segment_type, 73 partition_name => l_partition_name, 74 total_blocks => l_total_blocks, 75 total_bytes => l_total_bytes, 76 unused_blocks => l_unused_blocks, 77 unused_bytes => l_unused_bytes, 78 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 79 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 80 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 81 82 pipe row ( show_space_type( l_owner, l_segment_name, l_partition_name, 83 l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks, 84 l_lastUsedExtFileId, l_LastUsedExtBlockId, l_last_used_block ) ); 85 exception 86 when others then null; 87 end; 88 end loop; 89 close l_cursor; 90 91 return; 92 end; 93 / Function created. ops$tkyte@ORA9I.WORLD> set arraysize 1 ops$tkyte@ORA9I.WORLD> select SEGMENT_NAME, SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS 2 from table( show_space_for( '%',user,'%' ) ) 3 / SEGMENT_NAME SEGMENT_TYPE FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS --------------- ----------------- ----------- ------------ ------------- KEEP_SCN TABLE 1 64 62 EMPLOYEES TABLE 0 64 63 STINKY TABLE 0 64 63 OBJECT_TABLE TABLE 1 64 62 RUN_STATS TABLE 2 64 53 EMP TABLE 0 64 62 PROJ TABLE 0 64 62 X TABLE 1 64 62 WORDS TABLE 0 64 63 DOCS TABLE 0 64 63 KEYWORDS TABLE 0 64 63 DEPT TABLE 2 64 61 C TABLE 1 64 62 DSINVLINES TABLE 1 64 62 NUM_STR TABLE 1 64 23 T TABLE 4 64 28 T1 TABLE 0 64 63 T2 TABLE 0 64 63 BOM TABLE 1 64 62 PARTS TABLE 1 64 62 SYS_C001371 INDEX 0 64 62 SYS_C001372 INDEX 0 64 62 SYS_C001574 INDEX 0 64 62 SYS_C001694 INDEX 0 64 62 SYS_C001695 INDEX 0 64 62 BOM_PK INDEX 0 64 62 PARTS_PK INDEX 0 64 62 27 rows selected.
Тут было много комментариев и вопросов, которые я для сокращения объема выпуска переводить не стал - прим. В.К.
Комментарий Тома Кайта по поводу "системных" блоков в файлах данных
Да, первый блок файла в табличном пространств, управляемом ПО СЛОВАРЮ (DMT), используется системой.
Так же, как и первые 64 Кбайта в ЛОКАЛЬНО управляемых табличных пространствах (LMT).
Ниже представлено то, о чем вы спрашивали - что вы получаете и сколько пространства доступно для использования (я настоятельно рекомендую использовать LMT - избегайте DMT):
ops$tkyte@ORA817DEV.US.ORACLE.COM> Create tablespace TS1 datafile '/tmp/ts1.dbf' size 24k reuse;
Tablespace created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> host ls -l /tmp/ts1.dbf
-rw-r----- 1 ora817 32768 Dec 26 13:33 /tmp/ts1.dbf
ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
---------------- ------- ---------- --------- ------ ------- -------- ------
...
TS1 24 8 16 33.3 16 0 .0
------- ---------- ---------
sum 3,818,848 1,605,144 2,213,704
13 rows selected.
Видите: 24 Кбайта размер - 8 использовано, 16 свободно...
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter tablespace ts1 add datafile '/tmp/ts2.dbf' size 24k reuse;
Tablespace altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> host ls -l /tmp/ts2.dbf
-rw-r----- 1 ora817 32768 Dec 26 13:33 /tmp/ts2.dbf
ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
---------------- ------- --------- --------- ------ -------- -------- ------
...
TS1 48 16 32 33.3 16 0 .0
------- --------- ---------
sum 3,818,872 1,605,152 2,213,720
13 rows selected.
Теперь имеем 48 Кбайт (24*2), 16 Кбайт использовано (по одному блоку в файле)
Размеры экстентов в байтах
Спасибо за хорошее объяснение, Том. Но у меня есть один вопрос:
ops$tkyte@8i> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 and segment_type = 'TABLE'
5 /
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32
Вот это мы получили после создания таблицы T с первым и следующим экстентом размером 40 Кбайт. Так откуда взялся третий экстент - 81920 байт и четвертый, 57344? И почему им выделено, соответственно, 10 и 7 блоков?
Ответ Тома Кайта:
Причина в том, что я создал таблицу в табличном пространстве, управляемом по словарю, и мы выделяем пространство размером +- 5 блоков (чтобы избежать фрагментации свободного пространства).
Почитайте
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c02block.htm#2846
там описан алгоритм.
Что такое AUTO SEGMENT SPACE MANAGEMENT?
Каким должно быть значение freelist_group_id при каждом обращении к dbms_space.free_blocks? Вы по умолчанию используете значение 1, так надо ли задавать 1 для таблиц во всех случаях?
Я только что выполнил dbms_space.free_blocks для сегмента в ЛОКАЛЬНО управляемом табличном пространстве с AUTO SEGMENT SPACE MANAGEMENT, и получил следующее сообщение об ошибке:
10618, 00000, "Operation not allowed on this segment" // *Cause: This DBMS_SPACE operation is not permitted on segments in // tablespaces with AUTO SEGMENT SPACE MANAGEMENT // *Action: Recheck the segment name and type and re-issue the statement
Как найти свободные блоки в этом случае?
Ответ Тома Кайта:
Я просто предполагал, что список свободных блоков будет один. Если их несколько, утилита просто "недостаточно умная".
Для ASSM есть процедура dbms_space.space_usage.
Ваша процедура show_space и секционированные таблицы
Я пытаюсь использовать вашу процедуру show_space, но, похоже, она не работает для секционированных таблиц.
Я думаю, в курсоре
for x in ( select tablespace_name
from dba_tablespaces
where tablespace_name = ( select tablespace_name
from dba_segments
where segment_type = p_type
and segment_name = p_segname
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO'
)
в подзапросе надо добавить distinct...
Ответ Тома Кайта:
Ну, так добавьте. Я изменяю эту процедуру по мере надобности.
Свободные блоки и прозводительность
Как влияет большое значение HWM, количество свободных или неиспользуемых блоков на производительность при работе с таблицей (INSERT/UPDATE/SELECT). У меня в практике был случай, когда пакет работал 9 минут на одном экземпляре (EDEV) и 2 часа на другом (EPROD). Результат выполнения вашей процедуры и запроса к ALL_TABLES для обоих экземпляров представлен ниже.
EDEV Free Blocks 4 Total Blocks 1984 Total Bytes 16252928 Unused Blocks 1 Unused Bytes 8192 Last Used Ext FileId 112 Last Used Ext BlockId 5193 Last Used Block 15 EPROD Free Blocks 20 Total Blocks 2352 Total Bytes 19267584 Unused Blocks 11 Unused Bytes 90112 Last Used Ext FileId 346 Last Used Ext BlockId 3065 Last Used Block 5 BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS 2340 6 4462 2103 select blocks, empty_blocks, avg_space, num_freelist_blocks from user_tables where table_name = 'BACKLOG_ITEM' BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS 3956 11 4570 2004
Если я могу использовать оператор TRUNCATE, но в пакетном задании выполняется множество операторов DELETE и INSERT, как мне предотвратить большие значения отметки максимального уровня заполнения? Нет ли какой-то утилиты или сценария для уменьшения значения HWM для таблицы.
Ответ Тома Кайта:
1) Обе эти таблицы слишком маленькие. Если бы у меня было "пакетное задание", которое 9 минут обрабатывает 20 мегабайтов данных, меня бы одно это уже обеспокоило. Это, вероятно, на 8 с половиной минут дольше, чем должно быть.
Используйте TKPROF и SQL_TRACE для поиска наиболее существенной проблемы.
2) У вас очень, очень маленькая таблица. Я думаю, вы ищите не в том направлении. Скорее всего, используется плохой алгоритм. Надо найти проблему и устранить ее. Не "что-то исправить" и посмотреть, помогло или нет.
Комментарий читателя от 13 июня 2003 года
Спасибо за ответ. Я абсолютно согласен, что производительность пакетного задания, которое я упоминал, может быть повышена путем изменения алгоритма. Извините, если представленный пример отвлек от "реальной" сути моего вопроса.
Меня интересует, как я могу использовать результаты, выдаваемые вашей утилитой, при сопровождении базы данных. Например, есть ли корреляция между количеством свободных блоков и производительностью?
В моем примере, могу ли я связывать различие во времени выполнения пакетного задания на двух экземплярах с количеством сводных блоков? (Повторюсь, что я согласен с тем, что производительность может быть повышена.)
Еще один пример: у меня есть таблица с примерно 6 миллионами строк. Средняя длина строки составляет 1250 байтов. Мы выполняем с этой таблицей множество операторов "Delete/Insert/Update". Мы недавно пересоздали эту таблицу (используя экспорт, CREATE Table и импорт). Мы заметили существенное изменение производительности операторов SELECTS. Через некоторое время их производительность снова стала "нормальной". Могу ли я связывать это с количеством свободных блоков и не считаете ли вы, что нужно постоянно искать такие блоки и "вычищать" их, а если да, то как это сделать?.
Примечание: на этот раз я пытаюсь найти основную причину изменений производительности. По ходу чтения этого обсуждения мне показалось, что я, возможно, нашел "одну" из причин, и я хочу получить ваше подтверждение. Я также хочу отметить, что базы данных не сильно отличаются с точки зрения размера, параметров и т.п. Код тоже не изменился.
Ответ Тома Кайта:
Может быть, но в вашем примере размеры очень уж невелики. Мне казалось совершенно ясным, что, по моему мнению, небольшое количество блоков в списке свободных не могло существенно ни на что повлиять...
Нет, ничего и ни с чем вы тут связывать не можете. Для обоснованных выводов просто недостаточно данных.
Вы не сказали, как изменилась производительность, какого рода действия вы выполняли с этой таблицей, вообще ничего. Опять абсолютно недостаточно данных для любых выводов о чем бы то ни было (кроме гипотетических рассуждений, которые я не очень люблю). Теперь, если у вас есть конкретные показатели (полученные с помощью tkprof или statspack) - конкретные числовые данные, которые свидетельствуют о количестве consistent gets, планы запросов, входные данные и т.п. для всех случаев, то, конечно, можно будет сделать определенные выводы...
(Но, на производительность запросов количество блоков в списке свободных влияния не оказывает. Я могу установить параметры pctfree/pctused так, что каждый блок будет в списке свободных. Я могу установить параметры pctfree/pctused так, что НИ ОДНОГО блока в списке свободных не будет. Я могу организовать обе эти таблицы так, что все блоки у них будут, фактически, "одинаковые". При этом производительность запросов к ним будет одинаковой. Она никак не связана с количеством блоков в списке свободных (однако, вы можете делать определенные выводы на основе этого показателя, если понимаете, как установлены параметры pctfree/pctused и как таблицы используются). Все связано с тем, сколько блоков сервер Oracle должен обработать, чтобы выполнить ваши запросы.
Вам необходим анализ с помощью TKPROF. Надо проанализировать с помощью TKPROF выполнение этого "пакетного задания" на dev и на prod. Надо сравнить результаты. Это позволит пройти 90% пути к исходной причине проблемы.
В чем тут может быть проблема?
Том,
я не могу использовать процедуру show_space. Помоги мне, пожалуйста.
SQL> create user a identified by a; User created. SQL> grant create session, create procedure, 2 create table to a; Grant succeeded. SQL> alter user a quota unlimited on users; User altered. SQL> connect a/a Connected. SQL> @D:\share\oracle\asktom\show_space.sql 51 / Procedure created. SQL> create table t ( x int ) tablespace users; Table created. SQL> exec show_space( 'T' ) BEGIN show_space( 'T' ); END; * ERROR at line 1: ORA-10618: Operation not allowed on this segment ORA-06512: at "SYS.DBMS_SPACE", line 74 ORA-06512: at "A.SHOW_SPACE", line 22 ORA-06512: at line 1 SQL> desc t Name Null? Type ------------------------------------- -------- -------- X NUMBER(38) SQL> insert into t values(100); 1 row created. SQL> desc dbms_space PROCEDURE FREE_BLOCKS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN FREELIST_GROUP_ID NUMBER IN FREE_BLKS NUMBER OUT SCAN_LIMIT NUMBER IN DEFAULT PARTITION_NAME VARCHAR2 IN DEFAULT PROCEDURE SPACE_USAGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN UNFORMATTED_BLOCKS NUMBER OUT UNFORMATTED_BYTES NUMBER OUT FS1_BLOCKS NUMBER OUT FS1_BYTES NUMBER OUT FS2_BLOCKS NUMBER OUT FS2_BYTES NUMBER OUT FS3_BLOCKS NUMBER OUT FS3_BYTES NUMBER OUT FS4_BLOCKS NUMBER OUT FS4_BYTES NUMBER OUT FULL_BLOCKS NUMBER OUT FULL_BYTES NUMBER OUT PARTITION_NAME VARCHAR2 IN DEFAULT PROCEDURE UNUSED_SPACE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN TOTAL_BLOCKS NUMBER OUT TOTAL_BYTES NUMBER OUT UNUSED_BLOCKS NUMBER OUT UNUSED_BYTES NUMBER OUT LAST_USED_EXTENT_FILE_ID NUMBER OUT LAST_USED_EXTENT_BLOCK_ID NUMBER OUT LAST_USED_BLOCK NUMBER OUT PARTITION_NAME VARCHAR2 IN DEFAULT
Ответ Тома Кайта:
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- эта процедура использует authid current user, чтобы она могла запрашивать
-- представления DBA_*, используя привилегии РОЛИ и чтобы ее можно было
-- устанавливать в одном экземпляре в базе данных, а не отдельно для
-- каждого пользователя, которому она необходима
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
-- Вложенная процедура для выдачи значений в красивом формате
-- с простой меткой
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') //
to_char(p_num,'999,999,999,999') );
end;
begin
-- Этот запрос выполняется динамически, чтобы можно было создать данную процедуру
-- пользователем, имеющим доступ к представлениям DBA_SEGMENTS/TABLESPACES
-- через роль, как это обычно бывает.
-- ПРИМЕЧАНИЕ: во время выполнения, вызывающий ДОЛЖЕН иметь доступ к этим
-- двум представлениям!
-- Этот запрос определяет, является ли данный объект объектом ASSM или нет
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'Это секционированная таблица, используйте p_partition => ');
return;
end;
-- Если объект расположен в табличном пространстве ASSM, мы должны использовать
-- этот вызов для получения информации о пространстве, иначе мы используем
-- вызов FREE_BLOCKS для сегментов, управляемых пользователем
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
end if;
-- А затем мы вызываем процедуру unused_space для получения остальной
-- информации
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
Попробуйте использовать эту версию; похоже вы работаете в 9i с использованием ASSM, применяя старую версию из 8i - механизм assm не использует списков свободных блоков.
Необходим доступ к представлениям dba_*
Это немного напрягает! Я привык использовать эту замечательную утилиту (show_space) в базах многих клиентов (8i), для исследования проблем с пространством. По сути, ее можно было использовать в любой схеме! Теперь, в 9i, мне нужно просить привилегии доступа к этим представлениям. Нельзя ли это как-то обойти?
Ответ Тома Кайта:
Вместо запрос к представлениям dba_* вы можете сами передавать эту информацию (в табличном пространстве какого типа находится объект).
Используйте версию, аналогичную "8i", но параметризуйте ее, чтобы использовался соответствующий вызов.
Другие размеры экстентов
Спасибо за замечательное обсуждение. Однако, в самом первом ответе я так и не понял, почему представление DBA_EXTENTS показывает экстенты разных размеров (т.е. экстенты размером 5, 5, 5, 10, 7, что дает в сумме 32 блока). В ╨том примере, мЁ не указЁвали PCTINCREASE. Поэтому мне кажется, что все экстенты должны быть одинакового размера, и в одном экстенте свободного места должно быть меньше, чем в остальных из-за одной вставки. Однако, запрос дает нам другой результат. Не могли бы вы объяснить, как в DBA_EXTENTS оказались экстенты указанных размеров.
Ответ Тома Кайта:
Вы читали ответ, который я давал, со ссылкой на документацию?
Когда вы просите 40 Кбайт из табличного пространства, управляемого по словарю, и находится свободный экстент, размер которого отличается от запрошенного не более, чем на 5 блоков (например: 40k = 5 * 8k блоков - любой свободный экстент размером 5, 6, 7, 8, 9 или 10 блоков будет использован). Это делается во избежание ситуации, когда при наличии свободного экстента размером, скажем, 7 блоков и запросе 5 блоков, мы берем 5 и оставляем 2 "абсолютно бесполезных" блока.
Если бы в свободном экстенте было 11 блоков, вам бы выделили ваши 5, а 6 осталось бы для следующего запроса.
Иначе оставалось бы множество свободных экстентов по 1/2/3 блока, которые ни для чего нельзя было бы использовать.
Вопросы
1. Почему в вашем коде есть обработчик 'EXCEPTION WHEN OTHERS THEN NULL'? Вы же выступаете резко против сокрытия исключительных ситуаций, не так ли?
2. Я понимаю, что конструкция AUTHID CURRENT_USER позволяет устанавливать утилиту в базе данных в одном экземпляре, и каждый вызывающий пользователь не получает дополнительных привилегий из-за использования прав создателя.
Но как конструкция AUTHID CURRENT_USER обеспечивает то, чтобы процедура "могла запрашивать представления DBA_*, используя привилегии РОЛИ"?
3. "-- Этот запрос выполняется динамически, чтобы можно было создать данную процедуру пользователем, имеющим доступ к представлениям DBA_SEGMENTS/TABLESPACES через роль, как это обычно бывает."
Вместо этого почему просто не использовать представления ALL_*, чтобы EXECUTE IMMEDIATE вообще не понадобился? Каждый пользователь, вызывающий эту процедуру, автоматически видит то, что ему должно быть доступно, т.е. представления ALL_* решают все проблемы за нас?
4. Какие преимущества дает нам использование PIPELINED в версии 9iR2 этой утилиты?
5. Зачем нужна PRAGMA AUTONOMOUS_TRANSACTION?
Ответ Тома Кайта:
1) Потому что так я получаю простой отчет; вместо сбоя на полпути при обнаружении объекта, для которого нельзя получить отчет, я просто пропускаю его.
2)
l_sql := 'select owner, segment_name, segment_type, partition_name
from dba_segments ';
add_predicate( 'segment_name', p_segname );
add_predicate( 'owner', p_owner );
add_predicate( 'segment_type', p_type );
add_predicate( 'partition', p_partition );
execute immediate 'alter session set cursor_sharing=force';
open l_cursor for l_sql;
execute immediate 'alter session set cursor_sharing=exact';
Я использовал динамический sql - поэтому СОЗДАТЕЛЮ процедуры доступ к DBA_* не нужен; он нужен только ВЫЗЫВЮЩЕМУ. И он при этом сможет использовать привилегии роли, поскольку роли учитываются при выполнении подпрограммы с правами вызывающего.
3) Поскольку утилита создавалась для DBA_. Представления DBA_ не содержат фильтров.
4) Для большей эффективности - поищите pipelined во многих примерах.
5) Не нужна. Не знаю, о чем я думал, когда оставил ее в коде.
Ошибка ORA-00943
Как избежать ошибки ORA-00943 при подсчете свободных блоков с помощью DBMS_SPACE.FREE_SPACE для кластеров, принадлежащих другому пользователю, например:
SQL> show user
USER is "TEST123"
SQL> var x number;
SQL>
SQL> exec dbms_space.free_blocks ('TEST','TEST_CLUSTER','CLUSTER',0,:x);
BEGIN dbms_space.free_blocks ('TEST','TEST_CLUSTER','CLUSTER',0,:x); END;
*
ERROR at line 1:
ORA-00943: cluster does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at line 1
SQL>
SQL> conn test/test@dev
Connected.
SQL> exec dbms_space.free_blocks ('TEST','TEST_CLUSTER','CLUSTER',0,:x);
PL/SQL procedure successfully completed.
SQL> print x
X
----------
3
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Вопрос 1. Есть ли какая-то привилегия, необходимая для решения этой задачи пользователем, отличающимся от владельца кластера?
Я пытался вЁполнить GRANT ALTER ANY CLUSTER TO TEST123, но ╨то не помогло.
Ответ Тома Кайта:
ops$tkyte@ORA9IR2> create user a identified by a default tablespace manual quota
unlimited on manual;
User created.
ops$tkyte@ORA9IR2> create user b identified by b;
User created.
ops$tkyte@ORA9IR2> grant create session, create cluster to a;
Grant succeeded.
ops$tkyte@ORA9IR2> grant create session to b;
Grant succeeded.
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create cluster test_cluster (x number) size 1024;
Cluster created.
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> var x number;
b@ORA9IR2> exec dbms_space.free_blocks ('A','TEST_CLUSTER','CLUSTER',0,:x)
BEGIN dbms_space.free_blocks ('A','TEST_CLUSTER','CLUSTER',0,:x); END;
*
ERROR at line 1:
ORA-00943: cluster does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at line 1
b@ORA9IR2> @connect /
b@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> grant analyze any to b;
Grant succeeded.
ops$tkyte@ORA9IR2> @connect b/b
ops$tkyte@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> exec dbms_space.free_blocks ('A','TEST_CLUSTER','CLUSTER',0,:x)
PL/SQL procedure successfully completed.
Кластеры SYS дают ту же ошибку даже при наличии привилегии "ANALYZE ANY"
Спасибо за ответ.
Но даже для только что созданного кластера в схеме SYS выдается ошибка:
SQL> show user
USER is "SYS"
SQL> l
1 create cluster test_cluster (dept number(2))
2 size 1024
3* index
SQL>
SQL> /
Cluster created.
SQL> create index test_cluster_index on cluster test_cluster;
Index created.
SQL> create table x
2 cluster test_cluster(deptno)
3 as select * from scott.dept;
Table created.
SQL> create table y
2 cluster test_cluster(deptno)
3 as select * from scott.emp;
Table created.
SQL> exec dbms_space.free_blocks('SYS','TEST_CLUSTER','CLUSTER', 0, :x);
PL/SQL procedure successfully completed.
SQL> print x
X
----------
1
SQL> grant analyze any to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> show user
USER is "TEST"
SQL> var x number
SQL> exec dbms_space.free_blocks('SYS','TEST_CLUSTER','CLUSTER', 0, :x);
BEGIN dbms_space.free_blocks('SYS','TEST_CLUSTER','CLUSTER', 0, :x); END;
*
ERROR at line 1:
ORA-00943: cluster does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at line 1
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Ответ Тома Кайта:
Это результат вашей установки O7_DICTIONARY_ACCESSIBILITY, связанный с обеспечением защиты - чтобы оградить SYS от всего остального.
Вам надо либо выполнять эту утилиту как SYSDBA для анализа схемы SYS (предпочтительнее), либо установить O7_DICTIONARY_ACCESSIBILITY=true (не рекомендуется).
А что это за FS1...FS4?
Я нашел (и создал) утилиту Show_Space для LTM, но не могу найти описания назначения новых строк: Что такое "FS1(2,3,4) Blocks"? Что это за "Unformatted Blocks"?
Ответ Тома Кайта:
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
FS1 означает 0-25% свободного пространства в блоке
FS2 означает 25-50% свободного пространства в блоке
FS3 означает 50-75% свободного пространства в блоке
FS4 означает 75-100% свободного пространства в блоке
Неформатированные блоки (unformatted blocks) - это блоки, доступные для немедленного использования (до реальной отметки максимального уровня заполнения), но еще не содержащие никаких данных. Когда таблица говорит: "Я заполнилась", мы переносим в таблицу группу блоков из-за HWM, и они будут неформатированными, пока вы их не используете.
Еще один вопрос
Я видел ваш ответ на этот вопрос раньше, но я все равно не понимаю: Как получается, что Total Blocks не равно Unformatted Blocks + FS1 Blocks + FS2 Blocks + FS3 Blocks + FS4 Blocks + Full Blocks + System Block (в данном примере, у меня остается еще 660 блоков).
SQL> call show_space('TCOM_PERFORMANCE_DATA');
Unformatted Blocks ..................... 784
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 225
Full Blocks ..................... 172,410
Total Blocks............................ 174,080
Total Bytes............................. 1,426,063,360
Total MBytes............................ 1,360
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 317,576
Last Used Block......................... 2,048
Ответ Тома Кайта:
Есть блоки, которые сервер Oracle использует для управления вашими блоками.
У меня с DBMS_SPACE проблема
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Oct 19 15:30:14 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> select name from v$database;
NAME
---------
EK_PLSHD
SQL> show user
USER is "SYSTEM"
SQL> column object_name format a30
SQL> column object_type format a30
SQL> set lines 120
SQL> select object_name, object_type, status, created, last_ddl_time
2 from dba_objects
3 where object_name = 'DBMS_SPACE'
4 /
OBJECT_NAME OBJECT_TYPE STATUS CREATED
LAST_DDL_
------------------------------ ------------------------------ ------- ---------
---------
DBMS_SPACE PACKAGE VALID 03-JUN-04
19-OCT-04
DBMS_SPACE PACKAGE BODY VALID 03-JUN-04
19-OCT-04
DBMS_SPACE SYNONYM VALID 03-JUN-04
19-OCT-04
SQL> desc dbms_space
PROCEDURE FREE_BLOCKS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
FREELIST_GROUP_ID NUMBER IN
FREE_BLKS NUMBER OUT
SCAN_LIMIT NUMBER IN DEFAULT
PARTITION_NAME VARCHAR2 IN DEFAULT
PROCEDURE UNUSED_SPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
TOTAL_BLOCKS NUMBER OUT
TOTAL_BYTES NUMBER OUT
UNUSED_BLOCKS NUMBER OUT
UNUSED_BYTES NUMBER OUT
LAST_USED_EXTENT_FILE_ID NUMBER OUT
LAST_USED_EXTENT_BLOCK_ID NUMBER OUT
LAST_USED_BLOCK NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
SQL> desc show_space
PROCEDURE show_space
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SEGNAME VARCHAR2 IN
P_OWNER VARCHAR2 IN DEFAULT
P_TYPE VARCHAR2 IN DEFAULT
SQL> select * from dba_tab_privs where table_name = 'DBMS_SPACE';
GRANTEE OWNER TABLE_NAME
------------------------------ ------------------------------
------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
PUBLIC SYS DBMS_SPACE
SYS EXECUTE NO
SQL> select * from dba_tab_privs where table_name = 'SHOW_SPACE';
GRANTEE OWNER TABLE_NAME
------------------------------ ------------------------------
------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
PUBLIC SYSTEM SHOW_SPACE
SYSTEM EXECUTE NO
SQL> exec show_space('ETK_TEXT' , 'DIC' );
BEGIN show_space('ETK_TEXT' , 'DIC' ); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "SYSTEM.SHOW_SPACE", line 20
ORA-06512: at line 1
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Похоже, что не хватает каких-то привилегий на базовую таблицу или представление, но я не знаю, каких; пересоздание пакета с помощью сценариев dbmsutil.sql и prvtutil.plb не помогает: я получаю ту же ошибку.
Буду признателен за любые подсказки.
Ответ Тома Кайта:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_space.htm#1001674
см. раздел "Requirements".
Проблемы связаны не с привилегиями на пакет dbms_space, а с привилегиями на сам объект.
Пожалуйста, не используйте SYS, SYSTEM или любую другую стандартную учетную запись Oracle - используйте для всех задач свои собственные учетные записи.
Использовать или не использовать...
Благодаря вашим объяснениям, я раз и навсегда перешел к использованию локально управляемых табличных пространств. Но я все равно не понимаю:
1) Когда использовать, а когда не использовать uniform extents или autoallocate.
2) Когда я использую autoallocate, размеры выделяемых мне экстентов определяет система. Это странно, поскольку похоже на предсказание системой будущего. Или нет?
3) Autoallocate выделяет экстенты разных размеров. Это похоже на ситуацию с табличными пространствами, управляемыми по словарю. Привет, фрагментация!
Ответ Тома Кайта:
1) Мне нравится autoallocate - это опция для "недалеких".
Если вы точно знаете, какого размера будет объект, вы можете использовать uniform и подобрать правильный размер экстентов.
Или, если вы хотите контролировать рост ТОЧНО и знать, насколько большим будет объект изначально и насколько быстро он будет расти, можно использовать uniform (например: объект будет начинаться с размера 500 Мбайт, мы предполагаем, что он будет увеличиваться на 50 Мбайт в месяц. Можно использовать одинаковые экстенты размером 50 Мбайт и добавлять по экстенту в месяц, и вы сможете предсказать, когда добавится новый экстент).
2) Не совсем так: система говорит, что по мере роста объекта экстенты будут становиться больше.
3) Нет, не совсем - они прекрасно сочетаются друг с другом, поскольку используется всего несколько разных размеров.