How table rows are spread in datafiles
                                                 Last update (2008-05-19 12:15:33)
                                                                                                                   Date added (2008-02-25 00:53:43)

Summary
One table is stored in one tablespace. But one tablespace can have one or more datafiles. How many records of the table are stored in each datafile.

Example

1. Create a tablespace with 3 datafiles
CREATE TABLESPACE EXAMPLES DATAFILE 
  '/oraprom1/datafiles/entoles01.dbf' SIZE 100M AUTOEXTEND OFF,
  '/oraprom2/datafiles/entoles02.dbf' SIZE 100M AUTOEXTEND OFF, 
  '/oraprom3/datafiles/entoles03.dbf' SIZE 100M AUTOEXTEND OFF LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;
2. Create table ATHINA.ENTOLES stored in EXAMPLES tablespace.
CREATE TABLE ATHINA.ENTOLES TABLESPACE EXAMPLES 
AS SELECT * FROM ALL_OBJECTS;
3. How many records from table ATHINA.ENTOLES is stored in every datafile?
SELECT NAME FILENAME, COUNT "ROWS", ROUND((RATIO_TO_REPORT(COUNT) OVER ())*100, 2)|| '%' PERC_ROWS 
FROM (SELECT NAME ,t.COUNT FROM
v$datafile d, (SELECT COUNT(*) COUNT ,Dbms_Rowid.rowid_relative_fno(ROWID) FILE#
FROM athina.entoles GROUP BY Dbms_Rowid.rowid_relative_fno(ROWID)) t
WHERE t.FILE#=d.FILE#);
Output
FILENAME                                                  ROWS          PERC_ROWS
-----------------------------------------------------------------------------------
/oraprom1/datafiles/entoles01.dbf                               146581     42.26%
/oraprom2/datafiles/entoles02.dbf                               100499     28.98%
/oraprom3/datafiles/entoles03.dbf                                99763     28.76%
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search