Oracle Database Migration Scenarios _oracle

1 in data migration, users first have the right to modify the database, and the table space to create, delete and other rights

For example:

SELECT * from Dba_tab_privs where grantee= ' SCOT '; ---View Scott permissions (SYS user logon)

Display results as:

SELECT * from Dba_role_privs where grantee= ' SCOT '; --View Scott role

Display results as:

(1) If the user is locked through the following statement to unlock the table

Alter user Scott account unlock; --Ching

(2) Granting user rights

Grant Connect to Scott; --Connecting to database permissions Grant
resource to Scott;--granting basic permissions such as creating a table Grant
DBA to Scott;--Granting DBA authority

2 Create a table space

In the strict sense of executing the following commands rather than starting the table space directly

Drop tablespace table space name;

and start creating the table space

Create Tablespace xx_bp;
DataFile ' F:\app\xx_bp.ora '--local location of table space
Size 50M
Autoextend on next 1M;

Attached: datafile for the table space corresponding to the data file, followed by the data file path and data file name

Size is the initial size of the data file

Autoextend on indicates that the data automatically expands as the amount of data increases
In general, when you create a table, you create more than one table space to hold various data, for example, we typically create a history table space (HS), Index table space, and so on. Otherwise, Oracle will point the user to the SYS tablespace by default, and then point to the table space when the user is created.

3 Creating users

Create user username indentified by password tablespace xx_bp,xx_hs,xx_indx;

Attached: identified by to create a user password keyword, followed by the user password

4 authorizing users of the database

Grant Connect,resource to User name

Grant Connect and resource roles to users

To see what roles the current user has

SELECT * from User_role_privs;

5 building data structures, stored procedures, views, sequences

(1) Create a table

CREATE TABLE XX. BP_OPER_DETAIL_TB
(
task_id VARCHAR2 () not NULL,
flow_id number is not NULL,
Task_no VARCHAR2 (%) not n ull,
Flow_node VARCHAR2 (4) Not NULL,
Workitemid number is not NULL,
trans_id VARCHAR2 (a) not NULL,
Trans _no CHAR (6) Not NULL,
Vouch_group VARCHAR2 (a) not NULL,
Teller_no VARCHAR2 (?) Default ',
User_no VARCHAR2 (1 5 is not NULL,
Organ_no VARCHAR2 (a) is not NULL,
AreaCode VARCHAR2 (5) Default ',
Create_time CHAR ' is not null, Checkout_time char not NULL,
Checkin_time char default ',
Suspend_time char default ',
Resume _time CHAR default ',
Trans_time integer default 0,
Release_time integer default 0,
State integer not nul L,
Result VARCHAR2 (a) default ",
Reason VARCHAR2 () default '
)
tablespace xx_bp Pctfree 1 Maxtrans 255 Storage
(
Initial 64K next 8K minextents 1 maxextents Unlimited
);

CREATE INDEX constraint tuning, and so on

ALTER TABLE XX. BP_OPER_DETAIL_TB add constraint BP_OPER_DETAIL_PK primary key
(
task_id, Workitemid, Flow_node
)
Using index tablespace xx_bp pctfree Initrans 2 Maxtrans 255
(
Initial 64K Next 1M minextents 1 Maxextents Unlimited
);

6 Importing Data

Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0007 ', ' VH0000 ', ' 20160420074707 ');
Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0008 ', ' VH0000 ', ' 20160420074729 ');
Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0010 ', ' VH0000 ', ' 20160420074818 ');
Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0012 ', ' VH0000 ', ' 20160420074914 ');
Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0016 ', ' VH0000 ', ' 20160420075055 ');
Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0017 ', ' VH0000 ', ' 20160420075129 ');
Insert into SM_FIELD_PARENT_TB (field_id, Parent_field, Last_modi_date)
VALUES (' VH0021 ', ' VH0000 ', ' 20160420075305 ');
Commit

Finally re-enter the database to verify the new migration database