oracle - 如果我们在 oracle 中创建表或索引时不添加表空间子句，是否会产生重大影响？

Question

在创建表或索引时，我总是得到在查询中添加tablespace子句的建议。如果我们在创建它们时不使用该tablespace子句，以后会对我们的表产生重大影响吗？

这是我长期以来一直在做的事情。

CREATE TABLE XT_PMB_NOTIFY_UNSUB( 
TXNID NUMBER(15), 
APP_SEQNO NUMBER(15), 
PRIMARY_KEYVAL VARCHAR2(4000) NOT NULL, 
OP_CODE VARCHAR2(15), 
TXN_STATUS VARCHAR2(1), 
CREATE_DT DATE, 
PRIMARY KEY (TXNID) ); 

来自 的推荐DBA。

CREATE TABLE XT_PMB_NOTIFY_UNSUB( 
TXNID NUMBER(15), 
APP_SEQNO NUMBER(15), 
PRIMARY_KEYVAL VARCHAR2(4000) NOT NULL, 
OP_CODE VARCHAR2(15), 
TXN_STATUS VARCHAR2(1), 
CREATE_DT DATE, 
PRIMARY KEY (TXNID) )
TABLESPACE DATA_ENC_TS;

标签： oracleindexingtablespace

Answer 1

The answer is it depends on how your company has defined its tablespace rules.

Oracle users (or schemas) can have one "default tablespace" which you can see by querying the database:

select username, default_tablespace from dba_users;

or if you do not have permission for that and you want to know what it is for the current user only:

select username, default_tablespace from user_users;

Or perhaps this one to see all users that are visible to your current connected user:

select username, default_tablespace from user_users;

According to Oracle documentation (https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm) this is what it means:

Specify the default tablespace for objects that the user creates. If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.

So for your intents and purposes, when you create a table without explicitly using a tablespace at the end it will go to the user's default tablespace in general. If your DBAs tend to not define a default tablespace then it starts to have more serious impacts, because the table will be stored in a global default tablespace or (heaven forbid) it will go to SYSTEM tablespace. That last option would be extremely detrimental to the database health.

Some companies have the habit of assigning different tablespaces for tables and for indexes for instance. In that case, the users can only have one default tablespace, and if you omit the tablespace clause in the create index (or create table) statement, objects will go to the incorrect tablespace.

Now to the consequences of having a table or index in an incorrect tablespace. A tablespace is a collection of one or more physical operating system files (Oracle refers to them as data files). Whenever you create a table or index in a tablespace oracle allocates space in that datafile, which Oracle calls segments. Segments are logical units inside a data file. Keep in mind Oracle further breaks down segments into smaller logical units called extents and blocks, but that is a bit beyond the topic here. If you are interested there is more to read here: https://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm

Let's go back to segments. Segments exist inside datafiles that belong to tablespaces. When you put your object in a tablespace and you want to move it out to a different tablespace, Oracle needs to physically write to files on the OS. And that can be simple if the table is empty, or can be a fair amount of work if it concerns a massive table spanning multiple datafiles or containing gigabytes or terabytes of data. It may mean an application outage is required to fix it.

Oracle provides certain methods to avoid application outages in those scenarios, like for example Online Redefinition (package DBMS_REDEFINITION). But I would hope we can agree that their use can be better leveraged for application migrations and things of the sort.

Using default tablespace settings is fine in many cases, by all means, but if you will allow me perhaps, the rule of thumb for many things Oracle is if you can write code to do something explicitly instead of relying on default values, do yourself and your DBA the favor. In general, the flexibility of relying on it is trumped by even a couple times of facing yourself with a surprise and then being responsible for cleaning it up later.