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) 启动侦听 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; 重启 |
|