1. 查出PRODUCTS表的区间基本信息
SELECT segment_name,tablespace_name,extent_id,bytes,file_id,block_id FROM dba_extents WHERE owner='SYSTEM' AND segment_NAME='PRODUCTS';
------>
SEGMENT_NAME | TABLESPACE_NAME | EXTENT_ID | BYTES | FILE_ID | BLOCK_ID | |
1 | PRODUCTS | SYSTEM | 0 | 65536 | 1 | 82648 |
2. 根据file_id查出其所在的文件名。
SELECT NAME FROM v$datafile WHERE file#=&file_id;
输入file_id=1
------>
NAME | ||
1 | /AFC_TEST/oradata/afctest/system01.dbf |
SELECT DISTINCT NAME FROM v$datafile order by name;
------->
NAME | |
1 | /AFC_TEST/oradata/afctest/sysaux01.dbf |
2 | /AFC_TEST/oradata/afctest/system01.dbf |
3 | /AFC_TEST/oradata/afctest/undotbs01.dbf |
4 | /AFC_TEST/oradata/afctest/users01.dbf |
5 | /AFC_TEST/oradata/sy_data_operation01.dbf |
6 | /AFC_TEST/oradata/sy_data_operation02.dbf |
7 | /AFC_TEST/oradata/sy_data_parameter01.dbf |
8 | /AFC_TEST/oradata/sy_data_statis01.dbf |
9 | /AFC_TEST/oradata/sy_data_statis02.dbf |
10 | /AFC_TEST/oradata/sy_data_ud_consume01.dbf |
11 | /AFC_TEST/oradata/sy_data_ud_consume02.dbf |
12 | /AFC_TEST/oradata/sy_data_ud_sale01.dbf |
13 | /AFC_TEST/oradata/sy_data_ud_sale02.dbf |
14 | /AFC_TEST/oradata/sy_idx_operation01.dbf |
15 | /AFC_TEST/oradata/sy_idx_operation02.dbf |
16 | /AFC_TEST/oradata/sy_idx_parameter01.dbf |
17 | /AFC_TEST/oradata/sy_idx_space01.dbf |
18 | /AFC_TEST/oradata/sy_idx_ud_consume01.dbf |
19 | /AFC_TEST/oradata/sy_idx_ud_consume02.dbf |
20 | /AFC_TEST/oradata/sy_idx_ud_sale01.dbf |
21 | /AFC_TEST/oradata/sy_idx_ud_sale02.dbf |
3.根据所在的文件名、块大小及数据初始块id计算出改表在表空间中的具体位置。
SELECT BLOCK_SIZE*&block_id FROM dba_tablespaces WHERE tablespace_NAME='SYSTEM';
输入block_id=82648
------------>
BLOCK_SIZE*82648 | |
1 | 677052416 |
------------》
在文件SYSTEM 的645.6875MB 处