Oracle Database migration, table space creation, data table creation, and oracle instances
Database migration
For database migration, if the source database version is the same as the target database version, Oracle or pl/SQL import is recommended. if the version is different, you can use SQL import.
Create a tablespace
create tablespace test datafile 'E:\Database\data\test_data.dbf' size 20M autoextend on next 5M maxsize 500M
Test indicates the tablespace name; datafile 'e: \ Database \ data \ test_data.dbf' indicates the physical file of the tablespace; size 20 M indicates the size of the physical file, autoextend specifies that when the data volume exceeds the storage space of the physical file, the file size is automatically increased; on next 5 M specifies that the size of each increase is 5 M; maxsize 500 M the maximum size of the specified tablespace is M
Modify the default tablespace of a Database
alter database default tablespace test
Change the default tablespace to test.
Rename a tablespace
alter tablespace test rename to test_data
Rename the tablespace to test_data.
Delete a tablespace
When deleting a tablespace, you must ensure that the tablespace is no longer referenced by other users.
drop tablespace test_data including contents and datafiles
Delete the tablespace named test_data.
Create a data table
create table student (studentID Number not null,studentName varchar2(20),studentAge number,status varchar2(3),version number default 0)tablespace users
Tablespace users indicates that the table is created in the tablespace users.
The user_tables view can be used to obtain the table information owned by the current user, such as viewing the table space information of student.
select table_name ,tablespace_name from user_tables where lower(table_name)='student'
View the table structure (in the Command window, run the command, and the statement must end with a; sign. Press enter to run the command .)
desc student;
Add columns to a data table
alter table student add(class_id number);
Multiple columns are added at a time, separated by commas (,).
Modify the Data Type of a column
alter table student modify(calss_id varchar2(20))
Delete existing columns
alter table student drop column class_id
Rename a column
alter table student rename column studentID to id
Transfer tablespace
alter table student move tablespace users
Delete A data table
drop table studrnt
It cannot be deleted because of constraints. Executed
drop table student cascade constraints
Special Data Table dual
Dual actually belongs to the System user sys, which can be accessed by users with basic database permissions.
Dual provides the data format of one row and one column, so that various expressions and function operations can output a single row and single column when they are data sources.
select sysdate from dual
Get current date
select 5*4.5+7 result from dual