在Oracle9i里，Oracle提供了一个内部事件，用以强制刷新Buffer Cache，其语法为:

　　alter session set events 'immediate trace name flush_cache level 1';

　　或者:

　　alter session set events = 'immediate trace name flush_cache';

　　类似的也可以使用alter system系统级设置:

　　alter system set events = 'immediate trace name flush_cache';

　　在Oracle10g中，Oracle提供一个新的特性，可以通过如下命令刷新Buffer Cache:

　　alter system flush buffer_cache;

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

alter system set undo_tablespace=zxy
drop tablespace undotbs1  --非活动的undo表空间可以删除

SQL> select file_name,bytes/1024/1024 from dba_data_files where file_name like '%undo%';---undo表空间可以resize

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
/home/oracle/oracle10g/oradata/rac1/undotbs01.dbf
             95


SQL> alter database datafile '/home/oracle/oracle10g/oradata/rac1/undotbs01.dbf' resize 100m;

Database altered.

SQL> select file_name,bytes/1024/1024 from dba_data_files where file_name like '%undo%';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
/home/oracle/oracle10g/oradata/rac1/undotbs01.dbf

SQL> select usn,name from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM
        11 _SYSSMU11$
        12 _SYSSMU12$
        13 _SYSSMU13$
        14 _SYSSMU14$
        15 _SYSSMU15$
        16 _SYSSMU16$
        17 _SYSSMU17$
        18 _SYSSMU18$
        19 _SYSSMU19$
        20 _SYSSMU20$

11 rows selected.

能不能说说什么时候用啊？

手工管理rollback
create rollback segment tablespace
create public rollback segment tablespace
alter rollback segment online;--才可以用
undo_tablespace
rollback_segment=('rollseg1','rollseg2') --specify it in init.ora,use for startup private rollback segment

transactions
transactions_per_rollback_segment ---use for public rollback segment

在资料上看到估算undo表空间的大小的脚本如下：
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR
FROM v$parameter
WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM
(((end_time-begin_time)*86400))) AS UPS
FROM v$undostat),
(SELECT value AS DBS
FROM v$parameter
WHERE name = 'db_block_size');

[ 本帖最后由 wisdomone1 于 2008-7-3 16:37 编辑 ]

table type:regular table,partioned table,index-organized table,cluster

char ---length maximum 2000

SQL> create table test(a char(2001));
create table test(a char(2001))
                         *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> create table test(a char(2000));

Table created.


SQL> create table test(a varchar2(4001));
create table test(a varchar2(4001))
                             *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> create table test(a varchar2(4000));

Table created.


long|raw access data must 顺序存取,只有2gb,other 4gb and random access

extended rowid
data object number(  relative file number  block number row number   ---18,64进制

restricted rowid
block number row number file number

temporary table ---not genarate redo log,
                   not accuire lock,also can create index,view,trigger and so on
create global temporary table zxy
on commit delete rows|on commit  preserve rows
as
select * from scott.emp;

读写和只读表存储在不同表空间,对备份有作用,还有临时表和永久表不同
采用inital and next same extent size,not use pctincrease (in fact equal to 0)

SQL> conn scott/system;
Connected.
SQL> cl scr;
SQL> create global temporary table tmptb
  2  on commit delete rows
  3  as
  4  select * from emp;

Table created.

SQL> select * from tmptb;

no rows selected

SQL> insert into tmptb select * from emp;

14 rows created.

SQL> select count(*) from tmptb;

  COUNT(*)
----------
        14

SQL> commit;

Commit complete.

SQL> select count(*) from tmptb;

  COUNT(*)
----------
         0


alter table authors allocate extent(1m datafile '');---起作用就是强制给表分配在表空间不同物理文件上,对表的性能DML有好处提升



SQL> create global temporary table tmptb
  2  on commit preserve rows
  3  as
  4  select * from emp;

Table created.

SQL> select count(*) from tmptb;

  COUNT(*)
----------
        14

SQL> /

  COUNT(*)
----------
        14

SQL> commit;

Commit complete.

SQL>  select count(*) from tmptb;

  COUNT(*)
----------
        14

SQL> delete from tmptb where rownum<3;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tmptb;

  COUNT(*)
----------
        12

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@primary ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 4 02:29:16 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn scott/system;
Connected.
SQL> desc tmptb;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                              NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

SQL> select count(*) from tmptb;

  COUNT(*)
----------
         0


alter table scott deallocate unused;---refer to hwm above


alter table emp move tablespace users;---indexes,constraints,privelage可以preserve,但无效,要重建或recompile,
                                         用于性能平衡,负荷均匀
then

alter index rebuild|tablespace tbs;


alter table zxy drop column comments cascade constraints checkpoint 1000;


alter table zxy set unused column comments cascade constraints;
alter table zxy drop unused checkpoint 1000;

continue
alter table zxy drop columns continue checkpoint 1000;---加continue是为了防止下次重复删除动作,是为了大数据量表列的删除,从上次失败的中断点继续删除

index: independant physical structure
       speed query,but overhead data update

b-tree:nonleaf|leaf


              root
branch

leaf  ---pointer to data(index entry:index entry header,index column length,index column value,rowid

bitmap index:use data warehousing

index creation:do not use pctused
               5个block的倍数或miminum extent的整数倍
对大数据量create index nologging;

SQL> show parameter create_bitmap

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
SQL> select 8388608/1024 from dual;

8388608/1024
------------
        8192
create index idx table(col) reverse;--一般用于序列或前多位相同的
alter index idx rebuild online;--为了重建索引时保持系统并发性,也有一short time lock
reverse and b-tree index can not transform

alter index idx coalesce;
anylyze index idx validate structure;--更新索引统计信息,针对index_stats

在大批量加载数据量,先drop index,then create index

alter index idx no|monitoring usage;

SQL> select index_name,table_name,column_expression from dba_ind_expressions;

INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------ ------------------
--------------------------------------------------------------
I_WRI$_OPTSTAT_TAB_OBJ#_ST     WRI$_OPTSTAT_TAB_HISTORY       SYS_EXTRACT_UTC("S
AVTIME")
I_WRI$_OPTSTAT_TAB_ST          WRI$_OPTSTAT_TAB_HISTORY       SYS_EXTRACT_UTC("S
AVTIME")
I_WRI$_OPTSTAT_IND_OBJ#_ST     WRI$_OPTSTAT_IND_HISTORY       SYS_EXTRACT_UTC("S
AVTIME")
I_WRI$_OPTSTAT_IND_ST          WRI$_OPTSTAT_IND_HISTORY       SYS_EXTRACT_UTC("S

alter table emp add constraint pk check() enable novalidate;--旧数据不起作用,新数据起作用.
disable novalidate;--旧的不起作用,新的数据不起作用
disable validate;--对旧起作用,新不起作用
alter table zxy modify constraint pk
enable novalidate;---对旧不起作用,对新的起作用,在工作可以多采用它
enable validate;--default,新旧都起作用

enable|disable  --对运行过程中控制
no|validate  ---对已存在的数据进行控制