oracle记录[@more@]

orassh -P -g -f tomcat@61.242.223.36 -L 8080:61.242.223.36:8080 sleep 7200

kill -9 `ps -ef|grep "LOCAL=NO" |grep -v grep |awk '{print $2}'`

删除重复的记录
delete from heroinfo a where a.rowid!=(select max(rowid) from heroinfo b where a.fxno=b.fxno and b.fxno=a.fxno );

后台进程的SPID
SELECT s.SID SID, p.spid threadid, p.program processname, bg.NAME NAME
FROM v$process p, v$session s, v$bgprocess bg
WHERE p.addr = s.paddr AND p.addr = bg.paddr AND bg.paddr <> '00';

循环插入语句
BEGIN
LOOP
insert into t_temp
select * from T_NETSTAT_TOTAL_LOG
where NET_DATE and NET_DATE>to_date('20061101','YYYYMMDD')
AND ROWNUM<10000;
EXIT WHEN SQL%NOTFOUND;
COMMIT;
dbms_output.put_line('commit 10000');
END LOOP;
END;
/

查找系统参数是否能动态修改
set lines 160
col name for a40
col value for a40
select NAME,VALUE,TYPE,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from
v$parameter where name='open_cursors' or name='session_cached_cursors' order by name;

select owner,object_name from dba_objects where status='INVALID';

EXP-00056错误的解决
$ exp cc/smart@ora630 file=cc.dmp
EXP-00056: ORACLE error 19206 encountered
用Google查了一下，发现有个解决办法
http://www.orafaq.com/cgi-bin/msgboard/view-thr.cgi?board=serverutil&message=8508
然后联到数据库上做了一下
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @catmeta.sql

Oracle 监听启动报错:listener.log超过2G了就会这样,文件在$ORACLE_HOME/network/log/listener.log

TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

用下列SQL工具找出低效SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0
　AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

ORA-12154: TNS:could not resolve service name
查看
$ORACLE_HOME/network/admin/sqlnet.ora
注释NAMES.DEFAULT_DOMAIN = localdomain

释放表未使用的区(部能频繁使用)
alter table deallocate unused ;

不压缩空间到一个大的INITIAL参数里面的exp导出语法,防止产生特别大的INITIAL的存储参数报错
exp user/passwd file=user.dmp log=user.log compress=n

导入数据，开缓存,忽律已存在的数据对象（表结构）
imp user/passwd file=file_name.dmp log=file_name.log fromuser=user_1 touser=user_2 buffer=64000 commit=y ignore=y

导出dmp文件中的索引(不导入数据到帐号中)
imp user/passwd file=file_name.dmp tables=<(tables_1,tables_2)> log=file_name.log INDEXFILE=index_file.sql
编辑index_file.sql文件，吧缺省的tablespace是定到索引表空间
导入数据但不导入索引
imp user/passwd file=file_name.dmp log=file_name.log ignore=y indexes=n fromuser=user_1 touser=user_2 buffer=64000 commit=y
然后sqlplus user/passwd 后执行@index_file.sql创建索引

移动表到其他的表空间
alter table tablename move tablespace TB_NAME;

ORA-1653: unable to extend table

显示表空间的存储参数(dba)
select TABLESPACE_NAME,INITIAL_EXTENT,extents_management,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces;

显示指定表的存储参数
select INITIAL_EXTENT,NEXT_EXTENT,extents_management,MAX_EXTENTS ,PCT_INCREASE from user_segments where segment_name='T_LOG_BASE';

修改表空间的默认存储参数
alter tablespace tablespace_name default storage(initial xK next xxK pctincrease xx);

显示segment的存储参数
set lines 160
col segment_name form a30
col tablespace_name form a20
col PARTITION_NAME form a10
col SEGMENT_TYPE form a11
select segment_name,SEGMENT_TYPE,TABLESPACE_NAME,EXTENTS,INITIAL_EXTENT,
NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE
from user_segments order by NEXT_EXTENT;

显示指定表的存储参数
select INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS ,PCT_INCREASE from user_tables where table_name='T_LOG_BASE';
由于next 太大，造成free_space中连续自由空间不能满足next的要求，
修改表的存储参数，storage(next 50M)
alter table t_log_base STORAGE(next 50M pctincrease 20);

修改存储参数
alter table t_log_base STORAGE(next 50M pctincrease 10);
alter index IN_T_LOG_BASE STORAGE(next 50M pctincrease 10);
//alter object schema.object_name storage (maxextents 800);
object=table or index
alter table cms2.T_NETSTAT_TOTAL_LOG storage (maxextents 800);

设pctincrease为非零，通常设为1，alter tablespace tablespace_name default storage(pctincrease 1);
这样SMON就会自动整理碎片。
还可以手工整理：alter tablespace tablespace_name coalesce;
成城碎片整理的sql语句
select 'alter tablespace ' || tablespace_name || ' coalesce;' from dba_tablespaces;

自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等
可用 FSFI--Free Space Fragmentation Index （自由空间碎片索引）值来直观体现：
FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))
FSFI 的最大可能值为 100 （一个理想的单文件表空间）。随着范围的增加， FSFI 值缓慢下降，而随着最大范围尺寸的减少， FSFI 值会迅速下降。

rem FSFI Value Compute
rem fsfi.sql
column FSFI format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by 1;
rem spool fsfi.rep;
/
spool off;
统计出了数据库的 FSFI 值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且 FSFI 值超过 30 的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了。
4、自由范围的碎片整理
1）表空间的 pctincrease 值为非 0
可以将表空间的缺省存储参数 pctincrease 改为非 0 。一般将其设为 1 ，如：
alter tablespace temp
default storage(pctincrease 1);
这样 SMON 便会将自由范围自动合并。也可以手工合并自由范围：
alter tablespace temp coalesce;

对象创建时间
select OBJECT_NAME ,OBJECT_TYPE,CREATED,LAST_DDL_TIME, STATUS ,SECONDARY from user_objects;

7、如何在非down库的情况下恢复到Dedicate的连接方式,及启用更多的dispatcher?
回答：
7.1关掉:
ALTER SYSTEM SET MTS_DISPATCHERS='TCP,0';
7.2启用更多的dispatcher
ALTER SYSTEM SET MTS_DISPATCHERS='TCP,40';

select object_name from dba_objects where status='INVALID'
将无效的对象找出
建议对表做分析,将索引中blevel>3的索引进行rebuild online

关闭xdb
1、使用sys登录Oracle，利用dbms_xdb修改端口设置
SQL> -- Change the HTTP/WEBDAV port from 8080 to 8081
SQL> call dbms_xdb.cfg_update(updateXML(
dbms_xdb.cfg_get()
, '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()'
, 18081));
/

SQL> -- Change the FTP port from 2100 to 2111
SQL> call dbms_xdb.cfg_update(updateXML(
dbms_xdb.cfg_get()
, '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()'
, 12111));
/

SQL> COMMIT;
SQL> EXEC dbms_xdb.cfg_refresh;

数据库版本
select * from v$version;

实例
select * from v$instance;

创建表空间,帐号,授权
select file_name from dba_data_files;
create tablespace TB datafile '' size xxxM;
create user UN identified by UN default tablespace TB temporary tablespace temp quota unlimited on TB;
grant connect,resource to UN;
grant select on user_objects to username;

当前用户权限
select * from user_sys_privs;

DBA
select * from dba_sys_privs;

授权
grant connect,resource to shsp;

撤消授权
revoke create table from karen;

创建快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fastcompleteforce]
[start with START_DATE next NEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;

或者
create snapshot ss_name refresh force start with sysdate next sysdate+1/24 with primary key as
select * from table_name;

或者设定 oracle 自动在 1 分钟 (1/24*60) 后进行第一次快速刷新，以后每隔 10 分钟 (10/24*60) 快速刷新一次。
create snapshot test_1_ss refresh complete start with sysdate+1/1440 next sysdate+1/144 as select * from test_1@ecom

此 SQL 语句的意思为：设定 oracle 自动在 30 钞(30/24*60*60) 后进行第一次完全刷新,以后每隔 1 天完全刷新一次。
Alter snapshot s_yh_qfcx refresh complete Start with sysdate+1/2880 next sysdate+1 as select * from test_1@ecom;

刷新快照
execute dbms_snapshot.refresh('SNAPSHOT_NAME');

删除实体化视图(oracle 8i中叫快照)
DROP MATERIALIZED VIEW "ss_name" ;

创建数据库连接
create database link DB_LINK connect to USERNAME identified by PASSWORD using 'TNS_NAME_SERVERNAME';
create public database link DB_LINK connect to USERNAME identified by PASSWORD using 'TNS_NAME_SERVERNAME';

select * from user_db_links;
select * from all_db_links;

drop database link DB_LINK ;
drop public database link DB_LINK ;

创建同义词
create synonym USER_SYNONYM for TABLE@db_link;
create public synonym USER_SYNONYM for TABLE@db_link;

select * from user_synonyms;
select * from all_synonyms;

drop synonym USER_SYNONYM;
drop public synonym USER_SYNONYM;

insert into TABLE_NAME (id,name,url,des,lev,fa_id,cls)
values (1000,'栏目管理','#','栏目管理','2','0','2');

insert into table_name select * from table_name1;

收缩表空间
alter database datafile '$ORACLE_DATA/filename.dbf' resize nnnM;
nnn大于此数据文件的实际存储数据
注意:如果以前这个数据文件以前存放过很大的表，除非用
/****
1.截断表
truncate table [drop storage];
2.删除约束
1)查询强制约束
col owner for a15
col constraint_name for a20
col uc.table_name for a20;
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,uc.TABLE_NAME,uc.CONSTRAINT_NAME,ui.tablespace_name
from user_constraints uc,user_indexes ui
where uc.CONSTRAINT_NAME=ui.index_name;
2)删除强制约束
alter table drop constraint index_name;
*****/

Oracle
select username from dba_users;

select table_name from user_tables where table_name = '';
select * from user_sys_privs;
select view_name from user_views;
select SEQUENCE_NAME from user_sequences;
select index_name,index_type,table_name from,tablespace_name from user_indexes order by table_name;
select * from user_synonyms;
select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;

sqlldr 报错
SQL*Loader-605 用户表空间不够

显示表和列的注释
set pages 50000
set null 'No Comments'
tti 'Table Comments'
col comments format a29 wrap word
select * from user_tab_comments;
tti 'Column Comments'
col comments format a18 wrap word
break on table_name skip 1
select * from user_col_comments;
clear break
set null ''
set pages 23

生成控制文件备份的跟踪:
alter database backup controlfile to trace;
alter database backup controlfile to '/home/backup/control.bak';
//跟踪文件在由初始化参数USER_DUMP_DEST 指定的位置中建立

数据库中有索引的帐号
查找数据库中的帐号
1)
select username,created from dba_users order by created;

2)
col owner for a15;
set lines 132;
spool index.txt
select owner,index_name,index_type,table_name from dba_indexes where owner not in
('SYS','SYSTEM','OUTLN','DBSNMP','TRACESVR','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN',
'AURORA$ORB$UNAUTHENTICATED','ORDSYS','MDSYS','ORDPL','USERNAME','CTXSYS');
spool off;

查找表中的索引
set lines 160
col COLUMN_NAME format a20
col INDEX_NAME format a20
select * from user_ind_columns where table_name='TB_NAME';

select index_name,table_name,COLUMN_NAME from user_ind_columns order by table_name;

查看约束所在的表空间
col owner for a15
col constraint_name for a20
col uc.table_name for a20;
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,uc.TABLE_NAME,uc.INDEX_NAME,ui.tablespace_name
from user_constraints uc,user_indexes ui
where uc.index_name=ui.index_name;

//查看用户表的强制约束
select owner,constraint_name,constraint_type,table_name from user_constraints where table_name = 'TAB_NAME';

建表以后添加强制约束
ALTER TABLE ADD CONSTRAINT
PRIMARY KEY () USING INDEX
[PCTFREE
INITRANS MAXTRANS STORAGE (FREELISTS ) ]
TABLESPACE ;

alter table tab_name add constraint tab_name_key primary key(a1,b1) USING INDEX tablespace "TB_name" ;

删除完整性约束
alter table tab_name drop constraint tab_name_key ;

建表以后添加主建索引
ALTER TABLE table_name ADD PRIMARY KEY ("col_name") USING INDEX TABLESPACE "tb_name";

如何在SQL*PLUS中使用Autotrace？
sqlplus system
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
create public synonym plan_table for plan_table;
grant all on plan_table to public;
conn sys
@$ORACLE_HOME/sqlplus/admin/plustrce
grant plustrace to public;

表空间和user都需要删除
drop tablespace XXXX INCLUDING CONTENTS ;
drop user XXXX cascade;

====oracle 9i新功能
drop tablespace TABLESPACE_NAME including contents and datafiles;
====

修改表结构
ALTER TABLE [schema.] table_name MODIFY (column_name new_attributes);
alter table TABLE_NAME modify(column new_attributes);
这个例子中我们修改了表orders，将STATUS列的长度增加到15,将QUANTITY列减小到10,3;
修改列的规则如下：
　　　. 可以增加字符串数据类型的列的长度，数字数据类型列的精度。
　　　. 减少列的长度时，该列应该不包含任何值，所有数据行都为NULL.
　　　. 改变数据类型时，该列的值必须是NULL.
　　　. 对于十进制数字，可以增加或减少但不能降低他的精度。

增加列
语法：ALTER TABLE [schema.] table_name ADD column_definition
例：
ALTER TABLE orders ADD order_date DATE;
对于已经存在的数据行，新列的值将是NULL.

从9.2 的版本才开始支持改列名
alter table TABLE_NAME rename column old_column to new_column;

导出成insert 的sql语句
set lines 160
set pages 0
spool /dat2/temp/in.sql
select
'insert into t_log_base values('''||BAS_MOBNO||''','''||BAS_HOMECITY||''','''||BAS_BRANDID||''','''||BAS_SRVNAME||''','''||BAS_SUBMIT||''','''||BAS_IP||''','''||BAS_RESULT||''','''||BAS_USED||''','''||BAS_SUBMIT2||''','''||BAS_SUBMIT3||''');'
from yh_temp;
spool off

@/dat2/temp/in.sql

3、删除数据列
优化ORACLE数据库，唯一的方法是删除列，重新建立数据库。在ORACLE8i中有很多方法删除列，你可以删除未用数据列或者可以标示该列为未用数据列然后删除。
语法：ALTER TABLE [schema.] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINTS]
要注意的是在删除列时关于该列的索引和完整性约束也同时删除。注意关键字CASCADE CONSTRAINTS，如果删除的列是多列约束的一部分，那么这个约束条件相对于其他列也同时删除。
如果用户担心在大型数据库中删除列要花太多时间，可以先将他们标记为未用数据列，标记未用数据列的语法如下：
ALTER TABLE [schema.] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINTS]
这个语句将一个或多个数据列标记为未用数据列，但并不删除数据列中的数据，也不释放占用的磁盘空间。但是，未用数据列在视图和数据字典中并不显示，并且该数据列的名称将被删除，新的数据列可以使用这个名称。基于该数据列的索引、约束，统计等都将被删除。
删除未用数据列的语句是：
ALTER TABLE [schema.] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}

4、修改表名
rename oldname to newname;
alter table oldname rename to newname;

清空表
　　如果你想删除表中所有数据，清空表，可以考虑使用DDL语言的TRUNCATE语句。TRUNCATE就像没有WHERE子句的DELETE命令一样。TRUNCATE将删除表中所有行。TRUNCATE不是DML语句是DDL语句，他和DELETE右不同的特点。
TRUNCATE TABLE (schema)table DROP(REUSE) STORAGE
　　STORAGE子串是可选的，缺省是DROP STORAGE。当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数。
　　TRUNCATE和DELETE有以下几点区别：
　　1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。
　　2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
　　3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
　　4、TRUNCATE不能触发任何DELETE触发器。
　　5、不能授予任何人清空他人的表的权限。
　　6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
　　7、不能清空父表。

delete from table ;
删除数据不缩短表

truncate table reuse storage ;
清空数据，不写日志(不能回滚) 不缩短表

truncate table drop storage ;
清空数据，不写日志(不能回滚) 缩短表，释放表占用空间

查看对象的名称，类型
set lines 200
column object_name format a30
select object_name,object_type from user_objects where object_name like '% %';
show user;

set lines 200
column object_name format a30
select object_name,object_type from user_objects;
show users;

查看函数和过程的状态
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';

创建sequence
create sequence sequen_name increment by n start with n maxvalue nnnnnnnn nocycle;
select sequen_name.nextval from dual;
select SEQUENCE_NAME,last_number from user_sequences where SEQUENCE_NAME = upper('sequen_name');

显示session,系统进程，sql语句，pid
set lines 140
column SQL_TEXT format a70
col host format a12
col p.pid format a6
SELECT s.machine host ,p.spid OS_process,T.SQL_TEXT,p.pid,s.USERNAME FROM V$SQLTEXT T, V$SESSION S,v$process p
WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE and p.addr=s.paddr and s.machine = ' ';

查看数据库中每个表空间的空闲块情况
select tablespace_name,count(*) chunks ,max(bytes/1024/1024) max_chunk from dba_free_space group by tablespace_name;

其中，CHUNKS列表示表空间中有多少可用的空闲块(每个空闲块是由一些连续的Oracle数据块组成)，如果这样的空闲块过多，比如平均到每个数据文件
上超过了100个，那么该表空间的碎片状况就比较严重了，可以尝试用以下的SQL命令进行表空间相邻碎片的接合:

alter tablespace 表空间名 coalesce;

然后再执行查看表空间碎片的SQL语句，看表空间的碎片有没有减少。如果没有效果，并且表空间的碎片已经严重影响到了数据库的运行，则考虑对该
表空间进行重建。

MAX_CHUNK列的结果是表空间上最大的可用块大小，如果该表空间上的对象所需分配的空间(NEXT值)大于可用块的大小的话，就会提示ORA-1652、
ORA-1653、ORA-1654的错误信息，DBA应该及时对表空间的空间进行扩充，以避免这些错误发生。

建一个和a表结构一样的空表
create table b as select * from a where 1=2;
create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

Q]怎么样快速复制表或者是插入数据
[A]快速复制表可以指定Nologging选项
如：Create table t1 nologging
as select * from t2;
快速插入数据可以指定append提示，但是需要注意
noarchivelog模式下，默认用了append就是nologging模式的。
在archivelog下，需要把表设置程Nologging模式。
如insert /*+ append */ into t1
select * from t2
注意：如果在9i环境中并设置了FORCE LOGGING，则以上操作是无效的，并不会加快，当然，可以通过如下语句设置为NO FORCE LOGGING。
Alter database no force logging;
是否开启了FORCE LOGGING，可以用如下语句查看
SQL> select force_logging from v$database;

游标参数显示
show parameter _cursors

select count(*) from v$open_cursor
可以知道数据库已经使用了多少cursor

select sid,sql_test from v$open_cursor
可以知道cursor所对应的sql

创建long字型表的问题
从一老表创建一新表
create table t1 as select * from t2
由于T2中有一字型为LONG ,报错:
ORA-00997 illegal ues of long datatype
1.copy table 方式
set copycommit 1
set arraysize 5000
COPY FROM A1/A1@A1DB CREATE T2 USING SELECT * FROM T1;

2.CREATE TABLE T2 AS SELECT A1.C1,TO_LOB(A1.C2_LONG) FROM T1 A1

创建索引
CREATE [UNIQUE] INDEX index_name ON table_name(col,col....coln) TABLESPACE tb_name;

确定数据库是工作在Archive模式下的：
select log_mode from v$database;

9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;

show parameter archive;
alter database Archivelog;
log archive start ;
alter database open;
另外不要忘记修改INIT文件,把ARCHIVE Log存放的目录设置好,确认该目录是可以访问的(有写权限),另外设置
log_archive_start=true

connect internal
archive log list

[Q]如何开启/关闭归档
[A]如果开启归档，请保证log_archive_start=true开启自动归档，否则只能手工归档，如果是关闭了归档，则设置该参数为false
注意：如果是OPS/RAC环境，需要先把parallel_server = true注释掉，然后执行如下步骤，最后用这个参数重新启动
1、开启归档
a. 关闭数据库shutdown immediate
b. startup mount
c. alter database archivelog
archive log start
--alter system archive log start; (启用自动归档)
d. alter database opne
2、禁止归档
a. 关闭数据库shutdown immediate
b. startup mount
c. alter database noarchivelog
d. alter database open
归档信息可以通过如下语句查看
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:oracleora92databasearchive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133

计算高速缓冲区命中率
1-(physical reads/(db block gets + consistent gets))
select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');
改善:
修改init$SID.ora 文件的DB_BLOCK_BUFFERS = 参数可以改善

计算库缓冲区命中率
select sum(pins - reloads)/sum(pins) from v$librarycache;
改善库缓冲区命中率
增加在共享池中的内存改善它,
(注:在增加共享池之前,看看当前未用有多少 select * from v$sgastat where name = 'free memory';)
init$SID 文件中的:
shared_pool_size = 50000000 50 million bytes
shared_pool_siez = 10m 10 megabytes
shared_pool_siez = 10k 10 kilobytes

字典缓存命中率
select sum(gets - getmisses - usage - fixed)/sum(gets) from v$rowcache;
调整共享池的规模

检测回滚段争用
select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat;
检测收缩
查看v$rollstat 试图来核查发生的收缩的数量
select name,shrinks from v$rollstat,v$rollname where v$rollstat.usn=v$rollname.usn;

检查是否使用了回滚段自动管理：
set lines 160
col value for a50
select name, value from v$parameter where name like '%undo_%';

监控用户使用回滚段的频度
select a.name,b.extents,b.rssize,b.xacts,b.waits,b.gets,optsize,status from v$rollname a, v$rollstat b where a.usn = b.usn;

增加回滚段的方法：
create rollback segment rs_name tablespace RBS STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 10 MAXEXTENTS 50 OPTIMAL 10M);
alter rollback segment rs_name online;

调试redo 日值缓存器
redo 缓存器分配重试统计数字表明redo 日志缓存器是否发生争用。
select name,value from v$sysstat where name = 'redo buffer allocation retries';
改善
增加初始化参数:LOG_BUFFER =

获得整体的数据缓冲命中率，越高越好(缓冲区高速缓存的调整)
SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads, round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads';

如果Hit Ratio<60%～70%，则应增大db_block_buffers的参数值。db_block_buffers可以调整分配给缓冲区高速缓存的内存量，即db_block_buffers可设置分配缓冲区高速缓存的数据块的个数。缓冲区高速缓存的总字节数=db_block_buffers的值*db_block_size的值。 db_block_size 的值表示数据块大小的字节数，可查询 v$parameter 表：
select name,value from v$parameter where name='db_block_size';
在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用。

以下语句查询了Sql语句的重载率，越低越好
SELECT SUM(pins) total_pins,SUM(reloads) total_reloads, SUM(reloads)/SUM(pins)*100 libcache_reload_ratio FROM v$librarycache;

库缓冲区的调整
Select sum(pins),sum(reloads),round(sum(reloads)/sum(pins),6) "reloads/pins" from v$librarycache;
Pins列给出SQL语句，PL/SQL块及被访问对象定义的总次数；Reloads列给出SQL 和PL/SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。如果sum(pins)/sum(reloads) ≈0，则库缓冲区的命中率合适；若sum(reloads)/sum(pins)>1, 则需调整初始化参数 shared_pool_size来重新调整分配给共享池的内存量。

数据字典缓冲区的调整
Select sum(gets),sum(getmisses),round(sum(getmisses)/sum(gets),6) "getmisses/gets" from v$rowcache;
Gets列是对相应项请求次数的统计；Getmisses 列是引起缓冲区出错的数据的请求次数。对于频繁访问的数据字典缓冲区，sum(getmisses)/sum(gets)<10%～15%。若大于此百分数，则应考虑增加数据字典缓冲区的容量，即需调整初始化参数shared_pool_size来重新调整分配给共享池的内存量。

查看当前用户每个表占用表空间的大小：
set lines 132
col Segment_Name format a30
Select Segment_Name,Sum(bytes)/1024/1024 sum,tablespace_name,segment_type From User_segments Group
By Segment_Name,tablespace_name,segment_type order by sum ;

set lines 132
col Segment_Name format a30
Select Segment_Name,Sum(bytes)/1024/1024 sum,tablespace_name,segment_type From User_Extents Group
By Segment_Name,tablespace_name,segment_type order by sum;

select 'select sum(bytes)/1024/1024 from user_segments where segment_name = ''' || tname || ''';' from tab; //用''个单银号可以事生成的sql语句带有一个单引号

查看指定表空间中对象占用表空间的大小：
set lines 132
col Segment_Name format a30
Select Segment_Name,Sum(bytes)/1024/1024,tablespace_name,segment_type From dba_segments
where tablespace_name=''
Group By Segment_Name,tablespace_name,segment_type;

oracle 9i中查看非系统表空间中对象大小
set lines 132
col Segment_Name format a30
Select Segment_Name,Sum(bytes)/1024/1024,tablespace_name,segment_type From dba_segments
where tablespace_name not in ('SYSTEM','UNDOTBS1','TEMP','DRSYS','EXAMPLE','ODM','TOOLS','USERS','XDB')
Group By Segment_Name,tablespace_name,segment_type order by tablespace_name ;

select sum(BYTES), sum(blocks),sum(extents) from dba_segments where segment_name='AA';
计算当前用户每个表占用空间的大小
select sum(vsize(col1)+vsize(col2)+vsize(col3)+vsize(col4)+.....) from tb_name;
查看表空间的名称及大小:
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

查看表空间物理文件的名称及大小:
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

查看回滚段名称及大小:
set lines 160
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+) order by segment_name;

如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:
select d.sql_text,a.name
from v$rollname a,v$transaction b,v$session c,v$sqltext d
where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=
d.address and c.sql_hash_value=d.hash_value
and a.usn=1;
(备注：你要看哪个，就把usn=?写成几就行了)

Oracle所有回滚段状态的检查：
set lines 160
col tablespace_name for a30
select segment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.status from
dba_rollback_segs,v$datafile where file_id=file#;
Oracle回滚段扩展信息的检查:
col name format a10
set linesize 140
select substr(name,1,40) name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsize
from v$rollname rn,v$rollstat rs where (rn.usn=rs.usn);
extents:回滚段中的盘区数量。
Rssize:以字节为单位的回滚段的尺寸。
optsize：为optimal参数设定的值。
Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。
Extends:系统为回滚段增加的盘区的次数。
Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。
(如果回滚段平均尺寸接近OPTIMAL的值，那么说明OPTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)

查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;

数据库回滚段性能检查:
检查Ratio执行
select sum(waits)* 100 /sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets" from v$rollstat;
检查count/value执行:
select class,count from v$waitstat where class like '%undo%';

select value from v$sysstat where name='consistent gets';
(两者的value值相除)
检查average_wait执行:
select event,total_waits,time_waited,average_wait from v$system_event
where event like '%undo%';

检查RBS header get ratio执行:
select n.name,s.usn,s.wraps, decode(s.waits,0,1,1- s.waits/s.gets)"RBS
header get ratio" from v$rollstat s,v$rollname n where s.usn=n.usn;
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)

14、查看回滚段:
col name format a10
set linesize 160
select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents,
v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,v$rollstat.gets Gets, v$rollstat.waits Waits,
v$rollstat.writes Writes, sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
and v$rollstat.usn (+) = v$rollname.usn order by rownum;

7、查看数据库库对象:
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

检查数据字典的命中率:
select 1-sum(getmisses)/sum(gets) "data dictionary hit
ratio" from v$rowcache;
(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)

检查共享内存的剩余情况:
select request_misses, request_failures from v$shared_pool_reserved;
(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)

数据高速缓冲区性能检查:
select 1-p.value/(b.value+c.value) "db buffer cache hit
ratio" from v$sysstat p,v$sysstat b,v$sysstat c where
p.name='physical reads' and b.name='db block gets' and
c.name='consistent gets';
检查buffer pool HIT_RATIO执行
select name, (physical_reads/(db_block_gets+consistent_gets))
"MISS_HIT_RATIO" FROM v$buffer_pool_statistics WHERE (db_block_gets+ consistent_gets)> 0;
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)

查看控制文件:
select * from v$controlfile;

查看日志文件:
col member format a50
select * from v$logfile;

怎么判断当前正在使用何种SQL优化方式:
用explain plan产生EXPLAIN PLAN，检查PLAN_TABLE中ID=0的POSITION列的值。
select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;

如何查看系统当前最新的SCN号：
select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;

在ORACLE中查找TRACE文件的脚本:
select u_dump.value || '/' || instance.value || '_ora_' ||
v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid');

select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from (select p.spid from sys.v_$mystat m,sys.v_$session s,
sys.v_$process p where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d;

如何查看客户端登陆的IP地址:
select sys_context('userenv','ip_address') from dual;

如何在生产数据库中创建一个追踪客户端IP地址的触发器：
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;

REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
insert into session_history
select username,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
from v$session where audsid = userenv('sessionid');
END;

25、查询有哪些数据库实例在运行:
select inst_name from v$active_instances;

27、查看表是否是分区表：
select table_name,PARTITION_NAME from USER_TAB_PARTITIONS;

28、查看分区表的分区名和相应的表空间名：
select TABLE_NAME, PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name like ‘%USAGE%’;

28.1、建立分区表
CREATE TABLE tname(COL1 TYPE(n),COL2 TYPE(n)) PARTITION BY RANG(COL)(PARTITION PART1 VALUES LESS THAN(n) TABLESPACE tb_name,
PARTITION PART2 VALUES LESS THAN(m) TABLESPACE tb_name);

28.2、建立分区表
Create table Employee (
EmpNo varchar2(10) primary key,
Name varchar2(30),
DeptNo Number(2)
)
Partition by range(DeptNo)
( partition PART1 values less than (11)
tablespace PART1_TS,
partition PART2 values less than (21)
tablespace PART2_TS,
partition PART3 values less than (31)
tablespace PART3_TS
partition PART4 values less than (MAXVALUE)
tablespace PART4_TS
);

28.2、分区索引
Create index Employee_DeptNo on Employee(DeptNo) local (
partition PART1 tablespace PART1_NDX_TS,
partition PART2 tablespace PART2_NDX_TS,
partition PART3 tablespace PART3_NDX_TS,
partition PART4 tablespace PART4_NDX_TS,
);

29、查看索引是否是分区索引：
SELECT INDEX_NAME, TABLE_NAME, STATUS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME LIKE '%USAGE';

30、如果返回的PATITIONED为YES，请再执行如下语句来查询分区索引的类型：
SELECT index_name,table_name,locality FROM user_part_indexes;

31、Dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select中.
查看系统时间：
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') shijian from dual;

32、查看索引段中extent的数量：
select segment_name,count(*) from dba_extents
where segment_type='INDEX' and owner='SCOTT' group by segment_name;

33、查看系统表中的用户索引（用来检查在system表空间内其他用户索引的存在）：
select count(*) from dba_indexes where tablespace_name=’SYSTEM’ and owner NOT IN(‘SYS’,’SYSTEM’);

22、有关connection的相关信息:
1）查看有哪些用户连接
select s.osuser os_user_name,decode(sign(48 - command),1,to_char(command),
'Action Code #' || to_char(command))action,p.program oracle_process,
status session_status,s.terminal terminal,s.program program,
s.username user_name,s.fixed_table_sequence activity_meter,''query,
0 memory,0 max_memory,0 cpu_usage,s.sid,s.serial# serial_num
from v$session s,v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser;

select username,command,status,process,machine from v$session;

查询当前日期:
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

#!/bin/sh
# ck_tablespaces.sh
sqlplus $1/$2@$3 <set feed off
set lines 160
set pagesize 999
col tablespace_name format a10
col DBfile_name format a40
col b.file_id format a10
spool $4.txt
select b.file_id,b.FILE_NAME DBfile_name,b.tablespace_name,b.bytes,b.maxbytes maxbytes,( b.bytes-sum(nvl(a.bytes,0))) USED,sum(nvl(a.bytes,0)) SHENGYU,
sum(nvl(a.bytes,0))/(b.bytes)*100 SHENYUpre
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes,b.FILE_NAME,b.maxbytes
order by b.file_id;
spool off
exit
EOF

echo ""
date >>$4.txt

查看所有表空间对应的数据文件名：
select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

column tablespace_name format a18;
column Sum_M format a12;
column Used_M format a12;
column Free_M format a12;
column pto_M format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M' Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M' Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,
sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100 FreePercentRatio from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by b.file_id,b.tablespace_name,b.bytes
order by b.tablespace_name) s group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc;

数据库各个表空间增长情况的检查：
select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
From (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name;

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比", F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

表空间统计
A、脚本说明：
这是我最常用的一个脚本，用它可以显示出数据库中所有表空间的状态，如表空间的大小、已使用空间、使用的百分比、空闲空间数及现在表空间的最大块是多大。
B、脚本原文:
SELECT upper(f.tablespace_name) "表空间名",
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;

Oracle 创建本地管理表空间
create tablespace TB_name datafile '$ORADATA/$SID/FILE_DATnn.dbf' size xxxM extent management local autoallocate;
//区的大小由系统自动分配
create tablespace TB_name datafile '$ORADATA/$SID/FILE_DATnn.dbf' size xxxM extent management local uniform size xxxK;
//用相同大小的区默认是1M
create tablespace TB_name datafile '$ORADATA/$SID/FILE_DATnn.dbf' size xxxM extent management local;

alter database datafile '$ORADATA/$SID/FILE_DATnn.dbf' autoextend on next 50M maxsize 1000M;

oracle 817
alter system set COMPATIBLE=8.1.6
exec sys.dbms_space_admin.tablespace_migrate_to_local('TB_name') ;
exec sys.dbms_space_admin.tablespace_migrate_from_local('TB_name') ;

删除掉那个文件所属的表空间。
DROP TABLESPACE INCLUDING CONTENTS;

ORACLE 9I功能
DROP TABLESPACE INCLUDING CONTENTS ANDdatafiles;

使数据库所有用户都可用。
ALTER SYSTEM DISABLE RESTRICTED SESSION;

查看数据文件状态,执行如下的查询：
SELECT FILE#,NAME,STATUS FROM V$DATAFILE;
select file_name,status from dba_data_files;

检查数据文件的状态
select file_name,status from dba_data_files;

检查数据库定时作业的完成情况
select job,log_user,last_date,failures from dba_jobs;
select job,log_user,last_date,failures from user_jobs;

调度清除作业
disconnect;
connect repadmin/repadmin@SH;
begin
dbms_defer_sys.schedule_purge(
next_date=>sysdate,interval=>’sysdate + 1’,delay_seconds=>0);
end;

如果数据文件的状态是 offline 的，你必需先把它联机了：
ALTER DATABASE DATAFILE '' ONLINE;

执行如下的查询：
这将列出所有的联机的重做日志和他们的序号及首次改变号 (first change numbers).
SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

如果这个数据库是非归档模式的，执行如下的查询：
SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
如果其中的 CHANG# 比 4 中的最小的那个 FIRST_CHANGE# 大的话，用联机日志就可以完成恢复。

查看各个表空间占用磁盘情况:
col tablespace_name format a20;
select b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;

查看每个表空间占用空间的大小
set lines 200
col TS_PLACE format a50
col TS_NAME format a15
select b.file_id ID,b.FILE_NAME TS_place,b.tablespace_name TS_name,b.bytes BYTES,
(b.bytes-sum(nvl(a.bytes,0))) USED,sum(nvl(a.bytes,0)) surplus,
sum(nvl(a.bytes,0))/(b.bytes)*100 surplus_pre
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes,b.FILE_NAME
order by b.file_id;

查看每个表空间占用空间的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

查看段(表段、索引段)所使用空间的大小
A、脚本说明：
有时你可能想知道一个表或一个索引占用多少M的空间，这个脚本就是满足你的要求的，把<>中的内容替换一下就可以了。
B、脚本原文：
/*system*/
set lines 132
col segment_name format a40
SELECT segment_name,SUM(bytes)/1024/1024,segment_type,TABLESPACE_NAME
FROM dba_segments
WHERE owner= upper('ora_username') And segment_type='INDEX'
GROUP BY segment_name,segment_type,TABLESPACE_NAME
ORDER BY 2 DESC;

/*oracle username*/
set lines 132
col segment_name format a40
SELECT segment_name,SUM(bytes)/1024/1024,segment_type,TABLESPACE_NAME
FROM user_segments
WHERE segment_type='INDEX'
GROUP BY segment_name,segment_type,TABLESPACE_NAME
ORDER BY 2 DESC;

生成重建索引语句
-- DIFINE 定义一个已知的表空间
define index_tb=indx;
set pages 0
SELECT 'ALTER INDEX ' || SEGMENT_NAME || ' REBUILD TABLESPACE &index_tb ONLINE NOLOGGING; '
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX' AND TABLESPACE_NAME <> '&index_tb';
set pages 14;

查看表在表空间
select table_name,tablespace_name from user_tables;
生成移动表到新表空间语句
define newtb=jxlt;
set pages 0
select 'alter table '||TABLE_NAME ||' move tablespace &newtb;' from user_tables where TABLESPACE_NAME !='&newtb';

set pages 14;

-- 提示输入一个表空间
ACCEPT index_tb PROMPT 'Enter INDEX tablespace name: ' ;
SELECT 'ALTER INDEX ' || SEGMENT_NAME || ' REBUILD TABLESPACE %&index_tb% ONLINE NOLOGGING; '
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX' AND TABLESPACE_NAME <> '&index_tb%';
undefine index_tb;

删除索引
DROP INDEX newsindex;
强制删除索引
DROP INDEX newsindex FORCE;

重建索引
alter index INDEX_NAME rebuild tablespace INDX_TABLESPACE online nologging storage(initial 5k next 5k);
tablespace INDX_TABLESPACE 在索引表空间里建立索引的新版本
online 指定在重建过程中希望用户访问表（如果使用compute statistics 或reverse 字句，不能联机重建一个索引，位映射索引也不能联机重建)
nologging 防止oracle 在redo日志中记录重建过程.(如果需要从备份中恢复数据库并通过变化向前回滚，重建索引讲不包括在那，但是可以通过手工在重建索引）
storage (initial 5k next 5k) 位索引分配最初值位5k的范围，这些值使建立索引的表空间的默认设定无效
当选择online选项时，不允许某些类型的变化。例如，在联机重建过程中不能计算统计数字。如果试图进行oracle所不支持的联机重建，讲的到错误消息:
ORA-08108: may not build or rebuild this type of index online

创建了个重做日志组
alter database add logfile group 3 ("/home/oracle/app/oracle/oradata/8idb/redo03.log") size 4096K ;

索引重建
ALTER INDEX newsindex REBUILD online PARAMETERS('replace lexer my_lexer');

为这个表空间增加一个数据文件
alter tablespace 表空间名 add datafile '/u1/oradata/userdata_002.ora' size 50m;
alter database datafile '/u1/oradata/userdata_002.ora' autoextend on next 5M maxsize 8000M;

调整数据文件的大小
alter database datafile '/u01/oradata/ECOM/test01.dbf' resize 10M;

查看数据库物理文件是否是自动增长
set lines 132
col tablespace_name for a15
col file_name for a50
select file_id,file_name,tablespace_name,BYTES,maxbytes,USER_BYTES from dba_data_files;

set lines 132
col tablespace_name for a15
col file_name for a50
select tablespace_name,file_name,round(bytes/(1024*1024),0) total_space,
autoextensible,increment_by,maxbytes,user_bytes from dba_data_files order by tablespace_name;
如果你管理的数据库物理文件是自动增长的并有可能超过操作系统或Oracle版本文件最大限制时, 请尽快
改变它的属性, 把它设定成非自动增长的或者限定它最大的值.
假如物理文件的物理位置是 $ORACLE_DATA/DATAFILE.DBF 用下面的SQL语句可以实现这一操
作:
alter database datafile '$ORACLE_DATA/DATAFILE.DBF' autoextend off;
alter database datafile '$ORACLE_DATA/DATAFILE.DBF' autoextend on next 50M maxsize 8000M;

查看数据文件的大小
set lines 160
col file_name for a40
col TABLESPACE_NAME for a15
col bytes for 9,999,999.999
col maxbytes for 99,999,999,999.999
col user_bytes for 9,999,999.999
select FILE_ID,file_name,TABLESPACE_NAME,BYTES/1024/1024 bytes,MAXBYTES/1024/1024 MAXBYTES,USER_BYTES/1024/1024 USER_BYTES,status
from dba_data_files order by file_id;

set lines 160
col file_name for a50
col TABLESPACE_NAME for a15
select FILE_ID,file_name,TABLESPACE_NAME,BYTES,MAXBYTES,USER_BYTES,status
from dba_data_files order by file_id;

数据文件改名
alter tablespace TB_NAME offline
mv old_file new_file;
alter database rename file '/u01/oradata/test01.dbf' to '/u01/oradata/ECOM/test01.dbf';
//alter tablespace test rename datafile '/u01/oradata/test02.dbf' to '/u01/oradata/ECOM/test02.dbf';
alter tablespace TB_NAME ofline;

表空间改成只读
alter tablespace TB_NAME read only;

表空间改成读写
alter tablespace TB_NAME read write;

数据文件移动改名
1)表空间离线
alter tablespace TB_NAME offline;
2)移动文件到新位置
mv test01.dbf /u01/oradata/ECOM/test01.dbf
3)在oracle种重命名
alter database rename file '/u01/oradata/test01.dbf' to '/u01/oradata/ECOM/test01.dbf';
4)表空间联机在线
alter tablespace test online;
5)重写日志切换循环
alter system switch logfile;
6)查看v$recover_file和dba_data_files确定移动是否成功
select * from v$recover_file;
select file_name from dba_data_files where tablespace_name=upper('test');

Disk Read最高的SQL语句的获取：
select sql_text from (select * from v$sqlarea order by disk_reads)
where rownum<=5;

查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
sql_text FROM v$sqlarea ORDER BY disk_reads DESC)
WHERE ROWNUM<10 ;

重新调整数据文件的大小
alter database datafile '/u1/oradata/userdata_001.ora' resize 50M;

如何查看一张表是否建立了索引,及其索引是什么
SELECT * FROM USER_INDEXES WHERE TABLE_NAME=UPPER('table_name');

查询oracle server端的字符集
select userenv(‘language’) from dual;

如何查询dmp文件的字符集
用UltraEdit打开(16进制方式)，看第2第3个字节的内容，如0354
select nls_charset_name(to_number('0354','xxxx')) from dual;
如果dmp文件很大用文本编辑器打开很慢或者完全打不开，可以用以下命令(在unix主机上):
cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

查询oracle client端的字符集
注册表里面相应OracleHome的NLS_LANG
set nls_lang=AMERICAN_AMERICA.ZHS16GBK
在unix平台下，就是环境变量NLS_LANG。
$echo $NLS_LANG

检查数据库字符集
1
select userenv('language') from dual;
. 如何查看当前数据库的字符集？
在SQL*PLUS下，运行：
“select userenv('language') from dual;”
“select userenv('lang') from dual;”

2
set lines 100
col PARAMETER format a50
col value format a20
select * from V$NLS_PARAMETERS;

帐户加锁解锁
ALTER USER username ACCOUNT LOCK ;
ALTER USER username ACCOUNT UNLOCK ;

封锁表
共享方式的表封锁
LOCK TABLE <表名>[,<表名>] IN SHARE MODE [NOWAIT]
独占方式表封锁
LOCK TABLE <表名>[,<表名>]....IN EXCLUSIVE MODE [NOWAIT]

共享更新封锁方式
下的两种方式来获得行级封锁:
　　(1)、执行如下的SQL封锁语句，以显示的方式获得:
　　LOCK TABLE <表名>[,<表名>]....
　　IN SHARE UPDATE　MODE [NOWAIT]
　　(2)、用如下的SELECT ...FOR UPDATE语句获得:
　　SELECT <列名>[,<列名>]...
　　FROM <表名>
　　WHERE <条件>
　　FOR UPDATE OF <列名>[,<列名>].....[NOWAIT]

SESSION_CACHED_CURSORS参数对性能的意义
SESSION_CACHED_CURSORS,就是说的是一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解析的问题)
软解析同硬解析一样,比较消耗资源.所以这个参数非常重要.
Oracle 9i及以前,该参数缺省是0,10G上缺省是20

跟踪sql语句
1)查看操作系统进程
top查找消耗资源的oracle进程号

2)找出相应的sid,serial#
set lines 160
col username for a15
col osuser for a15
select pro.spid,ses.username,ses.osuser,ses.sid,ses.serial# from v$session ses,v$process pro
where ses.sid=SID and ses.paddr=pro.addr and pro.spid=OS_PID;

3)跟踪sid,serial#语句
alter session set sql_trace=true;
execute dbms_system.set_sql_trace_in_session(sid,serial#,true); /*sys执行*/
//或者是执行一下
select ses.username,ses.osuser ||' execute dbms_system.set_sql_trace_in_session('||ses.sid ||','||ses.serial#||',true);'
from v$session ses,v$process pro where ses.sid=SID and ses.paddr=pro.addr and pro.spid=&OS_PID;
//输入要跟踪的进程号

拷贝,执行生成的exec语句
4)停止跟踪
execute dbms_system.set_sql_trace_in_session(sid,serial#,false);
alter session set sql_trace=false;
5)分析跟踪记录
tkprof file.trc trace.out sys=no explain=username/passwd

查询表是否被锁
1
col object_name for a20
set lines 160
select a.username username, a.osuser os, a.sid, a.serial# ,c.object_name from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id and a.sid = b.session_id and c.OBJECT_NAME = '&table_name';

如果不能解锁(杀掉系统进程)
select pro.spid from v$session ses,v$process pro where ses.sid=SID and ses.paddr=pro.addr;
2
select object_id,session_id,locked_mode,ORACLE_USERNAME,OS_USER_NAME from v$locked_object;
3
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

解锁
alter system kill session 'sid,serial# ';
select object_id from dba_objects where object_name='&OBJECT_NAME';

select session_id from v$locked_object where object_id in (select object_id from dba_objects where object_name='&OBJECT_NAME');;

或者
select 'alter system kill session '''||sid||','|| serial#||''';' from v$session where sid in (select session_id from v$locked_object where object_id=OBJECT_ID);

简单查锁解锁语句
col object_name for a20
set lines 160
set verify off
col A.SID for a5
select 'alter system kill session '''||a.sid||','||a.serial#||''';' from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id and a.sid = b.session_id and c.OBJECT_NAME = '&OBJECT_NAME';

查session中的操作系统进程号
set lines 140
col session_username format a18
col host format a25
select s.sid,s.username session_username,p.spid OS_process,s.machine host
from v$session s,v$process p
where p.addr=s.paddr and s.username='USERNAME'; /*and p.spid = 'xxxxx';*/
从操作系统中杀掉不能断开的链接
host kill -9 'SPID'

15、耗资源的进程(top session):
select s.schemaname schema_name,decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) )
action,status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[Oracle process]')
user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st,v$session s,v$process p
where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr
order by st.value desc,p.spid asc,s.username asc,s.osuser asc;

耗资源的进程（top session）
set lines 140
select s.schemaname schema_name, decode(sign(48 - command), 1,
to_char(command), 'Action Code #' || to_char(command) ) action, status
session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc;

/*
找出消耗CPU最高的进程对应的SQL语句：
set line 240
set verify off
column sid format 999
column pid format 999
column S_# format 999
column username format A9 heading "ORA User"
column program format a29
column SQL format a60
COLUMN OSname format a9 Heading "OS User"
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
*/

CPU用率最高的2条SQL语句的获取
执行：top，通过top获得CPU占用率最高的进程的pid。
1)
select v$session.sid,v$session.serial# from v$session,v$process where v$session.paddr=v$process.addr and v$process.spid='OS_PID';
select sql_text from v$sqltext_with_newlines
where hash_value=(select SQL_HASH_VALUE from v$session where sid=%sid)
order by piece;

select sql_text from v$sqltext_with_newlines
where hash_value=(select SQL_HASH_VALUE from v$session where sid in (select v$session.sid from
v$session,v$process where v$session.paddr=v$process.addr and v$process.spid='OS_PID'))
order by piece;

执行：top，通过top获得CPU占用率最高的进程的pid。
2)
select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr and v$process.spid = OS_PID;

3)执行：top，通过top获得CPU占用率最高的进程的os_pid。
col machine format a30
col program format a40
set line 200
select sid,serial# ,username,osuser,machine,program,process,
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') from v$session where paddr in(select addr from
v$process where spid in([$OS_PID]));

查询最耗资源的sql的语句
select sql_text from v$sql where address in ( select sql_address from v$session where sid in
( select sid from v$session where paddr in ( select addr from v$process where spid=OS_PID)));

#SESSION 等待事件的OS_PID
select sid,event,p1,p1text from v$session_wait;
select spid from v$process where addr in (select paddr from v$session where sid in(SID1,SID2,SID3));

查看占io较大的正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,
se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC;
可以看一下这些等待的进程都在忙什么，语句是否合理？
Select sql_address from v$session where sid=;
Select SQL_TEXT from v$sqltext where address in ( Select sql_address from v$session where sid=);

执行以上两个语句便可以得到这个session的语句。
可以用alter system kill session 'sid,serial#';把这个session杀掉。

捕捉运行很久的SQL
set lines 140
column username format a10
column opname format a16
column progress format a8
column sql_text format a40
column time_remaining format a10
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
/

10、捕捉运行很久的SQL:
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops , v$sql
where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value;

监控当前数据库谁在运行什么SQL语句:
set lines 160
set long 10000
col MACHINE for a30
col osuser for a15
SELECT osuser, username,MACHINE, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
set lines 132

如何查出前台正在发出的sql语句:
select user_name,sql_text from v$open_cursor where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'));

查询当前所执行的SQL语句：
top后获得最高的os_pid
1
select program ,sql_address from v$session where paddr in (select addr from v$process where spid=OS_PID);

set long 10000
select sql_text from v$sqlarea where address='000000038FCB1A90';

2
set long 10000
select sql_text from v$sqlarea where address in (select sql_address from v$session where paddr in (select addr
from v$process where spid=OS_PID));

37、查看一下哪个用户在用临时段:
SELECT username,sid,serial#,sql_address,machine,program,tablespace,segtype,
contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr;

38、查看占io较大的正在运行的session:
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program, se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR
AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;

40、删除用户下所有表的语句:
set lines 160;
set pages 0;
col object_name for a60;
col object_type for a50;
select 'drop '||OBJECT_TYPE,'"'||OBJECT_NAME||'" ;' from user_objects;

select 'drop '||OBJECT_TYPE,'"'||OBJECT_NAME||'" purge ;' from user_objects;

set pagesize 14

43. 怎样识别IO竞争和负载平衡:
col 文件名 format a35
select
df.name 文件名,
fs.phyrds 读次数,
fs.phywrts 写次数,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) r_time,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) w_time
from
v$datafile df,
v$filestat fs
where df.file#=fs.file#
order by df.name
/

有关回滚段的常见错误及解决方法
(1) 回滚段空间不够
ORA-01562 - failed to extend rollback segment number string
回滚段空间不够的原因一般有以下几种情况:
A. 回滚段所在表空间剩余的空闲空间太小, 无法分配下一个EXTENT.
B. 回滚段扩展次数已经达到MAXEXTENTS限制

解决方法:
A. 扩大回滚段所在表空间
B. 设置较大的MAXEXTENTS参数
C. 为回滚段设置OPTIMAL参数
D. 用较大的E

ITPub博客

oracle

最新文章