ORACLE常用sql

斯文K

已于 2023-11-24 17:58:58 修改

阅读量281

点赞数

分类专栏： ORACLE 随笔文章标签： oracle 数据库

于 2023-07-20 21:33:21 首次发布

本文链接：https://blog.csdn.net/Z_xingwei/article/details/131823750

版权

ORACLE 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

随笔

1 篇文章 0 订阅

订阅专栏

工作中常用到的oracle操作记录

文章目录

1、查询用户下表的大小

select *
  from (select t.tablespace_name,
               t.owner,
               t.segment_name,
               t.segment_type,
               sum(t.bytes / 1024 / 1024) mb
          from dba_segments t
         where t.segment_type = 'TABLE'
           and owner = '用户名'
         group by t.tablespace_name, t.OWNER, t.segment_name, t.segment_type) t
order by t.mb desc;

2、查询表空间使用率

SELECT /*+parallel(8)*/ T1 "表空间",
       Z / 1024 / 1024 / 1024 "总表空间G",
       (Z - S) / 1024 / 1024 / 1024 "已用表空间G",
       S / 1024 / 1024 / 1024 "剩余表空间G",
       ROUND((Z - S) / Z * 100, 2) "使用率%",
       (Z - S) / 1024 / 1024 / 1024 / 0.7 - (Z / 1024 / 1024 / 1024) "扩展空间"
  FROM (SELECT TABLESPACE_NAME T1, SUM(BYTES) S
  FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME), (SELECT TABLESPACE_NAME T2, SUM(BYTES) Z
                               FROM DBA_DATA_FILES
                              GROUP BY TABLESPACE_NAME)
WHERE T1 = T2 order by 5 DESC;

3、查询表空间对应数据文件

SELECT A.*  FROM DBA_DATA_FILES A  WHERE TABLESPACE_NAME='DWCA_TABLESPACE';
--添加表空间，指定数据存放路径，大小
alter tablespace DWCA_TABLESPACE add datafile '/mnt/sd03/oradata/dwca/dwcatablespace02.dbf' size 32767M  autoextend on;

4、EXPDP/IMPDP工具使用

导出当前用户全表并排除排除部分表

expdp 用户/密码@服务名 directory=DUMP_DIR dumpfile=test.dmp logfile=test.log EXCLUDE=TABLE:"LIKE'VRA%'"
# directory指定数据目录，提前创建好
# dumpfile导出的文件名
# logfile 日志文件
# EXCLUDE 要排除的表，可以模糊匹配

导出当前用户中包含部分条件的表

expdp 用户/密码@服务名 directory=DUMP_DIR dumpfile=jt_qutit06.dmp logfile=dump06.log INCLUDE=TABLE:"LIKE'TEST%'"
# INCLUDE包含指定的表

导入数据,remap_schema指定用户，remap_tablespace表空间，table_exists_action表存在删除重建

impdp 用户/密码@服务名 remap_schema=用户a:用户a remap_tablespace=TABLESPACE_A:TABLESPACE_A \
directory=DUMP_DIR logfile=jt_audit69.log dumpfile=jt_audit692022-08-04.dmp  table_exists_action=TRUNCATE  parallel=3 INCLUDE=TABLE:"IN('ZXW_TEST1')"
# remap_schema指定用户，
# remap_tablespace指定表空间
# directory指定数据目录，提前创建好
# dumpfile导出的文件名
# logfile 日志文件
# table_exists_action,使用impdp完成数据库导入时，若表已经存在，有四种的处理方式：
	# skip：默认操作,跳过
	# replace：先drop表，然后创建表，最后插入数据
	# append：在原来数据的基础上增加数据
	# truncate：先truncate，然后再插入数据
# parallel 设置并行

5、imp/exp导入导出操作

nohup exp 用户/密码@ip/服务名 file=/data/expdp/data.dmp full=y  buffer=81920000  >> /data/expdp/data.log 2>&1 &

nohup imp 用户/密码@ip/服务名 file=/data/expdp/data.dmp full=y statistics=none buffer=81920000 ignore=y >> ./data.log 2>&1 &

6、创建directory目录

--查询
select * from dba_directories ;
--创建
create or replace directory dump_dir as 'D:\dump\dir';
--赋权
grant read,write on directory dump_dir to user01;

7、暂停或终止expdp/impdp进程

oracle数据库执行数据泵导出时，使用组合键“CTRL+C”来终止导出操作。此时虽然前台的导出任务页面停止了，但数据库内部执行导出任务的session依旧在运行中，观察导出文件也依旧在增长中，此时需要按以下步骤操作终止导出任务。
前台“CTRL+C”终止前后，数据库内部session状态STATE字段均为‘EXECUTING’。具体如下：

select * from dba_datapump_jobs;查看正在运行的进程
linux终端执行，查看正在导出进程的详细信息
impdp JT_AUDIT2/DaoLong#23_wgl@dwca ATTACH=SYS_IMPORT_FULL_01
Export> stop_job=immediate/kill_job # 输入yes停掉或杀死进程
Export> start_job # 重启停掉的进程
sql查询session状态STATE字段由STOPPING变为NOT RUNNING ，任务停止完成
select * from dba_datapump_jobs;

8、清理归档日志

归档日志空间不足时会导致数据库启动失败，卡死，可以参考一下方式解决。

# 登录
sqlplus /nolog
SQL>conn /as sysdba
# 关闭数据库
SQL>shutdown immediate（不会立即关闭，会先释放资源再关闭，推荐使用） 或 shutdown abort （立即关闭会断开进程，生产场景最好勿用）
# 装载数据库
SQL>startup mount
# 查看归档日志大小
SQL>show parameter db_recovery_file_dest_size
# 查看归档日志空间使用情况
SQL>select * from v$recovery_file_dest;
# --1、调整归档日志大小
alter system set db_recovery_file_dest_size=10G
# --2、清理归档日志
rman target /
# --无效的过期的archivelog标出来
RMAN>crosscheck archivelog all;
# --删除3天前的归档日志
RMAN>delete noprompt archivelog until time "sysdate -3";
# --删除过期日志
RMAN>delete expired archivelog all;
# --删除截止今天的所有日志
RMAN>delete archivelog until time 'sysdate';
# --即删除所有的归档日志
RMAN>delete noprompt archivelog all;

9、参数设置

-- 查看参数信息show parameter;
-- system级别设置参数，立即生效，重启后失效，省略参数scope=both，
--当scope=spfile修改后的参数在下次启动DB时生效，适用于动态和静态初始化参数。
--10G默认CONNECT BY WITH FILTERING方式，11g中的CONNECT BY NO FILTERING WITH SW (UNIQUE)
--屏蔽oracle11新特性oracle connect by或使用/*+ connect_by_filtering */
alter system set "_optimizer_connect_by_elim_dups" = false;         
alter system set "_connect_by_use_union_all" = "old_plan_mode";
-- session级别设置参数，只在当前窗口生效
alter session set "_optimizer_connect_by_elim_dups" = false;
alter session set "_connect_by_use_union_all" = "old_plan_mode";

10、查看sql执行计划

EXPLAIN PLAN FOR
select matrix_dir_id from matrix_dir;  --要执行的sql语句
select * from table(dbms_xplan.display);

11、创建DBLINK

CREATE DATABASE LINK LINK131
CONNECT TO 用户 IDENTIFIED BY "密码"
USING'(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 端口号))
)
(CONNECT_DATA =
  (SERVICE_NAME = 服务名)
)
)';

12、恢复drop掉的表数据

--drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。
select object_name,original_name,type,droptime from user_recyclebin;
--object_name和original_name就是回收站存放的表名和原来删除的表名
--使用flashback（闪回）恢复误删除的数据 或 误删除的表
flashback table original_name to before drop;
flashback table object_name to before drop new_table_name;