课程第1/2次（2018-09-18星期二和2018-09-20星期四）

botang · 发表于 2018-9-20 19:23:05

1. 虚拟机（环境下载）：
ftp://124.16.180.172/pub/594_BCP_Softwarefiles_WindowsVM_V4/1_下载/2_课程实验环境下载/40_O11g_Runtime/

VMware打开要选择“移动”。

验证：su - oracle：crs_stat -t 有以下输出：

[oracle@station80 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station80
ora.FRA.dg ora....up.type ONLINE ONLINE station80
ora....ER.lsnr ora....er.type ONLINE ONLINE station80
ora.asm ora.asm.type ONLINE ONLINE station80
ora.cssd ora.cssd.type ONLINE ONLINE station80
ora.diskmon ora....on.type ONLINE ONLINE station80
ora.orcl.db ora....se.type ONLINE ONLINE station80

复制代码

【修这虚拟机（root）：
/usr/sbin/crs.sh
su - oracle -c  /usr/bin/emca.sh】

启动图形界面（oracle）：
emctl status/start/stop  dbconsole

https://192.168.0.80:1158/em

2. 描述一下什么叫堆表
pct_free  10%  (populate  90%不能再插入)
pct_used  (老的表空间40%，低于这个值才算空块)  （新的表空间是空值）

3. 怎么创建表空间：

CREATE SMALLFILE TABLESPACE "TBS1" DATAFILE '+DATA' SIZE 5M AUTOEXTEND ON NEXT 2M MAXSIZE 2G LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT AUTO

复制代码

=

CREATE TABLESPACE "TBS1"
DATAFILE SIZE 5M AUTOEXTEND ON
NEXT 2M MAXSIZE 2G LOGGING
UNIFORM SIZE 512K
;

复制代码

create tablespace tbs2
datafile size 5M
segment space management manual;

复制代码

4. 获取表空间信息：

select t.tablespace_name, t.segment_space_management,
t.extent_management,
t.allocation_type
from dba_tablespaces t;

复制代码

	TABLESPACE_NAME	SEGMENT_SPACE_MANAGEMENT	EXTENT_MANAGEMENT	ALLOCATION_TYPE
1	SYSTEM	MANUAL	LOCAL	SYSTEM
2	SYSAUX	AUTO	LOCAL	SYSTEM
3	UNDOTBS1	MANUAL	LOCAL	SYSTEM
4	TEMP	MANUAL	LOCAL	UNIFORM
5	USERS	AUTO	LOCAL	SYSTEM
6	EXAMPLE	AUTO	LOCAL	SYSTEM
7	TBS1	AUTO	LOCAL	UNIFORM
8	TBS2	MANUAL	LOCAL	SYSTEM

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 20 20:23:40 2018
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05207_a ( a number ) tablespace tbs1 ;
Table created.
SQL> insert into t05207_a values ( 1 ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> create table t05207_m ( a number ) tablespace tbs2;
Table created.
SQL> insert into t05207_m values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL>

复制代码

select t.table_name,t.tablespace_name,
t.pct_free, t.pct_used , t.pct_increase , t.ini_trans
from dba_tables t
where t.owner='HR'
and t.table_name in ('T05207_A','T05207_M');

复制代码

	TABLE_NAME	TABLESPACE_NAME	PCT_FREE	PCT_USED	PCT_INCREASE	INI_TRANS
1	T05207_M	TBS2	10	40		1
2	T05207_A	TBS1	10		0	1

select
(select sum(bytes)/1024/1024
from dba_data_files df
where df.tablespace_name='SYSTEM')
-
(select sum(bytes)/1024/1024
from dba_free_space fs
where fs.tablespace_name='SYSTEM')
from dual;
select sum(bytes)/1024/1024
from dba_data_files df
where df.tablespace_name='UNDOTBS1';
select sum(ue.bytes)/1024/1024
from dba_undo_extents ue
where ue.status <> 'EXPIRED';
select sum(bytes)/1024/1024
from dba_temp_files tf
where tf.tablespace_name='TEMP';
select tfs.allocated_space/1024/1024, tfs.free_space/1024/1024
from dba_temp_free_space tfs where
tfs.tablespace_name='TEMP';

复制代码

上完1Z0-052 第7章表空间（1/40）