Oracle Database application
One ：.Oracle Database application knowledge
Two ： Table space and user rights management
- 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
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 .
- 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
（1.）CREATE TABLESPACE NAME
DATAFILE ‘filename’[size integer] [k|m]
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
- Create an automatically growing table space worktbs Of SQL The statement is as follows ：
Create tablespace worktbs
Size 10m autoextend on;
- Delete tablespace ：
drop tablespace name
- 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
（2.） Change the sequence
Alter sequence [schema.] sequence_name
[increment by integer]
（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
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
（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
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);
- 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,http://www.smartmarmot.com/product/orabb ...
- 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 ...
- 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 ...
- 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 ...
- Oracle Database upgrade (10.2.0.4->188.8.131.52)
Environmental Science : RHEL5.4 + Oracle 10.2.0.4 Purpose : Upgrade the database locally to 184.108.40.206 It was concluded that Oracle Database upgrade of different machines :http://www.cnblogs.com/jyzha ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 "--& ...
- 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. ...
- 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 ...
- 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 ...
- 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 ...
- Pressure acceleration -poj-2443-Set Operation
Topic link : http://poj.org/problem?id=2443 The title mean : Yes n A collection of (n<=1000), Each set has m Number ai(m<=10000,1=<ai<=100 ...
- 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 ...
- JSP Database connection successful
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding= ...
- 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\ ...
- _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 ...
- 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 ...