CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONEVARCHAR2(15) NOT NULL, EMAILVARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLE...
OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024size_MBfromdba_segmentswheresegment_type='TABLE PARTITION'and tablespace_name='&1'orderby5desc; 4.查询某个表空间中,所有【分区索引】的名称、大小: selectOWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_...
select segment_name,segment_type,partition_name, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'ZBX5_USER' and partition_name like 'P_%' group by segment_name,segment_type,partition_name; 手动删除分区 代码语言:txt AI代码解释 alter table TRENDS drop partition P_20221208 updat...
-- 3)将上一步得到的id:1670428800传入at()函数,时间和分区名保持一致,这里是P_20221208altertableTRENDS splitpartitionP_MAXat(1670428800)into(partitionP_20221208,partitionP_MAX)updateglobalindexes; 1.5 查询分区表 selectsegment_name,segment_type,partition_name,sum(bytes)/1024/1024/1024GBfromdba_segmentsw...
ALTER TABLE sales ADD PARTITION sales2000_q1 VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’) TABLESPACE ts_sale2000q1; 其他分区sales2000_q1,sales2000_q1,sales2000_q1依样画葫芦。 3 、删除不必要的分区: 公司规则:发卖的明细数据两年内必需保管在线。到2001年,DBA必需将1999年的数据备份...
partition_name, segment_type, bytes / 1024 / 1024 "字节数(M)", tablespace_name from user_segments where segment_name IN ('RANGE_PART_TAB', 'NOR_TAB'); 1. 2. 3. 4. 5. 6. 7. 二、分区表优势 引用Oracle官方文档的说法,https://docs.oracle.com/cd/B19306_01/server.102/b14220/part...
select segment_name,segment_type,partition_name, sum(bytes)/1024/1024/1024 GBfrom dba_segmentswhere owner = 'ZBX5_USER'and partition_name like 'P_%'group by segment_name,segment_type,partition_name; 手动删除分区 alter table TRENDS drop partition P_20221208 update gl...
where segment_type='TABLE PARTITION' and owner='EDW' and TABLESPACE_NAME='DATA_TBS_HIS' and (bytes/1024/1024/1024)>1; commit; 1. 2. 3. 4. 5. 6. 7. 8. 4、查询表碎片信息 数据段大小减去实际数据大小,近似于碎片大小 --查询指定分区数据段大小 ...
create table pdba ( id number, time date) partition by range (time) --创建基于日期的范围分区并存储到不同的表空间 ( partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), partition p3 values ...
alter table TRENDS split partition P_MAX at(1670428800) into (partition P_20221208,partition P_MAX) update global indexes; 1.5 查询分区表 select segment_name,segment_type,partition_name, sum(bytes)/1024/1024/1024 GB from dba_segments