Karan Rajpoot Oracle DBA Blog's

                                       How to Add, Resize, Drop and Rename a datafile in Tablespace In order to create a data file first, need to create a tablespace, To know how to create a tablespace  Click here To demonstrate, I will use USERS tablespace to Add,  Resize, Drop and Rename a data file. First, we can check how many data files are present under USERS tablespace through below command. Command: - select name from v$datafile; We can see from above output only a single data file is present under USERS tablespace. Addition of Datafile: - Command to add a datafile: - alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf' size 50m; The new data file has been added to my USERS tablespace. Resize of a Datafile: - Command to resize the datafile: - alter database datafile '/u01/app/oracle/oradata/prod/users02.dbf' resize 20m; Renaming of a Datafile: - We have a few steps to rename a data file: -

                                                            How to Add/Drop/Rename Tablespace in Oracle  Tablespace: - Tablespaces are the first level of logical origination in your database . Data for Oracle tables, indexes, etc is stored in data files, but never when an object is defined, the object is associated with a file directly. All the time the Oracle objects are located in the tablespaces. The tablespaces are logical concepts and each tablespace is in relation with one or more physical file. So, when an object is created in a tablespace, the data will be stored automatically in the files associated with that tablespace. In our database, we have several tablespaces which store our data: - 1) System Tablespace: - This tablespace is created automatically when the database is created. The SYSTEM tablespace is always online when the database is open. 2) Sysaux Tablespace: - Sysaux tablespace is also a mandatory tablespace and it is also created at the time of data