oracle数据库常用语句大全

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

01

Oracle表空间操作

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

create tablespace TS_MG_DATADATAFILE '/u01/app/oracle/oradata/data03/TS_MG_DATA_01.DBF'SIZE 30G AUTOEXTEND ON;

查看表空间使用率

select dbf.tablespace_name "Tablespace Name",
       round(dbf.totalspace,2) "Total(M)",
       round(dbf.used - dfs.freespace,2) "Use(M)",
       round(nvl(dfs.freespace, 0) + dbf.totalspace - dbf.used,2) "Free(M)",
       round(((dbf.used - nvl(dfs.freespace, 0)) / dbf.totalspace) * 100,2) "Use%",
       round(((nvl(dfs.freespace, 0) + dbf.totalspace - dbf.used) /
       dbf.totalspace) * 100,2) "Free%"
  from (select t.tablespace_name,
               sum(greatest(t.maxbytes, t.bytes)) / 1024 / 1024 as totalspace,
               sum(t.bytes) / 1024 / 1024 as used
          from dba_data_files t
         group by t.tablespace_name) dbf
  left join (select tt.tablespace_name,
                    sum(tt.bytes) / 1024 / 1024 freespace
               from dba_free_space tt
              group by tt.tablespace_name) dfs
    on dbf.tablespace_name = dfs.tablespace_name;

查看表空间数据文件路径

select * from dba_data_files t where t.tablespace_name='Tablespace_NAME';

‍

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

alter tablespace TABLESPACE_NAME add datafile '+DG_OR_IPRPT/iprpt/lv_iprpt11tbs' size 20G;

‍

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

alter database datafile '+DATA/hjkl/datafile/tbs_32k_trans_data1tbs' resize 30G;

查看Asm共享磁盘使用情况

select group_number,name,total_mb,free_mb from v$asm_diskgroup;

02

Oracle常用SQL语句

查看数据库版本信息

SELECT * FROM v$version;

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

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type;

查询oracle用户状态

select username,account_status,expiry_date,profile from dba_users;

查看oracle用户密码过期策略

select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

查看缺少主键的表

select a.table_name,
(select b.comments from
user_tab_comments b where b.table_name=a.TABLE_NAME)
from user_tables a where not exists
(select * from user_constraints b where a.table_name=b.table_name and b.constraint_type='P')

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

select a.username "用户名", 
a.sid "会话ID",
a.MACHINE "机器名",
a.MODULE "程序",a.STATUS "状态",
a.sql_id "sql_id",
a.SQL_EXEC_START "开始执行时间", 
b.SQL_TEXT, 
a.SQL_ADDRESS,
a.SQL_HASH_VALUE,
b.SQL_FULLTEXT "详细SQL",
'select * from table(dbms_xplan.display_cursor('''||B.SQL_ID||''',null,''ALL''));' "执行计划分析sql"
from gv$session a, gv$sqlarea b 
where a.sql_address = b.address and A.STATUS='ACTIVE'
--and  a.SQL_EXEC_START
order by a.SQL_EXEC_START;

检查无效索引

select OWNER,INDEX_NAME,STATUS,TABLESPACE_NAME from dba_indexes WHERE STATUS='UNUSABLE' and owner='SCHEMA';

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

SELECT owner, object_name, object_type,status FROM dba_objects WHERE status='INVALID';

‍

查看已知列在哪张表中

select table_name,column_name from user_tab_columns where column_name like '%列名%';

查看表所属用户和表空间

select owner,table_name,tablespace_name from all_tables where table_name='T1'

‍

查询当前scn号

 select current_scn from v$database;

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

 select name,log_mode,open_mode from v$database;

若是归档模式，则LOG_MODE=ARCHIVELOG

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

查看表空间是否自动扩展

select file_name,autoextensible,increment_by from dba_data_files;

查看日志文件路径其中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 table teacher add constraint PK_id primary key(ID);

增加列

alter table teacher add (rx_sj   TIMESTAMP(6));

删除列

alter table tablename drop (column);

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 from user_tables t where table_name in ('TABLE_NAME');

‍

当表结构变动或者表数据变化较大的时候，需要做表分析，否者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  set t.tgjmc=ltrim(t.tgjmc);

再次查询，空格已经去除

注：

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

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

实例4：开并行

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

ALTER SESSION FORCE PARALLEL DML PARALLEL 16;   #dml操作会话指定并行度
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16; #查询操作会话指定并行度

commit后关闭并行

ALTER SESSION DISABLE PARALLEL DML;    
ALTER SESSION DISABLE PARALLEL QUERY;

单表指定并行
alter table emp parallel 8;
强行启用并行度来执行当前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 * from user_sys_privs t where t.privilege like upper('%link%');
命令查询，从结果上看到当前用户有创建权限
CREATE DATABASE LINK--所创建的dblink只能是创建者能使用，别的用户使用不了
CREATE PUBLIC DATABASE LINK--public表示所创建的dblink所有用户都可以使用
赋权不足需要在sys下执行
grant create public database link,create database link to user1;
2、创建dblink
创建语法
create database  link testDblink connect to 
USER_NAME identified by password using 
'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)
(HOST = 192.168.101.5)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = search)))';
TestDblink ：表示dblink名字，任意起
dbName ：表示 远程数据库的用户
dbPassword：表示 远程数据库的密码
HOST ：表示远程数据库IP
PORT ：表示远程数据库端口
SERVICE_NAME ：远程数据库的实例名,通过如下sql查询
select name,value from v$parameter where name='service_names'
3、使用dblink
select * from teacher@testDblink;
teacher为表名，testDblink为创建的dblink名称
查看用户下已经创建的dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select * from dba_db_links;
4、删除dblink
--删除DBLINK
DROP DATABASE LINK [name];  
--或  
DROP PUBLIC DATABASE LINK [name]; 
name为创建的dblink名称
实例:6：oracle数据导入导出
oracle运维过程中，经常会遇到数据库导入导出的情况，甚至整库数据迁移，可以使用如下思路。
expdp在导出数据时会评估大小，这里的评估是通过两种方式，默认是通过block去计算，通过估算可以很好的评估我们对于文件系统大小的计算，方便规划和使用
1、通过block大小去估算，默认评估方式
expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=user ESTIMATE=blocks
2、通过统计信息去估算
expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=user ESTIMATE=statistics
expdp导出
导出命令如下，如果不停机的情况下，建议使用scn好导出，停机情况下不需要scn
expdp  \"/ as sysdba\"  
cluster=no COMPRESSION=ALL  DUMPFILE=user1%U.dmp filesize=1G 
parallel=4 DIRECTORY=DATA_PUMP_DIR
CONTENT=ALL  logfile=user1.log flashback_scn=16777336348982
schemas=user1
说明：
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目录，然后执行如下命令导入数据。
impdp \"/ as sysdba\"   
DIRECTORY=DATA_PUMP_DIR  parallel=4  dumpfile=user1%U.dmp   
logfile=user20805.log   schemas=user2 table_exists_action=replace
说明：
1、schema表示导入的用户
2、TABLE_EXISTS_ACTION该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP
使用方法：TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }
当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;当设置为APPEND时,会追加数据,为TRUNCATE时,导入作业会截断表,然后为其追加新数据;当设置为REPLACE时,导入作业会删除已存在表,重建表并追加数据。
‍‍‍‍‍‍‍‍‍‍‍‍‍
往期推荐
＋
＋
＋
＋
请扫码