oracle asm磁盘组三种模式_oracle数据库常用语句大全

点击蓝字关注我们

作为一名运维人员，需要能够时刻了解数据库当前的运行状态，给数据库把个脉，也需要在数据库出现问题的时候及时处理，那么掌握数据库运维常用的sql就很有必要了，那么本篇文章一定是你需要的。

01

Oracle表空间操作

创建表空间并设置为自动扩展

create 

查看表空间使用率

select dbf.tablespace_name 

查看表空间数据文件路径

select * 

‍

增加数据文件,单个数据文件最大32G。

alter 

‍

表空间除了增加数据文件的方式扩容，还可以通过修改原数据文件大小的方式进行扩容,如下将原10G数据文件resize扩容到30G。

alter 

查看Asm共享磁盘使用情况

select group_number,

02

Oracle常用SQL语句

查看数据库版本信息

SELECT * 

查询大表(占用表空间大)

select t.segment_name, t.segment_type, 

查询oracle用户状态

select username,account_status,expiry_date,profile 

查看oracle用户密码过期策略

select * 

查看缺少主键的表

select a.table_name,

查询执行时间超过20秒的正在执行的慢SQL

select a.username 

检查无效索引

select OWNER,INDEX_NAME,

oracle查看失效对象(存储过程引用的表结构经常发生变动导致)

SELECT owner, object_name, object_type,

‍ 查看已知列在哪张表中

select table_name,column_name 

查看表所属用户和表空间

select owner,table_name,tablespace_name 

‍

查询当前scn号

select current_scn 

查看数据库是否开启归档模式

select 

若是归档模式，则LOG_MODE=ARCHIVELOG若是非归档模式，则LOG_MODE=NOARCHIVELOG

查看表空间是否自动扩展

select file_name,autoextensible,increment_by 

查看日志文件路径其中background_dump_dest的value值即为日志文件存放位置

SQL> show parameter dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/jxoracle/trace
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/jxoracle/cdump
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/jxoracle/trace

增加表主键

alter 

增加列

alter 

删除列

alter 

03

Oracle运维案例

实例1：修改oracle最大连接数

select count(*) from v$process ; 

select value from v$parameter where name ='processes';

alter system set processes = 300 scope=spfile;

修改processes和sessions值必须重启oracle实例才能生效。

实例2：oracle表分析

oracle会定期收集表的统计信息，以此判断sql执行时的最优路径。

select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED 

‍

当表结构变动或者表数据变化较大的时候，需要做表分析，否者update或者select操作不走索引。

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME     => 'USER_NAME',
TABNAME          => 'TABLE_NAME',
ESTIMATE_PERCENT => 100,
NO_INVALIDATE    => FALSE, --立马生效
DEGREE           => 8,
CASCADE          => TRUE);
END;

实例3：oracle去空格

在oracle中，trim使用的形式多为人rtrim()与ltrim()两种，分别为去除字符串右边空格与去除字符串左边空格。

实战1：去除字段做空格

数据是excel导入的，导致TGJMC字段左侧出现空格，需要函数ltrim()批量处理。

update ryxx_table t  

再次查询，空格已经去除

注：

char字符后面如果字段不足会自动补空格，直接改是改不掉的，需要里外都加trim

select trim(lpad(trim('1 '),3,'0')) from dual;

实例4：开并行

并行执行是同时开启多个进程/线程来完成同一个任务，并行执行的每一个进程/线程都会消耗额外的硬件资源，所以并行执行的本质就是以额外的硬件资源消耗来换取执行时间的缩短。这里的额外硬件资源消耗是指对数据库服务器上多个CPU、内存、从个I/O通道，甚至是RAC环境下多个数据库节点的额外利用。一般可以对指定表，会话进行并行操作，特别是大批量更新或者查询的时候，开启并行，能够有效减少执行时间。

ALTER 

commit后关闭并行 

ALTER 

单表指定并行

alter 

强行启用并行度来执行当前SQL,可以强行启用Oracle的多线程处理功能。举例的话，就像电脑装了多核的CPU，但大多情况下都不会完全多核同时启用(2核以上的比较明显)，使用parallel参数，就会多核同时工作，来提高效率。可以加到insert、delete、update、select的后面来使用。 语法

/*+parallel(table_short_name,cash_number)*/    -- 单表时
/*+parallel(t,10) (b,10)*/    -- 多表时

实例：

select /*+ parallel(a 2) */ * from table_name a order by object_name;

实例5：dblink创建

当用户需要跨越本地数据库，访问远程数据库的数据时，oracle提供了dblink的方式

1、赋予用户创建dblink权限

首先查询当前用户是否有创建dblink的权限，如果没有，需要赋权

select * 

命令查询，从结果上看到当前用户有创建权限

CREATE DATABASE LINK--所创建的dblink只能是创建者能使用，别的用户使用不了

CREATE PUBLIC DATABASE LINK--public表示所创建的dblink所有用户都可以使用

赋权不足需要在sys下执行

grant 

2、创建dblink

创建语法

create 

TestDblink ：表示dblink名字，任意起

dbName ：表示 远程数据库的用户

dbPassword：表示 远程数据库的密码

HOST ：表示远程数据库IP

PORT ：表示远程数据库端口

SERVICE_NAME ：远程数据库的实例名,通过如下sql查询

select 

3、使用dblink

select * 

teacher为表名，testDblink为创建的dblink名称

查看用户下已经创建的dblink

select owner,object_name from dba_objects where object_type='DATABASE LINK';

select * from dba_db_links;

4、删除dblink

--删除DBLINK

name为创建的dblink名称

实例:6：oracle数据导入导出

oracle运维过程中，经常会遇到数据库导入导出的情况，甚至整库数据迁移，可以使用如下思路。

expdp在导出数据时会评估大小，这里的评估是通过两种方式，默认是通过block去计算，通过估算可以很好的评估我们对于文件系统大小的计算，方便规划和使用 1、通过block大小去估算，默认评估方式

'/ as sysdba\' ESTIMATE_ONLY=y schemas=user ESTIMATE=blocks

2、通过统计信息去估算

'/ as sysdba\' ESTIMATE_ONLY=y schemas=user ESTIMATE=statistics

expdp导出

导出命令如下，如果不停机的情况下，建议使用scn好导出，停机情况下不需要scn

"/ as sysdba\"  

说明：       

1、DATA_PUMP_DIR参数为dmp文件存放的路径，通过命令select * from all_directories;查询； 2、使用block方法预估大小，使用COMPRESSION压缩参数，大小为原来1/4左右； 3、使用 %U 和 filesize 参数将大数据拆分成小文件导出，文件最大为1g,拆分为01.dmp，02.dmp的形式； 4、parallel参数设置并发，能够显著提高导出速度，一般设置为show parameter cpu的值，能够以多个线程导出数据； 5、CONTENT用法

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

当设置content为all 时,将导出对象定义及其所有数据.为data_only时,只导出对象数据,为metadata_only时,只导出对象定义； 6、schema代表导出用户user1下对象。 此外，如果只是导出特定表，还可以使用 tables参数。 impdp导入

expdp从源库导出数据后，将dmp文件拷贝到目标库的DIRECTORY目录，然后执行如下命令导入数据。

"/ as sysdba\"   

说明：

1、schema表示导入的用户

2、TABLE_EXISTS_ACTION该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP

使用方法：TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }

当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;当设置为APPEND时,会追加数据,为TRUNCATE时,导入作业会截断表,然后为其追加新数据;当设置为REPLACE时,导入作业会删除已存在表,重建表并追加数据。

‍‍‍‍‍‍‍‍‍‍‍‍‍

往期推荐

＋

Oracle使用脚本定时清理归档

＋

Linux操作系统内核升级

＋

Linux如何配置本地yum源

＋

Docker基础使用篇

请扫码

给个关注

运 维 之 美

技术 | IT运维 | 分享| Linux系统

‍

  你“在看”我吗？