Oracle 自学

最新推荐文章于 2025-02-01 13:18:45 发布

原创最新推荐文章于 2025-02-01 13:18:45 发布 · 186 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨了Ubuntu 12.04 Server环境下使用Oracle 11g SQL Developer进行数据库管理的基础概念，包括表空间、段、区段、数据块等关键元素的理解，以及如何通过SQL查询来获取和分析数据库信息，如表、索引、分区等对象的状态和大小。文章还提供了实例展示了如何创建表空间、管理内存消耗和空间利用，特别关注了表分区和索引在处理大量数据时的效率对比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[size=large][b]自己看的[/b][/size]

ubuntu12.04 server version
oracle11g
sqldeveloper

一些基础概念
tablespace 表空间 = 若干段组成
segment 段 = 若干区段组成
extent 区段 = 若干相邻的数据块
block 数据块

如何了解table,index,table partition大小
[img]http://dl.iteye.com/upload/attachment/0084/3848/5251f7cb-00fe-3415-aad2-76353fe554e1.png[/img]

结合数据库的常用表，了解数据库的一些信息
-- check all user;
select * from all_users;

-- current oracle database name
select ora_database_name from dual

-- current user object
select owner, object_type, status, count(*) count# from all_objects where owner = 'SCOTT' group by owner, object_type, status;
SELECT object_type FROM user_objects GROUP BY object_type;

-- all object own by current user
select * from user_objects where object_type = 'TABLE PARTITION';
select * from user_objects where object_type = 'SEQUENCE';
select * from user_objects where object_type = 'TRIGGER';
select * from user_objects where object_type = 'INDEX';
select * from user_objects where object_type = 'TABLE';

select * from user_tablespaces;
select * from user_sequences;
select * from USER_TABLES;
select * from USER_TRIGGERS;
select * from user_indexes;
select * from user_procedures;

-- good resource, segment type (table, table partition, index) check capacity of segement type
-- check capacity, come here
select * from user_segments;

-- consume disk space
select tablespace_name, initial_extent/1024/1024, next_extent/1024/1024, min_extents/1024/1024, max_extents/1024/1024, max_size/1024/1024 from user_tablespaces;
select segment_type, segment_name, bytes/1024/1024 capacity from user_segments where segment_type = 'TABLE';
select segment_type, segment_name, bytes/1024/1024 capacity from user_segments where segment_type = 'TABLE PARTITION';
select segment_type, segment_name, bytes/1024/1024 capacity from user_segments where segment_type = 'INDEX';

结合oem,了解当前数据库主要空间消耗情况

-- start oem (Oracle Enterprise Manager) in ubuntu
0) $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/uix-config.xml inaccessible->accessible，
1) emctl start dbconsole
2) https://ubuntu:1158/em/
3) emctl start dbconsole

手动创建表空间 JRD_GPS, TAXI_Temp
[img]http://dl.iteye.com/upload/attachment/0084/3914/6d89b2f1-41bb-31df-8f2d-b5af54cc651c.png[/img]

Table Partition内存消耗， [b]（三千万条数据，2G多）[/b]
[img]http://dl.iteye.com/upload/attachment/0084/3910/c46f04db-16b3-31c5-baee-a4ab437de67e.png[/img]

Table Partition索引消耗 [b]（三千万条数据，主键索引4G，吓，其他键1G不到）[/b]
[img]http://dl.iteye.com/upload/attachment/0084/3912/c5673d1f-dd51-3872-87ff-ac765546cead.png[/img]