分类: Oracle
2010-12-03 10:42:52
[Q]在不知道用户密码的时候,怎么样跳转到另外一个用户执行操作后并不影响该用户?
[A]我们通过如下的方法,可以安全使用该用户,然后再跳转回来,在某些时候比较有用
需要Alter user权限或DBA权限:
SQL> select password from dba_users where username='SCOTT';
PASSWORD
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.
[Q]怎么样dump数据库内部结构,如上面显示的控制文件的结构
[A]常见的有
1、分析数据文件块,转储数据文件n的块m
alter system dump datafile n block m
2、分析日志文件
alter system dump logfile logfilename;
3、分析控制文件的内容
alter session set events 'immediate trace name CONTROLF level 10'
4、分析所有数据文件头
alter session set events 'immediate trace name FILE_HDRS level 10'
5、分析日志文件头
alter session set events 'immediate trace name REDOHDR level 10'
6、分析系统状态,最好每10分钟一次,做三次对比
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7、分析进程状态
alter session set events 'immediate trace name PROCESSSTATE level 10'
8、分析Library Cache的详细情况
alter session set events 'immediate trace name library_cache level 10'
[Q] 如何有效的删除一个大表(extent数很多的表)
[A] 一个有很多(100k)extent的表,如果只是简单地用drop table的话,会很大量消耗CPU(Oracle要对fet$、uet$数据字典进行操作),可能会用上几天的时间,较好的方法是分多次删除extent,以减轻这种消耗:
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
....
4. drop table big-table;
[Q]怎么快速查找锁与锁等待
[A]数据库的锁是比较耗费资源的,特别是发生锁等待的时候,我们必须找到发生等待的锁,有可能的话,杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话
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
如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表,而谁在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查询结果是一个树状结构,如果有子节点,则表示有等待发生。如果想知道锁用了哪个回滚段,还可以关联到V$rollname,其中xidusn就是回滚段的USN
[Q]怎么快速得到整个数据库的热备脚本
[A]可以写一段类似的脚本
SQL>set serveroutput on
begin
dbms_output.enable(10000);
for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop
dbms_output.put_line('--'||bk_ts.name);
dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;');
for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop
dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/');
end loop;
dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;');
end loop;
end;
/
[Q]控制文件包含哪些基本内容
[A]控制文件主要包含如下条目,可以通过dump控制文件内容看到
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
[Q]如果管理联机日志组与成员
[A]以下是常见操作,如果在OPA/RAC下注意线程号
增加一个日志文件组
Alter database add logfile [group n] '文件全名' size 10M;
在这个组上增加一个成员
Alter database add logfile member '文件全名' to group n;
在这个组上删除一个日志成员
Alter database drop logfile member '文件全名';
删除整个日志组
Alter database drop logfile group n;
[Q]联机日志损坏如何恢复
[A]1、如果是非当前日志而且归档,可以使用
Alter database clear logfile group n来创建一个新的日志文件
如果该日志还没有归档,则需要用
Alter database clear unarchived logfile group n
2、如果是当前日志损坏,一般不能clear,则可能意味着丢失数据
如果有备份,可以采用备份进行不完全恢复
如果没有备份,可能只能用_allow_resetlogs_corruption=true来进行强制恢复了,但是,这样的方法是不建议的,最好在有Oracle support的指导下进行。
[Q]怎么样创建RMAN恢复目录
[A]首先,创建一个数据库用户,一般都是RMAN,并给予recovery_catalog_owner角色权限
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
然后,用这个用户登录,创建恢复目录
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最后,你可以在恢复目录注册目标数据库了
rman catalog rman/rman target backdba/backdba
RMAN> register database;
[Q]怎么样快速下载Oracle补丁
[A]我们先获得下载服务器地址,在http页面上有
ftp://updates.oracle.com
然后用ftp登录,用户名与密码是metalink的用户名与密码
如我们知道了补丁号3095277 (9204的补丁集),则
ftp> cd 3095277
250 Changed directory OK.
ftp> ls
200 PORT command OK.
150 Opening data connection for file listing.
p3095277_9204_AIX64-5L.zip
p3095277_9204_AIX64.zip
……
p3095277_9204_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.
ftp>
知道了这个信息,我们用用flashget,网络蚂蚁就可以下载了。
添加如下连接
ftp://updates.oracle.com/3095277/p3...04_AIX64-5L.zip
或替换后面的部分为所需要的内容
注意,如果是flashget,网络蚂蚁请输入认证用户名及密码,就是你的metalink的用户名与密码!
[Q]dbms_output提示缓冲区不够,怎么增加
[A]dbms_output.enable(20000);
另外,如果dbms_output的信息不能显示,
需要设置
set serveroutput on
[Q]怎么知道那些表没有建立主键
[A]一般的情况下,表的主键是必要的,没有主键的表可以说是不符合设计规范的。
SELECT table_name
FROM User_tables t
WHERE NOT EXISTS
(SELECT table_name
FROM User_constraints c
WHERE constraint_type = 'P'
AND t.table_name=c.table_name)
其它相关数据字典解释
user_tables 表
user_tab_columns 表的列
user_constraints 约束
user_cons_columns 约束与列的关系
user_indexes 索引
user_ind_columns 索引与列的关系
[Q]如何创建约束的索引在别的表空间上
[A]1、先创建索引,再创建约束
2、利用如下语句创建
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;
[Q]怎么样获取对象的DDL语句
[A]第三方工具就不说了主要说一下9i以上版本的dbms_metadata
1、获得单个对象的DDL语句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLENAME','SCAME') from dual;
如果获取整个用户的脚本,可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然,如果是索引,则需要修改相关table到index
[Q]怎么查看数据库参数
[A]show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
或者select * from v$parameter
除了这部分参数,Oracle还有大量隐含参数,可以通过如下语句查看:
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME
[Q]Oracle有哪些常见关键字,不能被用于对象名
[A]以8i版本为例,一般保留关键字不能用做对象名
ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH
详细信息可以查看v$reserved_words视图
[Q]ORACLE的有那些数据类型
[A]常见的数据类型有
CHAR固定长度字符域,最大长度可达2000个字节
NCHAR多字节字符集的固定长度字符域,长度随字符集而定,最多为2000个字符或2000个字节
VARCHAR2可变长度字符域,最大长度可达4000个字符
NVARCHAR2多字节字符集的可变长度字符域,长度随字符集而定,最多为4000个字符或4000个字节
DATE用于存储全部日期的固定长度(7个字节)字符域,时间作为日期的一部分存储其中。除非
通过设置init.ora文件的NLS_DATE_FORMAT参数来取代日期格式,否则查询时,日期以
DD-MON-YY格式表示,如13-APR-99表示1999.4.13
NUMBER可变长度数值列,允许值为0、正数和负数。NUMBER值通常以4个字节或更少的字节存储,最多21字节
LONG可变长度字符域,最大长度可到2GB
RAW表示二进制数据的可变长度字符域,最长为2000个字节
LONGRAW表示二进制数据的可变长度字符域,最长为2GB
MLSLABEL只用于TrustedOracle,这个数据类型每行使用2至5个字节
BLOB二进制大对象,最大长度为4GB
CLOB字符大对象,最大长度为4GB
NCLOB多字节字符集的CLOB数据类型,最大长度为4GB
BFILE外部二进制文件,大小由操作系统决定
ROWID表示RowID的二进制数据,Oracle8RowID的数值为10个字节,在Oracle7中使用的限定
RowID格式为6个字节
UROWID用于数据寻址的二进制数据,最大长度为4000个字节
[Q]怎么样在Oracle中写操作系统文件,如写日志
[A]可以利用utl_file包,但是,在此之前,要注意设置好Utl_file_dir初始化参数
/**************************************************************************
parameter:textContext in varchar2 日志内容
desc: ?写日志,把内容记到服务器指定目录下
?必须配置Utl_file_dir初始化参数,并保证日志路径与Utl_file_dir路径一致或者是其中一个
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;
[Q]怎么快速获得用户下每个表或表分区的记录数
[A]可以分析该用户,然后查询user_tables字典,或者采用如下脚本即可
SET SERVEROUTPUT ON SIZE 20000
DECLARE
miCount INTEGER;
BEGIN
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'
INTO miCount;
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
END LOOP;
END IF;
END LOOP;
END;
[Q]怎么样把查询内容输出到文本
[A]用spool如
如sqlplus –s " / as sysdba" <
set heading off
set feedback off
spool temp.txt
select * from tab;
dbms_output.put_line(‘test’);
spool off
exit
EOF
[Q]怎么样从数据库中获得毫秒
[A]9i以上版本,有一个timestamp类型获得毫秒,如
SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,
to_char(current_timestamp) time2 from dual;
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
BEGIN
start_time := dbms_utility.get_time;
SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
--transaction start
INSERT INTO t1
SELECT * FROM All_Objects;
--other dml statement
COMMIT;
end_time := dbms_utility.get_time;
SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;
chinaunix网友2011-06-05 02:01:58
大连法律咨询在线 http://www.fabowang.com 大连律师在线咨询 http://www.fabowang.com 大连法律顾问网 http://www.fabowang.com 大连律师咨询 http://www.fabowang.com