JANE_ZDr的博客

sessionset linesize 300 pages 999 col sid format 99999 col s# format 99999 col username format a10 col spid for a10 col event format a36 col m...

分析表select owner,table_name,TABLESPACE_NAME from dba_tables where table_name=upper('&1');select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='&1';select a.column_name, b.num_rows, a.num_distinct C...

获取awr ash[ashrpt]@?/rdbms/admin/ashrpt.sql[awrddrpt]@?/rdbms/admin/awrddrpt.sql[awrsqrpt.sql]@?/rdbms/admin/awrsqrpt.sql[awrrpt]@?/rdbms/admin/awrrpt.sql示例：SQL> @?/rdbms/admin/awrrpt.sql....Enter value for report_type: html....Enter .

检查回收站数据量select count(*) from dba_recyclebin;手动清理purge dba_recyclebin;

oracle中新建用户，赋予权限，撤销权限，查看权限，角色新建用户create user 用户名 identified by "密码"; #新建用户alert user 用户名 identified by "新密码"; #修改用户密码赋予权限grant dba to 用户名; #给用户赋予所有权限(connect是赋予连接数据库的权限，resource是赋予用户只可以创建实例但是没有创建数据结构的权限)grant create session to 用户名; #给用户赋予登录的权...

查询归档日志总大小show parameter db_recovery_file_dest_size;增大归档日志空间alter system set db_recovery_file_dest_size=20G;查看当前归档日志使用大小，单位Bselect * from v$recovery_file_dest;查看当前用了多少归档日志了（按百分比）select * from v$flash_recovery_area_usage; #如果 ARCHIVED LOG 超过90%有宕机的危险.

11g dataguard 环境中用rman删除主库的归档，出现RMAN-08137:WARNING: archived log not deleted, needed for standby or upstream capture process通过下述两个sql检查，发现主库的归档都已经应用到standby库select max(sequence#) from v$archived_log;select max(sequence#) from v$archived_log where ...

如何正确地删除Archivelog？Archivelog并不能直接得从OS层直接物理删除，因为archivelog的相关信息是记录在controlfile中的，当物理删除后不会改变controlfile的设置，并且在查询相关的动态视图(例如v$archived_log)时，该部分日志仍然标注为存在，也就是说Oracle并不认为这些日志被删除了，所以在删除archivelog的时候，需要我们在其他地方做一些设置。一、使用RMAN清除物理删除后的记录可以使用RMAN来删除archivelog，具体可..

Oracle参数的修改比较复杂，有些参数是可以在session级别修改，有些则必须在system级别修改，有些参数修改后马上生效（不需要重启），有些参数则必须重启才能生效，那么我们如何知道这些信息呢？v$parameter 可以查询动态视图v$parameter中的两列得知：1.ISSES_MODIFIABLE 指示参数是否可以在session级别(Alter session set)修改(True or False)2.ISSYS_MODIFIABLE 指示参数是否可以在syste...

RAC异常重启分析-----信息收集命令：/u01/app/grid/grid_home/tfa/bin/tfactl diagcollect -from "Aug/5/2020 22:00:00" -to "Aug/5/2020 23:00:00"-----如果命令执行报错，使用下面命令启动tfa后再执行收集：/u01/app/grid/grid_home/tfa/bin/tfactl start修改KDUMP和重启-----修改KDUMP配置root执行，crsctl 用绝对路径:.

检查自动扩展是否打开：SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUSFROM DBA_TABLESPACES T,DBA_DATA_FILES DWHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME and D.AUTOEXTENSIBLE != 'NO' ORDER BY TABLESPACE_NAME,FILE_NAME;文件自动扩展问题---..

方式一：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char('&sql_id'),NULL));方式二：select sql_text from v$sqltext where sql_id='&sqlid' and address = (select address from v$sqltext where sql_id ='&sqlid' and piece=0 and rownum<2) order by pie

备份遇到错误：RMAN-06429: TARGET database is not compatible with this version of RMAN检查软件版本opatch lspatches实例数据字典中版本SQL> select * from v$version;SQL> set lines 200 pages 1000SQL> col COMMENTS for a40SQL> select ACTION_TIME, ACTION, COMME

确定增备scn范围，通过alert日志获取gap日志序列/u01/oracle/diag/rdbms/<sid>/<sid>/trace/ alert日志GAP - thread 1 sequence 76893-76992利用磁带备份处理GAP1.在主库上select 'catalog device type '|| '''SBT_TAPE''' ||' backuppiece ''/'||HANDLE||''';' from (select distinct a..

检查promp check db compcol COMP_NAME for a40col status for a10col version for a15select/*+gather_plan_statisticsmonitorpart: db_info.sqlsubpart: db_comp*/comp_name,version,statusfrom dba_registryorder by 1/promp输出：COMP_NAME ...

当前索引状态-----dba_indexesselect OWNER,INDEX_NAME,STATUS from dba_indexes where INDEX_NAME='&indexname';select owner,index_name ,status from dba_indexes where table_name='&tablename';select table_owner,table_name from dba_indexes where index_na

set linesize 300 pagesize 900col GRANTEE for a20col owner for a15col TABLE_NAME FOR A40col PRIVILEGE FOR A10select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where TABLE_NAME =upper('&table') order by PRIVILEGE;--------- dba_tab...

报错信息：select 'ERROR DG standby redolog transport delay 3600s',status,FIRST_TIME,LAST_TIME from v$standby_log *ERROR at line 1:ORA-00308: cannot open archived log '+X...

set linesize 3000set long 30000set pagesize 3000select dbms_metadata.get_ddl('TABLE','table_name','owner') from dual;

SQL> select scn_to_timestamp(current_scn) from v$database;SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------2021-03-05 16:49:52.000000000动态的设置：alter system set "_time_based_rcv_ckpt_target"=0

查询：select systimestamp-sys.scn_to_timestamp(current_scn) from v$database;set line 200;col NAME for a25;col UNIT for a30;col VALUE for a20;select name,value,unit,time_computed from v$dataguard_stats;

shutdown immediate;startup nomount;alter database mount standby database;alter database open read only;alter database recover managed standby database using current logfile disconnect from session;