Oracle Database application

One :.Oracle Database application knowledge

Two : Table space and user rights management

  1. Table space :

Table space is an important component of data logical structure , The table space can store all kinds of application objects , As shown in the table , Indexes . Each table space consists of one or more data files

2. The classification of table spaces can be divided into three categories :

Permanent table space

General saving table , View , Process and index data .SYSTEM,SYSAUX,USERS,EXAMPLE, Tablespaces are installed by default

Temporary table space

It is only used to save the short time in the system Data on the activities of , Such as sorting data

Undo tablespace

Used to help roll back uncommitted transaction data , The submitted data cannot be recovered here . Generally, there is no need to create temporary and undo table spaces , Unless you move them to other disks to improve performance .

  1. The purpose of the table space :

(1.) Allocate different tablespaces to different users , Assign different table spaces to different schema objects , It is convenient to operate user data , Management of schema objects ,

(2.) You can create different data on different disks , Good for disk management space , It's good for improving i/o performance , Conducive to backup and data recovery, etc . Usually when it's done Oracle Multiple table spaces are automatically created ,

3. Create table space syntax


DATAFILE ‘filename’[size integer] [k|m]

[qutoextend [off|on]];

Name: Is the name of the table space that needs to be created

Datafile: Specifies one or more data in a table space , Multiple data are separated by commas

Filename: The path and name of the data file

Size: Specify file size , use K Specify kilobyte size , use M Specify megabyte size

Autoextend: Clause is used to automatically expand data files and disable

  1. Create an automatically growing table space worktbs Of SQL The statement is as follows :

Create   tablespace    worktbs

Datafile  ‘D:\oracle\worktbs01.dbf’

Size     10m  autoextend   on;

  1. Delete tablespace :

drop  tablespace   name

including   contents

  1. The syntax for creating a user is as follows :

(1.)CREATE  USER  martin   user name

Identified  by   123456   password

Default   tablespace  worktbs   file name

Temporary  tablespace  temp;

(2. Change Password )

Alter user martin

Identified  by  654321;

3、 ... and : Database authority management

(1.) The syntax for granting permissions is as follows :

Grant  jurisdiction | role    to   user name ;

Example : user martin grant connect and resource Two roles

Grant   connect,resource  to  martin

(2.) The syntax for revoking permissions is as follows :

Revoke  jurisdiction | role   from  user name ;

Remove two roles

Revoke   connect,resource  from   martin;

(3.) Allow users to view emp The records in the table

Grant  select  on  scott.emp   to martin;

(4.) Allow users to update emp Records in

Grant  update  on   scott.emp   to   martin;

By analogy , Delete and query

Four : Create sequence

(1.) Example : Create sequence , From serial number 10 Start , Each time 1, The maximum is 2000, No circulation , In addition, errors will be reported , cache 30 Serial number .

Create   sequence   seq1

Start  with  10

Increment  by  1

Maxvalue  2000

Nocycle   10

Cache  30;

(2.) Change the sequence

Alter  sequence [schema.] sequence_name

[increment  by  integer]

[maxvalue  integer|momaxvalue]

[minvalue  integer|nominvalue]


[cache  integer|nocache];

(3.) Delete sequence : Delete seq1

Drop  sequence  seq1;

(4.) Usage sequence

Select   sys_guid()   from dual;

5、 ... and : The use of synonyms

Private synonyms , And shared synonyms

Create private synonyms :

Create  sysnonym  Synonym name    for   Object name

Create synonyms :sy_emp

Create    synonym  sy_emp   for   a_hr.emp

Create common synonyms :

Crate  public  synonym  name   for  object ;

Create  public  synonym  public_sy_emp  for  emp;

Delete synonyms :

Delete private synonyms

Drop  synonym   Name to delete ;

Delete common synonyms :

Drop public  sysnonym  Name to delete

6、 ... and : Indexes

(1.) Index classification table

Physical classification

Logical classification

Partitioned or non partitioned indexes

Singleton or composite index

B Tree index ( Standard index )

Unique or non unique index

Normal or reverse indexing

Based on functional index

Bitmap index

(2.) Create a normal index

Crate  index  The index name   on  Index table name    Multiple index names    Index space

(3.) Example : stay salgrade In the table , Number the level grade Column creates a unique index ,

Create  unique  index   index_unique_grade  on  salgrade(grade);

(4.) Reverse index example

stay emp The number in the table is empno Column reverse index

Create  index  index_reverse_empno  on  emp  (empno) reverse;

(5.) The advantages of bitmap indexing :

For a large number of timely inquiries , Can reduce response time

Compared to other Indexing Technologies , It takes up less space

Even if the configuration is low , You can also get significant gender

(6.) Delete index :

Drop  index  Index name ;

(7.) Rebuild index

Alter  index  Index name   rebuild  noreverse;

7、 ... and : Partition table

(1.) advantage

Improve query performance of tables

Tables are easier to manage

Easy for backup and recovery

Improve data security

( If the following conditions are met, a distinction table can be built 1. The amount of data is greater than 2G, The division of data is obvious )

(2.) Classification of partition tables

Range partitioning , List partition , Hash partition , Composite partition , Interval partition ( advantage : The division is clear , Detailed ), Virtualization partitions .

(3.) Create interval partition Syntax :

Alter  table   Partition table name    add  partition   Section name   ralue  less  than  (maxrelue);

Oracle More articles on database application

  1. Use Zabbix monitor Oracle database

    Orabbix Introduce monitor Oracle We need to install the database provided by a third party Zabbix plug-in unit , Let's test the famous ones first Orabbix, ...

  2. Oracle Database knowledge summary

    Oracle Database knowledge summary ( Updating ..) 1. Installation and deployment 2. Management and maintenance 3. Data migration 4. Trouble shooting 5. Performance tuning 6.SQL PL/SQL piece 7. Examination and certification 8. Principle system 9. Architecture design ...

  3. Oracle How to optimize a database SQL

    This is the ultimate question , Because the complexity of optimization itself is really difficult to summarize , Most of the time, the optimization method does not use any esoteric Technology , It's just a difference in ideology , And these are likely to lead to huge differences in performance . So sometimes we should first ...

  4. Oracle Database statement encyclopedia

    Oracle Database statement encyclopedia ORACLE Five types of integrity constraints are supported NOT NULL ( Non empty )-- prevent NULL Value into the specified column , Define... On a single column basis , By default ,ORACLE Allow... In any column NULL value . CH ...

  5. Oracle Database upgrade (>

    Environmental Science : RHEL5.4 + Oracle Purpose : Upgrade the database locally to It was concluded that Oracle Database upgrade of different machines : ...

  6. In a hybrid framework Oracle Database restore operation

    In earlier essays <Oracle How to create a database . Backup database and data import and export through-train operation > A brief introduction to Oracle Database backup and restore operation , In this paper, I would like to introduce Oracle Database scripts or ...

  7. Oracle The number of links to the database exceeds the limit

    Test scenarios :Oracle The number of links to the database exceeds the limit ,iServer Is it connected automatically . testing procedure :(1) Set the maximum number of connections to 85,oracle Background processes have 83:(2) Turn on 3 platform iserver(A,B,C)A,B Release tes ...

  8. take Oracle Data writing in the database Excel

    take Oracle Data writing in the database Excel 1. preparation Oracle database "TBYZB_FIELD_PRESSURE" The data in the table is shown in the figure : Excel Templates (201512.xls): 2 ...

  9. Oracle database 11g Introduction and function comparison of different versions

    . Standard edition and enterprise Industry Edition . All of these versions are built using the same common code base , This means that the enterprise's database management software can easily expand from a small single processor server to a multi processor server set Group , Without changing a line of code .Oracle database 11g Enterprise ...

  10. Oracle database , The increase of data 、 Delete 、 Change 、 check

    oracle In the database , The increase of data . Delete . Change . check , adopt SQL Statements for SQL: Structured query language : characteristic : Case insensitive : Strings are enclosed in single quotes : The end of a statement is indicated by a semicolon : Line notes , Add... To the front of the statement "--& ...

Random recommendation

  1. Detailed explanation spring Every jar The role of

    spring.jar  It's a single... With a full release module jar package . But not including mock.jar, aspects.jar, spring-portlet.jar, and spring-hibernate2. ...

  2. linux android Real machine test

    1. Run at terminal lsusb You will find that the results are similar to the following records : Bus 001 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hubBus 002 ...

  3. Some number theory concepts and algorithms —— from SGU261 Talking about

    I haven't been to the blog for a long time , It was too hard to train before , But there are big gains , I think it's necessary to summarize and share them . It used to be a number theory scum ( Primary school junior high school did not study the reason for the competition ), After a topic, I have a deep understanding of some basic algorithms ...

  4. cocos2dx SpriteBatchNode Sprite rendering optimization class

    The purpose is to reduce the rendering times of sprites , Usage is as follows SpriteFrameCache::getInstance()->addSpriteFramesWithFile("person.plist&qu ...

  5. Pressure acceleration -poj-2443-Set Operation

    Topic link : The title mean : Yes n A collection of (n<=1000), Each set has m Number ai(m<=10000,1=<ai<=100 ...

  6. postgresql+mybatis The return value is the database field name

    mybatis return map It's an underline when you're in the middle role_id, user_id Two solutions 1. rename postgresql No humps Rename... With double quotes  SELECT role_id "ro ...

  7. JSP Database connection successful

    <%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding= ...

  8. neutron dhcp ha experiment

    4 Nodes (controller, network,2 compute nodes) 1.0   on the network node 1.1 set –I ‘s/start] on/#start\ ...

  9. _THROW Why? ?

    Looking at /usr/include/........ in .h When defining the function interface in the header file , You can always see a... At the end of the function _THROW, I don't know what it means at the moment , And for some POSIX and ISO C A letter that is not recognized or clearly defined ...

  10. hdu6073[dfs+ Edge deletion ] 2017 Multi school 4

    The definition of bipartite graph is very special , refer to U,V In two parts ,U The vertex degree of must be 2,V There is no limit to the middle vertex . The title is For all matches , The weight of the match = The product of the edge weights of the edges selected in the match , Find the sum of all matching weights . about V Medium ...