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(比块的大小都大,显然不会出现行链接)
如果平均行长度比较短的话,就可能会出现行链接
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库