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

On October 14, 2018, I wrote about the installation of Oracle12C and took a preliminary look at the databases and instances in the Oracle architecture. From which we know:

A database is a collection of data on disk, located in one or more files on a database server that collects and maintains relevant information. The database consists of a variety of physical and logical structures, and the table is the most important logical structure in the database. The table consists of related rows and columns that contain data.

The files that make up the database are mainly divided into two categories: database files and non-database files. The difference between the two is what kind of data is stored. Database files contain data and metadata, while non-database files contain initial parameters and logging information. Database files are essential for ongoing database operations all the time. The main components of a typical enterprise server are one or more CPU (with multiple cores), disk space, and memory. The Oracle database is stored on the disk of the server, while the Oracle instance exists in the memory of the server. The Oracle instance consists of a large block of memory and a large number of background processes; the memory block is allocated in the global area of the system, and the background processes interact between SGA and database files on disk. In OracleRAC, multiple instances will use the same database. While instances of shared databases may be on the same server, it is most likely that these instances are on different servers that connect through high-speed interconnects and access databases that reside on dedicated, RAID-enabled disk subsystems. The Oracle Exadata database all-in-one machine is an example of combining database servers, Imax O servers, and disk storage into one or more cabinets and optimizing for RAC environments (including dual InfiniBand interfaces that connect all these devices at the speed of 40Gbps per interface). At the beginning of this article, I summarize the storage structure in the Oracle architecture: the storage structure of Oracle is viewed from two aspects:

1.Oracle logical storage structure (tablespace, block, extent, segment)

2.Oracle physical storage structure (data files, redo log files, control files, archived log files, initialization parameter files, alarm and trace log files, backup files, Oracle management files, password files) Under the logical storage structure, the logical database structure of Oracle: tables, constraints, indexes, views, users and schemas, configuration files, sequences, synonyms, PL/SQL, external file access, database links and remote databases; in addition, the biggest highlight of Oracle 12C and previous versions is that it supports database hot plug and can be connected with other types of databases through the structure diagram:

Oracle logical storage structure diagram:

Oracle logical database structure diagram:

Oracle physical storage structure diagram:

Explain the Oracle logical storage structure in detail:

Data files in the Oracle database are grouped into one or more tablespaces. In each table space, logical database structures, such as tables and indexes, are fragments that are further subdivided into "extent" and "block". This logical subdivision of storage allows Oracle to control disk space utilization more effectively.

Tablespace:

An Oracle tablespace consists of one or more data files, and a data file is and can only be part of one tablespace. For Oracle12c installation, at least two tablespaces are created: SYSTEM tablespaces and SYSAUX tablespaces. The default installation of Oracle12c creates 6 tablespaces; segments:

The next logical grouping level in the database is segment. A segment is a set of extents that make up a database object, such as a table or index, that is considered a unit by Oracle. Therefore, the segment is generally the smallest storage unit to be processed by the end user of the database. Four types of segments can be seen in the Oracle database: data segments (non-partitioned tables and each partition of partitioned tables), index segments, temporary periods, and rollback segments. Disk area:

It consists of one or more database blocks. When a database object is enlarged, the space added for that object is allocated as a disk. Block:

The database block is the smallest storage unit for the Oracle database. The size of a block is a specific number of storage bytes in a given tablespace within the database. The default block size is 8KB

Oracle logical database structure is composed of:

The structure of Oracle logical database is very complicated. Here is a brief summary of what it is composed of:

1. Table: it is the basic storage unit in Oracle database. If there is no table, the database has no value to the enterprise.

Including: relational table, temporary table, index organization table, object table, external table, cluster table, hash cluster, sorted hash cluster, partition table, partition index

two。 Constraints: an Oracle constraint is one or more rules that are defined on one or more columns of a table to help enforce business rules

Including: null constraint, unique column value, primary key value, referential integrity value, compound inline integrity, trigger-based integrity

3. Index: when retrieving a small number of rows in a table, using Oracle indexes provides faster access to those rows in the table

Including: unique index, non-unique index, reverse key index, function-based index, bitmap index

4. View: the view allows the user to view a custom representation of the data in a single table or multiple join tables. The view is also called "storage query", and the user cannot see the query details hidden at the bottom of the view.

Including: general view, materialized view, object view

5. Users and modes: database accounts that have access to the database are called "users". The user can exist in the database without owning any objects. If the user creates and owns objects in the database, they are part of the schema with the same name as the database user. A schema can have any type of object in the database: tables, indexes, sequences, views, and so on. The schema owner or DBA can authorize other database users to access these objects. Users always have full permissions and can control objects in user mode

6. Configuration files: database resources are not unlimited, so DBA must manage and allocate resources to all database users. Some examples of database resources are CPU time, concurrent session, logical read, and connection time.

A database configuration file is a set of commands for qualified resources that can be assigned to the user. After installing Oracle, the DEFAULT profile already exists, and the system assigns it to any user who has not yet explicitly assigned the profile. DBA can add new profiles or change DEFAULT profiles to meet the needs of the enterprise. The initial value of the DEFAULT configuration file allows unlimited use of all database resources.

7. Sequences: Oracle sequences are used to assign ordered numbers and ensure their uniqueness.

8. Synonyms: Oracle synonyms (synonym) are just aliases for database objects that simplify references to database objects and hide the details of the source of the database object. Synonyms can be assigned to tables, views, materialized views, sequences, procedures, functions, and packages. Like views, synonyms do not allocate any space in the data except as defined in the data dictionary.

Synonyms can be public or private. Private synonyms are defined in user mode and are available only to that user. Public synonyms are usually created by DBA and can be used automatically by all database users.

9.PL/SQL:PL/SQL Developer is an integrated development environment that specializes in developing applications for Oracle databases. PL/SQL is also a programming language, called procedural SQL language (Procedural Language/SQL). PL/SQL is an extension of SQL statements to Oracle databases. PL/SQL is available only in the Oracle database.

Oracle physical storage structure:

The composition and functions of the physical storage architecture:

Data file (.dbf): used to store all data in the database, including system data, data dictionary data, temporary data, index data, application data, etc.

Control file (.ctl): used to record and describe the physical storage structure information of the database

Redo log file (.log): used to record database modifications made by external programs (users)

Initialization parameter file: used to set the initial value of the parameters when the database is started

Tracking files: users record the operation of user processes and database background processes

Archive: used to save redo log files that have been fully written

Password file: used to save usernames and SYS passwords with SYSDBA,SYSOPER privileges.

Data files and their management:

When installing the Oracle database, the system will automatically create several data files for the database. The essence of the user's operation on the database is the operation of the data file. A tablespace can correspond to multiple data files, and a data file can only belong to one tablespace. Logically, the data objects are stored in the table space, in essence, in the data file corresponding to the space.

Oracle basic Operations Management:

1. Database on, off

* enter the database: * su-oracle* sqlplus / as sysdba # Log in as the highest administrator sysdba * SQL > help index # to view the command list * SQL > show user # to view the current user

* enable / close database * SQL > startup # enable: instance-database mount-database open * SQL > shutdown immediate # close: database shutdown-uninstall database-instance

* enable and disable listening (function: the client accesses the oracle database instance and installs the database. If you restart the host, you need to enable the listening service before the client can log in to the oracle database instance) * lnsrctl start* lnsrctl stop

two。 Create the database:

* dbca (execute on the desktop interface) # create a database (if you do not pop the window: export DISPLAY=:0.0) * the successful execution will jump out of the create database page, follow the steps below:

3. Tablespace operations:

Create a tablespace:

Create tablespace school / / create tablespace school2 datafile'/ oracle/app/oracle/oradata/school01.dbf' / / specify tablespace file storage location 3 size 10m autoextend on; / / specify size, space can be expanded

Resize the tablespace:

Resize: SQL > alter database datafile2'/ oracle/app/oracle/oradata/school01.dbf'3 resize 80m; the database has changed. Add file: SQL > alter tablespace school2 add datafile3'/ oracle/app/oracle/oradata/school02.dbf'4 size 20m autoextend on; tablespace has changed.

Tablespace permissions:

SQL > alter tablespace school read only; / / modified to read-only permissions tablespace has changed. SQL > alter tablespace school read write; / / permissions modified to read-write (default) tablespace has changed.

Delete tablespaces:

SQL > drop tablespace school including contents; tablespace has been deleted.

4. Plug and unplug the database (CDB to PDB conversion):

SQL > show con_name / / View the current container SQL > show pdbs; / / query all containers of the database

Convert the current database CDB to PDB:

CDB: default database PDB: container database SQL > alter pluggable database orclpdb open; / / modified pluggable library orclpdb open state pluggable database has been changed. Switch session to PDB under SQL > alter session set container=orclpdb; / / CDB. The session has changed. SQL > shutdown immediate / / shut down the pluggable database in PBD. The 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

5. Create a user:

* to create a user, you must specify a default tablespace. Create a user in CDB on version 12C, with a user name format of: cuser # username, and a user name format of user name in PDB. * SQL > create user c##jack2 identified by abc123 / / specify password 3 default tablespace users / / specify default tablespace 4 temporary tablespace temp / / specify temporary tablespace 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

6. Create data tables (insert data, transactions)

SQL > create table info2 (3 id number (4), 4 name varchar2 (10), 5 score number (5 riqi date7 2), 6 riqi date7)

7. Transaction:

In oracle, transactions are turned on by default and do not commit. Therefore, when performing data operations, be sure to remember to submit, or set to auto-commit. Otherwise, the data cannot be written to the hard disk. SQL > insert into info values; SQL > set autocommit on; / / sets automatic submission, rollback is invalid

8. Index:

(SQL >) B-tree index create index index name on table name (column name) unique index / non-unique index create unique index index name on table name (column name) reverse index create index index name on table name (column name) reverse bitmap index create bitmap index index name on table name (column name) other index create index index name on table name (upper (column name) / / uppercase 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%'; rebuild index alter index index name rebuild;alter index index name rebuild tablespace tablespace merge index fragment alter index index name coalesce; delete index drop index index name

9. View:

Pay attention to permissions when creating views SQL > create view vinfo as select from info; / / create views SQL > select from vinfo; / / query views SQL > drop view vinfo; / / Delete views

10. Materialized view:

First of all, you need to give the c##jack user the permission SQL > grant create materialized view to caches permission jackthrough the administrator account; / / create the materialized view permission SQL > grant query rewrite to centering jackjack; / / query, rewrite the permission SQL > grant create any table to cantilever jackjack; / / create all table permissions SQL > grant select any table to cantilever jacks; / / query all table permissions SQL > alter table info add primary key (id); # add the primary key SQL > create materialized view log on info # create materialized view log before creating materialized view: SQL > create materialized view mtview 2 build immediate / / generate new data immediately after creation 3 refresh fast / / refresh data 4 on commit / / submit 5 enable query rewrite / / enable query rewriting function 6 as7 select * from info;SQL > drop materialized view mtview; / delete materialized view

11. Sequence:

The self-incrementing column in oracle needs to be created separately and then called when the data is inserted. View sequence information SQL > select sequence_name,increment_by,cache_size from user_sequences; delete sequence SQL > drop sequence id_seq

twelve。 Synonym:

Commonly known as aliases, nicknames, can improve the security and reliability of data. Private synonyms: public synonyms can only be used by current users: all database users can use SQL > create synonym pr_info for info; / / to create private synonyms SQL > create public synonym pub_info for info; / / create common synonyms delete synonyms SQL > drop synonym pr_info;SQL > drop public synonym pub_info

13. Add:

Common oracle database query commands:

(1) Select * from tables (table name); / / query the contents of table tables (2) Truncate table tables (table name); / / clear the data in the table; but retain the format (3) Select ID (column name) from tables groupby ID (column name), ID2 (column name) havingcount (*) > 1; / / query duplicate data in the table (4) Select * from tables (table name) a where a.id (column name) = '07552223' (duplicate data) / / arrange all the same data as 07552223 in column ID (5) delete fromtables where rowid in (select min (rowid) fromtables where id='15252525') group by username); / / delete duplicates with ID column data of '1525252525' in table table (6) delete fromtable where rowid in (select min (rowid) fromtable group by id..id2) / / Delete all duplicate information about columns (ID) in table table (7) select* from tables where regex_like (id,' ^ [^ [: digit:]] + $'); / / query all data where table is numeric in id column (8) select* from tables where regex_like (id,' ^ ([Amurz] | [Amurz] + [0-9] +) $') / / query all table data with characters and numbers in the id column (9) select count (*) fromtables / / query the number of rows of all data in table (10) select table_name from xxcj_tj_zdcj a group by a.table_namecount (*) > 1 / / query whether there are duplicate tables in the database xxcj_tj_zdcj (11) select * from xxcj_tj_zdcj awhere a.table_name in ('05255') order by a.table_name for update;// modifies the values in the database xxcj_tj_zdcj (that is, the contents in parentheses) (12) select * from xxcj_tj_zdcj awhere a.iddistributors' for update;// modifies the contents of the id column in the database xxcj_tj_zdcj (13) the truncate table database / / data comparison (14) select t.*, t.rowid from table name t; / / Edit table (15) alter table table name add column name character type; / / New column (16) alter table table name modify column name character type; / / modify column type (17) alter table table name drop column column name; / / Delete column (last column) (18) select * from table name where asciistr (column name) not like'%\%' / / query the data with Chinese characters in the xx column in the data table (19) select substr (f_get_sfzhm18 (zjhm), 7 zjhm 8) as csrq from table name where regexp_like (substr (f_get_sfzhm18 (zjhm), 7 pen 1),'[^ 1 [2]')

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.