Oracle Database learning notes

Write down some important knowledge about Oracle databases for future reference!

I. file systems and functions
Execution file: the Core of the Oracle system. data files can only be identified by Oracle processing programs. it is meaningless to leave the executable file system. The space for storing executable files must be safe and reliable.
Parameter file: configure the basic information of the Oracle database when starting the instance, and determine the runtime environment. The parameter file should be named init <sid>. ora.
Control File: the binary file that identifies the physical file and database structure during Oracle server startup. the control file provides the file directory required to create a new instance. You can update the control file during Oracle operations and create multiple control file copies for database recovery.
Data File: a data file is used to store user data. Its stability and integrity are very important. Each database has at least one related data file.
Online redo log file: The transaction log of the database, which is used to restore the database.
Output file: it affects the recoverability of the database. It is generated by the output program of Oracle. It backs up the data and structure of the database at a specified time point. it is a logical copy of the database, not a physical copy.
Temporary File: used to save the result set when the memory is insufficient to save the intermediate result set. It does not generate Redo, that is, it does not generate online redo files.

Ii. background processes and functions
The main role of background processes is to improve the performance and reliability of databases. No process has its own responsibilities.
Pmon: monitors server processes, ensures that damaged or faulty processes can be destroyed, and resources can be released.
Smon: restart the system, clear the temporary segments, and execute disk areas and tasks to ensure system consistency.
Dbwr: writes cached data blocks to disks.
Lgwr: log writer writes submitted transactions to online redo log files.
Ckpt: indicates the checkpoint process. It updates the headers of all control files and data files with the latest checkpoint information. The database writer periodically writes the data to the disk to store the checkpoint.
Cjq0: manages and coordinates jobs.
Arc0: Archive: used to back up online redo logs to prevent them from being overwritten.

Iii. system tables, views and functions
View user information: dba_users
View the role information: dba_roles, dba_role_privs, role_sys_privs
View system permission information: dba_sys_privs
View the tablespace status of the current database: dba_tablespaces
View the user's system permissions: user_sys_privs
View the permissions a user has granted to another user: user_tab_privs_made
View the column-level permission granted by a user to another user: user_col_privs_made
View the permissions accepted by a user: user_tab_privs_recd
View the column-level permissions that a user accepts: user_col_privs_recd
View User role information: user_role_privs
View the system permissions granted to a role: role_sys_privs
View the object permission granted to a role: role_tab_privs
View the table information owned by the current user: user_tables
View the table information that the current user has permission to access: all_tables
View the column information of all tables owned by the current user: user_tab_columns
View the column information in the table that the current user can access: all_tab_columns
View All constraints of the current user: user_constraint
View the relationship between all constraints and columns owned by the current user: user_cons_constraint
View the comment in the Table: user_tab_comments
View the column comment in the Table: user_col_comments
Exercise table: dual
View the name and abbreviation of the relevant time zone: v $ timezone_names

Iv. Table space and functions
System tablespace: records running information.
Sysaux tablespace: stores many tools and options that support Oracle system activity.
Temp tablespace: stores intermediate results.
Undo tablespace: stores the changed data submitted or withdrawn by a transaction.

5. Default User Roles
DBA: perform all operations in the database
Connect: allows you to perform database connection and other operations.
Resource: You can perform operations such as using database resources.
Select_catalog_role: The select operation can be performed.
OLAP_DBA: allows you to perform operations related to tables and views.
Scheduler_admin: allows you to perform operations such as job management.
Exp_full_database: attackers can perform operations such as database extraction.
Imp_full_database: allows you to import databases.

Vi. Permissions
System permission: the permission to perform certain operations in the database is called the system permission. For example: create table
Create user system permission: only users with this permission can be created
Create session system permission: users with this permission can connect to the database
Create any table system permission: create a table in any mode
Create synonym system permission: you can create an authorization word.
Execute any procedure system permission: execute any process
Create role system permission: You can create roles.
Drop any table system permission: You can delete any table.
Create user: create user username identified by password [default tablespance default tablespace] [temporary tablespace]
Change user Password: alter user Username identified by new password
Lock user and unlock user: alter user Username account [lock | unlick]
Modify the user's default tablespace: alter user Username default tablespace new tablespace name [temporary tablespace new temporary tablespace name]
Delete user: drop user Username [cascade] if the current user has an object, the cascade must be added.
Grant system permissions to users: grant system permissions 1 [, system permissions 2, system permissions 3 ......] to username [with admin option] If you allow users to transfer permissions to other users, add the with admin option
Revoke the granted system permissions: revoke system permissions 1 [, system permissions 2, system permissions 3 ......] when the from user name revokes permissions, the permissions granted by the current user will not be revoked.
Object permission: The permission for objects such as tables and views in the database is the object permission. For example, select insert update delete execute
Grant object permissions to users: grant object permissions [(column name)] on Object Name to user name [with grant option] if the user is allowed to transfer the permission to another user, add with grant option
Revoke granted object permissions: revoke object permissions on Object Name from user name revocation permissions can only be withdrawn from the table, not from the column, when the permission is revoked, the permissions granted to the user are also revoked.
Create synonym: create [public] synonym name for Object Name public represents that all users can use public synonyms
Role: a role is a group of permissions that can be granted to users and other roles. A user can have multiple roles.
Create role: create role name [identified by role Password]
Grant role permissions: similar to granting User Permissions
Revoke role: drop role name

VII. Table
Create table: create [global temporary] table Name (column name column type [other information]) [on commit (delete | preserve) row] [tablespace name] on commit specifies the data duration in the table. delete is deleted after the transaction is executed. preserve indicates deletion after the session ends.
View the system information of the Table: desc | describe object name
Delete table: drop table Name
Add column: alter table name add column name column type
Update column: alter table name modify column name column type
Delete column: alter table Name drop column name
Constraint prohibited: alter table name discount contraint constraint name
Constraint prohibited: alter table name enable [novalidate] contraint constraint name if novalidate exists, it indicates that the data content is not verified to meet the constraint during activation.
Delete constraint: alter table Name drop constraint name
Modify Table Name: rename old table name to new table name
Comment table: the comment table adds text descriptions for the table and table columns so that others can understand the table structure.
Comment table Syntax: comment to table name is comment information comment to column table name. column name is comment information comment content with single quotation marks

8. operation data
Insert data: insert into Table Name (column Name List) values (column Value List) when a column is inserted, the default keyword is used to represent the value, which is the default value of the inserted column, if null is used, a null value is inserted. when inserting a column, the column constraints must be met.
Copy Data: insert into Table Name (column list) and query statement corresponding to the column
Modify data: update table name set column name = column value [, column name 1 = column value 1 ......] [where restriction list]
Delete data: delete from table name [where restriction list]
Two ways to delete all data in a table: delete from table name; truncate table is more efficient because it is used to reset the table storage space.
Merge data operations: merge two tables into one table and use a certain field as the criterion to determine whether the two tables are identical. for example, if Table A and table B use IDs to determine whether they are the same, then table A is merged into Table B based on the data in table. id = B. if the id is true, the other fields after the merge will use
Merge data: merge into table name 1 using table name 2 on (comparison expression) when matched then update statement when no matched then insert statement
Flash query: There are two types of Flash query 1. returns a time-based flash query to a previous time. 2. it is a version-based flash query that returns the system to the previous version number. you must have the execute Permission for the dbms_flashback package to use the flashback query.
Grant the object permission of the user to flash back query: grant execute on dbms_flashback to user name;
Execute time-based flash back query: execute dbms_flashback.enable_at_time (time); general time algorithm: sysdate-minutes/1440
Execute a flash query based on the system version number: execute dbms_flashback.enable_at_system_change_number (System Change number); Change number can be obtained through; dbms_flashback.get_system_change_number ();
Disable flashback query: Disable the flashback query function by dbms_flashback.disable ();
Transaction: a transaction is a unit operation, which either succeeds or fails.

9. Search
Arithmetic Operation: arithmetic operations (+,-, *,/) can be used in queries ,-,*,/)
Alias: For the convenience of viewing the name temporarily defined for the column in the Select statement. definition method: column name [as] alias
Merge output results: Use the '|' symbol to connect. Example: result 1 | result 2 [| result 3...]
Null Value processing: nvl (null value column, display value). If the column value is null, the display value is displayed.
Distinct keywords: duplicate values are not allowed in the search results; select distinct column list from Table Name
Where: where followed by an arithmetic expression; for example, select column name from table name where restriction
OPERATOR:
= Equal
<> Or! = Not equal
<Less
> Greater
<= Less than or equal
> = Greater than or equal
> Any (Value List): the minimum value in the ratio list is large.
<Any (Value List): the maximum value in the ratio list is small.
> All (Value List): the maximum value in the ratio list.
<All (Value List): the minimum value in the ratio list is small.
Like: matches like '_ A %' _ according to the specified pattern to indicate any character, % indicates any number of characters. If the matching string contains _ or %, use escape to indicate the actual data rather than the matching expression.
In: a list of matched values, such as: in (select a from TA)
Between: the value in the matching range, for example,)
Is null: matches null values.
Is nan: matches non-numeric values
Is infinite: matches infinite binary_float and binary_double
Not: used to obtain the inverse in various operators.
Or: or, when either of the two conditions is true, it matches
And: Match. Both conditions must be true.
Sort data: order by sort expression [asc | desc] [, sort expression 1 [asc | desc] asc and desc represent the sort order, asc is the forward order, and desc is the reverse order. by default, asc
Retrieve data from multiple tables 1: select column list from table name 1 [as] alias 1, table name 2 [as] alias 2 where restriction
Cartesian set: Multiplication set of two sets. For example, if set A contains 2 records and Set B contains 20 records, their Cartesian set has 40 records.
Inner join: All data queried by inner join meets the query conditions. Method: select column table name 1 alias 1 [inner] join table name 2 alias 2 on inner join Condition
Outer Join: The Outer Join can display the data of the non-conforming table in the result. divided into left Outer Join, right Outer Join, all outer join. the left Outer Join contains data that does not meet the conditions in the left table, and the right Outer Join contains data that does not meet the conditions in the right table. the full outer join contains data that does not meet the conditions in the left and right tables. the left table on the left side of the join keyword is the right table on the right side.
Outer join mode: select column list from table name 1 alias 1 [left | right | full] outer join table name 2 alias 2 on join Condition
Self-join: A connection connecting a table. You can use an alias to query the connection.
Auto join mode: select column list from table name alias 1 inner join table name alias 2 on join Condition
Crossover: The result is a Cartesian set.
Cross join method: select column list from table name 1 cross join table name 2

10. Packages
Dbms_flushback: used to execute a flashback query.

11. Functions
String functions:
Ascii (x): returns the ascii code of character x;
Char (x): the character that returns the asscii value;
Concat (x, y): attaches y to x and returns the result;
Initcap (x): Specifies the upper case of each English word in character x;
Instr (x, find_string [, start] [, occurrence]: searches for find_string in string x and returns the position where find_string appears. You can select the start position to start the search, you can also limit the number of occurrence
Length (x): returns the length of string X.
Lower (x): converts x to lowercase and returns
Lpad (x, width [, pad_string]): Fill the left side of x with spaces to set its length to width. If pad_string is provided, use pad_string.
Ltrim (x [, trim_string]): Delete the character on the left of x. Space is deleted by default. If trim_string is specified, trim_string is deleted.
Nanvl (x, value): If x is not a number, return value. Otherwise, return x.
Nvl (x, value): If X is null, value is returned. Otherwise, x is returned.
Nvl2 (x, value1, value2): If X is not null, value1 is returned; otherwise, value2 is returned.
Replace (x, search_string, replace_string): Search for search_string in string X and replace it with replace_string.
Rpad (x, width [, pad_string]): Same as lpad, but from the right
Rtrim (x [, trim_string]): Same as ltrim, but from the right
Soundex (x): returns the phonetic alphabet containing strings.
Substr (x, start [, length]): returns the substring of x. the start position is the length of start, which is length. All values are returned by default.
Trim ([char from] x): deletes strings on both sides of x. If char from exists, deletes the char on both sides of x.
Upper (x): converts letters in string x to uppercase letters.
Mathematical functions:
Abs (x): returns the absolute value of X.
Acos (x): returns the arc cosine of X.
Asin (x): returns the arc sine of X.
Atan (x): returns the arc tangent of X.
Atans (x, y): returns the arc tangent values of X and Y.
Bitand (x, y): returns the binary and result of X and Y.
Cos (x): returns the cosine of X, in which X is expressed in radians.
Cosh (x): returns the hyperbolic string value of X.
Ccil (x): returns the smallest integer greater than or equal to X.
Exp (x): returns the X power of e, where e equals 2.71828183
Floor (x): returns the largest integer smaller than or equal to x.
Log (x, y): returns the logarithm value outside the base of x.
Ln (x): returns the natural logarithm of x.
Mod (x, y): returns the remainder of X divided by Y.
Power (x, y): returns the power Y of X.
Round (x [, y]): round X value. if Y is not specified, the circle is rounded to an integer. If Y is specified, the circle is rounded to the number of decimal places specified by Y. If Y is negative, the circle is rounded to the number of digits at the left end of the decimal point.
Sign (x): symbol function. If X is a negative number,-1 is returned. If X is a positive number, 1 is returned. If X is 0, 0 is returned.
Sin (x): returns the sine of X.
Sinh (x): returns the hyperbolic sine of X.
Sqrt (x): returns the square root of X.
Tan (x): returns the tangent of X.
Tanh (x): returns the hyperbolic tangent of X.
Trunk (x [, y]): truncates the X value. If Y is not set, it is truncated to an integer. If Y is specified, it is truncated to the decimal place specified by Y; if Y is a negative number, the number of digits to the left end of the decimal point is truncated.
Conversion functions:
Asciistr (x): converts X to an ascii string. x can be a string in any character set.
Bin_to_num (x): converts X into a binary number and returns a number.
Cast (x as type_name): converts X from one data type to another.
Chartorowid (x): Convert X to the rowid value.
Compose (x): converts X to a unicode string. unicode uses a 2-Byte Character Set and can contain more than 65000 characters. It can be used to represent non-English characters.
Convert (x, source_char_set, dest_char_set): converts X from source_char_set to dest_char_set.
Decode (x, search, result, default)
Decompose (x): converts X to a UNICODE string.
Hextoraw (x): converts X characters that contain hexadecimal notation into binary numbers and returns raw numbers.
Numtodsinterval (x): converts the number x to the interval_day_to_second type.
Numtoyminterval (x): converts the number X to the interval_year_to_money type.
Rowtohex (x): converts binary data X to varchar2 characters that contain equivalent hexadecimal code.
Rowidtochar (x): converts X of the rowid type to varchar2.
Rowidtonchar (x): Convert the rowid type ELE. Me x to the nvarchar2 type
To_binary_double (x): converts X to binary_double.
To_binary_float (x): Convert x into binary_float
To_char (x [, format]): converts x to a varchar2 string and provides a format expression.
To_clob (x): converts X to a CLOB.
To_date (x [, format]): converts X to a date.
To_dsinterval (x): converts the character to the interval_day_to_second type.
To_multi_byte (x): converts a single-byte character in X into a multi-byte character.
To_nchar (x): converts x to the nvarchar2 type.
To_nclob (x): converts X to the nclob type.
To_number (x [, format]): converts X to number.
To_single_byte (x): converts the multi-Byte Character in X into a single-byte character.
To_timestamp (x): converts string X to a timestamp.
To_timestamp_tz (x): converts string X into timestamp with time zone
To_yminterval (x): Convert string X into interval_year_to_moth
Translate (x, from_string, to_string): replace all from_strings in X with to_string.
Unistr (x): Convert the characters in X to the nchar Character Set
[Format parameters]:
9: return number. If the number is negative, there is a negative number.
0: 0999 indicates that there is 0, 9990 in front of the number, indicating that there is 0 in the end of the number.
.: Indicates the decimal point.
,: Comma is displayed at the specified position.
$: S99 indicates the currency number before the number
B: If the integer is 0, spaces are used.
C: Use the ISO standard currency symbol at the specified position
D: return the decimal point at the specified position.
EEEE: recorded by scientific computation
FM: removes spaces before and after a number.
G: displays the group symbol at the specified position.
L: display the local currency symbol at the specified position
PR: the tail of a negative number has a triangle extension number <>, and the header and tail of a positive number have spaces.
MI: a negative number has a negative number and a space at the end of a positive number.
RN/m: returns the Roman numerals. RN indicates upper case, m is lower case, and the numbers must be between 1 and 3999.
S: S999 indicates that there is a negative number before the negative number, positive before the positive number, 999S indicates that the symbol is behind
TM: returns a number with the smallest number of characters
U: returns the double currency number at the specified position.
V: returns the power of a number multiplied by x 10. x is the specified number.
X: returns a hexadecimal number.
Regular Expression:
*: Matches 0 or multiple arbitrary characters.
? : Match 1 arbitrary character
^: Indicates the start of the string.
$: Indicates that the string ends.
X | y: matches x or y.
[]: Any character in the wildcard Extension
{M}: allow matching m times
{M, n}: match at least M times and at most N times
\ N: indicates that the previous expression is repeated N times.
[: Character class:]: Specifies a character class, matching any character in the class.
Regexp_like (search_string, pattern [, match_option]): similar to the like operator, you can use a regular expression to perform a pattern matching operation. option has four values for C matching, which are case sensitive, I matching is not sensitive in lower case, N is allowed. match any new characters,
Regexp_instr (search_string, pattern [, position [, occurrence [, return_opotion [, match_option]): instr operator extension, which can be used to search for input characters matching the Regular Expression pattern
Regexp_replace (seach_string, pattern [, replacestr [, position [occurrence [, match_option]): replace function extension, which allows you to search for input characters that match regular expressions, replace with the replacement string
Regexp_substr (search_string, pattern [, positioin [, occurrence [, match_option]): substr Function Extension
Aggregate functions:
Avg (x): Average Value
Count (x): count
Max (x): Maximum Value
Median (x): median
Min (x): Minimum value
Stddev (x): Standard Deviation
Sum (x): sum value
Variance (x): variance
Grouping technology: select column list from table name where restriction condition group by grouping column having grouping data should meet the conditions
Time functions:
Add_months (x, y): adds y months to x, and decreases if Y is negative.
Last_day (x), returns the last day of the month contained in X
Months_between (x, y): returns the number of months between X and Y. If Y returns a negative number before X
Next_day (x, day); returns only the next DAY of X, and day is a string
Sysdate (): returns the date of the current system.
Trunk (x [, unit]): truncates x'
Round (x [, unit]): round X
Current_date (): returns the current date of the region.
Dbtimezone (): returns the time zone of the current database.
New_time (x, time_zone1, time_zone2): converts x from Time Zone 1 to Time Zone 2
Sessiontimezone (); returns the time zone of the current database conversation.
Tz_offset (time_zone): returns the offset time of time_zone Based on the hour and minute.
Timestamp function:
Current_timestamp (): returns the timestamp with time zone type that contains the current session time and time zone information.
Extact ([year | month | day | hour | minute | second | [timezone_hour | timezone_minute] | [timezone_region | timezone_abbr] from x): extract parameter information from expression X
From_tz (x, time_zone): converts the specified timestamp and date to the timestamp with time zone type.
Localtimestamp (): returns the timestamp with time zone data of the session.
Systimestamp (): returns the timestamp with time zone data of the current database.
Sys_extact_utc (x): converts the expression of the tmestamp with time zone type to the timestamp type.
To_timestamp (x [, format]): converts string expression X to the timestamp type. You can use format to specify the format.
To_timestamp_tz (x [, format]): converts string expression X to timestamp with time zone. You can use format to specify the format.

12. Date and Time
Set the default time format: dd-mon-yy by default; alter session nls_date_format date format; you can change
Convert date data:
Cc: century of double digits
Scc: century with two digits of the negative number, indicating a century ago
Q: one-digit quarter
Yyyy: 4-digit year
Iyyy: 4-digit year, ISO format
Rrrr: indicates the full year of the four digits of the current year.
Y, yyy: 4-digit year with a comma
Yyy: the last three digits of the year.
Iyy: the last three digits of the Year, in ISO format
Yy: the last two digits of the year
Iyy: the last two digits of the Year, in ISO format
Rr: Year of the circle Based on Double digits of the current year
Y: the last digit of the year.
I: the last digit of the Year, in ISO format
YEAR: YEAR is an uppercase letter.
Year: The Year name starts with an uppercase letter.
Mm: two months
MONTH
Month
MON: the first three letters of the month name, in upper case
Mon: the first three letters of the month name, in upper case
RM: Rome digital month
Ww: 2 digit week in the year
Iw: Two digit week in the year, ISO Standard
W: The Week in the month.
Ddd: the third digit day of the year
Dd: double-digit day in a month
D: one digit day in a week
DAY: The full name of the DAY, capital letters
Day
DY: the first three letters of the day, in uppercase.
Dy: the first three letters of the day, with uppercase letters
Hh24: two-digit hour, in 24-hour format
Hh: two-digit hour in 12-hour format
Mi: two-digit minute
Ss: double-digit second
Ff [1... 9]: decimal number second, specified in decimal part
Sssss: the number of seconds in the past 12
Ms: milliseconds
Cs: s
[-/,.;:]: Delimiter in date and time
TH, th: digit suffix
SP, sp: Spell read number
Combination of SPTH, spth: sp and th
Tzh: Time Zone hour
Tzr: Time Zone
Set the current database session time zone: alter session set time_zone Time Zone
Timestamp: timestamp is more powerful than date and can store more information.
Timestamp type:
Timestamp [(secounds_preeision)]: can be used to store century, year, month, day, 24 hours. minutes, and seconds. if you use an optional parameter to specify the accuracy of the second, you can store fractional seconds. the range of this option is 0-9. The default value is 6.
Timestamp [(secounds_preeision)] with time zone: extends the timestamp class to store time zones.
Timestamp [(secounds_preeision)] with local time zone: extends the timestamp class to convert the time to the current database time zone.

13. subquery
Concept: a Select statement nested in a select statement is called a subquery. subqueries and connection queries can be converted to each other, and the efficiency of connection queries is much higher than that of subqueries, subqueries cannot use the order by statement. subqueries can be nested but can contain up to 255 layers.
Operator: Two Comparison operators can be used in a subquery: the single-line operator and the multi-line operator.
Single-row subquery: A subquery statement returns only the results of a single-row single-column and a constant value.
Multi-row subquery: The subquery statement returns the results of multiple rows in a single column and a group.
Multi-column subquery: The subquery statement returns multi-column results.
Associate subquery: one or more columns in the subquery reference statement.
Nested subquery: subquery statements contain subqueries.