Shulou(Shulou.com)06/01 Report--

Basic operations of oracle database;

Creating Tablespace Plugging Database Creating User Creating Data Table (Transaction) Index View Sequence Synonyms

First, create a table space.

example;

create tablespace school //create tablespace school

2 datafile '/oracle/app/oracle/oradata/school01.dbf' //Specify the location of the tablespace file

3 size 10m autoextend on; //Specify size, space can be expanded

Resize Tablespaces

1. Resize

SQL> alter database datafile

2 '/oracle/app/oracle/oradata/school01.dbf'

3 resize 80m;

The database has changed.

2, add files

SQL> alter tablespace school

2 add datafile

3 '/oracle/app/oracle/oradata/school02.dbf'

4 size 20m autoextend on;

Tablespace has changed.

Tablespace permissions

SQL> alter tablespace school read only; //modify to read only

Tablespace has changed.

SQL> alter tablespace school read write; //permissions modified to read write (default)

Tablespace has changed.

Delete Tablespace

SQL> drop tablespace school including contents;

Tablespace deleted.

II. Plugging database (CDB and PDB conversion)

SQL> show con_name //View current container

SQL> show pdbs; //Query all containers of the database

Convert current database CDB to PDB

CDB: Default database

PDB: Container Database

SQL> alter pluggable database orclpdb open; //modify pluggable database orclpdb open state

Pluggable database has changed.

SQL> alter session set container=orclpdb; //Switch session to PDB under CDB

Session has changed.

SQL> shutdown immediate //shutdown pluggable database in PBD

Pluggable database is closed.

SQL> startup //Open pluggable database in PBD

Pluggable database is open.

Switch PDB to CDB

SQL> alter session set container=cdb$root; //switch session to CDB

Third, create users

The creating user must specify a default table space

Create a user in CDB in version 12C, username format: c##username

Create a user in PDB, username format: username

SQL> create user c##jack

2 identified by abc123 //Specify password

3 default tablespaces users //specify default tablespaces

4 temporary tablespace temp //Specify temporary tablespaces

5 quota unlimited on users; //no quota

change user password

SQL> alter user c##jack identified by jack123;

delete user

SQL> drop user c##jack cascade;

IV. Create data tables (insert data, transaction)

SQL> create table info

2 (

3 id number(4),

4 name varchar2(10),

5 score number(5,2),

6 riqi date

7 );

V. Business

In Oracle, transactions are enabled by default without committing. Therefore, when performing data manipulation, be sure to remember to submit, or set it to automatic submission. Otherwise, data cannot be written to the hard disk.

SQL> insert into info values (1,'lisi',80,to_date('2018-08-26','yyyy-mm-dd'));

SQL> set autocommit on; //Set autocommit, rollback invalid

VI. Index

B-tree index

create index name on table name (column name)

Unique index/non-unique index

create unique index name on table name (column name)

inverted index

create index name on table name (column name) reverse

bitmap index

create bitmap index name on table name (column name)

other index

create index index name on table name (upper)//upper case function index

view index

select index_name,index_type,table_name,tablespace_name from user_indexes;

View index related information

select index_name,table_name,column_name from user_ind_columns where index_name like 'EMP%';

re-indexing

alter index name rebuild;

alter index index name rebuild tablespace table space

Merge index fragments

alter index index name coalesce;

delete the index

drop index name

VII, View

View creation should pay attention to permissions

SQL> create view vinfo as select from info; //create view

SQL> select from vinfo; //query view

SQL> drop view vinfo; //drop view

materialized view

The first step is to grant permissions to the c##jack user through the administrator account

SQL> grant create materialized view to c##jack; //create materialized view permission

SQL> grant query rewrite to c##jack; //query, rewrite permissions

SQL> grant create any table to c##jack; //create all tables permission

SQL> grant select any table to c##jack; //query all table permissions

Materialized view logs need to be created before creating materialized views

create materialized view log on info;

Create materialized views

SQL> create materialized view mtview

2 build immediate //create immediate new data

3 refresh fast //refresh data

4 on commit //submit

5 enable query rewrite //enable query rewrite

6 as

7 select * from info;

SQL> drop materialized view mtview; //delete materialized view

Eight, sequence.

Self-adding columns in oracle need to be created separately and then invoked when inserting data.

create sequence

SQL> create sequence id_seq

2 start with 10 //Initial value

3 increment by 1 //increment

4 maxvalue 1000 //max

5 cycle//pulmonary circulation

6 cache 50; //cache

Call sequence when inserting data

SQL> insert into info values (id_seq.nextval,'tom',80,to_date('2018-04-10','yyyy-mm-dd'));

Query sequence current value

SQL> select id_seq.currval from dual;

Change Sequence

SQL> alter sequence id_seq cache 30;

View sequence information

SQL> select sequence_name,increment_by,cache_size from user_sequences;

deleted sequences

SQL>drop sequence id_seq;

IX, synonyms

Commonly known as alias, nickname, can improve data security reliability.

Private synonyms: only available to the current user

Public synonyms: available to all database users

SQL> create synonym pr_info for info; //create private synonym

SQL> create public synonym pub_info for info; //create public synonym

Delete synonyms

SQL> drop synonym pr_info;

SQL> drop public synonym pub_info;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.