Select Segment_Name,segment_type,Sum(bytes)/1024/1024 sizeName_M From User_Extents Group By Segment_Name,segment_type order by sizeName_M desc
select * from user_operation_log_p
Select Count(1) From user_operation_log
Select Count(1) From alarm_events
Select * From platform_alarm_events_process
--数据主要来源于触发器,登录数据来源程序写入
select * from T_MEGA_LOG_REPORT where log_desc='登录系统' order by create_time desc
Select Count(1) From T_MEGA_LOG_REPORT
select log_desc from T_MEGA_LOG_REPORT group by log_desc
-- Create table
create table T_MEGA_LOG_REPORT_P
(
id CHAR(31) not null,
user_id CHAR(31),
user_name VARCHAR2(255),
device_id CHAR(31),
device_type VARCHAR2(255),
log_desc VARCHAR2(255),
log_type VARCHAR2(255),
create_time DATE,
status NUMBER(1),
is_upload NUMBER(1),
organ_id CHAR(31),
event_id VARCHAR2(255)
)
partition by range (create_time)
(
partition P_MEGALOG_201601 values less than (to_date('2016-02-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201602 values less than (to_date('2016-03-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201603 values less than (to_date('2016-04-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201604 values less than (to_date('2016-05-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201605 values less than (to_date('2016-06-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201606 values less than (to_date('2016-07-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201607 values less than (to_date('2016-08-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201608 values less than (to_date('2016-09-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201609 values less than (to_date('2016-10-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201610 values less than (to_date('2016-11-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201611 values less than (to_date('2016-12-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201612 values less than (to_date('2017-01-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201701 values less than (to_date('2017-02-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201702 values less than (to_date('2017-03-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201703 values less than (to_date('2017-04-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201704 values less than (to_date('2017-05-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201705 values less than (to_date('2017-06-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201706 values less than (to_date('2017-07-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201707 values less than (to_date('2017-08-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201708 values less than (to_date('2017-09-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201709 values less than (to_date('2017-10-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201710 values less than (to_date('2017-11-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201711 values less than (to_date('2017-12-01', 'yyyy-mm-dd')),
partition P_MEGALOG_201712 values less than (to_date('2018-01-01', 'yyyy-mm-dd'))
);
-- Create/Recreate indexes
create index IDX_CREATE_TIME_LOG on T_MEGA_LOG_REPORT (CREATE_TIME);
create index IDX_EVENT_ID on T_MEGA_LOG_REPORT (EVENT_ID);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_MEGA_LOG_REPORT add constraint PK_T_MEGA_LOG_REPORT primary key (ID);
create table USER_OPERATION_LOG_P
(
id VARCHAR2(31) not null,
optimistic_lock NUMBER(19) not null,
user_id VARCHAR2(255) not null,
user_name VARCHAR2(255),
user_session_id VARCHAR2(255) not null,
f_time NUMBER(19) not null,
target_type VARCHAR2(255) not null,
target_id VARCHAR2(255) not null,
target_name VARCHAR2(255) not null,
operation_id VARCHAR2(255) not null,
operation_name VARCHAR2(255) not null,
effect_id VARCHAR2(255) not null,
effect_name VARCHAR2(255) not null,
ip VARCHAR2(16)
)
partition by range (F_TIME)
(
partition P_USEROPERLOG_201501 values less than (1422720000000),
partition P_USEROPERLOG_201502 values less than (1425139200000),
partition P_USEROPERLOG_201503 values less than (1427817600000),
partition P_USEROPERLOG_201504 values less than (1430409600000),
partition P_USEROPERLOG_201505 values less than (1433088000000),
partition P_USEROPERLOG_201506 values less than (1435680000000),
partition P_USEROPERLOG_201507 values less than (1438358400000),
partition P_USEROPERLOG_201508 values less than (1441036800000),
partition P_USEROPERLOG_201509 values less than (1443628800000),
partition P_USEROPERLOG_201510 values less than (1446307200000),
partition P_USEROPERLOG_201511 values less than (1448899200000),
partition P_USEROPERLOG_201512 values less than (1451577600000),
partition P_USEROPERLOG_201601 values less than (1454256000000),
partition P_USEROPERLOG_201602 values less than (1456761600000),
partition P_USEROPERLOG_201603 values less than (1459440000000),
partition P_USEROPERLOG_201604 values less than (1462032000000),
partition P_USEROPERLOG_201605 values less than (1464710400000),
partition P_USEROPERLOG_201606 values less than (1467302400000),
partition P_USEROPERLOG_201607 values less than (1469980800000),
partition P_USEROPERLOG_201608 values less than (1472659200000),
partition P_USEROPERLOG_201609 values less than (1475251200000),
partition P_USEROPERLOG_201610 values less than (1477929600000),
partition P_USEROPERLOG_201611 values less than (1480521600000),
partition P_USEROPERLOG_201612 values less than (1483200000000),
partition P_USEROPERLOG_201701 values less than (1485878400000),
partition P_USEROPERLOG_201702 values less than (1488297600000),
partition P_USEROPERLOG_201703 values less than (1490976000000),
partition P_USEROPERLOG_201704 values less than (1493568000000),
partition P_USEROPERLOG_201705 values less than (1496246400000),
partition P_USEROPERLOG_201706 values less than (1498838400000),
partition P_USEROPERLOG_201707 values less than (1501516800000),
partition P_USEROPERLOG_201708 values less than (1504195200000),
partition P_USEROPERLOG_201709 values less than (1506787200000),
partition P_USEROPERLOG_201710 values less than (1509465600000),
partition P_USEROPERLOG_201711 values less than (1512057600000),
partition P_USEROPERLOG_201712 values less than (1514736000000),
partition P_USEROPERLOG_OTHER values less than (maxvalue)
);
-- Create/Recreate indexes
create index F_TIME_INDEX on USER_OPERATION_LOG (F_TIME);
create index USER_ID_INDEX on USER_OPERATION_LOG (USER_ID);
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_OPERATION_LOG add constraint PK_T_USER_OPERATION_LOG primary key (ID);
drop table USER_OPERATION_LOG_p;
--添加分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--显示当前用户所有分区表的子分区列信息:
select * from USER_SUBPART_KEY_COLUMNS
--显示数据库所有分区表的信息:
select * from DBA_PART_TABLES
分享到:
相关推荐
Oracle10g表分区技术管理.pdf
文件是本人oracle10g分区表自动按时间创建、删除分区的存储过程,测试代码,通过job调用存储过程,每天午夜12点运行一次。妥妥!跟大家分享下!
oracle10g分区表的简介,描述和使用
范围、哈希、列表、组合分区创建分区表的方法
oracle expdp impdp 分区表重映射导出导入 数据迁移方案,以SI01用户为例子,将用户分区表导出后,将分区表重映射到新的表空间,完成数据迁移和检查。照方案例子按步去做,一定能成功。
包括表分区方法、索引分区方法。 范围分区(range partitioning); 哈希分区(hash partitioning); 列表分区(list partitioning); 范围-哈希组合分区(composite range-hash partitioning); 范围-列表组合...
总结描述Oracle 11g分区表的种类及分区索引的类型。范围分区,列表分区,散列分区,组合分区,哈希分区,全局索引,分区索引
分区表坏块处理的一个实验,重点描述如何模拟分区表坏块,以及对坏块现场的处理过程。
oracle11g新特性--通过分区进行优化
1z0-033-11(B) 发布不同oracle文件的原因 诊断表空间 表空间分区原因 检查点工作 检查点优化 调整重做日志 13 1z0-033-13 关于自动段空间管理 ext 与oracle 空间使用 percent oracle块参数 行迁移问题 什么时间进行...
Oracle 数据库 11g 中的分区.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g:可管理性概述.pdf Oracle 数据库 11g:新特性概述.pdf Oracle 真正应用集群 11g .pdf Oracle性能...
Oracle 数据库 11g 中的分区.pdf Oracle 数据库 11g:可管理性概述.pdf Oracle 数据库 11g:新特性概述.pdf Oracle 真正应用集群 11g .pdf Oracle高级压缩.pdf Oracle性能优化包 11g .pdf Oracle真正应用测试...
oracle11g表分区实现原理,与各种应用业务场景实例指导
安装RAC集群,首先要创建虚拟机共享磁盘,然后分区,挂载裸设备和进行ASM分区,然后安装Oracle集群服务CRS,最后安装Oracle10g. 由于篇幅所限,本文档去除了所有图片。如需所要,请评论 ORACLE10g RAC FOR SUSE ...
ORACLE11g中表分区的新特性.pdf
资源中包含的分区的基本命令,可以进行熟悉数据库分区的用户就行训练。
Oracle 11g 官方中文文档 包括: Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g 中的分区.pdf Oracle 数据库...
Oracle_Database_11g_Release_2分区功能详解
官方资料:Oracle白皮书_Oracle数据库11g中的分区 分区的优势:分区的基本知识;使用分区提高可管理性;使用分区提岛性能;使用分区提高可用性;分区一为业务建模:基本分区策略;分区扩展;PARTITION ADVISOR;分区策略和...