Oracle常用操作整理_数据量较大可参考

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第8天，点击查看活动详情。

1.创建自动分区表 适用场景：

1）创建一个新的自动分区表

2）数据库迁移时，对于数据量较大的自动分区表，重新导入后无法保留自动分区属性，需要手动进行数据表重构，再进行数据导入。

/*

根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))

根据时分秒: INTERVAL(NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'}))

*/

--创建自动分区的新表，以“按月分区表”举例

-- Create table

create table IX_ODS_TRIPSUMMARY_1

(

ods_id NUMBER not null,

ods_type NUMBER,

ods_timestart DATE,

ods_timeend DATE,

ods_tripamount NUMBER(12),

ods_transferamount NUMBER(12),

ods_transportvolume NUMBER(12),

ods_tripdistanceavg NUMBER(10,2),

ods_triptimecostavg NUMBER(10,2),

ods_transfercoefficient NUMBER(10,3),

ods_transfertimecostavg NUMBER(12,2),

ods_transferstepdistanceavg NUMBER(12),

ods_consumeamount NUMBER(12,2),

ods_preferentialamount NUMBER(12,2),

ods_relatedkey VARCHAR2(50),

ods_routeid VARCHAR2(10),

ods_subrouteid VARCHAR2(10),

ods_segmentid VARCHAR2(20),

ods_stationid VARCHAR2(20)

)

PARTITION BY RANGE (ods_timestart) interval (numtoyminterval(1, 'month'))

STORE IN (tbs_ose_ic)

(

partition p_2016 values less than (date '2017-01-01')

tablespace tbs_ose_ic

);

-- Add comments to the columns

comment on column IX_ODS_TRIPSUMMARY_1.ods_id

is '标识';

comment on column IX_ODS_TRIPSUMMARY_1.ods_type

is '1:出行和换乘总量(半小时)2:出行分析(天),3:线路出行换乘量,4:站点出行换乘量5出租半小时,

6:线路下站点出行换乘量,7:小区出行量,8:小区到达量,9:小区间出行量,10:换乘半小时

11:站点O半小时,12:站点D半小时,13:线路站点O半小时,14:交通走廊内部半小时,15:交通走廊到发半小时

21:线路站点满载率半小时';

comment on column IX_ODS_TRIPSUMMARY_1.ods_timestart

is '本周期开始时间';

comment on column IX_ODS_TRIPSUMMARY_1.ods_timeend

is '本周期结束时间';

comment on column IX_ODS_TRIPSUMMARY_1.ods_tripamount

is '出行总流量 乘坐公交出行的旅客出行OD总次数。 ';

comment on column IX_ODS_TRIPSUMMARY_1.ods_transferamount

is '换乘总流量 旅客在出行中转换线路或方式的总次数。';

comment on column IX_ODS_TRIPSUMMARY_1.ods_transportvolume

is '客运周转量 公交旅客出行流量与乘坐距离的乘积。单位：公里';

comment on column IX_ODS_TRIPSUMMARY_1.ods_tripdistanceavg

is '平均运距 平均每位旅客单次公交出行的距离。单位：公里';

comment on column IX_ODS_TRIPSUMMARY_1.ods_triptimecostavg

is '平均耗时 平均每位旅客单次公交出行的时间。单位：分钟';

comment on column IX_ODS_TRIPSUMMARY_1.ods_transfercoefficient

is '换乘系数 换乘刷卡次数与出行OD量之间的比值。 ';

comment on column IX_ODS_TRIPSUMMARY_1.ods_transfertimecostavg

is '换乘时间 旅客前程下车与后程上车间的时间差值。秒';

comment on column IX_ODS_TRIPSUMMARY_1.ods_transferstepdistanceavg

is '换乘站距 旅客前程下车与后程下车间的走行距离。米';

comment on column IX_ODS_TRIPSUMMARY_1.ods_consumeamount

is '消费总金额 消费总金额';

comment on column IX_ODS_TRIPSUMMARY_1.ods_preferentialamount

is '优惠总金额 旅客通过免费换乘政策获取的优惠金额。元';

comment on column IX_ODS_TRIPSUMMARY_1.ods_relatedkey

is '关联键值';

comment on column IX_ODS_TRIPSUMMARY_1.ods_routeid

is '线路ID》关联MCROUTEINFOGS';

comment on column IX_ODS_TRIPSUMMARY_1.ods_subrouteid

is '子线路ID》关联MCSUBROUTEINFOGS';

comment on column IX_ODS_TRIPSUMMARY_1.ods_segmentid

is '单程ID》关联MCSEGMENTINFOGS';

comment on column IX_ODS_TRIPSUMMARY_1.ods_stationid

is '站点ID》关联MCSTATIONINFOGS';

-- Create/Recreate primary, unique and foreign key constraints

alter table IX_ODS_TRIPSUMMARY_1

add constraint IX_ODS_TRIPSUMMARY_1_PK primary key (ODS_ID)

using index

tablespace TBS_OSE_IC

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

--导入数据

insert into IX_ODS_TRIPSUMMARY_1 select * from IX_ODS_TRIPSUMMARY;

commit;

--删掉表

truncate table IX_ODS_TRIPSUMMARY;

drop table IX_ODS_TRIPSUMMARY;

--冲命名

rename IX_ODS_TRIPSUMMARY_1 to IX_ODS_TRIPSUMMARY;

--添加分区索引

create index index_name on IX_ODS_TRIPSUMMARY (column1,column2)

nologging local tablespace tbs_ose_indx;

2.手动执行数据收集 适用场景：

在数据库导入大量数据后（跨数量级），马上进行查询，查询速度是很慢的。是因为Oracle默认会开启每日自动统计数量级，一旦改变某个表中的数量级Oracle无法立马做出反应，通常在第二天查询会恢复正常。如何立马提升查询效率？执行下面的语句手动执行数据收集。

exec dbms_stats.gather_table_stats(ownname => 'hiose2019',tabname => 'BZ_BUSRUNRECORDLD',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);

3.按分区删除数据 适用场景：

大数据量的分区表批量删除数据时，使用 delete命令效率低，可以拷贝出需要删除数据所在的分区，使用Excel（或其他工具）整理好，批量执行。

alter table bz_icprocessinfogs drop partition SYS_P5926;

alter table bz_icprocessinfogs drop partition SYS_P5928;

alter table bz_icprocessinfogs drop partition SYS_P5929;

4.数据泵导入/导出操作（impdp/expdp） 适用场景：

适用于Oracle10g及更高版本，常用于大数据量的表进行导出操作，速度快

1.创建虚拟目录

create or replace directory dp_dir as 'D:\Hiose3.1\impdp';

2.授权

grant read,write on directory dp_dir to hiose2019;

不能自己给自己授权，需使用系统管理员

sqlplus /@orcl1 ad sysdba

3.查看是否创建成功

select * from dba_directories

4.在目录下手动创建文件夹。

（以上4步导入、导出均适用）

一、导入

1.将需要导入的dmp文件放至此文件夹中

2.需要确认：①dmp文件是用数据泵导出的；②需要知道导出数据库的用户名

3.执行：

impdp hiose2019/gjds2019@10.16.111.14:1521/orcl1 REMAP_SCHEMA = hiose:hiose2019 table_exists_action = append directory=dp_dir dumpfile=BSVCBUSRUNDATALD5_20200401-0402.DMP logfile=BSVCBUSRUNDATALD5_20200401-0402.log

说明：

①REMAP_SCHEMA =导出的用户名:导入的用户名

②table_exists_action={skip,如果已存在表则跳过；append，只增加数据；truncate，截断表然后增加数据；replace，删除已存在的表，重新建表并增加数据}

③directory=虚拟目录名

注意：

自动分区表，需要先进行表重构，再进行数据导入

二、导出

1.按条件导出

expdp yt/ed456 directory=UWP_DIR dumpfile=NEW_INDEX_TOOL_NOW.dmp tables=NEW_INDEX_TOOL_NOW query="'where pub_time>to_date(''2019-12-15 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')'"

2.按用户导出

Schemas=hiose

3.按表空间导出

Tablespaces=hiose_bsvc

4.整库导出

expdp hiose2019/gjds2019@orcl1 dumpflie=full.dmp lodfile=full.log full=y directory=dmp

5.创建用户/表空间、扩展表空间 --创建用户

1.打开cmd命令窗口

2.输入splplus /@XXX as sysdba

（XXX为实例名，服务中OracleServiceXXX可查看）

3.输入create user hiose2019 identified by gjds2019;

（创建用户名hiose2019 密码gjds2019，记得加分号）

4.输入grant connect,resource,dba to hiose2019;

(授权，记得加分号)

5.查看当前所有用户select username from dba_users;

创建用户成功！！

--创建表空间

CREATE TABLESPACE TBS_OSE_OD DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL1\TBS_OSE_OD.DBF'

SIZE 200M AUTOEXTEND ON NEXT 64M MAXSIZE unlimited;

define TABLESPACE_DATA='tbs_ose';

define TABLESPACE_INDEX='tbs_ose_indx';

define TABLESPACE_GPS = 'tbs_ose_bsvc';

define TABLESPACE_ARRLFT = 'tbs_ose_arrlft';

define TABLESPACE_BZ = 'tbs_ose_bz';

define TABLESPACE_JZ = 'tbs_ose_jz';

define TABLESPACE_IC = 'tbs_ose_ic';

define TABLESPACE_EM = 'TBS_ose_EM';

define TABLESPACE_OD = 'TBS_ose_OD';

（表空间与文件名称一致，全部大写）

--扩展USERS表空间

ALTER TABLESPACE TBS_OSE_BSVC ADD DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL1\TBS_OSE_BSVC04.DBF'

SIZE 200M AUTOEXTEND ON NEXT 64M MAXSIZE unlimited;

6.死锁的解锁方式 适用场景：

出现死锁时会出现查询不出来的现象，执行以下语句，注意区别正常锁。

select b.* from v$locked_object a,v$session b where a.SESSION_ID=b.SID;

--不断刷新，如果有一直存在的，基本为死锁，执行下面语句

alter system kill session '1023,31439'; --'SID,SERIAL#'

7.查询占用空间 select t.segment_name, t.segment_type , sum(t.bytes / 1024 / 1024) "占用空间(M)"

from dba_segments t

where t.segment_type='TABLE PARTITION'

and t.owner = 'HIOSE2020'

and t.segment_name='OSEBUSRUNPOINTDAY'

group by OWNER, t.segment_name, t.segment_type;