Oracle DBA Interview Questions PART 2

 Tablespace Management

1. What is a Tablespace?
 A database is divided into Logical Storage Unit called tablespaces.A tablespace is used to grouped related logical structures together

2. What are the Characteristics of Data Files?
A data file can be associated with only one Tablespaces. One or more data files can be use as data of database storage called a tablespace.

3. How do you drop a tablespace, if it has database objects?  

Drop tablespace tablespacename including contents

4. Can we create a tablespace with multiple datafiles in a single stroke?

Yes

5. Can a datafile be associated with two different tablespaces?

No.

6. Can we rename a datafile when the corresponding tablespace is in      

read-only mode?

 No

7. For transportable tablespace what should be the tablespace status?

Read-only

8. How to rename a datafile?

     Tablespace datafile rename: 

    -Take tablespace offline;

              Sys>>  Alter tablespace <TBSNAME> offline;

  -Change the name at OS level

               Linux> cp oldname to newname

-Change the datafile name;

               Sys>> Alter tablepsace <TBSNAME> rename datafile 'oldname' to 'newname';

9. Explain the relationship among Database, Tablespace and Data file? 

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

10. How do you drop a tablespace?  Drop tablespace ts1 including contents and datafiles;

11. What is the procedure for Transportable tablespace migration?

 Transportable Tablespaces (TTS) allows you to copy a set of datafiles for a tablespace on one database and plug that tablespace into a different database.As we noted, you cannot transport a single partition of a table without transporting the entire table. Therefore, we will need to exchange the partition with a stand-alone table temporarily, so that the partition becomes its own table.

12. How to rename the tablespace?

 Sys>alter tablespace <tablesapce name> rename to <tablespace name>

13. How to know the default tablespaces?

  Sys> select * from database-properties what property-name like ‘%default%’;

14. How many datafiles can you add for a tablespace?

 65,536