Oracle Database migration, table space creation, data table creation, and oracle instances

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