Summary of Oracle Database User object configuration and data image-reproduced in notes of the project team Zhou chaoyong

-- Reprinted in notes of Zhou chaoyong of the project team

Subject:
1. Configure table space and various user objects.
2. Create a data image from a real-time database to a backup database.

Environment: Sun Solaris UNIX 9
Oracle 9i

Condition: 1. Two empty databases exist: Live Database and backup database.
2. You have sysdba permissions on the above two databases.
2. the servers of the above two databases can access each other through the TCP/IP protocol.

 

1. Configure table space and various user objects.

Oracle is a database that allows users to freely configure various parameters and spaces.
-You can configure various Oracle initialization parameters in pfile.
Including the number of rollback segment, the specific rollback segment, and the output settings of the SQL log file.
-You can freely allocate data spaces for various objects.
Including dB file, segment, block, tablespace, and table.

Oracle storage space is divided into physical storage space and logical storage space, similar to Windows operating system disk management.

-Data Object carrier: DB File
-Basic storage unit: Block
-Data Object Space Configuration basic unit: Segment
-Carrier of all user objects: tablespace

Database concepts
========================================================== ========================================
Datafile physical multiple data files can be used as the storage space of tablespace
Segment physical must use the continuous space on the data file
The default value of block physical is 8 KB.

Tablespace logical is created on data file. Multiple data files can be used.
Table logical is created on tablespace.
MV log logical is created on tablespace.
MV logical is created on tablespace.
Index logical is created on tablespace.
View logical is created on system tablespace
Sequence logical is the object for creating a user

Strored procedure
Logical is the object for creating a user

The user has the default tablespace and temporary tablespace,
Defined in create user statement

* The MV is a materialized view.

1) create a tablespace

When you create the carrier of the user object and tablespace, Oracle will create (if it does not exist) and allocate data file.
In this article, we use the segment space management auto function.

I. E .:
Log on to the database with sysdba and run the following SQL:

Drop tablespace ts_xxx
Including contents and datafiles;
Create tablespace ts_xxx
Logging
Datafile '/Oracle/oradata/dbname/ts_xxx01.dbf' size 512 M Reuse
Autoextend on
Next 5120 K
Minimum extent 512 K
Segment space management auto
Default storage (
Initialize 512 K
Next 512 K
Pctincrease 0
Minextents 8
Maxextents Unlimited
);

The above script will be in the/Oracle/oradata/dbname/directory (created when the database is created .) Create ts_xxx01.dbf.
If ts_xxx already exists in the Database, Oracle will delete this ts_xxx, including all the content and data files created on it, ts_xxx01.dbf.
The size of the initialized data file is 512 MB, which can be reused after the tablespace is dropped.
The segment is automatically initialized to 512 K and automatically increases according to the tablespace configuration. The next segment will be 5120 K + 512 K.

2) create a user
Before creating a table, you must create a user.
In Oracle, all user objects belong to a scheme, which is a collection of all user objects of a user.
I. E .:
Log on to the database with sysdba and run the following SQL:

Drop user op cascade;
Drop role oprole;

Create role oprole;
Grant dBA, create session to oprole;

Create user op identified by oppwd
Default tablespace ts_xxx
Temporary tablespace ts_temp;
Grant oprole to OP;

Grant unlimited tablespace to OP;

The above script creates a user OP (with the password oppwd) and the role oprole.
If a user already exists before the script is run, all user objects in the corresponding theme will be deleted first, and the user will also be deleted.
Create a new user. The user's default tablespace is ts_xxx, and the temporary tablespace is ts_temp (custom creation when the tablespace is created ).
DBA and create session permissions will be granted to oprole. The unlimited tablespace in DBA cannot be granted
A role. Therefore, you must assign this role to the user op and then assign the unlimited tablespace to the user op independently.

3) create a table
Log on to the database with the user op and run the following SQL:

Alter table tb_xxx drop primary key cascade;
Drop table tb_xxx cascade constraints;

Create Table tb_xxx
(
Xxx_no varchar2 (9 byte) not null,
Xxx_id varchar2 (3 byte) not null,
Xxx number (13,2 ),
Xxx_dt date default sysdate
)
Tablespace ts_xxx
Pctused 40
Pctfree 10
Initrans 1
Maxtrans 255
Storage (
Initial 10 m
Minextents 1
Maxextents 2147483645
Pctincrease 0
Buffer_pool default
)
Logging
Nocache
Noparallel;

The preceding script is used to create table: tb_xxx. If the table already exists, delete it first. If the table has a primary key, the primary key is deleted first.
The preceding table is created on the tablespace named tb_xxx.

Alter table tb_xxx add (
Constraint idx_tb_xxx primary key (col_a, col_ B)
Using Index
Tablespace ts_index
Pctfree 10
Initrans 2
Maxtrans 255
Storage (
Initial 3 m
Minextents 1
Maxextents 2147483645
Pctincrease 0
));
The above script is used to create the table's primary key: col_a, col_ B, which is created on idx_tb_xxx and the index idx_tb_xxx is created on tablespace ts_index.
Index idx_tb_xxx is automatically generated when the above script is run. Tablespace ts_index should be created when tablespace is generated.

Alter table tb_xxx add (
Constraint idx_tb_xxx_unq unique (col_a, col_ B)
Using Index
Tablespace ts_index
Pctfree 10
Initrans 2
Maxtrans 255
Storage (
Initial 64 K
Minextents 1
Maxextents 2147483645
Pctincrease 0
));
Similar to creating a primary key, the preceding script is used to create the unique keys of the Table: col_a and col_ B. The primary key is created on index idx_tb_xxx_unq,
The index idx_tb_xxx_unq is created on tablespace ts_index.
Index idx_tb_xxx_unq is automatically generated when the above script is run. Tablespace ts_index should be created when tablespace is generated.

 

4) Create an index
Log on to the database with the user op and run the following SQL:

Create index idx_tb_xxx on tb_xxx
(Col_a, col_ B)
Logging
Tablespace ts_index
Pctfree 10
Initrans 2
Maxtrans 255
Storage (
Initial 10 m
Minextents 1
Maxextents 2147483645
Pctincrease 0
Buffer_pool default
)
Noparallel;
The preceding script is used to create the index idx_tb_xxx for col_a and col_ B, which is created on tablespace ts_index.

 

5) create a music video log
Oracle records all operations on the corresponding table. These records will be recorded in the log table. Data images are stored in
Logs in the log table, and images data operations to the backup database.
I. E .:
Log on to the database with the user op and run the following SQL:

Drop materialized view log on tb_xxx;

Create materialized view log on tb_xxx
Pctused 40
Pctfree 10
Initrans 1
Maxtrans 255
Storage (
Initial 20 m
Minextents 1
Maxextents 2147483645
Pctincrease 0
Buffer_pool default
)
Tablespace ts_xxx
Logging
Nocache
With primary key;
The above script will create a log on tb_xxx. If the log already exists, delete it first. The log is created on tablespace ts_xxx.

 

 

6) create a sequence
Log on to the database with the user op and run the following SQL:

Drop sequence sq_xxx;

Create sequence sq_xxx
Minvalue 1
Max value 99999999
Start with 1
Increment by 1
Cache 20
Cycle;

The preceding script is used to create sequence sq_xxx. If the sequence exists, delete it first.
The initial value of this sequence is 1, the maximum value is 99999999, and the step size is 1. Each time Oracle executes sq_xxx.nextval
20 sequence caches are generated in the memory of the database server. After the sequence reaches the maximum value, it returns to the initial initialization value.

 

7) create a view
Log on to the database with the user op and run the following SQL:

Create or replace view vw_xxx
Select * From tbl_xxx;

Run the preceding script to create view vw_xxx.

8) create a stored procedure:
Log on to the database with the user op and run the following SQL:

Create or replace package pk_xxx
...
/

Create or replace package body pk_xxx
...
/

In the preceding script, "/" indicates that an SQL statement is completed and executed. Run the preceding script to create a stored procedure.
The stored procedure includes package and package body. You can create an abstract interface definition in the package,
In the package body, the specific definitions of each abstract interface in the package body are established, that is, the abstract interface in the package is implemented.

The package body must correspond to the functions and procedure in the package.
If the corresponding package or package body exists before the package or package body is created, delete the package and create it again.

9) cancel some permissions of a user
Log on to the database with sysdba and run the following SQL:

Revoke DBA from oprole;
Revoke unlimited tablespace from Op;

The above script will cancel the DBA permission of the role bkop_role and the unlimited tablespace permission of the user bkop.

 

 

 

2. Create a data image from a real-time database to a backup database.
There are multiple ways to create data backup. Using materialized view for data mirroring is convenient, efficient, and secure.

Principles of using materialized view for data mirroring:
Reads the log records of the real-time database and backs up the real-time data to the backup database at a custom time.

1) similar to creating a real-time database, use sysdba to create a tablespace to accommodate the materialized view.

2) create a user similar to creating a user in a real-time database:
Log on to the database as sysdba and run the following SQL statement:

Drop user bkop cascade;
Drop role bkop_role;

Create role bkop_role;
Grant dBA, create session to bkop_role;

Create user bkop identified by bkoppwd
Default tablespace ts_xxxmv
Temporary tablespace ts_temp;
Grant bkop_role to bkop;

Grant unlimited tablespace to bkop;
The above script creates a user bkop (password: bkoppwd) and the role bkop_role.
If a user already exists before the script is run, all user objects in the corresponding theme will be deleted first, and the user will also be deleted.
Create a new user. The user's default tablespace is ts_xxxmv, and the temporary tablespace is ts_temp (custom creation when the tablespace is created ).
DBA and create session permissions will be granted to bkop_role. The unlimited tablespace in DBA cannot be granted
A role. Therefore, you must assign this role to the user bkop and then assign the unlimited tablespace to the user bkop independently.

3) Create a database connection:
Log on to the database as sysdba and run the following SQL statement:

Drop public database link frombktolive;

Create public database link frombktolive
Connect to OP identified by oppwd using 'livedatabasename ';

The preceding script is used to create a database link from the backup database to the real-time database.

4) create a Materialized View
Log on to the database with user bkop and run the following SQL:

Drop materialized view tb_xxx;

Create materialized view tb_xxx
Pctused 40
Pctfree 10
Initrans 1
Maxtrans 255
Storage (
Initialize 256 m
Minextents 1
Maxextents 2147483645
Pctincrease 0
Buffer_pool default
)
Tablespace ts_xxxmv
Logging
Nocache
Noparallel
Refresh fast
With primary key
Using default rollback segment
As
Select * From tb_xxx @ frombktolive;
After running the preceding script, Oracle returns the table structure of the corresponding table in the real-time database,
And data is directly copied to materialized view. The created table structure includes data definition,
And primary key definition (with primary key ). The corresponding index constraint is created on the default tablespace ts_xxxmv.

5) Create an index on the Materialized View:
Log on to the database with user bkop and run the following SQL:

Drop index idx_tb_xxx;

Create index idx_tb_xxx on tb_xxx
(Col_a, col_ B)
Logging
Tablespace ts_xxxmv
Pctfree 10
Initrans 2
Maxtrans 255
Storage (
Initial 10 m
Minextents 1
Maxextents 2147483645
Pctincrease 0
Buffer_pool default
)
Noparallel;
After running the above script, the index of the table tb_xxx about (col_a, col_ B) will be generated on the materialized view,
The index idx_tb_xxx is generated on tablespace ts_xxxmv.

 

6) create a stored procedure
Log on to the database with user bkop and run the following SQL:

Create or replace package pk_inserting ing
Type bk_mv is ref cursor; -- Materialized View table name set
Procedure sp_make (refreshtype varchar2 );
End pk_grouping ing;
/

Create or replace package body pk_inserting ing is
-- Primary Stored Procedure
Procedure sp_make (refreshtype varchar2) is
Cursor bk_mv is -- Materialized View table information
Select table_name from user_tables order by table_name;
V_mirror bk_mv % rowtype;
Begin
Open bk_mv;
Loop
Fetch bk_mv into v_mirror;
Exit when bk_mv % notfound;
Dbms_mview.refresh (v_).table_name, refreshtype );
End loop;
End;
End pk_grouping ing;
/

The preceding script is used to create the package and package body of the stored procedure.
One procedure sp_make (refreshtype varchar2), used to traverse the user bkop of the backup database
And perform materialized view refresh one by one. The refreshtype is 'F' (FAST)
Or a variety of 'C' (complete), usually choose fast, just to update the real-time data image to the backup database.
Complete copies all data from the real-time database to the backup database, which takes a lot of time and space.

You can log on to the backup database as bkop and run exec pk_mongoing.sp_make ('F ')

You can also create a task in the Unix crontabs table to run the image processing at a specific time point.

7) cancel some permissions of a user
Log on to the database with sysdba and run the following SQL:

Revoke DBA from bkop_role;
Revoke unlimited tablespace from bkop;

The above script will cancel the DBA permission of the role bkop_role and the unlimited tablespace permission of the user bkop.

 

 

A supplement to running the SQL script:
You can add the spool and prompt commands before and after SQL.
Generate log files in the current SQL _path running directory. It is easy to analyze the SQL running results.

 

Spool tb_xxx.log

Prompt
Prompt creating table tb_xxx
Prompt =
Prompt
... Specific SQL statement for creating tb_xxx...
Spool off

After the preceding script is run, an ASCII file tb_xxx.log is generated. The content is
Creating table tb_xxx
====================================
... The result of running the table creation statement is successful or failed.

 

Zhou chaoyong contact: YORCK_ZHOU@HOTMAIL.COM

My contact: LIGANG1000@HOTMAIL.COM