A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
Oracle basic concepts and comparison with informix
User: informix directly uses the user management system of the operating system. Generally, root and informix are used as super operators to manage other users. Oracle has its own user management SYSTEM. It generally uses SYS and SYSTEM as super operators to manage other users.
Instance: It is composed of a part of the computer memory required to access the database and the background process for auxiliary processing. This concept is the same for oracle and informix. Multiple instances can be created on one machine, each of which uses its own allocated memory and background processes. Generally, only one instance can be started on one machine. During database access, data is actually written to the hard disk only when the shared memory is reached.
Database: multiple databases can be created on each instance to share the resources of the same instance. oracle and informix are the same.
Table space: informix is called dbspace. It is composed of chunks created on bare devices. The table space itself has no size limit, but a single chunk of the table cannot exceed 2 GB; because the original disk and its own I/O system are used directly, data access is more efficient without the I/O system and overhead of the operating system. Oracle uses the file system of the operating system without the size limit.
On-screen interface for database access: informix supports dbaccess and isql. In dbaccess, a part of the database engine is included in informix installation. isql is an independent product that needs to be purchased separately; only SQL statements and informix extensions can be written. Oracle supports sqlplus and a more user-friendly web interface. It supports PL/SQL programming, including SQL statements. It feels similar to python.
Database features: oracle is an object-oriented relational database system (ORDBMS) that supports the concept of object-oriented. Informix is a relational database management system (RDBMS ).
Configuration File: informix is in the onconfig. std file, with one for each instance. The oracle initialization parameter file is in the $ ORACLE_HOME/admin/TEST/pfile/directory named init. ora.615200426947 (randomly generated ID ).
Ii. Data Types
Oracle Data Type:
CHAR character type, up to 2000 bytes
NCHAR Character Set-based character type, same as above
VARCHAR2 variable-length, up to 4000 bytes
NVARCHAR variable-length Character Set Based, same as above
VARCHAR and VARCHAR2
NUMBER (s, d) numeric type, total digit s bit, decimal point is d bit, total length is 38
LONG variable-length bytes, up to 2G bytes
RAW pure binary data type, which can contain a maximum of 2000 bytes
Long raw variable-length binary data type, up to 2 GB bytes
BLOB Binary large object type, up to 4 GB bytes
NLOB contains a fixed-width multi-digit character large object type, up to 4 GB bytes
CLOB contains a single character of large object type, up to 4 GB bytes
Large binary input type outside the BFILE database, up to 4 GB bytes
Binary label type in MLSLABLE Operating System
Physical address type of ROWID Ji Lu
Informix Data Type: (See Informix Guide to SQL Reference Guide: system directory table, data type, environment variable)
0 = char (n) specifies a character field whose width is n. the maximum length is 255.
1 = smallint short integer,-32767-32767
2 = integer,-2147483647-2147483648
3 = float (x) float, with a maximum of 16 valid digits
4 = smallfloat 8-bit floating point
5 = decimal (x, y) generates an x-bit precision storage body. x is the number of valid digits. If this parameter is not specified, the default value is 16 bits.
6 = The number value automatically specified when a row is inserted in the serial table. The specified value is 1 larger than the previous maximum value. Each table can have only one
7 = date type. The default value is MM/DD/YYYY, which can be customized. This value is actually an integer, indicating the number of days from January 1, December 31, 1899.
17 = int8 stores an integer of 8 bytes, ranging from-(2 ^ 63-1) to (2 ^ 63-1)
8 = money (x, y) generates an x-bit precision storage body. x indicates the number of valid digits, and y indicates the number of decimal places. If this parameter is not specified, x is 16 bits by default, and y is 2 bits by default.
10 = datetime a to B Date and Time type. The format can be defined. Values a and B indicate the details of the values to be stored. The a value indicates the maximum unit. The default value is year. The B value indicates the minimum unit. Time units include MONTH, DAY, HOUR, MINUTE, and FRACTION
11 = byte binary data, up to 31 bytes of 2
12 = text, 31 to the power of 2
13 = varchar storage variable-length bytes, up to 255 bytes
14 = interval indicates the time span value
15 = nchar (n) is the same as char, but different sorting orders of GLS applications are allowed
16 = nvarchar is the same as varchar, but different sorting orders of GLS applications are allowed.
18 = the storage range of serial8 is the same as that of INT8.
19 = non-sorted set of set (e) storage elements. All elements have the same type of e. Repeated values are not allowed.
20 = multiset (e) is a non-sorted set of stored elements. All elements have the same type of e. Repeated values are allowed.
21 = list (e) stores the sorting set of elements. All elements have the same type of e. Repeated values are allowed.
22 = row Storage named or unnamed row Type
40 = Varying-length opaque type stores user-defined data types. its internal structure is not transparent to the database server, that is, it cannot be accessed.
41 = Fixed-length opaque type stores user-defined data types. its internal structure is not transparent to the database server, that is, it cannot be accessed.
43 = lvarchar stores variable-length strings with a maximum of 32739 bytes
44 = boolean stores boolean values true and false
BLOB stores binary data in the form of Random Access Blocks
CLOB stores text data in the form of Random Access Blocks
Iii. oracle concepts and terms
Oracle Database system is a complex software system. Without understanding its internal structure principles and relationships, it is impossible to design and Compile High-Quality application software systems or manage a complex application system. To lay a solid foundation for subsequent chapters, this chapter briefly describes the system structure of ORACLE 8/ORACLE8i databases.
The minimum storage and processing unit in the ORACLE database, including the header information data of the block or PL/SQL code.
The size of the ORACLE block can be specified by selecting "custom installation" during installation, or when creating a DATABASE instance in create database. The minimum value is 2 K and the maximum value is 64 K.
A part that limits system performance.
Object-oriented Relational Database Service
It has all the functions of relational databases and supports object-oriented databases, called object-oriented relational database systems. Oracle7 is a fully functional relational database system; oracle8 is an object-oriented relational database system.
Online Data Analysis and Processing (OLAP)
It is a technology that provides quick, interactive, and convenient insight into data conditions. Such as Oracle Express, Cognos (Canada) Power Play, Business Objects Business Object, etc.
A user can run multiple programs to access the database at the same time. For example, Visual C/C ++ can start multiple processes at the same time, but Visual Basic cannot.
Data warehousing or data warehousing is supported to store a large amount of data information. When several small databases are integrated into a large database and serve a wide range of organizations, if the database stores historical data, it provides decision-making support, data aggregation, and read-only data, in essence, it acts as a data receiver for all the finished databases that provide data to it. Therefore, it is usually called a data warehouse.
It is a part of the computer memory required to access the Oracle database and assists in processing background processes.
Is a software representation that implements world entities, such as tables, indexes, views, synonyms, and processes.
Files used to store data (Application System data) in the Oracle system. (<> The examples in P14 are inaccurate and easy to mislead readers ).
Oracle stores some data files used by the system. For example, the path and file name of the data file, the initial file path and file name are all stored in the control file.
It is also called a transaction record file, which records all modification operations or transactions performed by transactions on the database.
DBA (Database administrator-Database administrator)
It is a technical tool or personnel for managing database systems. It provides DBA functions in a complete database system.
Dirty data block)
The raw data stored in the memory has been modified, and the modified data has not been written into the database.
Hot data block)
Stores frequently-modified data called Hot data blocks.
LRU (Least Recently Used-Least Recently Used)
An algorithm used by the Oracle system. For data blocks (memory blocks) that are in the memory but are not used recently, they are called LRU, oracle will remove the data from the memory based on the LRU and free up space to load other data.
Space created and allocated for storing specialized data.
Is a collection of one or more data files. All data objects are stored in the specified tablespace, but they are mainly stored in tables, so they are called tablespaces.
Recover the changed user (or transaction) to the last state.
The information used for recovery is called recovery information. Rollback is performed based on the recovery information.
2 Oracle Database Structure
The Oracle database structure is the main part of the Oracle database system. It is a visible software, including:
Oracle Data Dictionary
Table space and data files
A Oracle Data Dictionary
An important part of Oracle databases is data dictionary. It stores the information used by the Oracle database. It is a set of read-only tables for users. The data dictionary includes:
Information about all schema objects in the database, such as tables, views, clusters, and indexes.
How much space is allocated, and how much space is currently used.
Column default value.
Integrity of constraints.
The name of the Oracle user.
Permissions granted to users and roles.
Audit information accessed or used by the user.
Other generated database information.
A database data dictionary is a group of tables and view structures. They are stored in the SYSTEM tablespace.
The database data dictionary is not only the center of each database. It is also very important to every user. You can use SQL statements to access the database data dictionary.
B tablespace and Data File
Simply put, a tablespace is a set of one or more data files (physical files) (logical files ). The following table space is generally used:
1) System tablespace)
It is an essential part of different versions of Oracle. It stores some information about the Oracle SYSTEM and generally only has one SYSTEM tablespace.
2) Temporary tablespace (Temporary tablespace)
Since Oracle often requires some temporary disk space when working, these space is mainly used for queries with sorting (Group by, Order by, etc.) algorithms, and will be released immediately after use, the information recorded in the disk area is no longer used, so it is called a temporary tablespace. Generally, there is only one TEMP temporary tablespace after installation.
3) Tools tablespace)
Used to save the database objects required by the Oracle database tools. These tools store tablespaces in dedicated tables used during work. The tablespace is not available in the old Oracle version. The tool tablespace after installation is Tools.
4) User tablespace (Users tablespace)
It is used to store the space used in some experiment examples, and of course it can also be used to store the data of the application system. The old version does not have Users tablespace. The current user tablespace is USERS.
5) data and index tablespace
Table spaces that store data and indexes are promoted in application systems after Oracle7, especially systems with large amounts of data. Data and indexes should be stored separately in different partitions, this improves the query speed. After installation, the index tablespace may be INDX. The data table space is USERS.
6) Rollback Segment table space (Rollback Segment)
The Oracle database system requires a disk space to store the Undo Information when executing Insert, Update, and Delete operations, so that the original Information can be read during recovery. The tablespace In the rollback segment after installation is the RBS tablespace. The database administrator can also create another tablespace for the rollback segment based on the needs of the application.
Oracle stores logical data in table space and connects physical data.
A database is divided into one or more logical components, which are called tablespaces. The tablespace is also divided into logical parts called segments. These segments are further subdivided into extents ).
C Oracle instance
An Oracle Instance is composed of a group of background processes and memory structures. Each running database system is related to an instance. Therefore, Oracle instances are sometimes called database operating systems. The following is an explanation of the Oracle instance.
Oracle has two types of processes: server process and background process ).
An Oracle server process is a transaction that processes user-to-instance connections. The task is:
Analyze and execute the SQL statements issued by the application.
Read the necessary data from the data file to the shared data zone in the SGA area.
Return necessary information to the application.
The Oracle system uses some additional processes to handle the necessary work of the system. These processes are called background processes:
Database write (DBW0 or DBWn)
Log write (LGWR)
Detection point (CKPT)
System Monitoring (SMON)
Process Monitoring (PMON)
Work Queue (SNPn)
Queue monitoring (QMNn)
Oracle memory structure (SGA)
The SGA structure is:
Data High-Speed Buffer
Redo log Buffer
Large Shared Pool (optional)
Data Dictionary Buffer
Other data buffers
3. Oracle Database Files
ORACLE Database is a complex database operating system consisting of a series of components, including: 1) initialization parameter files, 2) data files, 3) control files, and 4) log files.
A. initialize the parameter file
The initialization parameter file (INITsid. ORA) is the main configuration point of orale rdbms. It is a set of configuration parameters and values. Each parameter value controls or modifies a certain aspect of the database and instance. In earlier versions, all parameters were written in initsid. ora file, but after ORACLE8, most parameters are no longer in this file. to modify these parameters, you can only use set session or set system in SQL>. View the availability of system parameters. For example:
SQL> select * from v $ parameter;
The Oracle initialization parameter file is a editable text file. It is accessed when the database instance is started to obtain the path of data files, log files, control files, and so on. In addition, the initialization parameter file also provides the parameters used by the instance.
The initialization parameter file is in the $ ORACLE_HOME/admin/TEST/pfile/directory named init. ora.615200426947 (randomly generated ID ).
B. Data File
It is used to store data files of all databases. Multiple necessary data files are automatically created during Oracle installation. These data files are used to store basic data of the Oracle system. During application system development, we can create some data files as needed.
If data files are classified based on their data types, they can be divided:
The data stored in the application system is user data.
The management of user data and the data of the database system, such as the data dictionary, the name and type of the table created by the user, are all recorded in the system data.
C control file
The control files are the internal binary files accessed by the Oracle database instance at startup. Their storage paths are determined by the control_files = parameter of the parameter file. oracle generally has two or more control files, and each control file record has the same information. If an error occurs in a control file during database operation, Oracle will automatically use another control file. The system cannot work when all control files are corrupted.
The Oracle database must have at least one control file;
Generally, more than two control files are automatically created after the database system is installed;
We recommend that you create multiple control files for security purposes;
The control file can be found using the following command:
Select name, value from v $ parameter where name like 'control _ files ';
D. redo log files.
Redo log files are an important file in the Oracle system. Features:
Redo logs are Oracle journals that record all user objects or system changes;
After the installation is complete, there are multiple redo log files, which are divided into several groups, and the size of the redo log files in the group must be the same;
To improve system performance, you can create multiple Log File groups;
You can run the following command to find the redo log file:
Select * from v $ logfile;
E. Other supported files
In addition to the above three types of files, there are:
Sqlnet. ora file;
Tnsnames. ora file;
Listener. ora file.
4. redo log and redo log Modes
Redo logs, log files, and redo daily work modes are important concepts in the Oracle system. administrators must fully understand their principles and usage methods. The following is a simple concept. The related management is provided in another chapter.
A redo log
All operations performed by Oracle are recorded in the log file. The Oracle system does not save the disk immediately (write data files) For Every modification of a record, but only makes modification records, online redo logs save all the changes. When all the modifications are finally written to the data file, all the modifications are still recorded in the online redo log, which will facilitate the recovery of these transaction records. However, if the log is not redone online, only some recent operations can be restored.
B. redo log Working Mode
Oracle can work in one of two modes:
1) ARCHIVELOG mode (recoverable)
Oracle generally has at least two log files. They are written in turn with all the changes made. When the system is set to the recovery mode, Oracle automatically backs up the log information to be overwritten (washed out) each time, and then records the modified information on it. In this way, you can use backup and recovery to recover data in a certain period of time.
2) NOARCHIVELOG mode (limited recovery)
The default value is NOARCHIVELOG. Oracle does not retain the old redo log information (you can overwrite the original basic information ). Therefore, it is generally irrecoverable.
5. Data blocks, ranges, and segments
A data block)
Oracle data blocks are also called Oracle blocks;
When creating a tablespace, the Oracle System formats the data file into several Oracle blocks;
Each Oracle block is the minimum Unit processed by the Oracle system;
The block size is determined during system installation. You can select "custom installation" to set the block size;
Once the block size is determined, it cannot be changed;
The block size ranges from 2 K to 64 K;
You can run the following command to check the block size:
Select name, value from v $ parameter where name like 'db _ block_size ';
Range B (extent)
Any continuous block allocated to an object (such as a table) is called an interval;
An interval is also called an extension, because when it runs out of an allocated interval, a new record must be inserted into a new interval (that is, to expand some blocks );
The interval size is determined by next;
Once an interval is assigned to an object (table, index, and cluster), the interval cannot be assigned to other objects;
The following command can be used to check the interval used by an object:
Select segment_name, tablespace_name, count (*) from dba_extents
Having count (*)> 1 group by segment_name, tablespace_name;
Segment C (segment)
Segment refers to the generic name that occupies the data file space or the set of space used by database objects;
Segments can include table segments, index segments, rollback segments, temporary segments, and high-speed cache segments;
Segments use several Oracle blocks in the same tablespace (which can be located in different data files ).
Create table abc (empno number (4), ename varchar2 (20), sal number (9, 2 ))
TABLESPACE user_data storage (initial 500 k next 256 k pctincrease 0 );
1) segment allocated = initial interval = 500 k;
2) 256 k will be allocated after the first 500 k is used up; this time period = 256 k + k;
3) if the allocated range is used up, 256 k will be allocated ,...
6 Oracle Database Process
Generally, when the database is started successfully (the Instance is started successfully), at least six background processes are active. These processes are divided according to the needs of the database. They are:
1) database writer (DBWR)
The Database Writer task writes the modified (in memory) data blocks back to the Database file. In some operating systems, Oracle can have two BDWR processes.
2) Check Point (CKPT)
Is an optional process. When a data query request occurs during system operation, the system finds the data from the database and stores the data in the memory zone. In this way, you can modify the data in the memory zone. When you need to write the modified data back to the data file, a Switch is generated for the redo log, and the verification point appears. The system writes the information in the gray data (modified) block in the memory back to the data file on the disk. In addition, the system will redo the log notification control file. DBA can change the CHECKPOINT_PROCESS TRUE parameter file to enable (make valid or invalid) the process.
3) log writer (LGWR)
The process used to write log information in the SGA area to the log file. Generally, the user's modification value is first recorded in the log file. The modification results will be written back to the data file after a certain period of time.
4) System Monitor (SMON)
System monitor is a mandatory process for recovery when the database System is started. For example, in Parallel Server mode (two servers share one disk group), SMON can restore another database that is in failure. Switch the system to another normal server.
5) Process Monitor (PMON)
Process Monitor is used to terminate failed users and release resources occupied by the user.
6) archive (ARCH)
Optional process. used when the database system is in ARCHIVELOG mode.
7) Lock (LCKn)
Optional process. Multiple locked processes can appear in Parallel Server mode to facilitate database communication.
Optional processes used in distributed databases (with different machines and different Oracle Systems in different locations) mode for data inconsistency recovery. Before the RECO resolves the restoration, the mark of the modified data is marked as "suspicious ".
9) Scheduling (Dnnn)
Optional process, used in multiple threads, that is, for each in use (D000 ,..., dnnn) Communication Protocol creates at least one scheduling process. Each scheduling process is responsible for routing requests from the connected user process to the available server process. Return the response to the appropriate user process.
10) snapshot process (SNPn)
The Snapshot process processes the automatic refresh of database snapshots and runs the predefined database process through the DBMS_JOB package. INITsid. the ORA parameter JOB_QUEUE_PROCESS sets the number of snapshot processes. The parameter JOB_QUEUE_INTERVAL determines the number of seconds for the snapshot process to sleep before being awakened to process suspended jobs or transactions.
11) parallel query process (Pnnn)
The ORACLE server can start or stop query processes based on the database activity parallel query options. These processes involve the creation of parallel indexes, table creation, and query. The number of started instances is the same as the number specified by PARALLEL_MIN_SERVERS. The number cannot exceed the value specified by this parameter.
7 Oracle Memory Structure
As mentioned above, Oracle database instances are composed of a group of background processes and memory structures. The memory structure includes:
System Global Area)
Program Global Area)
System A global Zone
The Oracle System is a storage area used to store system information. Both user processes and Oracle background processes can use SGA. SGA contains many components (different parts ).
Data Buffer Cache)
Data blocks recently used by the Oracle system are stored in the data high-speed buffer zone (that is, the user's high-speed buffer zone). When writing data to the database, it reads and writes data blocks, when the data high-speed buffer is filled up, the system automatically removes some infrequently accessed data. If the data you want to query is not in the Data High-speed buffer, Oracle automatically reads data from the disk. The data high-speed buffer zone includes three types:
1) Dirty partition (Dirty Buffers): contains data blocks that have been changed and need to be written back to the data file.
2) Free Zone (Free Buffers): it does not contain any data and can be written again. Oracle can read data blocks from data files.
3) Retention zone (Pinned Buffers): This zone contains a zone that is being processed or explicitly reserved for future use.
After Oracle8i, the buffer pool will be divided into three zones (when multiple buffer pool features are used ):
1) KEEP buffer pool: data blocks are retained in the memory and will not be squeezed out from the memory;
2) The RECYCLE buffer pool removes data from the unwanted memory;
3) The DEFAULT buffer pool contains allocated blocks.
Redo Log Buffer (Rado Log Buffer)
Any Transaction must be put in the Redo Log Buffer before it is recorded to the Redo Log (online Redo Log is required for recovery. The log is then written to the process (LGWR) to regularly write the content in the buffer to the redo log.
A shared pool is a reserved area of SGA and is used to store stored procedures and packages such as SQL and PL/SQL, data dictionaries, locks, character set information, and security attributes. The shared pool includes:
1) Library Cache );
2) Dictionary high-speed buffer (Dictionary Cache ).
The zone includes:
1) Shared SQL zone (Shared Pool Area );
2) Private SQL Area (Private SQL Area );
3) PL/SQL stored procedures and packages (PL/SQL Procedure and Package );
4) Control Structure ).
That is to say, this area contains SQL statements that have been analyzed by syntax and are ready to be executed at any time.
Dictionary high-speed buffer (Data Dictionary Cache)
It is used to store all the information required by the Oracle System Management. This information is the username used to log on to Oracle. These users have database objects and their locations.
SVRMGR> Show SGA
Total System Global Area 35544188 bytes
Fixed Size 22208 bytes
Variable Size 3376332 bytes
Database Buffer 122880 bytes
Redo Buffer 32768 bytes
The last four items are added to the previous one.
You can also use select * from v $ SGA; to query the SGA of the current instance.
A large pool in SGA is an optional buffer. It can be configured with management right as needed. It provides a large zone for backup and recovery operations such as databases.
2) program global zone (PGA)
PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.
Program B global Zone
PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.
8. Oracle instances
An Oracle Instance is composed of SGA, background processes, and data files. Each database has its own SGA and an independent Oracle process set.
In the case of distribution, Oracle uses a SID (System Identifer) to identify the names of each Oracle Server so that the names of different database systems are not confused, in UNIX environments, the Oracle_Sid variable is used to distinguish between them.
9 multi-threaded server (MTS)
Multi-threaded Oracle Server (Multithreader Server) allows multiple connections to the database to fully share memory and resources, which allows a small amount of memory to support more users.
All processes connected to the Oracle database occupy a certain amount of memory. Therefore, if too many processes connect to Oracle, a performance bottleneck occurs.
Oracle8 allows more than 10 thousand users to connect to Oracle at the same time, but not all users use MTS. Currently, some 4GL tools do not support MTS, such as VB and PB, and VC/C ++ does not support MTS. The Oracle multi-thread server has its own connection pool (that is, the Shared Server process ). Because users share open connections, this is much faster than the original dedicated method (eliminating bottlenecks ).
Multithreading is very suitable for some specialized application systems, such as the order registration system, where the customer submits the order, and the data of the recorded employee's order; another recorder is negotiating with the customer, not all data is being input (dedicated server processes are idle ). These terminals are forced to connect to the system and occupy the resources of other users.
Multi-threaded servers eliminate these shortcomings. A multi-threaded server only maintains one connection pool. When a terminal needs to talk to the system, it can be allocated a connection. You can remove it if you do not need it. In this way, the system resources are shared by multiple users.
Change the relevant parameters in the parameter file to make the system a multi-threaded server configuration (effective after restart ). In addition, the database instance must provide the same number of users as the number of users placed.
10 Oracle transaction processing process
Bank withdrawal process:
A. Issue the SQL statement for querying the remaining amount, such:
Select account_balance From banktable
Where account_number = '123'
And account_type = 'savings ';
The SQL statement uses SGA to obtain the server process;
The server process checks whether the statement exists in the Shared Pool. If no statement exists, it is placed in the Shared Pool and ready to run;
Execute the SQL statement to read the high-speed data buffer of SGA from the data file of the remaining data blocks;
Display results. For example, the remaining amount is $325.
B. Withdrawal $25: The SQL statement is:
Update Bank_table set account_balanct = 300
Where account_number = '123'
And account_type = 'savings ';
The customer process transmits the SQL statement to the server process through SGA;
The server process queries whether the statement exists and executes (;
Analyze SQL statements and store them in the shared pool;
Execute SQL statements;
Is the data to be processed in the Data High-speed buffer? Yes to 7;
Reads data blocks from data files to high-speed data buffer;
Record the original value ($325) in the rollback segment );
Generate a copy of the transaction in the redo log;
Change the balance in the Data High-speed buffer to $300;
Bank teller machines send work completion signals through SGA (submit ):
Record completed transactions in the redo log;
Clear the Undo Information in the rollback segment );
The customer has obtained the money.
11. SYS and SYSTEM modes of the ORACLE SYSTEM
SYS and SYSTEM are two accounts installed by default on each ORACLE database SYSTEM. SYS is the owner of all internal database tables, structures, process packages, and so on. It also has V $ and data dictionary views, and creates all encapsulated database roles (DBA, CONNECT, RESOURCE ). Sys is the only user who can access a specific internal data dictionary. System is also created when ORACLE is installed for DBA task management.
The default password after SYS is installed is change_on_install, and the default password for SYSTEM is manager. For security, after installation is complete. Use the alter user sys identified by password command to modify the passwords of these two privileged accounts.
12 ORACLE system tracking File
All ORACLE databases have at least one file used to record system information, errors, and major events. this file is called ALERTsid. log (the sid here is the System ID of oracle), where the storage location is INITsid. the BACKGROUND_DUMP_DEST parameter of the ORA file is provided.
Both the background process and the user process can create their own tracking files. The background process tracking file location is given by the BACKGROUND_DUMP_DEST parameter, and the user tracking file location is given by the USER_DUMP_DEST parameter. For example, the parameter file initora8.ora provides:
# Define directories to store trace and alert files
Background_dump_dest = d: \ oracle \ admin \ ora8 \ bdump
User_dump_dest = d: \ oracle \ admin \ ora8 \ udump
The background trace file is named sidPROC. TRC.
13 ORACLE System Data Dictionary
Data dictionary is the knowledge base for all objects stored in the database. The ORACLE database system uses the data dictionary to obtain object information and security information, users and DBAs use it to view database information. The data dictionary stores information about data objects and segments. Such as tables, views, indexes, packages, processes, and information about users, permissions, roles, and audits. A data dictionary is a read-only object and cannot be modified by anyone.
14 other data objects
In addition to the preceding data objects, ORACLE also includes views, sequences, synonyms, triggers, database chains, packages, processes, and functions. The following is a description.
View is the SQL statement stored in the database for query. It is mainly for two reasons:
Security reasons: the view can hide some data, such as the Social Insurance Fund table. You can use the view to show only the name and address, but not the social insurance number and wage number, another reason is that complex queries are easy to understand and use.
A sequence is a database object used to generate unique digital data. A sequence is created with an initial value, an increment value, a maximum value, and a maximum of 38 integers.
A trigger is a special stored procedure. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionary.
Synonym is a database pointer to other database tables. There are two types of synonyms: private and public ). Private synonyms are created in the specified mode and accessed only in the mode used by the creator. Public synonyms are accessed in the mode specified by public, and can be accessed by all database modes (users.
E database chain
Database link is a storage definition for connecting to a remote database. It is used to query remote users in a distributed database environment. Because they are stored in the DBA_DB_LINKS data dictionary, they can be considered as a database object type.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service