How to add datafiles to Tablespace in realtime scenario

Hey DBA-mates,

Welcome Again in Oracle Hub.

Adding a data file in Tablespace is a one of the daily task for any dba in any environments or corporations.

And for this also, I have seen almost DBA will search in Google for command and check for the command and process. But during interviews same people will the question about please tell the command for adding datafiles in tablespace.

One thing I don’t understand if a DBA have to search in Google during adding datafiles then why we need to remember and why we should give answer in interviews hehehhehhh.

But boss that is like a process which we can’t change. :D

I have very simple steps to add datafiles in tablespace which will be very important for DBA especially fresher. If they have joined any company and they are not sure about the stuff, they can refer it.

Do you know one reality, even a senior or junior joined any New corp./company, even they know the dba stuff but still they will cross check in Google or with some other colleague and there is nothing wrong in that.

Asking is better than doing mistake and also takes it as positive if you are doing mistake means you are working or learning hehehe yessssss. 

Even I am known for asking the stuff but if never ask twice same things.

Now let’s move to point:

Please follow the below points to add datafiles:

Check for Pre-Check Tablespace details from below command:

select a.tablespace_name, b.total,

       c.available, d.large_extent,

       (b.total - c.available) used,

       round((((b.total - c.available)*100)/b.total),2) pctused

  from (select tablespace_name, (sum(bytes)/1024)/1024 total

          from dba_data_files group by tablespace_name)  b,

       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

          from dba_free_space group by tablespace_name)  c,

       (select tablespace_name, (max(bytes)/1024)/1024 large_extent

          from dba_free_space group by tablespace_name)  d,

       dba_tablespaces  a

 where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = c.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

   and a.tablespace_name = upper ('&tablespace')

/

Output Sample:

Enter value for tablespace: SKU

old  15:    and a.tablespace_name = upper ('&tablespace')

new  15:    and a.tablespace_name = upper ('SKU')

ORA-DATA - Tablespace Usage Report                              September 04, 2017

                                              Largest

                          Total  Available  Available       Used Percent

Tablespace                   MB         MB     Extent         MB    Used

-------------------- ---------- ---------- ---------- ---------- -------

SKU                    306,742     43,845        412    262,897   85.71

                     ----------                       ----------

sum                     306,742                          262,897

SQL>

Now check for all the datafiles for a given tablespace.

SQL>set verify off

col file_id   heading ' File ID'   format 999999

col file_name heading ' File Name' format a50

col MB        heading 'Megabytes'  format 999999

select file_id, file_name, sum(bytes)/1024/1024 MB

  from dba_data_files

 where tablespace_name = upper('&tblspace')

 group by file_id, file_name

/

set verify on

Output Sample:

Enter value for tblspace: SKU

 File ID  File Name                                         Megabytes

-------- -------------------------------------------------- ---------

     600 /ora-data/SKU/data19/sku101.dbf                    1024

     289 /ora-data/SKU/data21/sku102.dbf                     1024

VERY IMPORTANT POINT:

Check for space available in directory where you are adding datafile.

Check for last name of datafile and add datafile name which is not in list.

Example: In my list, last datafile is 102. So, I will add 103 datafile as shown below:

SQL> show user

USER is "SYS"

SQL> alter tablespace SKU  add datafile '/ora-data/SKU/data21/sku103.dbf ' size 1024 M;

SQL>

Again Check for Tablespace details and space available from above command. 

Hope this may useful and helpful. Please let us know for any concerns either in comment box or contact @ora-data.blogspot.com

Some more important Points:

Oracle Data Blocks,Extents and Segments Concepts

Find Oracle blockingsessions

Oracle Data GuardConcept

ORA-00845:MEMORY_TARGET not supported on this system

Oracle Deadlocks

Regards,

ora-data Team.