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:
Regards,
ora-data Team.
