Oracle table Space Basic commands, modifying the data table structure basic commands

Table Space Basic Commands

--Create TABLE space initialization size 10M auto grow 5M max 50M

Create tablespace Duan datafile ' F:\oracle\product\10.2.0\oradata\orcl\duan.dbf ' size 10M
Autoextend on next 5M maxsize 50M;

--Querying table space for address and space names
Select File_name,tablespace_name from Dba_data_files order by file_name;

--Create a multiple tablespace containing two data files
Create tablespace multiple_data datafile ' F:\oracle\product\10.2.0\oradata\orcl\multiple_01.dbf ' size 5M,
' F:\oracle\product\10.2.0\oradata\orcl\multiple_2.dbf ' size 5M;

--View information for all tablespaces
Select Tablespace_name,status,allocation_type from Dba_tablespaces;

--Query the default table space for each user
Select User_id,username,default_tablespace from Dba_users;

--Modify the default tablespace for the database
ALTER DATABASE default tablespace Duan;

--Modify table space names
Alter tablespace Duan Rename to Duanxiangchao;

--Delete table space, delete only table space records
Drop tablespace Duan;
--delete table space, including data files
Drop tablespace Duan including contents and datafiles;


--Modify the data table structure with commands
--the command to modify the structure of the data table is alter table


--Named for rename
ALTER TABLE t_user Rename column user_email to email;

--Use the Modify keyword to modify the column's properties to modify the column length if there is a longer record length than the newly modified length, an error will be made
ALTER TABLE T_user Modify (user_name varchar2 (25));
--oracle allow multiple properties to be modified at once
ALTER TABLE T_user Modify (user_name varchar2 (e), email varchar2 (45));

--Add a column to the table
ALTER TABLE T_user Add (Remark varchar2 ());

--drop column To delete one of the columns in a table
ALTER TABLE t_user drop column remark;

--alter modifying the properties of the table itself
ALTER TABLE T_user Rename to My_user;
/* There is no need to add the column keyword for add and modify, and drop is required.
Because a delete operation might target certain constraints when modifying a table, column must be added to indicate that a column is to be deleted */

--Delete Database
drop table T_user;
--Deleting a database, acting on a constraint
DROP TABLE t_user cascade constraints;

Oracle table Space Basic commands, modifying the data table structure basic commands