Basic commands for oracle tablespace, basic commands for modifying the data table structure, and oracle Data

Basic commands for oracle tablespace, basic commands for modifying the data table structure, and oracle Data

Basic table space commands

-- Create a tablespace. The initial size is 10 MB, and the maximum size is 5 MB. the maximum size is 50 MB.

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

-- Query the table space address and space name
Select file_name, tablespace_name from dba_data_files order by file_name;

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

-- View information about all tablespaces
Select tablespace_name, status, allocation_type from dba_tablespaces;

-- Query the default tablespace of each user
Select user_id, username, default_tablespace from dba_users;

-- Modify the default tablespace of the database
Alter database default tablespace duan;

-- Modify the tablespace name
Alter tablespace duan rename to duanxiangchao;

-- Delete a tablespace. Only the tablespace records are deleted.
Drop tablespace duan;
-- Delete tablespace, including data files
Drop tablespace duan including contents and datafiles;


-- Use commands to modify the data table structure
-- Alter table


-- Rename a column
Alter table t_user rename column user_email to email;

-- Use the modify keyword to modify the column attribute. If the length of a record is greater than the length of the new modification, an error is returned.
Alter table t_user modify (user_name varchar2 (25 ));
-- Oracle allows you to modify multiple attributes at a time
Alter table t_user modify (user_name varchar2 (30), email varchar2 (45 ));

-- Add a column to the table
Alter table t_user add (remark varchar2 (50 ));

-- Drop column: delete a column in the table
Alter table t_user drop column remark;

-- Alter: Modify the table attributes
Alter table t_user rename to my_user;
/* You do not need to add the column keyword for add and modify, but drop needs.
When you modify a table, the delete operation may be subject to certain constraints. Therefore, you must add column to indicate that a column is to be deleted */

-- Delete a database
Drop table t_user;
-- Delete a database and act on constraints
Drop table t_user cascade constraints;










What table of oracle data needs to be changed using the alter command?

The alter command is used to change the table structure rather than data. DDL Data Definition Language

How to view the table structure using common Oracle commands

The following articles mainly introduce how to view the table structure in common Oracle commands. If you are interested in the actual operating solution of how to view the table structure in common Oracle commands, you can view the following articles to better understand them. EDITDATA table name; modify table field: Alter table name modify (field name type constraints); alter table test modify (addd varchar2 (10) null ); alter table name add (field name type constraints); alter table test add (age varchar2 (5); 1. log on to the system and view the table structure sqlplus in common Oracle commands. Then, enter the system username and password to log on to the other user's conn username/password. 2. create tablespace datafile 'C: \ Space name' size 15 M -- storage path of the tablespace, initial Value: 15 M autoExtend on next 10 M -- the Automatically increasing space value is 10 M permanent online; -- permanent use 3. create user shi -- create username shi identified by scj -- create password scj default tablespace name -- default tablespace name temporary tablespace temp -- temporary tablespace is temp profile default -- by profile file quota unlimited on tablespace name; -- creating a table under a tablespace is unrestricted. 4. create role name identified by password; 5. authorize the role grant create session to role name; -- grant the role the permission to create a session to grant the role name to the user name; -- grant the role to the user 6. grant permissions to the user Grant connect, resource to shi; -- grant all permissions to the shi user grant dba to shi;-Grant the DBA permission to the shi user grant create table to shi; -- Grant the table creation permission to the shi user. select table_name from user_tables; view all tables under the current user 8. select tablespace_name from user_tablespaces; check the tablespace of the current user 9. select username from dba_users; view all user name commands must be logged in with sys as sysdba 10. create table name (id int not null, name varchar2 (20) not null) tablespace name -- the tablespace storage to which the table belongs (initial 64 K -- the initial value of the table minextents 1 -- Minimum extended value maxextents unlimited -- maximum extended value); 11. add the primary key and index alter table users add constraint pk primary key (ID); 12. add the foreign key alter table users add constraint fk_roleid foreign key (roleid) references role (role_id) on delete cascad to the created users table; -- write the column on delete casca in the base table below ...... remaining full text>