数据迁移-oracle



oracle

oracle

not in 优化

反向索引

使用exists替换not in

使用inner join替换not in

使用limit限制结果集

存储过程

赋值  := 要连着一起写

TO_DATE('2021-01-01', 'YYYY-MM-DD')

查看库里面的存储过程

select text from user_source where name = upper('存储过程名') order by line ;

hint

通过特定的注释来指导数据库查询优化器如何执行查询，从而优化查询性能

当优化器自动生成的执行计划不够理想时

可以通过Hint来指定优化器使用的存取路径或连接类型

常见的Hint类型

与优化器模式相关的Hint

ALL_ROWS：指示优化器选择基于开销的优化方法，以获得最佳吞吐量，使资源消耗最小化。

FIRST_ROWS(n)：返回查询结果前n行 的执行计划

CHOOSE：如果数据字典中有访问表的统计信息，则基于开销的优化方法；如果没有，则基于规则的开销优化方法。

RULE：强制优化器使用基于规则的优化方法

与表访问相关的Hint

FULL(table)：指示优化器对指定表执行全表扫描。

ROWID(table)：指示优化器对指定表根据ROWID进行访问

与索引访问相关的Hint

INDEX(table index_name)：指示优化器对指定表上的索引执行索引扫描。

NO_INDEX(table index_name)：禁止优化器对指定表上的索引执行索引扫描。

INDEX_DESC(table index_name)：指示优化器对指定表上的索引执行降序扫描。

INDEX_FFS(table index_name)：指示优化器对指定表上的索引执行快速全索引扫描

与表连接顺序相关的Hint

ORDERED：指示优化器按照FROM子句中表的顺序进行连接。

LEADING(table1, table2, ...)：指定多个表的连接顺序

与表连接方法相关的Hint

USE_HASH(table1, table2, ...)：指示优化器对指定表使用哈希连接。

哈希连接

USE_NL(table1, table2, ...)：指示优化器对指定表使用嵌套循环连接。

嵌套循环连接

USE_MERGE(table1, table2, ...)：指示优化器对指定表使用排序合并连接

排序合并连接

注

告诉优化器 用哪个索引 如何连接

外层循环遍历驱动表 内层循环比遍历被驱动表 小表驱动大表

作用：改善查询性能，特别是在优化器未能自动选择最佳执行计划时

使用场景：在统计信息无误、物理结构合理但查询性能仍不理想时，可以考虑使用Hint

sqlplus as / sysdba

解决半删除状态的问题

用oracle用户登录

执行 drop table 表名 purge;

查oracle的用户

select username from allusers ;

数据字典视图

DBA_OBJECTS

字段

OBJECT_NAME：对象的名称

OBJECT_ID：对象的唯一标识符

DATA_OBJECT_ID：数据对象的唯一标识符（对于表、索引等）

OBJECT_TYPE：对象的类型（如表、索引、视图等）

OWNER：对象的拥有者（即创建该对象的用户或模式）

STATUS：对象的状态（如有效、无效等）

LAST_DDL_TIME：对象最后一次DDL操作的时间戳

使用场景

查表的创建时间 所有者 最后的修改时间

all_tables

TABLE_NAME：表的名称。

OWNER：表的所有者，即创建表的用户。

TABLESPACE_NAME：表所属的表空间。

STATUS：表的状态，可能的值包括VALID（有效）、INVALID（无效）等

tab

dba_tab_privs

表权限相关

DBA_DATA_FILES

FILE_NAME：

类型：VARCHAR2(513)

描述：数据库数据文件的名称，也即是物理文件存放地址。

FILE_ID：

类型：NUMBER

描述：数据库数据文件的ID，是数据库文件的唯一标识。

TABLESPACE_NAME：

类型：VARCHAR2(30)

描述：数据文件所属的表空间的名字。

BYTES：

类型：NUMBER

描述：数据文件的大小，以字节（bytes）为单位。

BLOCKS：

类型：NUMBER

描述：数据文件的大小，以 Oracle 块为单位。

STATUS：

类型：VARCHAR2(9)

描述：数据文件的状态，可能的值为 "INVALID" 或 "AVAILABLE"。

RELATIVE_FNO：

类型：NUMBER

描述：表空间相对文件号。

AUTOEXTENSIBLE：

类型：VARCHAR2(3)

描述：指示数据文件是否可自动扩展，可能的值为 "YES" 或 "NO"。

MAXBYTES：

类型：NUMBER

描述：如果数据文件是自动扩展的，此字段表示数据文件可以增长到的最大字节数。

MAXBLOCKS：

类型：NUMBER

描述：如果数据文件是自动扩展的，此字段表示数据文件可以增长到的最大块数。

INCREMENT_BY：

类型：NUMBER

描述：当数据文件自动扩展时，每次增加的字节数或块数。

USER_BYTES：

类型：NUMBER

描述：用户数据使用的字节数。

USER_BLOCKS：

类型：NUMBER

描述：用户数据使用的块数。

ONLINE_STATUS：

类型：VARCHAR2(7)

描述：数据文件的在线状态，可能的值包括 "ONLINE" 或 "OFFLINE"

导数

流程

复制dmp文件导数据泵能找到的目录

给文件赋权

chmod 777 x.dmp

创建用户并赋权

测试新建的用户是否可以连接

sqlplus 用户名/密码

生成sql文件 查看文件的内容 如tablespace  schema

impdp 用户名/密码 DIRECTORY=EXPDP_DIR DUMPFILE=HSOWN01.dmp sqlfile=a.sql

导入

impdp 用户名/密码 DIRECTORY=EXPDP_DIR DUMPFILE=HSOWN01.dmp remap_schema=a:b REMAP_TABLESPACE=c:d logfile=k.logfile

注意  换行 需要加 \

导出

传统的 exp 工具

数据泵

expdp username/password@database schemas=schema_name dumpfile=export.dmp logfile=export.log

问

如何找到remap_schema的原来的schema

vi a.sql

:set ic 不区分大小写

/schema

create table "a"."b"

a 就是 schema

如何找tablespace

grep -i 'TABLESPACE' a.sql | sort -u

权限

用root用户

chmod 用户名 目录名

change mode

修改目录的访问权限

chown

change owner

修改所有者

给用户建表的权限

grant create table to 用户名

索引

删除索引在线

drop index index_name online ;

使用场景 索引的存在导致导入数据特别慢

建索引在线 + 并发

并发创建索引

create index anbmx_indx3 on anbmx(a,b) online parallel 8 ;

alter index anbmx_index3 noparallel ;

建完之后关掉并发

表空间

sqlplus / as sysdba

要用dba操作

加临时表空间

alter tablespace temp add tempfile '/oradata/cbs/datafile/temp02.dbf' size 30000M;

加小表空间

alter tablespace SMALLDBS add tempfile '/oradata/cbs/datafile/SMALLDBS3.dbf' size 30000M;

加索引空间

alter tablespace MIDDLE_IDXDBS add tempfile '/oradata/cbs/datafile/MIDDLE_IDXDBS_31.dbf' size 30000M;

用户

创建用户需要指定默认的表空间 不让建索引的时候会建到系统空间里面去

-- 创建用户并指定默认表空间和临时表空间 

CREATE USER new_user 

IDENTIFIED BY password  -- 请将 password 替换为你希望设置的密码 

DEFAULT TABLESPACE "默认表空间名称"  -- 指定默认表空间 

TEMPORARY TABLESPACE "临时表空间名称";  -- 指定临时表空间

看用户下面有那些表

select tname from tab ;

删除用户

drop user cascade ;

查所有的用户

select username from all_users;

死锁

showlock

终止会话

alter system kill session 'a,b'  a是sid  b是serial#

日志

查日志中的错误

cat *.log | grep "failed"

sftp

命令

cd lcd

获取单个文件

get 文件名

获取多个文件

mget 文件名*

目录

DBA_DIRECTORIES是一个系统视图

创建目录对象

CREATE DIRECTORY my_dir AS '/path/to/directory';

授予访问权限

GRANT READ, WRITE ON DIRECTORY my_dir TO my_user;

查询

select * from dba_directories where directory_name = upper('expdp_dir');

查询

层次查询

START WITH ... CONNECT BY PRIOR

eg

SELECT column1, column2, ..., LEVEL

FROM table_name

START WITH condition (condition 可以是 a.b = c.d)

CONNECT BY PRIOR column_name = parent_column_name;

查表的创建时间和最后修改时间

select object_name , created , last_ddl_time , owner from dba_objects where object type ='TABLE' and object_name = upper('bajdk');

查表的分区

select partition_name from user_tab_partitions where table_name = 'ANBMX' order by 1

数据库名

select name from v$database;

相关参数

undo空间|表空间|索引空间

temp

sga

System Global Area，系统全局区

pga

Program Global Area，程序全局区

processes

最大并发数量

top

物理内存

虚拟内存

df -h

其他

数据库实例的唯一名称标识

echo $ORACLE_SID

SQL语句调优

用DBMS_SQLTUNE包

授予权限

GRANT ADVISOR TO your_user;

创建调优任务

DECLARE

  tuning_task_name VARCHAR2(30);

  tuning_sqltext CLOB;

BEGIN

  tuning_sqltext := 'SELECT * FROM your_table WHERE your_column = :bind_variable';

  tuning_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

    sql_text => tuning_sqltext,

    bind_list => sql_binds(anydata.convertvarchar2('your_bind_value')),

    scope => 'COMPREHENSIVE',

    time_limit => 60,

    task_name => 'your_tuning_task',

    description => 'Tune your SQL statement'

  );

END;

/

执行调优任务

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('your_tuning_task');

查看调优结果

避免折行看不到

SET LONG 1000000

SET PAGESIZE 0

SET LINESIZE 150

SET SERVEROUTPUT ON SIZE 1000000

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('your_tuning_task') FROM DUAL;

删除调优任务

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('your_tuning_task');

查看任务的状态

SELECT task_name, status, start_time, end_time, time_remaining, elapsed_time

FROM user_advisor_tasks

WHERE task_type = 'SQL TUNING ADVISOR'

  AND task_name = 'your_tuning_task_name';

数据库备份

用expdp

创建目录对象

CREATE OR REPLACE DIRECTORY expdp_dir AS '/path/to/backup';

赋予权限

GRANT READ, WRITE ON DIRECTORY expdp_dir TO your_user;

GRANT all ON DIRECTORY expdp_dir TO your_user;

执行expdp导出

expdp your_user/your_password@your_db DIRECTORY=expdp_dir DUMPFILE=full_backup.dmp LOGFILE=full_backup.log FULL=Y

expdp cbs1qy/密码@10.225.240.1:17632/cbs DIRECTORY=expdp_dir DUMPFILE=dmp20241111%U.dmp schemas=cbs1qy compression=all parallel=8 LOGFILE=dmp20241111.log

库对库传数

Database Link

创建Database Link

CREATE DATABASE LINK link_to_orclB

CONNECT TO userB IDENTIFIED BY passwordB

USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orclB)))';

使用Database Link查询数据

SELECT * FROM employees@link_to_orclB;

使用Database Link插入数据

INSERT INTO employees@link_to_orclB

SELECT * FROM new_employees;

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

  • oracle错误集合
    ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 O...
    我想起个好名字阅读 11,036评论 0赞 9
  • Oracle.1Z0-052-0
    一、源题QUESTION 1The instance abnormally terminates because ...
    猫猫_tomluo阅读 5,607评论 0赞 2
  • 15天的OCP培训笔记
    第一天 7月13日OCP笔记： Oracle Ocp11g准备资料： OracleFundmentals 书 管理...
    fjxCode阅读 7,804评论 0赞 4
  • ORACLE数据泵
    数据泵使用EXPDP和IMPDP时应该注意的事项： EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以...
    黏小莲阅读 4,330评论 0赞 3
  • oracle数据泵的学习
    --21.1 Data Pump工具Data Pump从oracledatabase 10g开始引入了data p...
    liutoliu阅读 7,895评论 0赞 1

友情链接更多精彩内容