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; 重启
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; 重启