Oracle 自学

自己看的

ubuntu12.04 server version
oracle11g
sqldeveloper

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

如何了解table,index,table partition大小



结合数据库的常用表，了解数据库的一些信息
-- 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


Table Partition内存消耗， （三千万条数据，2G多）


Table Partition索引消耗   （三千万条数据，主键索引4G，吓，其他键1G不到）