oracle

1. 命令

1) 连接数据库
sqlplus "/as sysdba"
sqlplus system/manager@mplus


2) 启动、停止数据库 (在orace用户下以dba用户进入sql)
shutdown immediate;
startup;


3) 查看当前服务中有多少数据库(即用户) (以dba用户进入sql)
select USERNAME from all_users;


4) 查看当前数据库中有多少表 (以数据库名进入sql)
select TABLE_NAME from user_tables;
或select table_name from all_tables where owner='aaaaa';


5) 查看当前表是否有分区
select PARTITION_NAME from USER_TAB_PARTITIONS where table_name = 'TBL_USER_STORAGE';


6) 查看某个分区表中的数据存储在哪个分区
SQL> SELECT rowid, user_id from tbl_user_storage; //从表中查出行号，最好带个表的主键以便确认是哪条记录
ROWID USER_ID
------------------ ----------
AABml0AAKAAAAdnAAA 1
AABml1AAKAAAAduAAA 2
AABml2AAKAAAAd2AAA 3

SQL> select dbms_rowid.ROWID_OBJECT('AABml2AAKAAAAd2AAA') from dual; //从dual中查出行的对象
DBMS_ROWID.ROWID_OBJECT('AABML2AAKAAAAD2AAA')
---------------------------------------------
420214

SQL> select subobject_name from dba_objects where data_object_id = 420214; //根据对象ID查出分区
SUBOBJECT_NAME
------------------------------------------------------------
PART_4

ps: 在建表时指定做为分区索引的字段，如MC中的part_index， 此字段是根据userid%分区数来计算的。
如在插入一条记录时，userid为2，分区数为100，所以可计算出part_index为2，插入数据库的记录中也为(2,2)
但实际上，此条记录是被存储在分区3中的，查询时，需要指定在分区3中查询。

插入记录时通过指定part_index指定分区，但在其它操作时，必需显式指定分区号 如 TBL_USER_STORAGE partition(PART_4)


7) 创建数据库
以oracle/oracle登录后，sqlplus "/ as sysdba"
SQL> create tablespace zyan datafile '/opt/oracle/oradata/oracle9/zyan01.dbf' size 100M;
//create tablespace zhaoyan datafile '/dev/rmmslv3' size 100M;
表空间已创建。
（其中mmsc_data为表空间的名称，mmscdata01.dbf为数据库文件，这两个名称自己修改）

//SQL> create tablespace mmsc_index datafile '/opt/oracle/ora_data/ora817/mmscindx01.dbf' size 100M;
//表空间已创建。
//（其中mmsc_index为表空间的索引名，mmscindx01.dbf为索引文件，这两个名称自己修改，这个操作可选，如果不作的话，默认使用的是oracle系统的）

SQL> create user zyan identified by zyan default tablespace zyan;
用户已创建
（如果第二步没有做的话，这一句要省去“temporary tablespace temp”）

SQL> grant connect,resource to zyan;
授权成功。

SQL> commit;
提交完成。

注意，裸设备的话不能让表空间自动增长（AUTOEXTEND OFF）


8) 启动侦听
oracle> lsnrctl
lsnrctl> start
lsnrctl> stop

或是直接执行 lsnrctl start/status/stop


9) ping service_name是否可用
tnsping service_name 如 tnsping mplus


10) 显示Oraclenb 系统参数
select name,value ,issys_modifiable from v$parameter;
或show parameter;


11) 查看哪些表被锁住了
select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id;
或
SELECT /*+ rule */ s.username,decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null

解锁：
alter system kill session 'sid,serial#';


12) 检查Sql最后执行的SQL语句
select sql_text,count(*) from v$sql s , v$session se where se.prev_hash_value =s.hash_value group by sql_text;


13) 查看回滚段信息
select segment_name,tablespace_name, status from sys.dba_rollback_segs;
select segment_name, tablespace_name,bytes,blocks,extents from sys.dba_segments where segment_type='ROLLBACK';


14) Oracle字符集
在数据库端：选择需要的字符集（通过create database中的CHARACTER SET与NATIONAL CHARACTER SET子句指定）；
在客户端：设置操作系统实际使用的字符集（通过环境变量NLS_LANG设置）
select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';

select * from v$nls_parmater;
一般使用AL32UTF8


15) 用对户进行解锁
　　　alter user PORTALDB account unlock;


16) 用户权限查询
　　　确定角色的权限
　　　select * from role_tab_privs;
select * from role_role_privs;
select * from role_sys_privs;

确定用户帐户所授予的权限
select * from DBA_tab_privs;
select * from DBA_role_privs;
select * from DBA_sys_privs;

确定当前用户的权限
select * from session_privs;


17) 某个文件需要恢复
　　　SQL> startup
　　　　　　　ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2142976 bytes
Variable Size 159386880 bytes
Database Buffers 50331648 bytes
Redo Buffers 5296128 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/opt/oracle/oracle/oradata/zyan01.dbf'

//试图启动数据库时报错，某个文件出错

　　　　　　　//解决方法
SQL> alter database datafile '/opt/oracle/oracle/oradata/zyan01.dbf' online;

Database altered.

SQL> recover database;
Media recovery complete.

SQL> shutdown　　　　　//先关闭
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup //再次启动
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2142976 bytes
Variable Size 159386880 bytes
Database Buffers 50331648 bytes
Redo Buffers 5296128 bytes
Database mounted.
Database opened.
　　　
或：
　如果出错的文件不重要的话，，可以
　先 startup mount
再 alter database datafile '/opt/oracle/oracle/oradata/zyan01.dbf' offline;


18)　　ORA－01031　insufficient proivileges　　解决方法
1.　首先查看　network/admin下sqlnet.ora文件的权限是否正确
2.　检查环境变量是否正确　　ORACLE_HOME ORACLE_SID等
3.　查看共享内存是否释放　　　ipcs -m /ipcs -s 如果没有释放，就强制删除。



20) 归档模式
　　　 查看当前模式：　　　archive log list
#从归档修改成非归档，归档模式会导致程序挂起，直到手动归档完成
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> shutdown immediate;
SQL> startup

#从非归档修改成归档模式
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set log_archive_start=FALSE scope=spfile;
SQL> shutdown immediate;
SQL> startup


21) oracle内存管理
　　　 sga_max_size表示当前总空间，sga_target值必须和sga_max_size一样。
　　　 db_cache_size java_pool_size large_pool_size shared_pool_size　全部设置为0，让oracle自动管理内存，均衡分配。

　　　 建议值：
alter system set sga_max_size = 1536M scope=spfile;
alter system set sga_target = 1536M scope=spfile;

alter system set db_cache_size = 0 scope=spfile;
alter system set java_pool_size = 0 scope=spfile;
alter system set large_pool_size = 0 scope=spfile;
alter system set shared_pool_size = 0 scope=spfile;

alter system set shared_pool_reserved_size = 31037849 scope=spfile;

查看当前SGA值 select sum(value)/1024/1024 from v$sga;
　　　　　查看当前buffer剩余值 select current_size from v$buffer_pool;
　　　　　查看当前buffer分配 select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;


　　　 PGA: program globle area 程序全局区，服务器进程所使用的内存区域。　
　　　　　　oracle会尽是保证总PGA内存不超过pag_aggregate_target，但如果不够用，为了避免操作磁盘，
oralce还是会使用内存，PGA值将超过pag_aggregate_target
　　　 查看advice: select * from v$pga_target_advice;
如果内存够，可以选择命中率接近100%的值

pag_aggregate_target 也建议设置为 (总内存 - SGA)

alter system set pga_aggregate_target = 1659m scope=spfile;

查看SGA的状态 select * from v$SGA

22) parallel相关

parallel_execution_message_size = 4k
　　　 parallel_max_servers = cpus * parallel_threads_per_cpu * 4 * 5
parallel_threads_per_cpu = 2(default)

alter system set parallel_max_servers = 80 scope=spfile;


23)　　查看错误码含义

　　　 oerr ora 3499(error code)


24) R2板参数建议值
　　　 alter system set sga_max_size = 1536M scope=spfile;
alter system set sga_target = 1536M scope=spfile;

alter system set db_cache_size = 0 scope=spfile;
alter system set java_pool_size = 0 scope=spfile;
alter system set large_pool_size = 0 scope=spfile;
alter system set shared_pool_size = 0 scope=spfile;

alter system set pga_aggregate_target = 1659m scope=spfile;
alter system set shared_pool_reserved_size = 31037849 scope=spfile;

alter system set parallel_max_servers = 80 scope=spfile;
alter system set open_cursors = 800 scope=spfile;
alter system set processes = 1100 scope=spfile;
alter system set sessions=1215 scope=spfile;
alter system set transactions=1336 scope=spfile;


25) 查看所有的表空间 select * from dba_tablespaces

查看每个表空间总的尺寸 select tablespace_name, sum(bytes) / 1048576 from dba_data_files group by tablespace_name

查看每个表空间剩余空间 select TABLESPACE_NAME, sum(bytes)/1024/1024,sum(blocks) from dba_free_space group by tablespace_name

查询某个表空间使用哪个设备文件：
select * from dba_data_files where TABLESPACE_NAME = 'OME_SM_IDX_3_32K';

查询设备文件的表空间
select * from dba_data_files where FILE_NAME = '/dev/raw/raw24';

查看表空间的使用情况　（能显示使用率）
select 'RESULT='||df.tablespace_name "Tablespace",df.bytes/(1024*1024) "Total Size(MB)", sum(fs.bytes)/(1024*1024) "Free Size(MB)", round(sum(fs.bytes)*100/df.bytes) "% Free",round((df.bytes-sum(fs.bytes))*100/df.bytes) "% Used" from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df where fs.tablespace_name=df.tablespace_name group by df.tablespace_name, df.bytes;

查看表/索引所占的空间 select sum(bytes) / 1048676 from user_extents where segment_name='T_PUB_COMMONINFO‘
查看所有的表空间 select * from dba_tablespaces

查看当前用户下所有的对象 select * from user_objects


26) 数据导入导出
exp icd/icd@orauidb tables=tbilllog1 rows=y indexes=n file=tbilllog1.dmp
imp icd/icd@orauidb feedback=5000 full=y file=full.dmp

按用户备份 exp icd/icd@orauidb owner=icd feedback=5000 file=icd.dmp
全库备份 exp icd/icd@orauidb feedback=5000 full=y file=full.dmp
备份指定的表空间 exp icd/icd@orauidb tablespaces=service_core_dat file=service_core_dat.dmp
按条件导出 exp icd/icd@orauidb tables=tbilllog1 query=\”where logdate>to_date('2005-06-01','yyyy-mm-dd')\" file=tbilllog1.dmp
恢复整个备份文件 imp icd/icd@orauidb feedback=5000 full=y file=full.dmp


27)　　表空间
　　　 因db_block_size的限制，表空间每个文件的大小是有限制的。
　　　 一般　db_block_size=8192，即一个表空间文件最大32G。超过此值时，建表空间会失败。
　　　 ORA-01144: File size (125440000 blocks) exceeds maximum of 4194303 blocks

oracle中db_block_size=8192，此参数据在创建oracle实例时定义
在init.ora文件中，值为8192表示，当前实例中，创建表空间可使用的单个文件或裸设备最大为32G,且大小不可更改
计算方式（oracle最大允许4194303个block，） block个数＊大小/(1024*1024)
即4194303*8192/1024*1024 = 32.767G

解决方法：
CREATE BIGFILE TABLESPACE OME_SM_DATA_1_32K DATAFILE '/dev/raw/raw19' SIZE 980000M REUSE;

使用bigfile建表空间，但bigfile有一个限制，只能建一个表空间文件。


28) #删除一个用户下的所有表
select 'Drop table '||table_name||';' from all_tables where owner='ZXF';
执行上面的sql可以生成删除这个用户下所有表的sql，拷贝出来到sqlPlus下执行即可。


29) 查找失效的对象 select object_name from user_objects where status = 'INVALID‘
编译失效的对象 alter procedure p_my_proc compile

查看被锁的对象 select * from v$locked_object


30) 查找耗用资源高的SQL语句
select SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS, DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_DISK, BUFFER_GETS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_BUFF from v$sqlarea order by AVER_BUFF desc


31) 查看数据库使用的数据文件 select name, bytes from v$datafile

Data buffer 命中率检查
select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';

32) 如何測試SQL語句執行所用的時間
SQL>set timing on ;
SQL>select * from tablename;

33) 密码有效期
　　　查看密码有效期
　　　SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND　resource_name='PASSWORD_LIFE_TIME';

　　　修改为永久有效：
　　　ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


34)
35)


2. 语句使用

1) in 与 not in 的性能差异
父表存储父亲，子表存储孩子，然后通过pid和父表关联，查询需要的结果是找到尚未有孩子的父亲。

select * from parent where id not in (select pid　from childen) 花费 10 秒
select * from parent where id in ( select id from parent minus select pid　from childen ) 花费 1 秒内



三。　问题解决
1.　11.1.0.6版本安装完成后，/home/oracle/db/diag/rdbms/orcl/orcl/cdump 目录每分钟有三个以上的core文件产生
　　方法1　升级到11.1.0.7即可解决此问题。
　　方法2　设置：alter system set JAVA_JIT_ENABLED= FALSE scope = both; 重启