Oracle能在表格中使用的数值类型只有NUMBER和FLOAT，只是可以使用一些别名，如REAL其实是FLOAT类型，INT和DECIMAL都是NUMBER类型。值得一提的Oracle并不存在DOUBLE类型，LONG类型并不是长整型，而是二进制类型。下面的是Oracle数值类型的一个简单演示。
SQL> create table number_table( 2 cint integer, 3 cfloat float, 4 creal real, 5 cdecimal decimal); 表已创建。 SQL> desc number_table 名称 是否为空? 类型 ----------------- -------- ---------- CINT NUMBER(38) CFLOAT FLOAT(126) CREAL FLOAT(63) CDECIMAL NUMBER(38)
Oracle Trace Events是对于追踪Oracle很有用，可以通过alter system set events来设置事件追踪。
alter system set events有两种格式
(1) alter system|session set events ‘eventnumber trace name eventname [forever] [[,]level levelnumber] [: ......]’
(2) alter system|session set events ‘immediate trace name eventname [level levelnumber] [: ......]‘
通过冒号（:），可以连续设置多个事件，也可以通过连续使用alter session set events来设置多个事件。
格式说明：eventnumber指触发dump的事件号，事件号可以是Oracle错误号（出现相应错误时跟踪指定的事件）或oralce内部事件号，内部事件号在10000到10999之间。
immediate指命令发出后，立即将指定的结构dump到跟踪文件中，不能与eventnumber、forever关键字同时使用。
eventname指事件名称，即要进行dump的实际结构名。若eventname为context指根据内部事件号进行跟踪。
forever指事件在实例或会话的周期内保持有效状态。
level 为事件级别关键字，levelnumber指事件级别号。
下面是两条示例：
ALTER SYSTEM SET EVENTS '10231 trace name context forever: 1461 trace name errorstack';
ALTER SESSION SET EVENTS 'immediate trace name controlf level 8';
上面的方法只对对内存生效，无法对SPFILE生效，如果要对SPFILE生效要用ALTER SESSION SET EVENT=语句。
ALTER SYSTEM SET EVENT='1461 trace name errorstack level 3' scope=spfile;
用show parameter event可以看到event的值。
如果想取消，可以用下面的语句：
ALTER SYSTEM RESET EVENT scope=spfile sid='*';
SELECT parameter.value || '/' || LOWER(RTRIM(thread.instance, CHR(0))) || '_ora_' || process.spid || '.trc' TRACE_FILE_NAME FROM (SELECT process.spid FROM sys.v$mystat m, sys.v$session s, sys.v$process process WHERE m.statistic# = 1 AND s.sid = m.sid AND process.addr = s.paddr) process, (SELECT t.instance FROM sys.v$thread t, sys.v$parameter parameter WHERE parameter.NAME = 'thread' AND ( parameter.VALUE = 0 OR t.thread# = TO_NUMBER(parameter.value) )) thread, (SELECT value FROM sys.v$parameter WHERE NAME = 'user_dump_dest') parameter /
CREATE PFILE='initDBNAME.ORA' FROM SPFILE; ALTER SYSTEM ARCHIVE LOG CURRENT; $ocopy D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\TEMP01.DBF D:\ORACLE\BAK\ ALTER TABLESPACE SYSAUX BEGIN BACKUP; $ocopy D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\SYSAUX01.DBF D:\ORACLE\BAK\ ALTER TABLESPACE SYSAUX END BACKUP; ALTER TABLESPACE SYSTEM BEGIN BACKUP; $ocopy D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\SYSTEM01.DBF D:\ORACLE\BAK\ ALTER TABLESPACE SYSTEM END BACKUP; ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP; $ocopy D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\UNDOTBS01.DBF D:\ORACLE\BAK\ ALTER TABLESPACE UNDOTBS1 END BACKUP; ALTER TABLESPACE USERS BEGIN BACKUP; $ocopy D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\USERS01.DBF D:\ORACLE\BAK\ ALTER TABLESPACE USERS END BACKUP; ALTER SYSTEM ARCHIVE LOG CURRENT; --select name from v$archived_log; host xcopy /s/y D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DBNAME\ARCHIVELOG D:\ORACLE\BAK\ARCHIVELOG\ ALTER DATABASE BACKUP CONTROLFILE TO 'D:\ORACLE\BAK\CONTROL01.CTL'; -------------------------------- SHUTDOWN IMMEDIATE $ocopy D:\ORACLE\BAK\TEMP01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\ $ocopy D:\ORACLE\BAK\SYSAUX01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\ $ocopy D:\ORACLE\BAK\SYSTEM01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\ $ocopy D:\ORACLE\BAK\UNDOTBS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\ $ocopy D:\ORACLE\BAK\USERS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\ host xcopy /s/y D:\ORACLE\BAK\ARCHIVELOG\ D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DBNAME\ARCHIVELOG\ $ocopy D:\ORACLE\BAK\CONTROL01.CTL D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\CONTROL01.CTL STARTUP MOUNT RECOVER AUTOMATIC DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; --ARCHIVEDLOG MODE ALTER DATABASE OPEN RESETLOGS; --USE 'ALTER DATABASE OPEN;' IN NO ARCHIVEDLOG MODE ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBNAME\TEMP01.DBF'
ALTER USER scott ACCOUNT UNLOCK; CREATE USER gavin IDENTIFIED BY gavin DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP; GRANT connect, resource TO gavin; REVOKE UNLIMITED TABLESPACE FROM gavin; ALTER USER gavin QUOTA UNLIMITED ON users; -- ALTER USER gavin QUOTA 100m ON users;
I wrote a simle SQL script. All user can use Oracle Autotrace after you execute this script. I grant select privilege on some userful view to public, e.g. V$SEESION, V$LATCH and so on. RDBMS_ADMIN_utlxplan.sql was modified from
Batch File:
SET ORACLE_HM=E:\oracle\product\10.2.0\db_1 SET SYS_PWD=gavin SET SYSTEM_PWD=%SYS_PWD% REM create PLAN_TABLE table to get AUTOTRACE working. sqlplus system/%SYSTEM_PWD% @RDBMS_ADMIN_utlxplan.sql sqlplus sys/%SYS_PWD% as sysdba @sqlplus_admin_plustrce.sql
RDBMS_ADMIN_utlxplan.sql
create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob
);
CREATE OR REPLACE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
GRANT ALL ON PLAN_TABLE TO PUBLIC;
exit
sqlplus_admin_plustrce.sql
drop role plustrace; create role plustrace; grant select on v_$sesstat to plustrace; grant select on v_$statname to plustrace; grant select on v_$mystat to plustrace; grant select on v_$session to plustrace; grant select on v_$latch to plustrace; grant select on v_$lock to plustrace; grant select on v_$fixed_table to plustrace; grant select on v_$parameter to plustrace; grant plustrace to public; exit
通常我们在查看SQL Server的补丁版本时是用select @@version，然后根据查询出来的版本号然后与资料对照查出对应的版本，比如8.00.2039表示SQL Server 2000, SP4版。这种方法我们要找资料来查。其实有一种简单的方法，可以用select serverproperty(‘productlevel’)来查询，比如我的系统查询出的结果为SP4。serverproperty还有很多有用的属性，可以参考联机帮助获得这方面的资料。
Recent Comments