Common oracle data dictionaries and common functions

Data Dictionary Name Description basic data dictionary and description dba_tablespace information about tablespaces dba_ts_quotas all user tablespaces quota dba_free_space free partitions dba_segments in all tablespaces describe all partitions in the dba_extents database of all segments in the database information in dba_tables Database

Data Dictionary Name Description basic data dictionary and description dba_tablespace information about tablespaces dba_ts_quotas all user tablespaces quota dba_free_space free partitions dba_segments in all tablespaces describe all partitions in the dba_extents database of all segments in the database information in dba_tables Database

Data Dictionary name	Description
Basic data dictionary and description
Dba_tablespace	Table space information
Dba_ts_quotas	Quota of all user tablespaces
Dba_free_space	Free partitions in all tablespaces
Dba_segments	Describe the storage space of all segments in the database
Dba_extents	Information about all partitions in the database
Dba_tables	Description of all data tables in the database
Dba_tab_columns	Columns of all tables, views, and clusters
Dba_views	Information of all views in the database
Dba_synonyms	Query synonym Information
Dba_sequences	All user sequence information
Dba_constraints	Constraint information for all user tables
Dba_indexs	Description of all indexes in the database
Dba_ind_columns	Compress index columns on all tables and clusters
Dba_triggers	Trigger information of all users
Dba_source	Stored Procedure information of all users
Dba_data_files	Query Information about database files
Dba_tab_grants/privs	Query object authorization information
Dba_objects	All objects in the database
Dba_users	Information about all users in the database
Common dynamic performance View
V $ database	Description about the database
V $ datafile	Data File information used by the database
V $ log	Extract information about redo log groups from control files
V $ logfile	Information about instance resetting log group file names and locations
V $ archived_log	Record basic information of archived log files
V $ archived_dest	Record the path information of the archived Log File
V $ controlfile	Describes information about the control file.
V $ instance	Record basic instance information
V $ system_parameter	Displays the currently valid parameter information of the instance.
V $ sga	Displays the size of the SGA area of the instance.
V $ sgastat	SGA usage statistics
V $ parameter	Record the values of all items in the initialization parameter file
V $ lock	Set all object lock information by accessing database sessions
V $ session	Session information
V $ SQL	Records detailed information about SQL statements
V $ sqltext	Records SQL statement Information
V $ bgprocess	Display Background Process Information
V $ process	Current process information

SQL language classification
Data Query Language DQL	Queries the statements in the database, mainly select statements.
Data manipulation language DML	Used to change data in the database, including insert, update, and delete
Things control language TCL	Maintain data consistency, including commit, rollback, and savepoint)
Data Definition Language DDL	Create, modify, and delete database objects, create/alter/drop
Data Control Language DCL	Grant and revoke permissions, including grant and revoke commands
SQL keywords are case-insensitive, but character values are case-sensitive. The string and date values must be identified by single quotes. The statement ends with a semicolon.

Innner join	Display All record rows that meet the conditions
Left Outer join	In addition to the data rows that meet the conditions, the data rows that do not meet the connection conditions in the left table are also included.
Right join	In addition to the data rows that meet the conditions, the data rows that do not meet the connection conditions in the right table are also included.
Full join	In addition to the data rows that meet the conditions, the data rows that do not meet the connection conditions in the left and right tables are also included.
Natural join	Connect two tables with the same column name without specifying the column name
Self-connection	The "Auto-reference" foreign key refers to a column in the table that can be a foreign key of the table's primary key.
Cross join	No join condition is required. The result is a Cartesian product of the two tables, redundant.

Common oracle system functions
Character Functions
ASCII (c)	Returns the ASCII code of character c.
CHR (I)	Returns the characters corresponding to ASCII code I.
Concat (s1, s2)	Connect string s2 to the end of string s1
Initcap (s)	Uppercase of the first letter of each word in string s
Instr (s1, s2 [, I] [, j])	Returns the position where character s2 appears for the first time in string s1, and searches for the position starting from the I character of string s1.
Length (s)	Returns the length of string s.
Lower (s), upper (s)	Returns the lowercase and upper-case string s.
LTRIM (s1, s2)	Delete string s2 on the left of string s1. s2 is a space by default.
RTRIM (s1, s2)	Delete string s2 on the Right of string s1
TRIM (s1, s2)	Delete the string s2 at both ends of string s1
REPLACE (s1, s2 [, s3])	Replace all s2 strings that appear in the s1 string with the s3 string
SUBSTR (s, I [, j])	Starting from the I position of character s, truncate the child string with the length of j.
Number Functions
ABS (n)	Returns the absolute value of n.
CEIL (n)	Returns the smallest integer greater than or equal to the value n.
COS (n)	Returns the cosine of n. n is a radian.
EXP (n)	Returns the n power of e, e = 2.7182813
FLOOR (N)	Returns the largest integer less than or equal to n.
LOG (n1, n2)	Returns the base n2 logarithm of n1.
MOD (n1, n2)	Returns the remainder of n1 divided by n2.
Power (n1, n2)	Returns the n2 power of n1.
ROUND (n1, n2)	Returns the n1 value rounded to the right of the decimal point.
SIGN (n)	Returns the n symbol. If it is a negative value,-1 is returned. If it is a positive value, 1 is returned. If it is 0, 0 is returned.
SIN (n)	Returns the sine of n.
SQRT (n)	Returns the square root of n, and n is a radian.
TRUNC (n1, n2)	Return the n1 value ending with n2 decimal places. n2 is set to 0 by default,
Date and Time Functions
ADD_MONTHS (d, I)	Returns the result after date d plus I months, where I is an arbitrary integer.
LAST_DAY (d)	Returns the last day of the month containing the date d.
MONTHS_BETWEEN (d1, d2)	Returns the number between the d1 and d2 dates ???
NEW_TIME (d1, t1, t2)	Returns the date and time in Time Zone t2 Based on the date d1 in Time Zone t1.
SYSDATE ()	Returns the current system date.
Conversion functions
Chartorwida (s)	Convert string s to RWID Data Type
CONVERT (s, aset [, bset])	Convert string s from bset character set to aset Character Set
ROWIDTOCHAR ()	Convert ROWID data type to char type
TO_CHAR (x [, format])	Converts an expression to a string. format indicates the string format.
TO_DATE (s [, format [lan])	Converts string s to the date type. format indicates the string format, and lan indicates the language used.
TO_NUMBER (s [, format [lan])	Returns the number represented by string s, which is displayed in format.
Aggregate functions
Avg (x [distinct | all])	Calculates the average value of a selected list item. A list item can be a column or expression of multiple columns.
Count (x [distinct | all)	Returns the number of records in the query results.
Max (x [distinct | all])	Select the maximum value,
Min (x [distinct | all])	Minimum value
Sum (x [distinct | all])	Select the sum of values in the list items
Variance (x] distinct | all)	Select statistical variance from the list items
Stddev (x [discinct | all])	Select standard variance for the list item