oracle 常用数据字典、常用函数说明
数据字典名称 说明 基本数据字典及其说明 dba_tablespace 关于表空间的信息 dba_ts_quotas 所有用户表空间限额 dba_free_space 所有表空间中的自由分区 dba_segments 描述数据库中所有段的存储空间 dba_extents 数据库中所有分区的信息 dba_tables 数据库中
| 数据字典名称 | 说明 |
| 基本数据字典及其说明 | |
| dba_tablespace | 关于表空间的信息 |
| dba_ts_quotas | 所有用户表空间限额 |
| dba_free_space | 所有表空间中的自由分区 |
| dba_segments | 描述数据库中所有段的存储空间 |
| dba_extents | 数据库中所有分区的信息 |
| dba_tables | 数据库中所有数据表的描述 |
| dba_tab_columns | 所有表、视图、簇的列 |
| dba_views | 数据库中所有视图的信息 |
| dba_synonyms | 关于同义词的信息查询 |
| dba_sequences | 所有用户序列信息 |
| dba_constraints | 所有用户表的约束信息 |
| dba_indexs | 关于数据库中所有索引的描述 |
| dba_ind_columns | 在所有表及簇上压缩索引的列 |
| dba_triggers | 所有用户的触发器信息 |
| dba_source | 所有用户存储过程信息 |
| dba_data_files | 查询关于数据库文件的信息 |
| dba_tab_grants/privs | 查询关于对象授权的信息 |
| dba_objects | 数据库中所有的对象 |
| dba_users | 关于数据库中所有用户的信息 |
| 常用动态性能视图 | |
| v$database | 描述关于数据库的相关信息 |
| v$datafile | 数据库使用的数据文件信息 |
| v$log | 从控制文件中提取有关重做日志组的信息 |
| v$logfile | 有关实例重置日志组文件名及其位置的信息 |
| v$archived_log | 记录归档日志文件的基本信息 |
| v$archived_dest | 记录归档日志文件的路径信息 |
| v$controlfile | 描述控制文件的相关信息 |
| v$instance | 记录实例的基本信息 |
| v$system_parameter | 显示实例当前有效的参数信息 |
| v$sga | 显示实例的SGA区的大小 |
| v$sgastat | 统计SGA使用情况的信息 |
| v$parameter | 记录初始化参数文件中所有项的值 |
| v$lock | 通过访问数据库会话，设置对象锁的所有信息 |
| v$session | 有关会话的信息 |
| v$sql | 记录sql语句的详细信息 |
| v$sqltext | 记录sql语句的语句信息 |
| v$bgprocess | 显示后台进程信息 |
| v$process | 当前进程信息 |
| sql语言分类 | |
| 数据查询语言DQL | 检索数据库中的语句，主要是select语句 |
| 数据操纵语言DML | 用于改变数据库中的数据，主要包括insert、update、delete |
| 事物控制语言TCL | 维护数据的一致性、包括commit、rollback、savepoint（设置保存点） |
| 数据定义语言DDL | 建立、修改、删除数据库对象，create/alter/drop |
| 数据控制语言DCL | 权限授予和权限回收，包括grant、revoke命令 |
| sql关键字不区分大小写、但字符值区分大小写，字符串和日期的值必须以单引号标识，语句以分号结尾 | |
| 内连接innner join | 显示所有满足条件的记录行 |
| 左外连接 left join | 除了满足条件的数据行，还包括了左表中不满足连接条件的数据行 |
| 右外连接 right join | 除了满足条件的数据行，还包括了右表中不满足连接条件的数据行 |
| 全连接 full join | 除了满足条件的数据行，还包括了左、右表中不满足连接条件的数据行 |
| 自然连接nature join | 连接具有相同列名称的两个表，无需明确指出列名称 |
| 自连接 | “自引用式”外键是指表中的一个列可以是该表主键的一个外键 |
| 交叉连接cross join | 不需要任何连接条件的连接，结果为两个表的笛卡尔积，冗余 |
| oracle常用系统函数 | |
| 字符类函数 | |
| ASCII(c) | 返回字符c的ASCII码 |
| CHR(i) | 返回ASCII码i对应的字符 |
| concat（s1,s2） | 将字符串s2连接到字符串s1的后面 |
| initcap(s) | 将字符串s的每个单词的第一个字母大写 |
| instr(s1,s2[,i][,j]) | 返回字符s2在字符串s1中第j次出现的位置，搜索从字符串s1的第i个字符开始 |
| length(s) | 返回字符串s的长度 |
| lower（s),upper(s) | 返回字符串s的小写、大写形式 |
| LTRIM(s1,s2) | 删除字符串s1左边的字符串s2，s2默认为空格 |
| RTRIM（s1,s2) | 删除字符串s1右边的字符串s2 |
| TRIM（s1,s2) | 删除字符串s1左右两端的字符串s2 |
| REPLACE（s1，s2[,s3]) | 使用s3字符串替换出现在s1字符串中的所有s2字符串 |
| SUBSTR（s,i[,j]) | 从字符s的第i个位置开始，截取长度为j的子字符串 |
| 数字类函数 | |
| ABS（n） | 返回n的绝对值 |
| CEIL（n) | 返回大于或等于数值n的最小整数 |
| COS(n) | 返回n的余弦值，n为弧度 |
| EXP（n) | 返回e的n次幂，e=2.7182813 |
| FLOOR(N) | 返回小于或等于n的最大整数 |
| LOG(n1,n2) | 返回以n1为底n2的对数 |
| MOD（n1,n2) | 返回n1除以n2的余数 |
| Power（n1，n2) | 返回n1的n2次方 |
| ROUND(n1,n2) | 返回舍入到小数点右边n2位的n1的值 |
| SIGN（n） | 返回n的符号，为负值返回-1，为正值返回1，为0返回0 |
| SIN（n) | 返回n的正弦值 |
| SQRT（n） | 返回n的平方根，n为弧度 |
| TRUNC（n1,n2) | 返回结尾到n2位小数的n1的值，n2默认设置为0， |
| 日期和时间类函数 | |
| ADD_MONTHS(d,i) | 返回日期d加上i个月之后的结果，其中i为任意整数 |
| LAST_DAY(d) | 返回包含日期d月份的最后一天 |
| MONTHS_BETWEEN(d1,d2) | 返回日期d1，d2之间的数目？？？ |
| NEW_TIME(d1，t1，t2） | 根据时区t1中的日期d1，返回时区t2中的日期和时间 |
| SYSDATE() | 返回系统当前日期 |
| 转换类函数 | |
| chartorwida(s) | 将字符串s转换为RWID数据类型 |
| CONVERT(s,aset[,bset]) | 将字符串s由bset字符集转换为aset字符集 |
| ROWIDTOCHAR（） | 将ROWID数据类型转换为char类型 |
| TO_CHAR（x[,format]) | 将表达式转换为字符串，format表示字符串格式 |
| TO_DATE（s[,format[lan]]) | 将字符串s转换成date类型，format表示字符串格式，lan表示所使用的语言 |
| TO_NUMBER(s[,format[lan]) | 返回字符串s代表的数字，以format格式显示 |
| 聚合类函数 | |
| avg（x[distinct|all]） | 计算选择列表项的平均值，列表项可以是一个列或者多个列的表达式 |
| count（x[distinct|all) | 返回查询结果中的记录数 |
| max(x[distinct|all]) | 选择列表项目中的最大值、 |
| min(x[distinct|all]) | 最小值 |
| sum(x[distinct|all]) | 选择列表项目中的数值总和 |
| variance(x]distinct|all) | 选择列表项目中的统计方差 |
| stddev（x[discinct|all]) | 选择列表项目的标准方差 |
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How long will Oracle database logs be kept?
May 10, 2024 am 03:27 AM
The retention period of Oracle database logs depends on the log type and configuration, including: Redo logs: determined by the maximum size configured with the "LOG_ARCHIVE_DEST" parameter. Archived redo logs: Determined by the maximum size configured by the "DB_RECOVERY_FILE_DEST_SIZE" parameter. Online redo logs: not archived, lost when the database is restarted, and the retention period is consistent with the instance running time. Audit log: Configured by the "AUDIT_TRAIL" parameter, retained for 30 days by default.
The order of the oracle database startup steps is
May 10, 2024 am 01:48 AM
The Oracle database startup sequence is: 1. Check the preconditions; 2. Start the listener; 3. Start the database instance; 4. Wait for the database to open; 5. Connect to the database; 6. Verify the database status; 7. Enable the service (if necessary ); 8. Test the connection.
How to see the number of occurrences of a certain character in Oracle
May 09, 2024 pm 09:33 PM
To find the number of occurrences of a character in Oracle, perform the following steps: Get the total length of a string; Get the length of the substring in which a character occurs; Count the number of occurrences of a character by subtracting the substring length from the total length.
Oracle database server hardware configuration requirements
May 10, 2024 am 04:00 AM
Oracle database server hardware configuration requirements: Processor: multi-core, with a main frequency of at least 2.5 GHz. For large databases, 32 cores or more are recommended. Memory: At least 8GB for small databases, 16-64GB for medium sizes, up to 512GB or more for large databases or heavy workloads. Storage: SSD or NVMe disks, RAID arrays for redundancy and performance. Network: High-speed network (10GbE or higher), dedicated network card, low-latency network. Others: Stable power supply, redundant components, compatible operating system and software, heat dissipation and cooling system.
How much memory does oracle require?
May 10, 2024 am 04:12 AM
The amount of memory required by Oracle depends on database size, activity level, and required performance level: for storing data buffers, index buffers, executing SQL statements, and managing the data dictionary cache. The exact amount is affected by database size, activity level, and required performance level. Best practices include setting the appropriate SGA size, sizing SGA components, using AMM, and monitoring memory usage.
Oracle scheduled tasks execute the creation step once a day
May 10, 2024 am 03:03 AM
To create a scheduled task in Oracle that executes once a day, you need to perform the following three steps: Create a job. Add a subjob to the job and set its schedule expression to "INTERVAL 1 DAY". Enable the job.
How much memory is needed to use oracle database
May 10, 2024 am 03:42 AM
The amount of memory required for an Oracle database depends on the database size, workload type, and number of concurrent users. General recommendations: Small databases: 16-32 GB, Medium databases: 32-64 GB, Large databases: 64 GB or more. Other factors to consider include database version, memory optimization options, virtualization, and best practices (monitor memory usage, adjust allocations).
70B model generates 1,000 tokens in seconds, code rewriting surpasses GPT-4o, from the Cursor team, a code artifact invested by OpenAI
Jun 13, 2024 pm 03:47 PM
70B model, 1000 tokens can be generated in seconds, which translates into nearly 4000 characters! The researchers fine-tuned Llama3 and introduced an acceleration algorithm. Compared with the native version, the speed is 13 times faster! Not only is it fast, its performance on code rewriting tasks even surpasses GPT-4o. This achievement comes from anysphere, the team behind the popular AI programming artifact Cursor, and OpenAI also participated in the investment. You must know that on Groq, a well-known fast inference acceleration framework, the inference speed of 70BLlama3 is only more than 300 tokens per second. With the speed of Cursor, it can be said that it achieves near-instant complete code file editing. Some people call it a good guy, if you put Curs
