oracle things and commonly used database object notes and experiments

Posted Jun 16, 20208 min read

1:What is a thing
It is a logical unit in the business, it can guarantee that all operations on the data will either succeed or fail

2:Characteristics of things
Atomicity, consistency, isolation, durability

3:Control of things
Using COMMIT and ROLLBACK to realize transaction control
COMMIT:Submit a transaction, that is, permanently save the modification of the database in the transaction
ROLLBACK:Roll back the transaction, that is, cancel any modifications made to the database

Use AUTOCOMMIT to realize the automatic submission of transactions

1:The meaning of the index
The index is an object of oracel, which is an optional structure associated with the table, provides a way to quickly access data, and improves database retrieval performance

2:The characteristics of the index
Improve query speed
Can index one or more columns of a table
There is no limit to the number of indexes
Index requires disk storage
Index reference is determined by oracle

3:Classification of the index
B-tree index
Unique and non-unique indexes
Reverse key index
Bitmap index
Composite index
Function-based index

Three View
1:The meaning of the view
View:A view is a virtual table that does not occupy physical space. The data in the view is obtained from one or more actual tables.
Materialized view:Materialized view is also called materialized view, contains data and takes up space.

2:The role of the view
Provides another level of table security
Hidden data complexity
Simplified user SQL commands
Isolation base table structure changes
Provide data from another perspective by renaming columns

1:The meaning of the sequence
A sequence is an object used to generate a series of unique numbers. It is usually used to automatically generate the value of a primary key or a unique key.

2:access sequence
NEXTVAL returns the next value of the sequence
CURRVAL returns the current value of the sequence

1:Meaning of synonyms
Synonym is an alias for an existing object

2:The benefits of synonyms
Simplify SQL statements
Hidden object name and owner
Provide public access to objects

3:Types of synonyms
Private synonyms:can only be accessed within its schema, and cannot have the same name as the object of the current schema
Public synonyms:can be accessed by all database users

Six:partition table
1:The meaning of the partition table
Allows the user to divide a table into multiple partitions, this partitioned table is the Oracle partition table

2:The characteristics of the partition table
Users can execute queries and only access specific partitions in the table
Store different partitions on different disks to improve access performance and security
Each partition can be backed up and restored independently

3:Advantages of partition table
Improve table query performance
Tables are easier to manage
Easy to backup and restore
Improve data security

4:The conditions of the partition table
The amount of data is greater than 2GB
There is a clear boundary between the existing data and the newly added data

Experimental part:

Add c##scott account to prepare for follow-up experiment

1:Log in to Oracle as sys

2:Create a test account scott

SQL> create user c##scott identified by scott default tablespace users temporary tablespace temp quota unlimited on users;


SQL> grant connect,resource to c##scott;

4:Import the script file(copy the script to the /opt directory in advance)

SQL> @/opt/test.sql

5:Switch to c##scott user

SQL> conn c##scott/scott


1:Use commit and rollback to achieve transaction control

1):Insert data into the dept table in the scott database

SQL> conn c##scott

SQL> col loc for a10;

SQL> select * from dept;


---------- --------------------------------------- --- ----------





SQL> insert into dept values(50,'a',null);

SQL> insert into dept values(60,'b',null);

SQL> commit;

SQL> select * from dept;

2):Insert the data again, do not submit, perform a rollback operation

SQL> insert into dept values(70,'c',null);

SQL> select * from dept;

SQL> rollback;

SQL> select * from dept;

2:Automatically submit things

SQL> set autocommit on;\\\Automatic submission of things

SQL> insert into dept values(90,'b',null);

SQL> rollback;

SQL> select * from dept;


1:Index creation

SQL> conn c##scott

(1) Create a B-tree index

SQL> create index emp_ename_idx on emp(ename); In the emp table, create a B-tree index for the employee name column

(2) Create a unique index

SQL> create unique index emp_grade_unique_idx on salgrade(grade); In the salgrade table, create a unique index for the level number grade

(3) Reverse key index

In emp, create a reverse key index for employee number empno

SQL> create index emp_deptno_reverse_idx on emp(deptno) reverse;

(4) Bitmap index

SQL> create bitmap index emp_job_bit_idx on emp(job);\\\Create bitmap index for job type column

(5) Function-based index

SQL> create index emp_ename_upper_idx on emp(upper(ename));\\\Create an uppercase function index for the employee name

2:View the index

(1) View the index name, index type, table name, table space name

SQL> SQL> select index_name,index_type,table_name,tablespace_name from user_indexes;

(2) View the index name, table name, index column

SQL> select index_name,table_name,column_name

from user_ind_columns

where index_name like'EMP%';

3:Maintain the index

1):SQL> alter index emp_job_bit_idx rebuild; \\rebuild index

2):SQL> alter index emp_job_bit_idx coalesce; \\ merged index fragment

3):SQL> drop index emp_job_bit_idx; delete index

Three View

Normal view

1:Create a view

(1) Create a table

[oracle@oracle admin]$sqlplus sys/aptech as sysdba

SQL> create table order_master(

orderno number(5) constraint p_ord primary key,

odate date,

vencode number(5),

o_status char(1));

(2) Insert data

SQL> insert into order_master values (1,to_date('2006-01-01','yyyy-mm-dd'),1,'a');

SQL> insert into order_master values (2,to_date('2007-01-01','yyyy-mm-dd'),2,'p');

(3) Create a view

SQL> create view pen_view as

Select * from order_master

Where o_status ='p'; \\This column is used as a condition to create a view and cannot be modified

(4) Query view

SQL> select * from pen_view;

2:Due to defects in the view creation, resulting in incorrect modification, the view cannot be queried, and constraints can be added to the view,

(1) Modify the data through the view(after incorrect modification, the view cannot be queried and the value of the o_status column is modified)

SQL> update pen_view set o_status='d' where o_status='p';

SQL> select * from pen_view;

(2) In order to avoid modifying view errors, use with check option to create check constraints

SQL> create or replace view pen_view as select * from order_master where o_status='p'

with check option constraint penv;

(3) Insert data

SQL> insert into order_master values (3,to_date('2007-01-01','yyyy-mm-dd'),3,'p'); Insert data

(4) If you modify the view, an error will be reported because the check constraint is violated

SQL> update pen_view set o_status='d' where o_status='p';

3:Use read only to create a read-only view to prevent modification of the view

SQL> create or replace view pen_view as select * from order_master with read only;

4:When creating a view, the base table does not exist

(1) Create the wrong view(venmaster table does not exist)

SQL> create force view ven as select * from venmast;

An error will be reported, indicating that the table does not exist. At this time, you can create this table. If you execute a SQL statement against the table, the system will automatically recompile the view, or you can manually recompile the view

(2) Create a venmast table

SQL> create table venmast(

venno number(5) constraint p_ven primary key,

vendate date,

vencode number(5),

ven_status char(1));

(3) Manually compile the view,

SQL> alter view ven compile;

4:Create a view with order by words

SQL> create or replace view pen_view as select * from order_master order by orderno;

5:DML statements and complex views

(1) Query the view through the data dictionary

SQL> select view_name from user_views;

(2) Delete the view

SQL> drop view pen_view;

Materialized view

1:Check whether the query rewrite function of the materialized view is enabled

SQL> show parameter query_rewrite_enabled;

2:Create a materialized view

(1) Bit c##scott account authorization

SQL> conn system\\First link to oracle as system

SQL> grant create view to c##scott; \\ grant scott permission to create views

SQL> grant create materialized view to c##scott; \\ grant scott permission to create materialized view

SQL> grant query rewrite to c##scott; \\ grant permission to scottquery rewrite

SQL> grant create any table to c##scott; \\ grant scott to create table permissions

SQL> grant select any table to c##scott; \\ gives Scott permission to query any table

(2) Create a materialized view log

SQL> conn c##scott\\Login with scott account

Create Materialized View Log

SQL> create materialized view log on dept with rowid;

SQL> create materialized view log on emp with rowid;

(3) Create a materialized view

SQL> create materialized view mtrlview_test

build immediate

refresh fast

on commit

enable query rewrite


select d.dname,d.loc,e.ename,e.job,e.mgr,

e.hiredate,e.sal,d.rowid d_rowid,e.rowid e_rowid

from dept d,emp e

where d.deptno=e.deptno;

(4) Delete the materialized view

SQL> drop materialized view mtrlview_test;


1:Create a sequence

SQL> create sequence toy_seq

Start with 10

Increment by 1

Maxvalue 2000


Cache 30;

2:Use of sequence

(1) Create a table

SQL> create table toys(

Toyid number not null,

Toyname varchar2(20),

Toyprice number);

(2) Insert data

SQL> insert into toys(toyid,toyname,toyprice)

Values (toy_seq.nextval,'twenty',25);

(3) View data

SQL> col toyname for a10;

SQL> select * from toys;

(4) View the current value of the sequence and the next value

SQL> select toy_seq.currval from dual;

SQL> select toy_seq.nextval from dual;

3:Change the sequence

SQL> alter sequence toy_seq

Maxvalue 5000


4:View the sequence

SQL> select sequence_name,increment_by,cache_size

From user_sequences;

5:Delete sequence

SQL> drop sequence toy_seq;


1:Create a private synonym in system mode to access the emp table in scott mode

(1) Log in as system and query the emp table in scott

SQL> conn system

SQL> select * from c##scott.emp;

(2) Create the synonym emp

SQL> create synonym sy_emp for c##scott.emp;

(3) Visit the synonym sy_emp

SQL> select * from sy_emp;

2:Case:Access to the table T in the remote database named orcl

(1) System identity login

SQL> conn system/aptech@orcl

(2) The following statement is to create a table T and add two records(there is no such process in the book, the table T will not exist anymore, so we need to create a table T)

SQL> create table T(

Orderno number(5) constraint p_ord primary key,

Odate date,

Vencode number(5),

_O_status char(1));

(3) Add data

SQL> insert into T values (1,to_date('2006-01-01','yyyy-mm-dd'),1,'a');

SQL> insert into T values (2,to_date('2007-01-01','yyyy-mm-dd'),2,'p');

(4) Create a database link

SQL> create database link dblink_sw_orcl connect to system identified by aptech using'orcl';

(5) Query with the link name just created

SQL> select * from T@dblink_sw_orcl;

(6) Create private synonyms, orcl is the instance name

SQL> create synonym sy_T for T@orcl;

(7) Search with private synonyms

SQL> select * from sy_T;

3:Create a common synonym public_sy_dept for the department table dept in scott mode, so that other users can also use this common synonym

SQL> conn system

SQL> create user c##user1 identified by aptech;

SQL> grant connect,resource to c##user1;

SQL> grant create public synonym to c##scott;

SQL> conn c##scott

SQL> grant select on dept to c##user1;

SQL> create public synonym public_sy_dept for dept;

SQL> conn c##user1

SQL> select * from public_sy_dept;

4:Delete synonyms

SQL> drop synonym system.sy_emp;

SQL> drop public synonym public_sy_dept;

Six:Partition table

1:Establish quarterly sales information in a partitioned manner

SQL> conn sys as sysdba

create table sales


sales_id number,

product_id varchar2(5),

sales_date date not null


partition by range(sales_date)


partition p1 values less than(to_date('2013-04-1','yyyy-mm-dd')),

partition p2 values less than(to_date('2013-07-1','yyyy-mm-dd')),

partition p3 values less than(to_date('2013-10-1','yyyy-mm-dd')),

partition p4 values less than(to_date('2014-01-1','yyyy-mm-dd')),

partition p5 values less than(maxvalue)


2:Insert data

SQL> alter session set nls_date_format ='yy-mm-dd'; \\Set date format

SQL> INSERT INTO SALES VALUES(1,'P001','2013-02-3');

3:View data for a quarter

SQL> select * from sales partition(p1);

4:Delete the data of a quarter(if there is no data in the table, it will prompt 0 rows deleted.)

SQL> delete from sales partition(p1);

5:Check the partition status

SQL> select * from user_tab_partitions;