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

Oracle tablespace

Tablespaces are logical components of a database. Physically, database data is stored in data files.

Logically, a database is stored in a tablespace, which consists of one or more data files

Logical structure of database

Logical structures in oracle include tablespaces, segments, extents and blocks.

Explain that the database is made up of tablespaces, which in turn are made up of segments, which in turn are made up of extents, and

Zone is such a structure made up of oracle blocks, which can improve the efficiency of data.

Tablespaces are used to logically organize the data of the database. A database logically consists of one or more tablespaces

The following functions can be achieved through tablespaces:

1. Control the disk space occupied by the database

2. Dba can deploy different data types to different locations, which is beneficial to improve the performance of Imax O, and at the same time

Facilitate administrative operations such as backup and recovery

Create a tablespace

Tablespaces are created using the create tablespace command, and it should be noted that, in general, tablespaces are established

It is executed by a privileged user or dba. If another user is used to create a tablespace, the user needs to have a create tablespace

System permissions of

Establish a data tablespace

After setting up the database, it is best to set up your own tablespace in order to manage the tables.

SQL > create tablespace data01 datafile'/ oracle/datafile/data01.dbf' size 10m uniform 128k

Create tablespace data01 datafile'/ oracle/datafile/data01.dbf' size 10m uniform 128k

*

ERROR at line 1:

ORA-02180: invalid option for CREATE TABLESPACE

The error area is uniform 128k, and the zone size is set incorrectly.

SQL > create tablespace data01 datafile'/ oracle/datafile/data01.dbf' size 10m

Tablespace created.

View tablespace information

[oracle@aliyun_test oracle] $ll datafile/data01.dbf

-rw-r- 1 oracle oinstall 10493952 Sep 27 09:54 datafile/data01.dbf

Use data tablespaces (if there is no tablespace, it will be placed in the system tablespace by default)

Create a table and data01 the tablespace to the created table

SQL > create table mydept (deptno number (2), dname varchar2 (14), loc varchar2 (13)) tablespace data01

Table created.

How to query the created tablespace? Query the table information by dab_tablespaces. Check the table structure and field description first.

SQL > desc dba_tablespaces

Name Null? Type

-

TABLESPACE_NAME NOT NULL VARCHAR2 (30)

BLOCK_SIZE NOT NULL NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NOT NULL NUMBER

MAX_EXTENTS NUMBER

MAX_SIZE NUMBER

PCT_INCREASE NUMBER

MIN_EXTLEN NUMBER

STATUS VARCHAR2 (9)

CONTENTS VARCHAR2 (9)

LOGGING VARCHAR2 (9)

FORCE_LOGGING VARCHAR2 (3)

EXTENT_MANAGEMENT VARCHAR2 (10)

ALLOCATION_TYPE VARCHAR2 (9)

PLUGGED_IN VARCHAR2 (3)

SEGMENT_SPACE_MANAGEMENT VARCHAR2 (6)

DEF_TAB_COMPRESSION VARCHAR2 (8)

RETENTION VARCHAR2 (11)

BIGFILE VARCHAR2 (3)

PREDICATE_EVALUATION VARCHAR2 (7)

ENCRYPTED VARCHAR2 (3)

COMPRESS_FOR VARCHAR2 (12)

View table empty data01:

SQL > select * from dba_tablespaces where tablespace_name='DATA01'

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR

- -

DATA01 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO

You can query the details of the tablespace in detail and clearly

Modify the state of the tablespace

When a tablespace is established, the tablespace is in the online state, and the tablespace is accessible and read-write.

You can query the data in the tablespace and execute various statements in the tablespace. But in system maintenance or data

During maintenance, you may need to change the state of the tablespace. In general, it is operated by privileged users or dba.

1. Take the tablespace offline

Alter tablespace data01 offline; data01 is the tablespace name

2. Bring the tablespace online

Alter tablespace data01 online

3. Set the tablespace to read-only

When creating a tablespace, the tablespace can be read and written, if you do not want to perform update,delete,insert on that tablespace

Then you can change the tablespace to read-only tablespace

Alter tablespace data01 read only

Examples are as follows:

The above has used the table space on top of the table mydept, so insert data into it

SQL > insert into mydept values (1 recordings xiaohuangjia wuhan`)

1 row created.

Then set the tablespace to read-only

SQL > alter tablespace data01 read only

Tablespace altered.

Then insert data into table mydept to see if it is successful

SQL > insert into mydept values (2 to Xiaobaiqi and Shanghai)

Insert into mydept values (2 recordings xiaobaiqi penciled shanghai`)

*

ERROR at line 1:

ORA-00372: file 5 cannot be modified at this time

ORA-01110: datafile 5:'/ oracle/datafile/data01.dbf'

An error indicates that the data file for the tablespace cannot be modified

How to restore it, just make the tablespace readable and writable again, as shown below

SQL > alter tablespace data01 read write

Tablespace altered.

And then insert data into it.

SQL > insert into mydept values (2 to Xiaobaiqi and Shanghai)

1 row created.

Data inserted successfully

Delete tablespace

In general, it is operated by a privileged user or dba. If it is operated by another user, you need to have drop tablespace system permission.

Drop tablespace 'tablespace name' including contents and datafiles

Description: including contents means to delete all database objects in a tablespace while deleting that tablespace, while datafiles

Indicates that the database file is also deleted

SQL > drop tablespace data01 including contents and datafiles

Tablespace dropped.

Indicates that the delete tablespace operation is complete. Let's check whether it has been deleted.

SQL > select * from dba_tablespaces where tablespace_name='DATA01'

No rows selected is not queried by the dba_ tables table, that is, it has been deleted

[oracle@aliyun_test ~] $ll / oracle/datafile/

Total 0 can see that the data file has also been deleted

1. Know the name of the table space and display all the tables included in the table space

Create the tablespace data01 again to use

SQL > create tablespace data01 datafile'/ oracle/datafile/data01.dbf' size 10m

Tablespace created.

Create a table to use the tablespace

SQL > create table mydept (deptno number (2), dname varchar2 (14), loc varchar2 (13)) tablespace data01

Table created.

Then how do you query what tables use it by the tablespace name data01?

The table used is all_tables

SQL > desc all_tables

Name Null? Type

-

OWNER NOT NULL VARCHAR2 (30)

TABLE_NAME NOT NULL VARCHAR2 (30)

TABLESPACE_NAME VARCHAR2 (30)

You can query a field with a tablespace_name, so do the following

SQL > select tablespace_name,table_name from all_tables where tablespace_name='DATA01'

TABLESPACE_NAME TABLE_NAME

DATA01 MYDEPT

Query the table used and the corresponding tablespace

As above, you can also know the tablespace if you know the table name.

SQL > conn scott/redhat@test

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott@test

SQL > select tablespace_name,table_name from user_tables where table_name='MYDEPT'

TABLESPACE_NAME TABLE_NAME

DATA01 MYDEPT

The following uses the system user query

SQL > select tablespace_name,table_name from all_tables where table_name='MYDEPT'

TABLESPACE_NAME TABLE_NAME

DATA01 MYDEPT

Through 2, you can learn that the scott.emp table is on the system tablespace. If you change the tablespace system to read-only

But it will not succeed, because system is a system tablespace, if it is a normal tablespace, it can be changed to read-only

Extended tablespace

The table space is made up of data files. The size of the table space is actually the size of the added data files. It is assumed that the table emp is stored in the

On the data01 tablespace, the initial size is 2m. When the data is over 2m, if you insert the data into the table emp, the error of insufficient space will be displayed.

So you need to expand the tablespace?

1. Add data files

Alter tablespace data01 add datafile'/ oracle/datafile/data02.dbf' size 20m

SQL > alter tablespace data01 add datafile'/ oracle/datafile/data02.dbf' size 20m

Tablespace altered.

[oracle@aliyun_test ~] $ll / oracle/datafile/

Total 30736

-rw-r- 1 oracle oinstall 10493952 Sep 27 14:05 data01.dbf

-rw-r- 1 oracle oinstall 20979712 Sep 27 14:06 data02.dbf

2. Increase the size of the data file

Alter tablespace data01'/ oracle/datafile/data01.dbf' resize 20m

3. Set the automatic growth of files

Alter tablespace data01'/ oracle/datafile/data01.dbf' autoextend on next 10m maxsize 500m

How do I migrate data files for tablespaces?

1. Determine the tablespace where the data file is located

Select tablespace_name from dba_data_files where file_name='/oracle/datafile/data01.dbf'

TABLESPACE_NAME

-

DATA01

After you know the tablespace, then take the tablespace state offline

2. Take the tablespace offline

Ensure the consistency of the data files and change the tablespace to the offline state

Alter tablespace data01 offline

3. Use the command to move the data file to the specified target location

Sql > host move / oracle/datafile/data01.dbf / test/oracle/datafile/data01.dbf

4. Execute the alter tablespace command and rename the data file

After you physically move the data, you must also execute the alter tablespace command to logically modify the database file

Alter tablespace data01 rename datafile'/ oracle/datafile/data01.dbf' to'/ test/oracle/datafile/data01.dbf'

5. Make the tablespace online

After the data file is moved, in order for the user to access the tablespace, it must be changed to the online state

Alter tablespace data01 online

At this point, the data file for migrating the tablespace is complete.

Display tablespace information

Query the data dictionary view dba_tablespaces to display information about the tablespace

Select tablespace_name from dba_tablespaces

Display the data files contained in the tablespace

Query data dictionary view dba_data_files to display the data files contained in the tablespace

Select file_name,bytes from dba_data_files where tablespace_name=' table space name'

Other tablespaces

In addition to common data tablespaces, there are other types of tablespaces

1. Index tablespace

2. Undo tablespace

3. Temporary tablespace

4. Non-standard block tablespace

Integrity of data

Data integrity is used to ensure that the database complies with certain business and logical rules. In oracle, data integrity can use constraints.

Triggers and applications (procedures, functions) are implemented in these three methods, because constraints are easy to maintain and have

The best performance, so as the first choice to maintain data integrity

Constraint

Constraints are used to ensure that database data meets specific business rules. In oracle, constraints include: not null, unique, primary key, foreign key, and check.

Not null: non-empty, if not null is defined on a column, you must provide data for the column when inserting data

Unique: unique key. When a unique constraint is defined, the value of this column cannot be repeated, but it can be null.

Primary key: primary key, used for data that uniquely identifies table rows. When a primary key constraint is defined, this column cannot be repeated and cannot be null. A table can have at most one primary key, but can have multiple unique keys.

Foreign key: foreign key, used to define the relationship between the master table and the slave table. If the foreign key constraint is defined on the slave table, the master table must have a primary key constraint or unique constraint. When a foreign key constraint is defined, the foreign key column data must exist in the primary key column of the master table or be null

Check: used to enforce the conditions that row data must meet, assuming that a check constraint is defined on the sal column and requires the sal column value to be between 1000 and 2000, and an error will be prompted if not between 1000 and 2000

SQL > create table goods (goodid char (8) primary key,-- primary key

2 goodname varchar2 (30)

3 unitprice number (10L2) check (unitprice > 0),-- the unit price must be greater than 0

4 category varchar2 (8)

5 provider varchar2 (30))

6

SQL > create table customer (customerid char (8) primary key,-- Primary key

2 name varchar2 (20) not null,-- is not empty

3 address varchar2 (20)

4 email varchar2 (20) unique,-- does not allow repetition, unique key

5 sex char (4) default 'male' check (sex in ('male', 'female')),-default is male, either male or female

6 cardid char (18))

7

SQL > create table purchase (customerid char (8) references customer (customerid), since this is a foreign key, it must be associated with the field of the main table

2 goodid char (8) references goods (goodid),-as above, the data type must be the same as the main table

3 nums number (5) check (nums between 1 and 30)-the quantity must be between 1 and 30

4

If you forget to establish the necessary constraints when creating a table, you can use the alter table command after creating the table for

The table adds the corresponding constraints. But note: to add a not null constraint is to modify the null constraint, that is, to use keywords

Modify option, while adding other constraints uses the add keyword option

Alter table goods modify gooname not null

Alter table customer add constraint cardunique (constraint name) unique (cardid (char))

Alter table customer add constraint addresscheck check (address in ('East side', 'West side')

Delete constraint

Delete when a constraint is not needed

Alter table table name drop constraint constraint name

An error may be reported when deleting a primary key constraint

Alter table table name drop primary key

This is because if there is a master-slave relationship between two tables, then the primary key constraint of the primary table must be deleted with the cascade option, for example

The alter table table name drop primary key cascade; is equivalent to breaking the primary foreign key relationship.

Display constraint information

1. Display constraint information

By querying the data dictionary view user_constraint, you can display all the constraint information for the current user

Select constraint_name,constraint_type,status,validated from user_constraints where table_name=' table name'

2. Display constraint columns

By querying the data dictionary view user_cons_columns, you can display the table column information that the constraint uses

Select column_name,position from user_cons_columns where constraint_name=' constraint name'

3. Of course, there is also an easy way to use the pl/sql tool to check

You can define constraints in the following ways:

Column level definition

Column-level definitions define constraints while defining columns.

Such as defining primary key constraints in the department table

Create table department (dept_id number (2) constraint pk_department primary key,name varchar (12), loc varchar2 (12))

Keyword: constraint constraint name: pk_department constraint type: primary key

Table level definition

Table-level definition means that after defining all columns, not null constraints can only be defined at the column level after defining constraints.

Create emp2 (emp_id number (4), name varchar2 (15), dept_id number (2), constraint pk_emp primary key (emp_id), constraint

Fk_department foreign key (dept_id) references department (dept_id))

Index is a data object used to speed up data access. Reasonable use of index can greatly reduce the number of Icano, thus improving the performance of data access.

Create an index

A single-column index is an index based on a single column.

Create index index name on table name (column name)

Query by name field: select * from customer where name='sp'; if you check this frequently, you can index the name field and improve the query speed.

Create index name_index on customer (name);-create an index for the name field

Composite index

A composite index is an index based on two or more columns. There can be multiple indexes on the same table, but the combination of columns must be different.

Create index emp_index1 on emp (ename,job); select * from customer where ename='sp' and job='MANAGER'; to create federated indexes

Create index emp_index1 on emp (job,ename)

Principle of use

1. It makes sense to build an index on a large table.

2. Create an index on a column that is frequently referenced on a where sentence or join condition

3. The level of the index should not exceed 4 layers (multi-level indexes should not exceed 4 layers)

Shortcomings of the index

1. To build an index, the system takes up about 1.2 times the hard disk and memory space of the table to hold the index.

2. When updating data, the system must have additional reality to update the index at the same time in order to maintain the consistency of the data and the index.

It is not appropriate to index the following fields

Fields that are rarely or never referenced

2. For logical fields, such as male or female, the query speed is improved at the cost of consuming certain system resources, and the index cannot be established blindly.

Show all indexes of the table

You can have multiple indexes on the same table. By querying the data dictionary views dba_indexes and user_indexes, you can display index information among which dba_indexes

Used to display all the index information of the database, while user_indexs is used to display the index information of the current user

Select index_name,index_type fom user_indexes where table_name=' table name'

Show index column

By querying the data dictionary view user_ind_columns, you can display information about the columns corresponding to the index

Select table_name,column_name fom user_ind_columns where index_name=' index name'

Administrative permissions and roles

Introduction to system permissions

System permission refers to the right to execute a specific type of sql command, which is used to control one or a set of database operations that a user can perform. For example, when users

When you have create table permission, you can create a table in its scheme. When the user has 100 system permissions,

The commonly used ones are:

Create session connection database create table table creation

Create view build View create public sysnonym: key consent word

Create procedure building process, function, package create trigger building trigger

Create cluster clustering

Show system permissions

Oracle provides multiple system permissions, and the higher the version of oracle, the more system permissions are provided.

Select * from system_privilege_map order by name

Grant system permissions

In general, the granting of system permissions is done by dba, and if another user grants system permissions, the user must have grant any privilege

With the with admin option option, and the granted user or role can also grant the system permission to other users

1. Create two user ken,tom

Create user ken identified by redhat

2. Authorize the user to key

Grant create session,Crete table to ken with admin option

Grant create view to ken

3. Authorize tom

Grant create session,create table to tom; this is the system permission to log in.

Revoke create session from tom

Grant permissions to data objects

Roles cannot have with grant option (Grant roles)

Modify only one column (field) of a table

Grant update on emp (sal) to monkey; can only be modified on the field sal in the table

Grant select on emp (ename,sal) to monkey; can be queried on field ename,sal

Grant index on emp to monkey; allows monkey to create indexes on table emp

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.