Zhong Weicheng Study Notes

PROMPT Flashback StatusPROMPT ================select flashback_on from v$database;PROMPT Flashback ParametersPROMPT ====================column name format A30column value format A50select nam

Create procedure:create or replace procedure get_case_sqls_for_ddls_ver1 as cursor get_username is select username from dba_users;begin for l_user in get_username loop DBMS_OUTPUT.P

对象比较select a.object_type, a.ds_count, b.dt_count, ds_count - dt_count minus_count from (select object_type, count(*) ds_count from dba_objects@dbverify wher

sqlplus / as sysdbaCREATE TRIGGER open_all_pdbs   AFTER STARTUP   ON DATABASEBEGIN   EXECUTE IMMEDIATE 'alter pluggable database all open';END open_all_pdbs;/

python code#!/usr/bin/env pythonimport cx_Oracleusername='zwc'password='zwc'database='oraprod'conn=cx_Oracle.connect(username,password,database)curs=conn.cursor()user_query='select username

[P720:/home/dsg]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 5 21:50:30 2013Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g En

[oracle@vzwc2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 9 21:43:58 2013Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g E

set null "NULL VALUE"set feedback offset heading offset linesize 132 set pagesize 9999set echo offset verify offset trimspool oncol table_name for a30col column_name for a30col data_type fo

--当前普通用户创建 issgztcreate table TAB_DDL(  LOGIN_USER         VARCHAR2(60),  AUDSID             NUMBER,  MACHINE            VARCHAR2(60),  IPADDRESS          VARCHAR2(20),  SCHEMA_USER

This is a bit twisted solution, but works. Basically you first need to convert LOB (in these example I refer to CLOB, BLOB is similar but you will probably not use varchar2) to multiple varchar2(4000)

The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.It is very important that the scripts are run in the correct order and on

SELECT b.sid, c.sql_text  FROM v$process a, v$session b, v$sqlarea c WHERE a.addr = b.paddr   AND b.sql_address = c.address   AND a.spid = &pid/

以下脚本可用于列出数据库中的失效的索引Select owner, index_name, status From dba_indexes where status = 'UNUSABLE' and owner not in ('SYS', 'SYSTEM', 'SYSMAN',

以下脚本可以用于列出数据库中没有主键的表，已排除了系统schemaSELECT owner, table_name FROM dba_tables WHERE 1 = 1 AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN',

SET LINESIZE 250 SET PAGESIZE 1000 COLUMN username FORMAT A15 COLUMN osuser FORMAT A15 COLUMN sid FORMAT 99999 COLUMN serial# FORMAT 9999999 COLUMN wait_class FORMAT A15 COLUMN state

COLUMN name FORMAT A40COLUMN highwater FORMAT 999999999999COLUMN last_value FORMAT 999999999999SET PAGESIZE 24SELECT hwm1.name, hwm1.highwater, hwm1.last_valueFROM dba_high_wate

COLUMN name FORMAT A40COLUMN value FORMAT A40SELECT name, valueFROM v$parameterWHERE SUBSTR(name, 1, 1) = '_'ORDER BY name;COLUMN FORMAT DEFAULT

COLUMN name FORMAT A50COLUMN detected_usages FORMAT 999999999999SELECT u1.name, u1.detected_usagesFROM dba_feature_usage_statistics u1WHERE u1.version = (SELECT MAX(u2.version)

被添加分区的表必须初始化分区CREATE TABLE t_test ( spBillno VARCHAR2 ( 20 ) NOT NULL, orderId VARCHAR2 ( 50 ) NOT NULL, userId NUMBER ( 12 ), bankType CHAR ( 5 ), payMoney NUMBER ( 10 ) DEFAULT 0 NOT N

CREATE OR REPLACE PROCEDURE p_test ASBEGIN NULL;END p_test;HR@joydb:~>select text from user_source where name=upper('p_test');TEXT-----------------------procedure p_test asbegin null;

CREATE OR REPLACE PROCEDURE p_sendmail( p_recipient IN varchar2, -- 邮件接收人 p_subject IN varchar2, -- 邮件标题 p_me

我们知道通过启动Oracle内部事件可以获得更多额外的可用信息,但是如何知道每个事件对应哪些功能呢？这里可以使用pl/sql来获取所有事件的名称。SET linesize 200SET feedback offSET SERVEROUTPUT ONDECLARE err_msg VARCHAR2(200);BEGIN dbms_output.enable(1000000);

displays one row for every disk discovered by the ASM instance, including disks which are not part of any disk groupSQL> set pages 50000 lines 130 echo on head on flush on veri on feed onSQL> col

SQL> grant select on v_$session to zhongwc;Grant succeeded.SQL> grant execute on dbms_system to zhongwc;Grant succeeded.SQL> @ddl_triggerTrigger created.SQL> !cat ddl_trigger.sqlcre

--Oracle 8iSQL> select NAME,CLASS,SQL_FEATURE from v$sql_hint where VERSION LIKE '8%';NAME                                     CLASS                                    SQL_FEATURE---------