テーブルが存在するファイルの確認

DB

 

(8.0.32)

基本的にテーブルはインデックスも含めてテーブルごとにibdファイルに格納
特にサイズ上限はない模様

(19c)

drop tablespace tbs1 including contents and datafiles;

create tablespace tbs1 datafile '/u01/app/oracle/oradata/orcl/pdb1/tbs1_01.dbf' size 8M autoextend off;

alter tablespace tbs1 add datafile '/u01/app/oracle/oradata/orcl/pdb1/tbs1_02.dbf' size 16M autoextend off;
alter tablespace tbs1 add datafile '/u01/app/oracle/oradata/orcl/pdb1/tbs1_03.dbf' size 24M autoextend off;

 

select * from dba_data_files order by file_id;
select * from dba_tablespaces;

 

drop table tab1 purge;
create table tab1(col1 int, col2 nchar(1000)) tablespace tbs1;

insert into tab1 select row_number() over(order by owner),'dummy' from dba_tab_columns fetch first 1000 rows only;
commit;

col SEGMENT_NAME for a10
col TABLESPACE_NAME for a10

select s.SEGMENT_NAME, s.TABLESPACE_NAME, df.FILE_ID, df.FILE_NAME
from dba_segments s
inner join dba_data_files df
on s.TABLESPACE_NAME = df.TABLESPACE_NAME
where s.segment_name = 'TAB1';

 

 

(15)

/usr/pgsql-15/bin/oid2name
/usr/pgsql-15/bin/oid2name -d test
/usr/pgsql-15/bin/oid2name -d test -i

基本的にテーブルやインデックスはそれぞれ1ファイルに格納

最大サイズは1Gで、それを超えると
41375.1
41375.2
のような名前で新ファイルが追加作成される

 

(2022)

use master
go

alter database test remove file f11;
alter database test remove file f12;
alter database test remove file f13;
alter database test remove filegroup fg1;

alter database test add filegroup fg1;

alter database test add file
(name = N'f11',
 filename = N'c:\data\f11.ndf',
 size = 8mb,
 filegrowth = 0
) to filegroup fg1
;

alter database test add file
(name = N'f12',
 filename = N'c:\data\f12.ndf',
 size = 16mb,
 filegrowth = 0
) to filegroup fg1
;

alter database test add file
(name = N'f13',
 filename = N'c:\data\f13.ndf',
 size = 24mb,
 filegrowth = 0
) to filegroup fg1
;

use test
go
select * from sys.filegroups;
select * from sys.database_files;

drop table tab1;
create table tab1(col1 int, col2 nchar(1000)) on fg1;

insert into tab1 select top 1000 row_number() over(order by object_id),'dummy' from sys.columns;

 

select i.object_id,object_name(i.object_id), i.data_space_id, f.name filegroup_id, df.name file_name, df.physical_name
from sys.indexes i
inner join sys.database_files df
on i.data_space_id = df.data_space_id
inner join sys.filegroups f
on df.data_space_id = f.data_space_id
where object_name(i.object_id) = 'tab1'
;