Data File suffix

Data File suffix

Today, we saw 99 tablespaces in the production system. We decided to add data files to the tablespaces. No problem.

At night, you get cold sweat. Production system RAC. data files are stored in shared storage. The path is correct, but the suffix of the data file is incorrect!

Original data file name:

+ Data/datafile/xxxxxx. dat

Data Files Added by Alibaba Cloud:

+ Data/datafile/xxxxxx. dbf

The business won't be so paralyzed, right ????

Decisive test:

Create an include. dbf and. create tablespace dxmy datafile '/u01/app/oracle/oradata/single1/dxmy001.dat 'size 2 m autoextend off; alter tablespace dxmy add datafile '/u01/app/oracle/oradata/single1/dxmy002.xo' size 2 m autoextend off; alter tablespace dxmy add datafile '/u01/app/oracle/oradata/single1/dxmy003.dbf 'size 4 m autoextend off; create table tab_test (id number, name varchar2 (100 )) tablespace dxmy;

Insert data: declarebegin for I in 1 .. 100000 loop insert into tab_test values (I, I | 'dxmy'); end loop; commit; end;

View the table space: select owner, table_name, NUM_ROWS, BLOCKS * 8192/1024/1024 "Size M", EMPTY_BLOCKS, LAST_ANALYZED from dba_tables where table_name = 'tab _ test '; OWNER TABLE_NAME NUM_ROWS Size M EMPTY_BLOCKS LAST_ANALYZEDLM TAB_TEST 200000 4.859375 0 6:29:07

The data size exceeds 1 MB, indicating that all three data files are in use.
That is to say, if Oracle uses a data file that belongs to the tablespace, it can be used regardless of the suffix.

PS: If no data is occupied by the table, execute the table Analysis Statement.

Exec dbms_stats.get_tables_stats (ownname => '', tabname => '');