Orace tablespace extension operation

oracle-11gtablespaces

Could somebody clarify whether tablespace expansion procedure in Oracle 11g database requires any downtime or could impact availability of the database? My understanding that this can be done "on the fly" w/o downtime or restricted-mode (at least when we just allocate free space from physical disk)… but I guess there could be some exclusions caveats to this?

Best Answer

There shouldn't be any impact adding datafiles to an existing tablespace.

Easiest is to let oracle manage the files, make sure you have DB_CREATE_FILE_DEST set:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_BASE%\oradata\';

Now the only thing to do if your existing datafiles are full or hitting the max autoextend limit is:

alter tablespace USERS add datafile;

where USERS is the name of the tablespace.

Related Solutions

Estimating tablespace size

Are you using Oracle 7? If not, please make sure to always use current versions of documentation. Oracle is already on 12C, and while some concepts still the same, a huge part of features has changed.

You actually won't get column size. This is why it is only an "estimate". You need to have an idea of what you are going to need, and then you can use the package dbms_space with the create_table_cost procedure to get an idea of space required.

For example:

I need to create a table with 3 columns (val1, val2, val3), and I know each one is going to take up 4bytes (3 of literals, like abc, and the leading byte that varchar2 takes). I also estimate that this table will have approximately 1000 rows. Now just think about cartesian graph:

row_count
^
|
|
|
|-----------> avg_row_len

and use the following to estimate the size:

 set serveroutputon
    DECLARE
      estimated_used_size   NUMBER;
      allocated  NUMBER;
    BEGIN
        DBMS_SPACE.create_table_cost (
        tablespace_name => 'USERS',
        avg_row_size    => 12,
        row_count       => 1000,
        pct_free        => 10,
        used_bytes      => estimated_used_size,
        alloc_bytes     => allocated);

      DBMS_OUTPUT.put_line ('the table (TS=USERS): will use =' || estimated_used_size ||' bytes  to be allocated =' ||  allocated);
end;
/

Regarding your last question, to estimate the size of an existing table, you can check the view dba_segments (also to query index and other objects size).

Example:

SQL> SELECT bytes/1024/1024 "Size in MB" FROM dba_segments WHERE segment_name = 'TABLE_NAME_HERE' AND segment_type = 'TABLE';

“Server not open” / “Drop tablespace” catch-22 situation

No, it is not circular, alter database open is the 3rd step.

If you have a backup of that datafile, then restore and recover it.

If you do not have a backup, then do what the post you referenced tells you. Since your database is already mounted, you can skip step 1.

If the database is down, mount it.

STARTUP MOUNT;

Offline drop the datafile.

ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

If the database is at mount, open it.

ALTER DATABASE OPEN;

...