This video presents how you can view existing tablespaces, view their datafiles, as well as create and delete a tablespace.
- [Instructor] The physical manifestation of the Oracle database storage system for user data is data files. All database data is stored inside data files. However, when a user creates a table, the users specify which table space that table should be created in. A table space is a collection of data files used for storage. Let's see the names of existing table spaces we already have defined in our database. In order to do that let's open a sqlplus prompt from the sysdba user, and log on to our database.
We can use the dba_tablespaces data dictionary view to view information about existing table spaces. Let's start off by describing the structure of the dba_tablespaces view. Remember, no need for a semicolon at the end of this command, because the describe command is not a sql statement, it's an internal command for sqlplus. Once we know the structure of the dba_tablespaces data dictionary view, we can view the names of existing table spaces by writing select tablespace_name from dba_tablespaces, and we can see we have six table spaces already created in our database.
These are the default table spaces created as part of the Oracle installation. For example, the assistant table space is used to store the Oracle data dictionary itself, so if you lost that table space, you basically lost your database. We can also get a list of data files in our database and which table spaces they belong to by typing select file_name, tablespace_name from dba_data_files, and as we can see, we have a list of data files and their corresponding table space.
We can actually see the physical location, the physical path of those data files. We can create our own table spaces, let's try that out. Let's type create tablespace, and let's give it a name, such as my_data. Remember, this is the name users will have to use when they want their tables to be created inside this table space. We'll need to create one or more data files for this table space, and we can do that as part of the command by specifying datafile, and then the location we want that data file to be created in.
Let's use the same path as all of our other data files, which in my machine is just a local directory on the Linux file system, but remember, in a production Oracle database, this path should be a directory on highly available enterprise storage, and we also have to specify a name for the data file, such as my_data01.dbf, just in case we'd like to add more data files to that table space in the future, and we can do that.
Remember to close the path for the data file with the quotation mark. We also need to specify the size of the data file. Let's start off with 200 megabytes. We can also set the data file to automatically extend in size if it's exhausted all of its existing storage. To do that we specify the autoextend keyword, and then on. And there you have it, we have created our very own table space.
We can actually view it now as well as its data file by typing select file_name, tablespace_name from dba_data_files, and there you have it, our new my_data table space at the bottom of our output, just above the undo table space. We can see that it has one data file associated with it. When we create our table, we can also choose that it will be created in a specific table space.
So let's create a test table and choose that it will be created inside our new my_data table space. We just type create table, and let's give it a name such as my_first_table, let's say it has a single column, column one, and that is a number column. Following the create table command, we just specify tablespace, and my_data, which is the name of the table space we have just created.
Our new table will now be placed inside that table space, and any data that we insert to our my_first_table table, sounds funny, right? Will actually be stored inside the my_data01.dbf data file we associated with our table space. Let's go one step further and see our actual data file inside our Linux file system. Remember, data files are physical manifestations of Oracle storage, so in order to do that, let's exit sqlplus and let's cd into the mnt/san_storage/oradata/oracl directory where all of our data files are stored.
Type in ls, we can see all of our data files including the data file I have just created, my_data01.dbf, it's the third one from the left. We can also see the size of the data files by typing ls -l$ and we can see that the my_data01.dbf data file is approximately 200 megabytes in size.
That's what we specified when we created the table space. Let's see how we can drop our newly created table space. Let's open a sqlplus connection with the sys user. Again, log onto our database, and type drop tablespace my_data, and pay close attention to the following, I also have to specify the keyword including contents and datafiles, that will instruct Oracle to drop the my_data table space even though it has tables created inside it.
The end data files parameter will also delete the data files from the Linux file system. Let's give it a moment, and there we have it, our table space was dropped. Going back to the Linux file system, we see that the data file is no where to be found. Oracle cleaned up after us. So there you have it, a quick overview of how you can create and view table spaces and data files in an Oracle database.
After completing this course, you'll have fundamentals required for installation, configuration, and administration of an Oracle 12c database.
- Database instance and storage
- Instance memory pools
- Instance background processes
- Client connections
- Database storage file types
- Control files and backup files
- Multitenant databases
- Starting and stopping the database
- Installing Oracle 12c software
- Using the developer tools
- Database management