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

I. user management

1. Create a tablespace

Create tablespace school # specify tablespace name datafile'/ orc/app/oracle/oradata/school01.dbf' # specify data file path size 200m # specify tablespace size autoextend on # set tablespace automatic extension

2. Create a user

Create user c##tom # create user "Tom" identified by abc123 # set user password "abc123" default tablespace school # specify default tablespace "school" temporary tablespace temp # specify default temporary tablespace "temp" quota unlimited on school # No disk quota limit for "school" tablespace password expire; # set user login each time, forcibly change the password

Change user password

Alter user c##tom identified by 123123; # change the user's "tom" password to "123123"

Delete user

Drop user c##tom cascade

3. User authorization

Grant connect,resource to clocked encrypted tomb; # connect is the connection permission; resource is the administrative database permission revoke connect,resource from caterpillar permission; # revoke user authorization (under the sysdba user)

Log in as tom

Method 1:

Sqlplus # enter the username and password!

Method 2:

SQL > conn

Note: tom is bound to the school tablespace, so the written data will be saved in the school file!

4. Commit the transaction (the transaction is enabled by default)

Commit; # Note: oracle manually commits transactions by default. After editing the data, you must use commit to commit rollback; # Note: transaction rollback uses this command set autocommit on; # to set automatic commit. 2.

1. Description

1) Index is one of the database objects, which is used to speed up the retrieval of data, similar to the index of books. Indexing in the database can reduce the amount of data that the database program needs to read when querying the results, just like in books, we can use the index to find the information we want without flipping through the whole book.

2) the index is an optional object built on the table; the key of the index is to replace the default full table scan retrieval method with a set of sorted index keys, so as to improve the retrieval efficiency.

3) the index is logically and physically independent of the relevant tables and data, and when an index is created or deleted, it does not affect the basic table.

4) once the index is established, when performing DML operations on the table (such as inserting, modifying or deleting related operations), oracle will automatically manage the index and delete the index without affecting the table

5) the index is transparent to the user, and the usage of the sql statement remains the same regardless of whether there is an index on the table or not.

6) oracle automatically creates an index on the column when it creates the primary key

2. The principle of index

1) if there is no index, when searching for a record (such as looking for name='wish'), you need to search all records, because there is no guarantee that there is only one wish, you must search all of them.

2) if you build an index on name, oracle will search the whole table once, sort the name values of each record in ascending order, and then build index entries (name and rowid) and store them in the index segment. If the query name is wish, you can directly find the corresponding place.

3) after creating an index, it is not necessary to use it. After oracle automatically counts the information of the table, decide whether to use the index or not. When there is little data in the table, the speed of using full table scan is very fast, so there is no need to use the index.

3. Principles of indexing resumes

1) if there are two or more indexes, one of which is unique and the other is non-unique, oracle will use the unique index and ignore the non-unique index completely

2) include at least the first column of the composite index (that is, if the index is built on multiple columns, the optimizer will use the index only if its first column is referenced by the where clause)

3) small tables do not want resume index

4) B-tree index is suitable for columns with large cardinality, and resume bitmap index for columns with small cardinality.

5) there are many null values in the column, but indexes should be established when querying non-empty records on the column.

6) columns with frequent join queries should create indexes

7) when using create index, put the most frequently queried columns first

8) LONG (variable length string data, up to 2G) and LONG RAW (variable length binary data, up to 2G) columns cannot create indexes

9) limit the number of indexes in the table (it takes time to create the index and increases as the amount of data increases; the index takes up physical space; when the data in the table is added, deleted, and modified, the index is also maintained dynamically, reducing the speed of data maintenance)

4. Index usage (create, modify, delete, view)

Create an index

Create index index_name on table_name (column)

Modify the index

1) rename the index

Alter index index_sno rename to bitmap_index

2) merge indexes (the table will be fragmented in the index after being used for a period of time, and the index efficiency will be reduced. You can choose to rebuild the index or merge the index. It is better to merge the index without additional storage space and at a lower cost.

Alter index index_sno coalesce

3) rebuild the index

Method 1: delete the original index and re-establish the index

Method 2: alter index index_sno rebuild

Delete index

Drop index index_sno

View Index

Select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name = 'tablename'; # View Index

Example:

Create index index_sno on student ('name'); select * from all_indexes where table_name='student'

5. Classification of index

A, B index (default index, which holds sorted index columns and corresponding rowid values)

Description:

1) the most commonly used index in oracle; the B-tree index is a binary tree; the leaf node (bidirectional linked list) contains the index column and the ROWID value pointing to each matching row in the table

2) all leaf nodes have the same depth, so the query speed is basically the same regardless of the query conditions.

3) it can adapt to precise query, fuzzy query and comparison query.

Classification:

UNIQUE,NON-UNIQUE (default), REVERSE KEY (data in the data column is stored in reverse)

Create an example

Create index index_info on info (score); create unique index uni_index_info on info (id); # unique index create index rev_index_info on info (createtime) reverse; # reverse index

Working with scen

When the column cardinality (the number of column values that do not repeat) is large, it is suitable to use the B-number index.

B, bitmap index

Description:

When creating a bitmap index, oracle scans the entire table and establishes a bitmap for each value of the index column (in the bitmap, one bit (bit,0 or 1) is used for each row in the table to identify whether the row contains the value of the index column of the bitmap. If it is 1, the record in which the corresponding rowid resides contains the bitmap index column value. Finally, the bit-to-row ROWID conversion is completed through the mapping function in the bitmap index.

Create an example:

Create bitmap index bt_index_info on info (address)

Use the scene:

Suitable for resume bitmap index for columns with a small base (such as gender, etc.)

C, single-column index and composite index (based on multiple columns)

Note:

That is, if the index is built on multiple columns, the optimizer will use the index only if its first column is referenced by the where clause, that is, it must contain at least the first column of the combined index.

D, functional index

Description:

1) when you often want to access some functions or expressions, you can store them in the index so that the value has been calculated the next time you visit, which can speed up the query.

2) the function index can use either the B number index or the bitmap index; when the function result is uncertain, the B tree index can be used, and when the result is fixed, the bitmap index can be used.

3) len, trim, substr and upper can be used in the functional index (each row returns independent results), but cannot be used such as sum, max, min, avg, etc.

Create column children:

Create index up_index_info on info (upper (name)); # uppercase function index select * from student where upper (name) = 'WISH'; # verify view III. View

1. Definition of view

Views (view), also known as virtual tables, do not take up physical space, which is also a relative concept, because the definition statements of the view itself are stored in the data dictionary. Views are only logically defined. Each time you use it, you just re-execute the SQL.

Views are obtained from one or more actual tables whose data is stored in the database. The tables used to generate the view are called the base tables for that view. One view can also be generated from another view.

The definition of the view is stored in the database, and the data related to this definition is not stored in the database. The data seen through the view is stored in the base table.

The view looks very much like the physical table of the database and operates on it just like any other table. When you modify data through a view, you are actually changing the data in the base table; conversely, changes in the base table data are automatically reflected in the view generated by the base table. For logical reasons, some Oracle views can modify the corresponding base table, while others cannot (only query).

There is also a view: materialized views (MATERIALIZED VIEW), also known as materialized views, snapshots (as mentioned before 8i), which contain data and take up storage space.

There are no restrictions on querying the view, and the operation of inserting / updating / deleting the view will be limited to a certain extent; all operations against the view will affect the base table of the view; to prevent users from indirectly modifying the data of the base table through the view, the view can be created as a read-only view (with the with read only option)

2. The function of view

1) provide a variety of data representations, and the data of the base table can be displayed in front of the user in a variety of different ways in order to conform to the user's habits (main means: using aliases)

2) hide the logical complexity of the data and simplify the query statement, the multi-table query statement is generally more complex, and the user needs to understand the relationship between the tables, otherwise it is easy to make mistakes; if you create a view based on such a query statement, the user can directly make a "simple query" of the view and get the result. This hides the complexity of the data and simplifies the query statement. This is one of the reasons why oracle provides a variety of "data dictionary views". All_constraints is a view with 2 subqueries and 9 tables joined (defined in catalog.sql)

3) execute some queries that must use views. Some queries can only be completed with the help of views. For example, if some queries need to join a grouped table and another table, you can first create a view based on the results of the grouped statistics, and then join this view and another table in the query

4) provide some security guarantees. Views provide a controllable way to allow different users to see different columns without allowing access to sensitive columns, thus ensuring that sensitive data is not seen by users.

5) simplify the management of user rights. Permissions for views can be granted to users without having to grant permissions to users for certain columns in the base table, simplifying the definition of user permissions.

3. General view

1) create a normal view

Grant create view to caterpillar; # Sysdba login to create a view for the user authorizing SQL > create view view_info as select * from info;2) View View

SQL > select * from view_info

3) Delete the view

SQL > drop view view_info

4. Materialized view

1) switch dba identity

SQL > conn / as sysdba

2) Authorization

SQL > grant create materialized view to cantilever created tomb; # Grant permission to create materialized view SQL > grant query rewrite to cased permission tomb; # Grant query and rewrite permission SQL > grant create any table to cached tomb; # Grant permission to create any table SQL > grant select any table to c##tom # Grant any table permission to query 3) create materialized view log SQL > conn # connect tom user SQL > create materialized view log on info 4) create materialized view SQL > create materialized view mtrlview_pro 2 build immediate # create materialized view whether to generate data immediately. Immediate represents true 3 refresh fast # setting to update synchronously with the base table. If this parameter is not added, it is equivalent to snapshot function 4 on commit # enable submission function 5 enable query rewrite # enable query and rewrite function 6 as select * from info;5) delete view

SQL > drop materialized view mtrlview_pro

4. Import and export data drop index up_index_info; # Delete the previously created index, otherwise you cannot change the attribute alter table info modify name varchar2 (11) of the column field; # change the bit width of the name field begin 2 for i in 2.. 100 3 loop 4 insert into info values | | iMagne8 ('2018-10-10), nanjing'); 5 end loop; 6 commit; 7 end; 8 /

Select count (*) from info; / / final view of data volume

1. Data import:

Ho ls / home/oracle # oracle software is compatible with Linux. Use the ho command to follow the liunx command to view the home directory file. Use the specified user import data imp c##tom/abc123 full=y file=/home/oracle/test.dmpselect * from tab; # to view related table information.

2. Export data

Exp c##tom/abc123 file=/home/oracle/test.dmp # specify the user to export data ls / home/oracle/ View verification v. sequence

1. Sequence definition

A SEQUENCE is a sequence number generator that automatically generates sequence numbers for rows in a table, producing a set of evenly spaced numeric values (of type numeric). Does not take up disk space, takes up memory.

Its main purpose is to generate the primary key value of the table, which can be referenced in the insert statement, check the current value through a query, or increase the sequence to the next value.

A sequence is a set of values used by oracle to produce a set of equally spaced values. The sequence is incremental and continuous. The oracle primary key has no self-increment type, so it is common to use the values generated by the sequence as the primary key of a table to achieve self-increment of the primary key. The number of the sequence is not automatically generated when the record is inserted, but must be generated by calling the method of the sequence (usually calling the nextval method). We can also write insert triggers for tables to generate automatically.

2. Create a sequence

SQL > create sequence toy_seq 2 start with 1 # specify initial value 3 increment by 1 # specify increment 4 maxvalue 2000 # specify maximum value 5 nocycle # specify non-cyclic 6 cache 30; # specify cache 30 values, idle wait

3. Add data

Delete from info; # clear the table SQL > insert into info values (toy_seq.nextval,'Sony',999999,to_date ('2018-10-09mm ok')); # insert the value to execute the statement many times, and find that the sequence number corresponding to ID is self-increasing Select * from info; # to check the verification

4. View the sequence

SQL > select toy_seq.currval from dual; # View the value of the current sequence

SQL > select sequence_name,increment_by,cache_size from user_sequences; # to view information about the sequence

5. Change the sequence

SQL > alter sequence toy_seq maxvalue 5000 cycle

6. Delete the sequence

SQL > drop sequence toy_seq

VI. Synonyms

1. Definition

Literally, it means an alias, similar to the function of the view. It's a mapping relationship. Mainly divided into private (unique to users) and public (shared by system users)

2. Management of private synonyms

1) create private synonyms

SQL > grant create synonym to c##tom # dba create synonyms for users SQL > show user; # View current user SQL > create synonym pr_info for info; # set an alias "pr_info" for the table 2) invoke private synonyms

SQL > select * from pr_info; # Note: private synonyms are defined at this time, which are only valid for the current user and cannot be recognized by another user.

3. Management of public synonyms

1) Grant user permissions

SQL > conn / as sysdba # administrators log in to SQL > grant create public synonym to cantilever administrators; # create public synonyms for users authorizing 2) create public synonyms SQL > conn c##tom/abc123 # connect to ordinary users SQL > create public synonym public_sy_info for info;3) View public synonyms SQL > conn / as sysdba # administrators log in to SQL > select * from public_sy_info # you can check after switching dba. There is no info table in dba, which means that common synonyms take effect.

7. Partition table

1. Definition

Store data in different tablespaces based on a field in a table

2. Establish several tablespaces

SQL > show user; # the current user is the system administrator SQL > create tablespace tmp01 # pay attention to the establishment of four tablespaces, namely tmp01, tmp02, tmp03, tmp04 2 datafile'/ orc/app/oracle/oradata/tmp01.dbf' 3 size 100MS SQL > edit # the operation before editing will modify the name of the poop space and dbf file name SQL > / # specify edit operation to repeat operation to create four tablespaces SQL > select tablespace_name from dba_tablespaces / / check whether the tablespace is created successfully

3. Create a data table

SQL > create table sales # create Table 2 (3 sales_id number (4), 4 product_id varchar2 (5), 5 sales_date date 6) 7 partition by range (sales_date) # specify partition 8 by time field (9 partition p1 values less than (to_date ('2018-04-03 copyright copyright mm values less than dd`)) tablespace tmp01, # p1 specify name Less than is less than the specified time; tablespace specifies tablespace 10 partition p2 values less than (to_date ('2018-05-03)) tablespace tmp02, 11 partition p3 values less than (to_date (' 2018-06-03)) tablespace tmp03, 12 partition p4 values less than (maxvalue) tablespace tmp04 13)

4. Insert test data

Insert into sales values should be stored in P1insert into sales values ('2018-03-23); # should be stored in P1insert into sales values (' 2018-04-23); # should be stored in P2 insert into sales values ('2018-05-23) # it should be stored in P3 insert into sales values (1). It should be stored in P4.

5. Query partition verification

SQL > select * from sales partition (P1); # distributed storage according to time, has been stored in P1

VIII. Control documents

1. Definition

The physical structure of the oracle database is recorded in the control file, that is, the location of the database data file and log file is recorded, and a variety of SCN are recorded in the control file, which are used to determine whether the data file and log file are correct. If not, then the database needs to be restored.

2. View control files

3. Backup control files

Alter database backup controlfile to'/ home/oracle/controlfile.bk'

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.