Oracle Table Space

Create table spaces and more

Select Tablespace_name from DBA_TABLESPACES;--DBA permissions user queries the table space in the database
SELECT * from All_tables where tablespace_name= ' tablespace_name ';--query table space for tables, pay attention to uppercase
Select Tablespace_name,table_name from User_tables where table_name= ' tb_employee ';--the table space in which the query table is located

--When creating a user, define the default tablespace, set the user's default tablespace and temporary table space, and set the user-created data object to be only 3m, set the initial account to be locked
Create user Db_user identified by password default tablespace tablespace_name temporary tablespace tablespace_name Quota 3 M on tablespace_name account lock;
Create tablespace tablespace_name datafile ' E:\tablespace_name.dbf ' size 20m uniform size 128k;--build table space
CREATE TABLE table_name (DEPTNO number (4), dname varchar2 (+), loc varchar2 () tablespace tablespace_name;--specify tablespace for table
CREATE INDEX index_name on table_name (column_name) tablespace tablespace_name;--Specify table space for index
Alter user Db_user default tablespace tablespace_name;--Modify Users
Users with unlimited tablespace permissions can operate on any tablespace (Grant unlimited tablespace to Db_user). Users who do not have unlimited tablespace permissions need to have quotas in the target table space to operate on non-default tablespaces, that is, to allocate space to the user in the target tablespace or unrestricted space.
Alter user Db_user Quota 100m| | Unlimited on Tablespace_name;

Change the state of a table space

When a tablespace is established, the tablespace is in the online state, and the tablespace is accessible, and the table space is readable and writable, that is, you can query the table space's data, and you can execute various statements in the table space. However, in the case of system maintenance or data maintenance, you may need to change the state of the tablespace. Typically, this is done by a privileged user or DBA.
Alter Tablespace Tablespace_name offline;--1. Take the table space offline
alter tablespace tablespace_name online;--2. Make tablespace online   br> when you create a tablespace, the tablespace can read and write, and if you do not want to perform update,delete,insert operations on that tablespace, you can modify the tablespace to read-only
Alter tablespace Tablespace_name Read only ;--3. Read-only tablespace
(modified to writable is alter tablespace Tablespace_name read write;)
We give you an example of the read-only feature:
SELECT * from All_tables where Tablespace_name= ' Tablespace_name ';--know tablespace_name, show all tables included in the Tablespace, query tables in tablespace, pay attention to uppercase
Select Tablespace_name, table_name from User_tables where table_name= ' tb_employee ';--know table_name, view the table that belongs to that table space
by 2. We can know System.tb_ Employee is in system This table space, now we can change system to read-only, but we will not succeed, because system table space, if it is normal table space, then we can set it as read-only, to give you a demonstration, can enhance understanding.
Alter tablespace Tablespace_name Read only;
Alter tablespace Tablespace_name read write;--make tablespace readable and writable

Typically, a privileged user or DBA is operating, and if it is another user operation, the user is required to have the drop tablespace system permission.
Drop tablespace tablespace_name including contents and datafiles;--Delete table space
Description: Including contents indicates that all database objects for the space are deleted when the tablespace is dropped, and datafiles means that the database files are also deleted.

Extending table Space

Table spaces are made up of data files, and the size of the tablespace is actually the size of the data file after it is added. Then we can imagine, assuming that the table emp stored on the Tablespace_name table space, the initial size is 2m, when the data is full of 2m space, if you are inserting data into the EMP table, this will show an insufficient space error.
Case Description:
1. Set up a tablespace fj01
2. Create a plain table in the table space mydment its structure and dept
3. Add data to the table insert into mydment SELECT * from dept;
4. What happens when there is a problem that cannot be extended at some point?
5. Expand the tablespace to add more storage space to it. There are three ways to do this:
1. Add Data files
Alter tablespace fj01 add datafile ' E:\fj01.dbf ' size 20m;
2. Increase the size of the data file
Alter tablespace tablespace_name ' E:\fj01.dbf ' resize 20m;
It is important to note that the size of the data file should not exceed 500m.
3. Set the automatic growth of the file.
Alter tablespace tablespace_name ' E:\FJ01.DBF ' autoextend on next 10m maxsize 500m extend management local;

Delete the tablespace and all its contents while deleting its corresponding data file:
Drop tablespace tablespace_name including contents and datafiles;
Delete the tablespace and all its contents, and delete the data files that they correspond to, as well as the referential integrity constraints related to tablespaces in other table spaces:
Drop tablespace tablespace_name including contents and datafiles cascade constraints;

Oracle Table Space