Oracle notes (5), tablespace Management

1. Create and set tablespaces

Create tablespace mydb2
Datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ test \ mydb4.dbf'
Size 30 m autoextend on next 10 m maxsize unlimited,
'D: \ oracle \ product \ 10.2.0 \ oradata \ test \ mydb5.dbf'
Size 20 m autoextend on next 5 maxsize 100 m;

Alter database datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ test \ mydb4.dbf' resize 40 m;

Alter database datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ test \ mydb5.dbf'
Autoextend on next 10 m
Maxsize unlimited;

PS.
If the file does not exist, datafile requires size. If the file exists, datafile needs to specify the reuse keyword.

2. Change the user's default tablespace and authorize

Alter user kiant default tablespace mydb2;

Select username, default_tablespace from dba_users;

Grant unlimited tablespace, DBA to kiant;

3. Create a table and insert Test Data

Create Table K7 (A1 varchar2 (255 ));

Select * From dba_tables where table_name = 'k7 ';

Insert into K7 select sys_guid () from dual connect by level <=10000;

Commit;

Select * From K7 where rownum <= 5;

4.1 user-level data import and export

Alter tablespace mydb2 read only;
Ho $ exp userid = kiant/love2you @ test file = D: \ 123.dmp;

Alter tablespace mydb2 read write;
Ho $ imp userid = kiant/love2you @ test file = D: \ 123.dmp full = y ignore = y rows = N;
Ho $ imp userid = kiant/love2you @ test file = D: \ 123.dmp full = y ignore = y rows = y buffer = 64000;

PS.
When importing data, you must first import all database structures rows = n before importing the structure and data rows = y.

4.2. Import and export tablespace data

Alter tablespace mydb2 read only;
Ho $ exp userid = kiant/love2you @ test file = D: \ 124.dmp tablespaces = mydb2;

Ho $ imp userid = kiant/love2you @ test file = D: \ 124.dmp tablespaces = mydb2 full = y ignore = y rows = N;
Ho $ imp userid = kiant/love2you @ test file = D: \ 124.dmp tablespaces = mydb2 full = y ignore = y rows = y buffer = 64000;

4.3. tablespace migration

Alter tablespace mydb2 read only;
Ho $ exp userid = 'sys/love2you as sysdba 'file = D: \ mydb2.dmp tablespaces = mydb2 transport_tablespace = y;

Drop tablespace mydb2 including contents;
Ho $ imp userid = 'sys/love2you as sysdba 'file = D: \ mydb2.dmp tablespaces = mydb2 transport_tablespace = y
Datafiles = D: \ oracle \ product \ 10.2.0 \ oradata \ test \ mydb4.dbf, D: \ oracle \ product \ 10.2.0 \ oradata \ test \ mydb5.dbf;

PS.
A. You must connect to the table store as 'sysdb' to restore the table space at the time point or import the table space that can be transferred.
B. when moving a tablespace, the owner of all data objects stored in the source tablespace must exist in the target database. If not, create a user before importing the tablespace.

Reference file:

Database migration methods

[Recommended] tablespace for mobile Oracle

Use the IMP and exp commands of Oracle to import and export data

Procedure for Oracle to export and import data using the transmission tablespace