Logo MurCode
Oracle Новое сообщение

Свободное место в ТП

led_yurik
Дата: 21.10.2015 11:15:36
Есть интересная ситуация. БД Oracle EE 11.2.0.4, ASM, Oracle Linux 6.6

Натурный эксперимент:
В ТП есть свободное пространство, около 80Гб.
SQL> select round(sum(bytes)/1024/1024/1024) from dba_free_space where tablespace_name='DWH_DATA_STAGE';

ROUND(SUM(BYTES)/1024/1024/1024)
--------------------------------
                              80


Создаю пустую таблицу
SQL> create table test1.t1 tablespace DWH_DATA_STAGE as select * from dba_objects where 1=2;

Table created.

пытаюсь выделить экстентов на 40Гб для этой таблицы и получаю ошибку
SQL> alter table test1.t1 ALLOCATE EXTENT (size 40g);
alter table test1.t1 ALLOCATE EXTENT (size 40g)
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 4096 in tablespace DWH_DATA_STAGE


SQL>

Но если запрошу 30Гб, ошибки нет:
SQL> alter table test1.t1 ALLOCATE EXTENT (size 30g);

Table altered.

SQL> select round(sum(bytes)/1024/1024/1024) from dba_free_space where tablespace_name='DWH_DATA_STAGE';

ROUND(SUM(BYTES)/1024/1024/1024)
--------------------------------
                              50

SQL>

Т.е. у нас еще 50Гб свободно, пробуем еще выделить 30Гб и получаем ошибку, но 5Гб выделяет
SQL> alter table test1.t1 ALLOCATE EXTENT (size 30g);
alter table test1.t1 ALLOCATE EXTENT (size 30g)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST1.T1 by 4096 in tablespace DWH_DATA_STAGE

SQL> alter table test1.t1 ALLOCATE EXTENT (size 5g);

Table altered.

SQL> alter table test1.t1 ALLOCATE EXTENT (size 5g);
alter table test1.t1 ALLOCATE EXTENT (size 5g)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST1.T1 by 4096 in tablespace DWH_DATA_STAGE


SQL> alter table test1.t1 ALLOCATE EXTENT (size 2g);

Table altered.
...

SQL>

Так можно продолжать уменьшать параметр size, при выделении extent-ов, пока при любом значении не будут появляться ошибки
SQL> alter table test1.t1 ALLOCATE EXTENT (size 100m);

Table altered.

SQL> alter table test1.t1 ALLOCATE EXTENT (size 100m);
alter table test1.t1 ALLOCATE EXTENT (size 100m)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST1.T1 by 512 in tablespace DWH_DATA_STAGE

SQL> alter table test1.t1 ALLOCATE EXTENT (size 65m);
alter table test1.t1 ALLOCATE EXTENT (size 65m)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST1.T1 by 512 in tablespace DWH_DATA_STAGE


SQL> alter table test1.t1 ALLOCATE EXTENT (size 64m);

Table altered.

SQL> alter table test1.t1 ALLOCATE EXTENT (size 64m);

Table altered.
...
SQL> alter table test1.t1 ALLOCATE EXTENT (size 5k);
alter table test1.t1 ALLOCATE EXTENT (size 5k)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST1.T1 by 64 in tablespace DWH_DATA_STAGE

SQL>

SQL> select round(sum(bytes)/1024/1024/1024) from dba_free_space where tablespace_name='DWH_DATA_STAGE';

ROUND(SUM(BYTES)/1024/1024/1024)
--------------------------------
                              39

SQL>

Т.е. в ТП довольно много свободного пространства, но сегменты не могут расширяться.

Пока не могу сообразить в чем проблема, можете подсказать?

Еще немного информации по ТП
TABLESPACE_NAME                  GB_ALLOC    GB_FREE    GB_USED   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ----------
DWH_DATA_STAGE                        192         39        153         79        192

+

SQL> SELECT a.tablespace_name,
  2         ROUND (a.bytes_alloc /1024/ 1024 / 1024) gb_alloc,
  3         ROUND (NVL (b.bytes_free, 0) /1024/ 1024 / 1024) gb_free,
  4         ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024/1024)
  5            gb_used,
  6         100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  7         ROUND (maxbytes / 1048576/1024) MAX
  8    FROM (  SELECT f.tablespace_name,
  9                   SUM (f.bytes) bytes_alloc,
 10                   SUM (
 11                      DECODE (f.autoextensible,
 12                              'YES', f.maxbytes,
 13                              'NO', f.bytes))
 14                      maxbytes
 15              FROM dba_data_files f
 16          GROUP BY tablespace_name) a,
 17         (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
 18              FROM dba_free_space f
 19          GROUP BY tablespace_name) b
 20   WHERE a.tablespace_name = b.tablespace_name(+) and b.tablespace_name= 'DWH_DATA_STAGE';

TABLESPACE_NAME                  GB_ALLOC    GB_FREE    GB_USED   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ----------
DWH_DATA_STAGE                        192         39        153         79        192

SQL>
led_yurik
Дата: 21.10.2015 11:27:41
led_yurik,

Еще забыл уточнить, эта БД находится в режиме snapshot standby
Ручной гранат
Дата: 21.10.2015 11:29:01
Глупое предположение, но у нас подобное бывало, когда физически место на диске кончалось, а квоты не были достигнуты (или были неограниченными).
Прошу прощения, если предлагаю глупости.
led_yurik
Дата: 21.10.2015 11:31:33
Ручной гранат,

Файлы уже расширены до максимального своего размера, ну и про квоты на ASM я не слышал.
led_yurik
Дата: 21.10.2015 11:35:13
Ручной гранат
Глупое предположение, но у нас подобное бывало, когда физически место на диске кончалось, а квоты не были достигнуты (или были неограниченными).
Прошу прощения, если предлагаю глупости.

И еще одно замечание.
65мб не может выделить, на множество раз по 64мб выделяет!

SQL> alter table test1.t1 ALLOCATE EXTENT (size 65m);
alter table test1.t1 ALLOCATE EXTENT (size 65m)
*
ERROR at line 1:
ORA-01653: unable to extend table TEST1.T1 by 512 in tablespace DWH_DATA_STAGE


SQL> alter table test1.t1 ALLOCATE EXTENT (size 64m);

Table altered.

SQL> alter table test1.t1 ALLOCATE EXTENT (size 64m);
SeaGate
Дата: 21.10.2015 11:57:23
led_yurik,

led_yurik
65мб не может выделить, на множество раз по 64мб выделяет!

Похоже на фрагментацию: общее пространство большое, но для конкретного extent-а не хватает.
extent_management для DWH_DATA_STAGE какой? Если не dictionary, то приведите полный DDL tablespace.
Что-то не вижу сходу подходящего скрипта. Есть такой Script to Detect Tablespace Fragmentation (Doc ID 1020182.6), но я смотрю, что он file_id никак не учитывает и не будет он корректно работать для табличных с множеством файлов.
led_yurik
Дата: 21.10.2015 12:07:22
SeaGate,

EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

+

CREATE TABLESPACE DWH_DATA_STAGE DATAFILE 
  '+DGROUP2/dwh02/datafile/dwh_data_stage.300' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2/dwh02/datafile/dwh_data_stage.301' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 8G,
  '+DGROUP2' SIZE 8G AUTOEXTEND ON NEXT 128G MAXSIZE 8G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
led_yurik
Дата: 21.10.2015 12:13:03
Если будет вопрос по квоте, то я выдал пользователю UNLIMITED TABLESPACE

SQL> select * from dba_sys_privs where grantee='TEST1';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST1                          UNLIMITED TABLESPACE                     NO
TEST1                          CREATE TABLE                             NO
Alex URS
Дата: 21.10.2015 12:17:03
second cause
led_yurik
Дата: 21.10.2015 12:42:09
SeaGate
led_yurik,

led_yurik
65мб не может выделить, на множество раз по 64мб выделяет!

Похоже на фрагментацию: общее пространство большое, но для конкретного extent-а не хватает.
extent_management для DWH_DATA_STAGE какой? Если не dictionary, то приведите полный DDL tablespace.
Что-то не вижу сходу подходящего скрипта. Есть такой Script to Detect Tablespace Fragmentation (Doc ID 1020182.6), но я смотрю, что он file_id никак не учитывает и не будет он корректно работать для табличных с множеством файлов.


Вывод этого запроса.

TABLESPACE_NAME                # OF EXTENTS      TOTAL BYTES
------------------------------ ------------ ----------------
DWH_DATA_STAGE                        66127   42,408,345,600

Да, я согласен, фрагментация есть и похоже очень высокая.

Например в ТП, уже нет свободных кусков размером больше 5Мб

SQL> Select * from dba_free_space where bytes >= 4*1024*1024 and tablespace_name = 'DWH_DATA_STAGE';


TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
DWH_DATA_STAGE                        129      87808    4194304        256          129
DWH_DATA_STAGE                        124     170752    4194304        256          124
DWH_DATA_STAGE                        119     379904    4194304        256          119

SQL>

Но почему и эти экстенты не выделяются?