ORACLE零散乱记

PGA_AGGREGATE_TARGET = (物理内存大小 * 80%) * 20%

 

alter index xxx unusable;
配合参数
SKIP_UNUSABLE_INDEXES=true
使用

 

select object_name from all_objects where status ='INVALID' and object_type ='PROCEDURE' and
owner in ('CPSASST','FACTORY','MANAGE','MANAGEBAK');

 

DEDICATED

DB shared_servers = 0 ， TNSname.ora server=shared或者不设置 ==> DEDICATED连接
DB shared_servers = 0 ， TNSname.ora server=DEDICATED ==> DEDICATED连接
DB shared_servers > 0 ， TNSname.ora server=DEDICATED ==> DEDICATED连接
DB shared_servers > 0 ， TNSname.ora server=shared或者不设置 ==> Shared连接



select sum(sessions_current) cur_sessions,sum(sessions_highwater) high_sessions from v$license;--查看当前以及历史最大SESSION数

 

 

select * from user_jobs;--查看调度任务
select * from dba_jobs_running;--正在执行的任务
select * from dba_jobs;--执行完的任务


--utlrp.sql
--\rdbms\admin\utlrp.sql;

--过程： 
alter   procedure   factory.p_gettask   compile; 
alter   procedure   factory.p_afreshworkinfo   compile; 
--函数： 
alter   function   function_name   compile; 
--包： 
alter   package   package_name   compile; 
--包体: 
alter   package   package_name   compile   body;

 

 

Enable row movement在3种情况下需要使用。

1.       flashback table

2.       change the partition key

3.       alter table shrink space

alter table t enable row movement;

because DML operations are used to bring the table back to its former state, the ROWIDs in the table change.

 

 

建立一张表
SQL> create table test_shrik (id number ) tablespace users;

 

Table created.

 

查看各个表空间管理信息
SQL> select tablespace_name,t.segment_space_management

 2  from dba_tablespaces t

 3 where tablespace_name in ('SYSTEM','USERS');

 

TABLESPACE_NAME               SEGMEN

------------------------------ ------

SYSTEM                        MANUAL

USERS                         AUTO

 

插入数据
SQL> insert into test_shrik

 2 select rownum from dual connect by level <=200000;

 

200000 rows created.

 

SQL> commit ;

 

Commit complete.

 

查看统计信息

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

 2  from user_tables t

 3 where table_name = upper('test_shrik');

 

TABLE_NAME                        BLOCKS EMPTY_BLOCKS  NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK

收集统计信息
SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');

 

PL/SQL procedure successfully completed.

 

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

 2  from user_tables t

 3 where table_name = upper('test_shrik');

 

TABLE_NAME                        BLOCKS EMPTY_BLOCKS  NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                           306           0    197855

 

 

删除数据
SQL> delete from test_shrik where id >=100000 ;

 

100001 rows deleted.

 

SQL> commit ;   

 

Commit complete.

 

查看统计信息
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

 2  from user_tables t

 3 where table_name = upper('test_shrik');

 

TABLE_NAME                        BLOCKS EMPTY_BLOCKS  NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                           306           0    197855

 

再次收集，查看信息
SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');

 

PL/SQL procedure successfully completed.

 

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

 2  from user_tables t

 3 where table_name = upper('test_shrik');

 

TABLE_NAME                        BLOCKS EMPTY_BLOCKS  NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                           306           0    101835

 

启用表的行转移功能
SQL> alter table test_shrik enable row movement ;

 

Table altered.

 

压缩高水位线
 

SQL> alter table test_shrik shrink space ;

 

Table altered.

 

查看统计信息
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

 2  from user_tables t

 3 where table_name = upper('test_shrik');

 

TABLE_NAME                        BLOCKS EMPTY_BLOCKS  NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                           306           0    101835

收集统计信息
SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');

 

PL/SQL procedure successfully completed.

再次查看
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

 2  from user_tables t

 3 where table_name = upper('test_shrik');

 

TABLE_NAME                        BLOCKS EMPTY_BLOCKS  NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                           151           0     99344