您的位置：360文档中心› Oracle DBA常用SQL语句系列

Oracle DBA常用SQL语句系列

合集下载

oracle sql并发查询语句

oracle sql并发查询语句

oracle sql并发查询语句Oracle SQL是一种强大的关系型数据库管理系统，支持并发查询，可以同时处理多个查询请求。

下面列举了十个符合题目要求的Oracle SQL并发查询语句。

1. 查询所有员工的姓名和工资```sqlSELECT 姓名, 工资 FROM 员工表;```2. 查询员工工资大于5000元的部门名称和平均工资```sqlSELECT 部门名称, AVG(工资) AS 平均工资FROM 员工表WHERE 工资 > 5000GROUP BY 部门名称;```3. 查询销售额最高的产品名称和销售额```sqlSELECT 产品名称, MAX(销售额) AS 最高销售额FROM 销售表GROUP BY 产品名称ORDER BY 最高销售额 DESCFETCH FIRST ROW ONLY;```4. 查询在某个日期范围内有销售记录的员工姓名和销售数量```sqlSELECT 姓名, COUNT(*) AS 销售数量FROM 员工表INNER JOIN 销售表 ON 员工表.员工ID = 销售表.员工ID WHERE 销售日期BETWEEN TO_DATE('2021-01-01', 'YYYY-MM-DD') AND TO_DATE('2021-12-31', 'YYYY-MM-DD') GROUP BY 姓名;```5. 查询每个部门的员工数量和平均工资```sqlSELECT 部门名称, COUNT(*) AS 员工数量, AVG(工资) AS 平均工资FROM 员工表GROUP BY 部门名称;```6. 查询没有销售记录的产品名称和库存数量```sqlSELECT 产品名称, 库存数量FROM 产品表WHERE 产品ID NOT IN (SELECT 产品ID FROM 销售表);```7. 查询员工工资排名前10的姓名和工资```sqlSELECT 姓名, 工资FROM (SELECT 姓名, 工资, RANK() OVER (ORDER BY 工资 DESC) AS 排名FROM 员工表)WHERE 排名 <= 10;```8. 查询每个部门的销售总额和销售数量```sqlSELECT 部门名称, SUM(销售额) AS 销售总额, COUNT(*) AS 销售数量FROM 员工表INNER JOIN 销售表 ON 员工表.员工ID = 销售表.员工ID GROUP BY 部门名称;```9. 查询在某个日期之后有销售记录的产品名称和最早销售日期```sqlSELECT 产品名称, MIN(销售日期) AS 最早销售日期FROM 产品表INNER JOIN 销售表 ON 产品表.产品ID = 销售表.产品ID WHERE 销售日期 > TO_DATE('2021-01-01', 'YYYY-MM-DD') GROUP BY 产品名称;```10. 查询销售额排名前5的员工姓名和销售额```sqlSELECT 姓名, 销售额FROM (SELECT 姓名, 销售额, RANK() OVER (ORDER BY 销售额DESC) AS 排名FROM 员工表INNER JOIN 销售表 ON 员工表.员工ID = 销售表.员工ID) WHERE 排名 <= 5;```以上是十个符合题目要求的Oracle SQL并发查询语句。

oracle常用SQL语句(汇总版)

oracle常用SQL语句(汇总版)

Oracle数据库常用sql语句ORACLE 常用的SQL语法和数据对象一.数据控制语句(DML) 部分1.INSER T (往数据表里插入记录的语句)INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名;字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)TO_DATE()还有很多种日期格式, 可以参看OR ACLE DOC.年-月-日小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SSINSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,方法借用ORACLE里自带的DBMS_LOB程序包.INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号CREATE SEQUENCE 序列号的名称(最好是表名+序列号标记) INCREMENT BY 1 START WI TH 1 MAXVALUE 99999 CYCLE NOCACHE;其中最大的值按字段的长度来定, 如果定义的自动增长的序列号NUMBER(6) , 最大值为999999 INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL2.DELETE (删除数据表里记录的语句)DELETE FROM表名 WHERE 条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录, 可以用TRUNCATE 命令, 它可以释放占用的数据块表空间TRUNCATE TABLE 表名;此操作不可回退.3.UPDATE (修改数据表里记录的语句)UPDATE表名SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;值N超过定义的长度会出错, 最好在插入前进行长度校验..注意事项:A. 以上SQL语句对表都加上了行级锁,确认完成后, 必须加上事物处理结束的命令COMMIT 才能正式生效,否则改变不一定写入数据库里.如果想撤回这些操作, 可以用命令ROLLBACK 复原.B. 在运行INSERT, DELETE 和UPDATE 语句前最好估算一下可能操作的记录范围,应该把它限定在较小(一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理.二.数据定义(DDL) 部分1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)ORACLE常用的字段类型有CHAR 固定长度的字符串VARCHAR2 可变长度的字符串NUMBER(M,N) 数字型M是位数总长度, N是小数的长度DATE 日期类型创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面创建表时可以用中文的字段名, 但最好还是用英文的字段名创建表时可以给字段加上默认值, 例如DEFAUL T SYSDATE这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间创建表时可以给字段加上约束条件例如不允许重复UNIQUE, 关键字PRIMAR Y KEY2.AL TER (改变表, 索引, 视图等)改变表的名称AL TER TABLE 表名1 TO 表名2;在表的后面增加一个字段AL TER TABLE表名ADD 字段名字段名描述;修改表里字段的定义描述AL TER TABLE表名MODIFY字段名字段名描述;给表里的字段加上约束条件AL TER TABLE 表名ADD CONSTRAINT 约束名PRIMAR Y KEY (字段名);AL TER TABLE 表名ADD CONSTRAINT 约束名UNIQUE (字段名);把表放在或取出数据库的内存区AL TER TABLE 表名CACHE;AL TER TABLE 表名NOCACHE;3.DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)删除表和它所有的约束条件DROP TABLE 表名CASCADE CONSTRAINTS;4.TRUNCATE (清空表里的所有记录, 保留表的结构)TRUNCATE 表名;三.查询语句(SELECT) 部分SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;字段名可以带入函数例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名), TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')NVL(EXPR1, EXPR2)函数解释:IF EXPR1=NULLRETURN EXPR2ELSERETURN EXPR1DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数解释:IF AA=V1 THEN RETURN R1IF AA=V2 THEN RETURN R2..…ELSERETURN NULLLPAD(char1,n,char2)函数解释:字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位字段名之间可以进行算术运算例如: (字段名1*字段名1)/3查询语句可以嵌套例如: SELECT …… FROM(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;两个查询语句的结果可以做集合操作例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT分组查询SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1[HAVING 条件] ;两个以上表之间的连接查询SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE表名1.字段名= 表名2. 字段名[ AND ……] ;SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE表名1.字段名= 表名2. 字段名(+) [ AND ……] ;有(+)号的字段位置自动补空值查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESCSELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]ORDER BY字段名1, 字段名2 DESC;字符串模糊比较的方法INSTR(字段名, ‘字符串’)>0字段名LIKE ‘字符串%’[‘%字符串%’]每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.四.ORACLE里常用的数据对象 (SCHEMA)1.索引(INDEX)CREATE INDEX 索引名ON 表名( 字段1, [字段2, ……] );AL TER INDEX 索引名REBUILD;一个表的索引最好不要超过三个(特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引ORACLE8.1.7字符串可以索引的最大长度为1578 单字节ORACLE8.0.6字符串可以索引的最大长度为758 单字节2.视图(VIEW)CREATE VIEW 视图名AS SELECT …. FROM …..;AL TER VIEW视图名COMPILE;视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.3.同义词(SYNONMY)CREATE SYNONYM同义词名FOR 表名;CREATE SYNONYM同义词名FOR 表名@数据库链接名;4.数据库链接(DATABASE LINK)CREATE DATABASE LINK数据库链接名CONNECT TO 用户名IDENTIFIED BY 密码USING ‘数据库连接字符串’;数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样数据库全局名称可以用以下命令查出SELECT * FROM GLOBAL_NAME;查询远端数据库里的表SELECT …… FROM 表名@数据库链接名;五.权限管理(DCL) 语句1.GRANT 赋于权限常用的系统权限集合有以下三个:CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)常用的数据对象权限有以下五个:ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名, DELETE ON 数据对象名, INSER T ON 数据对象名, AL TER ON 数据对象名GRANT CONNECT, RESOURCE TO 用户名;GRANT SELECT ON 表名 TO 用户名;GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;2.REVOKE 回收权限REVOKE CONNECT, RESOURCE FROM 用户名;REVOKE SELECT ON 表名FROM 用户名;REVOKE SELECT, INSERT, DELETE ON表名FROM 用户名1, 用户名2;查询数据库中第63号错误：select orgaddr,destaddr from sm_histable0116 where error_code='63';查询数据库中开户用户最大提交和最大下发数：select MSISDN,TCOS,OCOS from ms_usertable；查询数据库中各种错误代码的总和：select error_code,count(*) from sm_histable0513 group by error_code orderby error_code;查询报表数据库中话单统计种类查询。

Oracle常用SQL命令

Oracle常用SQL命令

Oracle常⽤SQL命令创建表空间1.创建默认表空间WLP_DATCREATE TABLESPACE "MYSPACE" DATAFILE'D:\oracle\oradata\orcl\data/WLP_DAT.dbf' SIZE 32M REUSE AUTOEXTEND ON NEXT 32MLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;2.创建临时表空间WLP_TEMP（⽤于缓存,可以不建）ALTER TABLESPACE "TEMP" ADD TEMPFILE'D:\oracle\oradata\orcl\data/WLP_TEMP.dbf' SIZE 32m REUSE autoextend on next 32m;创建⽤户并授权1CREATE USER "WLPING" PROFILE "DEFAULT"2 IDENTIFIED BY "123456"3DEFAULT TABLESPACE "MYSPACE" //默认指定表空间4TEMPORARY TABLESPACE "TEMP" //指定缓存表空间5 ACCOUNT UNLOCK; //⽤户⾮锁定67GRANT EXECUTE ON dbms_comparison TO "WLPING";8GRANT UNLIMITED TABLESPACE TO "XIR_MD";9GRANT "CONNECT" TO "XIR_MD";10GRANT "RESOURCE" TO "XIR_MD";1112-- GRANT DBA TO XIR_MD;PROFILE：Oracle系统中的profile可以⽤来对⽤户所能使⽤的数据库资源进⾏限制，使⽤Create Profile命令创建⼀个Profile，⽤它来实现对数据库资源的限制使⽤，如果把该profile分配给⽤户，则该⽤户所能使⽤的数据库资源都在该profile的限制之内。

oracle数据库语句汇总

oracle数据库语句汇总

oracle数据库语句汇总在Oracle数据库中，有许多常用的SQL语句可以用于查询、插入、更新和删除数据。

下面列举了一些常见的Oracle数据库语句，以供参考。

1. 查询表中的所有数据：```SELECT * FROM 表名;```2. 查询表中的特定字段数据：```SELECT 字段1, 字段2, ... FROM 表名;```3. 查询表中满足特定条件的数据：```SELECT * FROM 表名 WHERE 条件;```4. 对查询结果进行排序：```SELECT * FROM 表名 ORDER BY 字段 ASC/DESC;```5. 对查询结果进行分组：```SELECT 字段1, 字段2, ... FROM 表名 GROUP BY 字段;```6. 对查询结果进行统计：```SELECT COUNT(*) FROM 表名;```7. 插入数据到表中：```INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...);```8. 更新表中的数据：```UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2 WHERE 条件;9. 删除表中的数据：```DELETE FROM 表名 WHERE 条件;```10. 创建新表：```CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型,...);```11. 修改表结构：```ALTER TABLE 表名 ADD (字段数据类型); ```12. 删除表：DROP TABLE 表名;```13. 创建索引：```CREATE INDEX 索引名 ON 表名 (字段);```14. 删除索引：```DROP INDEX 索引名;```15. 创建视图：```CREATE VIEW 视图名 AS SELECT * FROM 表名 WHERE 条件; ```16. 删除视图：```DROP VIEW 视图名;17. 创建存储过程：```CREATE PROCEDURE 存储过程名ISBEGIN-- 存储过程的具体逻辑END;```18. 调用存储过程：```EXEC 存储过程名;```以上是一些常见的Oracle数据库语句，可以满足大部分基本的数据操作需求。

oracle数据库常用语句大全

oracle数据库常用语句大全

Oracle数据库是甲骨文公司的一款关系数据库管理系统，它在数据库领域一直处于领先地位。

以下是Oracle数据库常用的一些SQL语句：SELECT：查询语句，用于从一个或多个表中检索数据。

例如：sqlSELECT column1, column2 FROM table_name;INSERT：插入语句，用于向表中插入新记录。

例如：sqlINSERT INTO table_name (column1, column2) VALUES (value1, value2);UPDATE：更新语句，用于修改表中的数据。

例如：sqlUPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;DELETE：删除语句，用于从表中删除记录。

例如：sqlDELETE FROM table_name WHERE condition;CREATE TABLE：创建表语句，用于创建新的数据库表。

例如：scssCREATE TABLE table_name (column1 datatype, column2 datatype, ...);ALTER TABLE：修改表语句，用于添加、删除或修改表中的列。

例如：sqlALTER TABLE table_name ADD column_name datatype;ALTER TABLE table_name DROP COLUMN column_name;ALTER TABLE table_name MODIFY COLUMN column_name datatype; WHERE：条件语句，用于在查询中过滤数据。

例如：sqlSELECT * FROM table_name WHERE condition;GROUP BY：分组语句，用于将查询结果分组。

在使用GROUP BY时，SELECT子句中的列表中的所有个体值（除聚组函数avg、count等外）必须是GROUP BY子句中的表达式或常量。

oracle的dba_tab_columns语句用例

oracle的dba_tab_columns语句用例

DBA_TAB_COLUMNS 是Oracle 数据库中的一个数据字典视图，它描述了数据库中所有表的列信息。

这个视图对于数据库管理员（DBA）来说非常有用，因为它提供了关于列名、数据类型、是否可以为NULL、列的默认值等详细信息。

以下是一些使用DBA_TAB_COLUMNS 视图的示例查询：查询指定表的列信息如果你想查询某个特定表（例如EMPLOYEES）的所有列信息，你可以使用以下SQL 语句：sqlSELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLEFROM DBA_TAB_COLUMNSWHERE TABLE_NAME = 'EMPLOYEES'ORDER BY COLUMN_ID;注意：TABLE_NAME 通常是大写的，除非在创建表时使用了双引号指定了大小写。

2. 查询某个用户所有表的列数如果你想查询某个用户（例如HR）所有表的列数，你可以使用以下SQL 语句：sqlSELECT TABLE_NAME, COUNT(*) AS NUM_COLUMNSFROM DBA_TAB_COLUMNSWHERE OWNER = 'HR'GROUP BY TABLE_NAMEORDER BY TABLE_NAME;查询所有没有默认值的列如果你想查询某个用户所有没有默认值的列，你可以使用以下SQL 语句：sqlSELECT TABLE_NAME, COLUMN_NAMEFROM DBA_TAB_COLUMNSWHERE OWNER = 'HR' AND DATA_DEFAULT IS NULLORDER BY TABLE_NAME, COLUMN_ID;查询所有VARCHAR2 类型的列如果你想查询数据库中所有VARCHAR2 类型的列，你可以使用以下SQL 语句：sqlSELECT TABLE_NAME, COLUMN_NAME, DATA_LENGTHFROM DBA_TAB_COLUMNSWHERE DATA_TYPE = 'VARCHAR2'ORDER BY TABLE_NAME, COLUMN_ID;注意：在执行这些查询之前，确保你有足够的权限访问DBA_TAB_COLUMNS 视图。

oracle常用的SQL及语法

oracle常用的SQL及语法

1、两个oracle 开发工具介绍a)SqlDbx王金生曾推荐过的一款数据库客户端开发工具，使用容易，不需要额外的安装其它插件，有sql提示功能，支持Oracle,Sybase ASE, IBM,DB2/UDB, MicrosoftSQL Server 和ODBC数据源，免费绿色版。

b)PLSQL Developer专门针对oracle开发的一款比较专业的客户端工具，除了常用的数据库操作外，还支持存储过程及函数的调试功能等比较高级的功能，详细的操作可以安装完毕后大家去慢慢研究。

配置方式见网上找的一段方法，第二种：/view/65b5a80f6c85ec3a87c2c583.html以上提到的工具及可能用到的插件见共享目录中的文件。

2、oracle 与 sqlserver 语法大比较a)oracle各类型字段描述b)字段类型比较c)常用函数比较注：此处仅记录语法有区别的地方，更多丰富的语法请查看附件中的手册。

以下的exp为expression的缩写d)关于脏读目前我们使用的sqlserver 数据库，在查询数据时，都要求在select语句中后面跟一个（nolock）或（with nolock）来保证读取大表时不影响其它程序进程的数据操作。

在oracle中，目前还不允许脏读的方式，在每次select 后，读到的数据都是已经commit的数据，所以为了避免读取数据的不准确，程序中如果有比较耗时的sql 操作，尤其是在insert 和update后，尽量减小事务，而且要在事务结束后及时commit。

e)表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）在创建表及其主键、外键、CHECK、UNIQUE、DEFAULT、INDEX时，SQL SERVER 与ORACLE的语法大致相同。

主要区别如下：Oracle定义表字段的default属性紧跟字段类型之后，如下：Create table MZ_Ghxx( ghlxh number primay key ,rq date default sysdate not null,….)而不能写成Create table MZ_Ghxx( ghlxh number primay key ,rq date not null default sysdate,….)f)存储过程/函数结构的不同SQLSERVER中存储过程的结构大致如下CREATE PROCEDURE procedure_name/*输入、输出参数的声明部分*/ASDECLARE/*局部变量的声明部分*/BEGIN/*主体SQL语句部分*//*游标声明、使用语句在此部分*/ENDORACLE中存储过程的结构大致如下CREATE OR REPLACE PROCEDURE procedure_name（/*输入、输出参数的声明部分*/ )AS/*局部变量、游标等的声明部分*/BEGIN/*主体SQL语句部分*//*游标使用语句在此部分*/EXCEPTION/*异常处理部分*/END ；ORACLE端FUNCTION语法说明CREATE [OR REPLACE] FUNCTION function_name[(argument [{IN | OUT | IN OUT }] ) type,…[(argument [{IN | OUT | IN OUT }] ) typeRETURN return_type {IS | AS}BEGIN…END;变量赋值在SQL SERVER语句中用如下语句对局部变量赋值（初始值或数据库表的字段值或表达式）：“SELECT 局部变量名= 所赋值（初始值或数据库表的字段值或表达式）”；而在ORACLE中，将初始值赋给局部变量时，用如下语句：“局部变量名: = 所赋值（初始值或表达式）;”，将检索出的字段值赋给局部变量时，用如下语句：“SELECT 数据库表的字段值INTO 局部变量名…”。

oracle sql 循环语句

oracle sql 循环语句

在Oracle SQL中，可以使用循环语句来重复执行一系列语句块。

Oracle SQL提供了三种类型的循环语句：基于条件的循环（WHILE循环）、基于计数的循环（FOR循环）和基于游标的循环（CURSOR循环）。

下面是它们的示例用法。

1. WHILE循环：DECLAREvariable_name data_type;BEGINvariable_name := initial_value;WHILE (condition) LOOP--执行的语句块--在每次迭代后更新variable_name的值END LOOP;END;其中，`variable_name`是循环中使用的变量名，`data_type`是变量的数据类型，`initial_value`是变量的初始值，`condition`是循环的终止条件。

在每次循环迭代时，需要在语句块中执行相应操作，并在需要时更新`variable_name`的值。

2. FOR循环：BEGINFOR variable_name IN initial_value..final_value LOOP--执行的语句块END LOOP;END;其中，`variable_name`是循环中使用的变量名，`initial_value`是循环变量的初始值，`final_value`是循环变量的结束值。

在每次迭代中，`variable_name`会依次取`initial_value`到`final_value`之间的值，执行相应的语句块。

3. CURSOR循环：DECLARECURSOR cursor_name ISSELECT columns FROM table WHERE condition;BEGINFOR cursor_variable IN cursor_name LOOP--使用cursor_variable的值执行的语句块END LOOP;END;其中，cursor_name是游标的名称，SELECT columns FROM table WHERE condition是一个SELECT查询语句，用于定义游标的结果集。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

DBA常用SQL语句系列一、数据库构架体系1、表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息SELECTTABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,EXTENT_MANAGEMENT, -- Columns not available in v8.0.xALLOCATION_TYPE, -- Remove these columns if runningPLUGGED_IN, -- against a v8.0.x databaseSEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later FROM DBA_TABLESPACESORDER BY TABLESPACE_NAME;2、对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。

监控表空间，最主要的就是监控剩余空间的大小或者是使用率。

以下是监控表空间使用率与剩余空间大小的语句SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfileSELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)3、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。

以下语句将完成这一功能SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES DWHERE T. TABLESPACE_NAME =D. TABLESPACE_NAMEORDER BY TABLESPACE_NAME,FILE_NAME4、使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。

以下语句检查那些表的扩展将引起表空间的扩展。

SELECTA.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNKFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE F.TABLESPACE_NAME = A.TABLESPACE_NAMEAND A.NEXT_EXTENT > F.BIG_CHUNK5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。

所以，段的大小与区间监控也是一个很重要的工作SELECTS.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME, ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"FROM DBA_SEGMENTS SWHERE S.OWNER NOT IN ('SYS','SYSTEM')ORDER BY Used_Extents DESC6、对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。

CREATE OR REPLACE PROCEDURE show_space(p_segname in varchar2,p_type in varchar2 default 'TABLE' ,p_owner in varchar2 default user)ASv_segname varchar2(100);v_type varchar2(10);l_free_blks number;l_total_blocks number;l_total_bytes number;l_unused_blocks number;l_unused_bytes number;l_LastUsedExtFileId number;l_LastUsedExtBlockId number;l_LAST_USED_BLOCK number;PROCEDURE p( p_label in varchar2, p_num in number )ISBEGINdbms_output.put_line( rpad(p_label,40,'.')|| p_num );END;BEGINv_segname := upper(p_segname);v_type := p_type;if (p_type = 'i' or p_type = 'I') thenv_type := 'INDEX';end if;if (p_type = 't' or p_type = 'T') thenv_type := 'TABLE';end if;if (p_type = 'c' or p_type = 'C') thenv_type := 'CLUSTER';end if;--以下部分不能用于ASSMdbms_space.free_blocks( segment_owner => p_owner,segment_name => v_segname,segment_type => v_type,freelist_group_id => 0,free_blks => l_free_blks );--以上部分不能用于ASSMdbms_space.unused_space( segment_owner => p_owner,segment_name => v_segname,segment_type => v_type,total_blocks => l_total_blocks,total_bytes => l_total_bytes,unused_blocks => l_unused_blocks,unused_bytes => l_unused_bytes,LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK );--显示结果p( 'Free Blocks', l_free_blks );p( 'Total Blocks', l_total_blocks );p( 'Total Bytes', l_total_bytes );p( 'Unused Blocks', l_unused_blocks );p( 'Unused Bytes', l_unused_bytes );p( 'Last Used Ext FileId', l_LastUsedExtFileId );p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );p( 'Last Used Block', l_LAST_USED_BLOCK );END;执行结果将如下所示SQL> set serveroutput on;SQL> exec show_space('test');Free Blocks (1)Total Blocks (8)Total Bytes (65536)Unused Blocks (6)Unused Bytes (49152)Last Used Ext FileId (1)Last Used Ext BlockId (48521)Last Used Block (2)PL/SQL procedure successfully completed7、数据库的常规参数除了V$parameter中的常规参数外，ORACLE还有大量的隐含参数，下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。

相关文档
最新文档