sybrandb wrote:Unless there are other factors I didn't include in my tests, that is not true.
Import will begin allocating extents in file 1, until it is full, proceed to file 2 until it is full, etc.
You don't have an 'issue'
Everything is working as designed.
Senior Oracle DBA
user640001 wrote:As others have pointed up - of course when you drop a user:
Our one of the TS is having three datafiles, two of them having size of 1000 mb and one 512 mb.
File 1) 1000 mb ---- 74 % used
File 2) 1000 mb ---- 74 % used
File 3) 512 mb ---- 80 % used
Now, i have dropped the user and again imported the same through exp / imp.
After doing that one of the file 3) got empty and File 1) got full, File 2) 88 % used
Why the file 3) got empty ???
does the extents have been released ???
also the fragmentation in the TS got removed ??? will this improve the performance at some level ???
(So, Locally Managed Autoallocate -everything happens to file 7, as the old post I linked to said it would).
SQL> create tablespace LMTA datafile '/u01/app/oracle/oradata/lin10/lmta01.dbf' size 10m; Tablespace created. SQL> alter tablespace LMTA add datafile '/u01/app/oracle/oradata/lin10/lmta02.dbf' size 10m; Tablespace altered. SQL> create table lmtatest (col1 char(5)) tablespace lmta; Table created. SQL> alter table lmtatest allocate extent; Table altered. SQL> alter table lmtatest allocate extent; Table altered. SQL> select file_id from dba_extents where segment_name='LMTATEST'; FILE_ID ---------- 7 7 7
(So, Locally Managed with a Uniform Extent size... files 8 and 9 are used in a round-robin fasion, also as the old post said it would).
SQL> create tablespace lmtu datafile '/u01/app/oracle/oradata/lin10/lmtu01.dbf' size 10m extent management local uniform size 64k; Tablespace created. SQL> alter tablespace lmtu add datafile '/u01/app/oracle/oradata/lin10/lmtu02.dbf' size 10m; Tablespace altered. SQL> create table lmtutest (col1 char(5)) tablespace lmtu; Table created. SQL> alter table lmtutest allocate extent; Table altered. SQL> alter table lmtutest allocate extent; Table altered. SQL> select file_id from dba_extents where segment_name='LMTUTEST'; FILE_ID ---------- 9 8 9
My LMTA tablespace consists of files 6 and 7 -and yet it's the second of those that is being used as the store for new extents, not the first. So when I posted earlier that "file 1 of any tablespace is loaded until it is full and only then are second and subsequent files brought in to play", I was being correct in the generalities but incorrect in the specifics. It pays all of us to re-test things from time to time, in other words! Which file in an autoallocate tablespace gets used as the target for all storage until it is full is, in other words, not deterministic. But whichever one it is that gets picked for the first extent is the one that will stay picked for all subsequent extents until it is full. My apologies for over-stating the case earlier.
SQL> select file#, name from v$datafile; FILE# NAME ---------- ------------------------------------------------------- 1 /u01/app/oracle/oradata/lin10/system01.dbf 2 /u01/app/oracle/oradata/lin10/undotbs01.dbf 3 /u01/app/oracle/oradata/lin10/sysaux01.dbf 4 /u01/app/oracle/oradata/lin10/users01.dbf 5 /u01/app/oracle/oradata/lin10/example01.dbf 6 /u01/app/oracle/oradata/lin10/lmta01.dbf 7 /u01/app/oracle/oradata/lin10/lmta02.dbf 8 /u01/app/oracle/oradata/lin10/lmtu01.dbf 9 /u01/app/oracle/oradata/lin10/lmtu02.dbf 9 rows selected.
It is extremely hard (though not, I will grant, completely impossible) to fragment it in such a way that a large extent allocated to Table A which has now been dropped couldn't be reused by Table B which is acquiring smaller extentsI am talking of table A having dropped smaller (64KB extents in AutoAllocate) and table B growing with larger extents (8MB and then 64MB).
That's round-robin in auto-allocated LMTs from extent 1 onwards, not extent 16 as stated in Tom's piece. Why? Because when you specify a large initial extent size when creating a table in autoallocated LMTs, Oracle skips allocating small 16K and 64K extents and goes straight to 1MB ones (or even 8MB or 64MB if the initial setting is large enough) -at which point the round robin technique kicks in from the get-go. It was a detail that I didn't bother to mention because it wasn't relevant to the point being made, I thought, though I will cheerfully confess to (and apologise for) having completely forgotten about round-robin kicking in after 1MB for small extent allocations. Your reminder from Tom's work is therefore a useful reminder for me.
SQL> create table LMTA2 (col1 char(5)) storage (initial 16M next 16M) tablespace LMTA; Table created. SQL> select file_id from dba_extents where segment_name='LMTA2'; FILE_ID ---------- 7 6 7 6 7 6 7 6 7 6 7 6 7 6 7 6 16 rows selected.
Autoallocate has switched straight to 64MB extents, so we're immediately into round-robin and file 6 is mostly full (896MB) with the residual coming from file 7 (576MB plus an extra 8MB). So that more or less explains why the OP's file 1 is full (or nearly so) and file 2 is not.
FILE_ID BYTES/1024/1024 ---------- --------------- 7 64 6 64 7 64 6 64 7 64 6 64 7 64 6 64 7 64 6 64 7 64 6 64 7 64 6 64 7 64 6 64 7 64 6 64 6 64 6 64 6 64 6 64 6 64 7 8