ITPub博客

和系统权限相关的表是system_privilege_map，一直不太注意和对对象权限相关的表table_privilege_map，oracle的命名貌似不够严谨，为什么不命名成object_privilege_map呢，包括和对象权限相关的视图dba_tab_privs，为什么中间不是obj，从命名也能看得出对象权限其实主要是指表的权限。[@more@]doc对table_privilege_map的解释：TABLE_PRIVILEGE_MAPTABLE_PRIVILEGE_MAP describes privilege (auditing option) type codes. This table can be used to map privilege (auditing option) type numbers to type names.ColumnDatatypeNULLDescr

在客户现场配置streams环境以sys用户执行execute dbms_logmnr_d.set_tablespace时一直提示下面错误：ERROR at line 1:ORA-01950: no privileges on tablespace 'TBS_STREAMS'ORA-06512: at "SYS.DBMS_LOGMNR_D", line 233ORA-06512: at line 1仔细分析之后觉得不是sys用户缺少权限而是system缺少...[@more@]SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');BEGIN dbms_logmnr_d.set_tablespace('tbs_streams'); END;*ERROR at line 1:ORA-01950: no privileges on tablespace 'TBS_STREAMS'ORA-06512: at "S

讲课时，一位学员遇到的问题，很棘手，后来通过修改server端os的时区问题得以解决，但是原来os的时区是什么，他想不起来了，我反复修改了几次不同的时区，问题重现过一次就消失了，真奇怪，又改了几个不同的值，会出现Errorjava.lang.Exception: IOException in sending Request :: Connection refused: connect等不同提示的类似错误，但是em还是可以使用的，总之这个问题就是由于os时区设置的问题引起的。[@more@]http://www.itpub.net/thread-1458831-1-2.html

我一直觉得oracle在v$session中应该包含v$process的spid字段，这样使用起来更方便，同时在sqlplus中应该把sid，spid以及user和sid等信息显示出来，这样方便使用...[@more@]今天搜了一篇文章，参考一下，同时做了一点测试：参考的文章：http://blog.csdn.net/tianlesoftware/article/details/6412769防止连接失效，内容拷贝如下：当管理的数据库比较多的时候，在sqlplus里切换就是一件麻烦的事。 要非常小心，以防出错。 可以修改sqlplus 的提示符：SQL> ，把这个改成我们用户和实例名，这样就不容易出错。先看一下Oracle 自定义的参数：

HandlerAn optional event handler is a PL/SQL procedure that defines any additional actions that must betaken during auditing. For example, the event handler can send an alert page to the administrator.If it is not defined, then an audit event entry is inserted into the audit trail. If an audit eventhandler is defined, then the audit entry is inserted into the audit trail and the audit event handleris executed.The audit event entry includes the FGA policy that caused the event, the user executing

在修改参数audit_trail=xml,extended时千万不要加单引号...[@more@]受到v$parameter_valid_values显示结果的误导，我以为EXTENDED是一个单独的参数内容，所以直接修改audit_trail=extended时实例无法启动，最后修改audit_trail='XML,EXTENDED'重启实例时依然发生了错误ORA-01078，原因就是加上了单引号：SQL> select * from v$parameter_valid_values where name='audit_trail';NUM NAME ORDINAL VALUE ISDEFAULT---------- --------------- ---------- -------------------- ----------907 audit_trail 1 DB FALSE907 audit_trail 2 O

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.To grant a role, you must either have been granted the role with the ADMIN OPTION or have been granted the GRANT ANY ROLE system privilege, or you must have created the role.To grant an object privilege, you must own the object, or the owner of the object must have granted you the object privileges with the GRANT OPTION, or you mu

原文连接：http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4674224.aspx[@more@]防止网页失效，拷贝过来保留一份：一. 官网说明1. Oracle 10g文档如下：http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#i1010293 Data Pump ComponentsOracle Data Pump is made up of three distinct parts:（1）The command-line clients, expdp and impdp（2）The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)（3）The DBMS_METADATA PL/SQL package (also known as the Metadata API)

以前对临时段的认识到底是出现了错误还是oracle 10g有变化，发现临时段在sort之后查询结束释放了。[@more@]下面这些doc都是来着oracle官方文档，都是介绍临时段的，可以参考。Introduction to Temporary SegmentsWhen processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a database area for sorting. Oracle does not cr

doc的原话，记录一下[@more@]Overview of Instance and Crash RecoveryCrash recovery is used to recover from a failure either when a single-instance database fails or all instances of an Oracle Real Application Clusters database fail. Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters database.The goal of crash and instance recovery is to restore the data block changes located in the cache of the terminated instance and t

可以通过2个视图来监控flash_recovery_area空间的使用[@more@]SQL> show parameter db_recoveryNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string g:oracleproduct10.2.0flash _recovery_areadb_recovery_file_dest_size big integer 2GSQL>--=================================SQL> select * from v$flash_recovery_area_

和statspack相比awr收集快照的级别只有2种[@more@]SQL> select distinct snap_level from dba_hist_snapshot;SNAP_LEVEL---------- 1 22 rows selected.SQL> show parameter statistics_levelNAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string ALLSQL>其中dba_hist_snapshot中字段snap_level=1表示该快照要么是在参数statistics_level=typical自动生成的，要么是用下面

无意中发现了这一现象，记录一下...没看到到doc里是否有说明...观查了一下发现，oracle默认只会online10个undo segment[@more@]SQL> select segment_name,owner,tablespace_name,status,block_id,relative_fno status from dba_rollback_segs;SEGMENT_NA OWNER TABLESPACE STATUS BLOCK_ID STATUS---------- ------ ---------- ---------------- ---------- ----------SYSTEM SYS SYSTEM ONLINE 9 1_SYSSMU1$ PUBLIC UNDOTBS1 ONLINE 9 2_SYSSMU2$ PUBLIC UNDOTBS1 ONLINE 25 2_SYSSMU3$ PUBLIC UNDOTBS1 ONLINE 41 2_SYSSMU4$ PUBLIC

我们通过通过在ora_dba（windows）和dba(unix/linux)以及在sqlnent.ora中修改SQLNET.AUTHENTICATION_SERVICES的值来控制os认证的启用以及屏蔽。[@more@]有关sqlnet.ora中SQLNET.AUTHENTICATION_SERVICES的含义oracle给出了如下解释：SQLNET.AUTHENTICATION_SERVICESPurposeUse the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.DefaultNoneVa

讲课的时候经常安装，下面列出一个步骤供学员参考，安装rpm包这一步最好都能满足oracle的检查要求，内核参数如果只是学习用的话其实可以不设置，出现有关和内核参数相关的错误提示直接忽略就可以了。安装过程中最麻烦的其实就是安装rpm包和编辑内核参数了，其他的都非常容易...[@more@]--==========================参考doc安装oracle环境需要的liunux rpm包如：rpm -Uvh compat-gcc-32-3.2.3-47.3.i386.rpmrpm -Uvh compat-gcc-32-c++-3.2.3-47.3.i386.rpm--==========================根据自己的服务器配置以及环境需要编辑内核参数：/e

在使用em的时候如果ip是动态的，那么通常都会受到困扰，我提供一种解决办法供大家参考...[@more@]如果我们的电脑是动态ip，那么机器安装了em之后ip发生了改变，此时em通常都不能正常使用了，一般我们可能都会选择重配EM，问题是如果重配之后ip再次发生变化，那么em就需要再次重配，总不能ip变化一次重配一次em吧，如果以后我们不能保证ip是静态的，那么大家在安装em的时候其实把hosts文件里的所有内容都屏蔽就可以了，包括localhost和127.0.0.1，这样在安装em之后，在oracle主目录和oracle主目录的oc4j/j2ee/下都会生成以主机名和SID组合起

如何获得db server端和client端的ip和host_name[@more@]获得db server端的ip和host_name:SQL> exec dbms_output.put_line(utl_inaddr.get_host_name);LENOVO-382E0EF1PL/SQL 过程已成功完成。SQL> exec dbms_output.put_line(utl_inaddr.get_host_address);192.168.1.37PL/SQL 过程已成功完成。--======================================utl_inaddr中的get_host_address和get_host_name都是函数也可以直接通过下面方式显示出来：SQL> select utl_inaddr.get_host_address from dual;GET_HOST_ADDRESS-----------------------

讲课时随手的一些总结[@more@]SQL> col member format a40SQL> select group#,member from v$logfile; GROUP# MEMBER---------- ---------------------------------------- 2 D:ORADATATESTREDO02.LOG 3 D:ORADATATESTREDO03.LOG 1 D:ORADATATESTREDO01.LOGSQL> select group#,members,status from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 1 1 CURRENT 2 1 INACTIVE 3 1 INACTIVESQL> alter database add

讲课时随手的一些总结[@more@]要想启动flashback database的功能需要满足3个条件：1.要求db在archivelog2.startup mount3.使用flash recovery area,因为flashback log必须要求放在flash recovery area里--======================SQL> select flashback_on from v$database;FLASHBACK_ON------------------NOSQL> archive log list;数据库日志模式 存档模式自动存档 启用存档终点 USE_DB_RECOVERY_FILE_DEST最早的联机日志序列 4下一个存档日志序列 7当前日志序列 7SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACL

这个问题我想可能不仅仅困挠我一个人，不过很多时候我们却又解释不清，觉得oracle似乎使用using backup controlfile恢复db之后没必要resetlogs，但是我觉得resetlogs似乎又有些道理...[@more@]SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.--冷备份SQL> startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1247876 bytesVariable Size 83887484 bytesDatabase Buffers 75497472 bytesRedo Buffers 7139328 bytes数据库装载完毕。数据库已经打开。SQL&g