Oracle database replication

Time:2021-4-11

—1.0 query existing users
Select * From Dba_Users

—2.0 get the path of the table space
Select File#, Name From V$datafile;

—3.0 creating and adding table spaces
Select Distinct ‘CREATE SMALLFILE TABLESPACE “‘ || a.Tablespace_Name ||
‘” DATAFILE ‘ || ”” || ‘C:\app\Administrator\oradata’ ||
a.Tablespace_Name || ’01’ || ”” ||
‘size 100m AutoText on next 100m maxsize 8g logging extension management local segment space management auto; ‘create a table space,
‘ALTER TABLESPACE “‘ || a.Tablespace_Name || ‘” DATAFILE ‘ || ”” ||
‘C:\app\Administrator\oradata’ || a.Tablespace_Name || ’02’ || ”” ||
‘size 100m reuse AUTOEXTEND on next 100m maxsize 1g;’increase table space
From Dba_Data_Files a, Dba_Users b
Where a.Tablespace_Name = b.Default_Tablespace
And b.Account_Status = ‘OPEN’;

—4.0 creating users
Select Distinct ‘CREATE USER “‘ || b.Username ||
‘” PROFILE “DEFAULT” IDENTIFIED BY “‘ || b.Username ||
‘” DEFAULT TABLESPACE “‘ || a.Tablespace_Name ||
‘”temporary tablespace” temp “account unlock;’ create user,
‘grant connect,resource,dba,exp_full_database,imp_full_database to ‘ ||
b. User name | ‘;’ authorization statement
From Dba_Data_Files a, Dba_Users b
Where a.Tablespace_Name = b.Default_Tablespace
And b.Account_ Status =’open ‘; — user status open is normal

—5.0 export / import
Export:
1 export the database test completely, the user name system and password manager to D:\ daochu.dmp in
exp System/[email protected] file=d:\XX.dmp full=y Log=;
2 export the tables of system user and sys user in the database
exp System/[email protected] file=d:\XX.dmp owner=(system,sys) Log=;
3 export the tables table1 and table2 in the database
exp System/[email protected] file=d:\XX.dmp tables=(table1,table2) Log=;
4 export the field filed1 of Table 1 in the database starting with “00”
exp System/[email protected] file=d:\XX.dmp tables=(table1) query=” where filed1 like ‘00%'” Log=;
Import:
Imp System/ [email protected] Rows = y Ignore = y Statistics = None Fromuser = His3 Touser = His3 File =C:\ XX.dmp Log =C:\ XX.Log When importing multiple users (xx1, XX2, XX3)

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]