24、表空间及段区块_2(EM工具使用、行链接、行迁移)

EM工具

1、网络版
2、单机版(我们现在用的)

oracle软件安装上以后,默认em这个工具也有了,只需要配置一下就可以用

em的配置和使用

1、首先使用emca这个命令对em做一个删除,然后建立:
删除em:

[oracle@db11g ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Mar 15, 2017 9:37:54 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl  -- 数据库实例名(区分大小写)
Listener port number: 1521  -- 端口号
Password for SYS user:  -- 密码:oracle
Password for SYSMAN user:  -- 密码:oracle

----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Mar 15, 2017 9:38:17 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2017_03_15_09_37_54.log.
Mar 15, 2017 9:38:18 AM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
WARNING: ORA-28000: the account is locked

Mar 15, 2017 9:38:18 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Mar 15, 2017 9:38:18 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed. Some of the possible reasons may be:
  1) EM is configured with different hostname then physical host. Set environment variable ORACLE_HOSTNAME=<hostname> and re-run EMCA script
  2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script
Mar 15, 2017 9:38:18 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Mar 15, 2017 9:39:36 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully droppedEnterprise Manager configuration completed successfully
FINISHED EMCA at Mar 15, 2017 9:39:36 AM

建立em(建完以后,em默认就启动了):

[oracle@db11g ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Mar 15, 2017 9:43:04 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]: 
Password for SYS user:  
Password for DBSNMP user:  
Password for DBSNMP user: 
Mar 15, 2017 9:45:28 AM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
WARNING: ORA-28000: the account is locked

Password for SYSMAN user:  
Password for SYSMAN user: 
Email address for notifications (optional): 
Outgoing Mail (SMTP) server for notifications (optional): 
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

Local hostname ................ db11g
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... 
Outgoing Mail (SMTP) server for notifications ............... 

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Mar 15, 2017 9:45:40 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2017_03_15_09_43_04.log.
Mar 15, 2017 9:45:41 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) 
Mar 15, 2017 9:48:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 15, 2017 9:48:58 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) 
Mar 15, 2017 9:49:44 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 15, 2017 9:49:46 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) 
Mar 15, 2017 9:50:30 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 15, 2017 9:50:30 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) 
Mar 15, 2017 9:50:52 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 15, 2017 9:50:52 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://db11g:1158/em <<<<<<<<<<<
Mar 15, 2017 9:50:55 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING: 
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the
file: /u01/app/oracle/product/11.2.0/db_1/db11g_orcl/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 15, 2017 9:50:55 AM

2、查看em的状态(使用emctl命令):

[oracle@rac11g ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://rac11g:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/rac11g_jinshang/sysman/log

还有另外一个方式查看连接的端口号:

[oracle@db11g ~]$ cd $ORACLE_HOME
[oracle@db11g db_1]$ cd install
[oracle@db11g install]$ ls
chainedInstall      oratab
config              portlist.ini
envVars.properties  readme.txt
jlib                root_db11g_2016-12-12_15-32-21.log
make.log            unix
modmakedeps.pl      utl
[oracle@db11g install]$ cat portlist.ini
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938

3、开启em:

[oracle@rac11g ~]$ emctl start dbconsole

4、关闭em:

[oracle@rac11g ~]$ emctl stop dbconsole

5、登录em:

https://192.168.56.12:1158/em  -- 192.168.56.12:服务器地址
注意:使用ie浏览器登陆,不要用别的

使用EM:

1、建立一个表空间


普通表空间都是永久表空间


显示生成的SQL文本:

2、给表空间增加一个数据文件

3、建一个表,表建立在一个指定的表空间上



4、建立了一个表、查询一下这个表所占用的extent

select * from dba_extents e where e.segment_name='TABLE1';

5、查询段和表的对应关系

select * from dba_segments s where s.segment_name='TABLE1';

查询段的类型:

select distinct segment_type from dba_segments;

6、系统默认表空间、默认临时表空间
默认表空间:就是在建立一个表的时候,看你是否指定表空间,如果没有指定表空间的话,用户有默认的表空间,就建在默认表空间上,如果用户没有默认表空间就建在system表空间上

查询数据库的默认表空间:

select * from database_properties;

查询用户的默认表空间:

select * from dba_users d where d.username='SCOTT';

7、建立一个用户,用户默认表空间、用户默认临时表空间

8、temp表空间以及该注意的地方
关掉自动扩展,temp表空间最好做成20G大小的临时表空间

9、undo表空间以及该注意的地方
要选择自动扩展

10、建立一个32K的表空间
一开始建不了32k的表空间,需要调一个参数:

SQL> show parameter 32
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_32k_cache_size		     big integer 0

SQL> alter system set db_32k_cache_size=100M;  -- 设置在buffer cache里面有32k大小的buffer的一个100M的空间
System altered.

11、fet$视图、uet$视图
首先要有一个字典管理的表空间(现在oracle不让建立字典管理的表空间了):

查询fet$ 和 uet$(没有字典管理的表空间,没有数据):

select * from fet$;
select * from uet$;

12、字典管理表空间(现在oracle不让建了)

13、manual 段、pct free、pct used、free list

14、行链接与行迁移、行长度
查询一个表是否有行链接和行长度:

select * from dba_tables where table_name='TB3';

chain_cnt:这个表行链接和行迁移总数
AVG_BOW_LEN(平均行长度):98(比块的大小都大,显然不会出现行链接)
如果平均行长度比较短的话,就可能会出现行链接

posted @   一只c小凶许  阅读(8)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示