ORACLE sql语句笔记

windows上存在32bit的限制，如AIX、HP UNIX 等有明确的64BIT OS and ORACLE的版本，32bit oracle可以装在64bit os 上，64 bit oracle不能装在32 bit OS上

oracle是64bit or 32 bit,32bit 通常 SGA有 1.7G 的限制(某些OS的处理或者WINDOWS上有特定设定可以支持到2G以上甚至达到3.7G

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

为这个表空间增加一个数据文件:
alter tablespace 表空间名 add datafile '/u1/oradata/userdata_002.ora' size 50m;

重新调整数据文件的大小:
alter database datafile '/u1/oradata/userdata_001.ora' resize 50M;
表：
　　select * from cat;
　　select * from tab;
　　select table_name from user_tables;
视图：
　　select text from user_views where view_name=upper('&view_name');
索引：
　　select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
触发器：
　　select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照：
　　select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
同义词：
　　select * from syn;
序列：
　　select * from seq;
数据库链路：
　　select * from user_db_links;
约束限制：
　　select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints;
本用户读取其他用户对象的权限：
　　select * from user_tab_privs;
本用户所拥有的系统权限：
　　select * from user_sys_privs;
用户：
　　select * from all_users order by user_id;
表空间剩余自由空间情况：
　　select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name；
数据字典：
　　select table_name from dict order by table_name;
锁及资源信息：
　　select * from v$lock;不包括DDL锁
数据库字符集：
　　select name,value$ from props$ where name='NLS_CHARACTERSET';
inin.ora参数：
　　select name,value from v$parameter order by name;
SQL共享池：
　　select sql_text from v$sqlarea;
数据库：
　　select * from v$database
控制文件：
　　select * from V$controlfile;
重做日志文件信息：
　　select * from V$logfile;
来自控制文件中的日志文件信息：
　　select * from V$log;
来自控制文件中的数据文件信息：
　　select * from V$datafile;
NLS参数当前值：
　　select * from V$nls_parameters;
ORACLE版本信息：
　　select * from v$version;
描述后台进程：
　　select * from v$bgprocess;
查看版本信息：
　　select * from product_component_version;

看文件名，读次数，写次数，读时间，写时间
select  df.name ,fs.phyrds ,fs.phywrts ,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) ,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) from  v$datafile df,v$filestat fs where df.file#=fs.file# order by df.name;

查询锁表用户
SELECT OBJECT_ID,SESSION_ID,SERIAL#,ORACLE_USENAME,OS_USER_NAME,S_PROCESS FROM V$LOCKED_OBJECT 1,V$SESSION S WHERE 1.SESSION_ID=S.SID;
------------------------------------------------------------------------
然后在目录/oracle_backup/bin/下写一个类似下面的shell文件scott_select.sh
   
 su - oracle -c "sqlplus -s scott/tiger@servie_name"<<EOF
 spool /oracle_backup/log/scott_select.txt;
 @/oracle_backup/bin/scott_select.sql;
 spool off;
 exit;
  
  说明：
      spool语句把scott_select.sql语句的执行结果输出到/oracle_backup/log/scott_select.txt文件
      @符号是执行/oracle_backup/bin/scott_select.sql文件
      在要执行的*.sql文件里可以存放DML、DDL等多条SQL语句。
-------------------------------------------------------------------------
如何查看数据文件所在的路径？
SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
-------------------------------------------------------------------------
查看数据文件是否自动扩展
SQL> col FILE_NAME format a40
SQL> col TABLESPACE_NAME format a20
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id;
-------------------------------------------------------------------------
查出前台正在发出的sql语句
select user_name,sql_text from v$open_cursor where sid in (select sid from(select sid,serial#,username,program from v$session where status='ACTIVE'));
***************
子查询
select sid,serial#,username,program
　　 from v$session
　　 where status='ACTIVE'
查出的是不活动的session的sid。
-------------------------------------------------------------------------
（1）关闭数据库

dbshut 的执行文件位置在$ORACLE_HOME/bin，利用这个执行文件可以把数据库关闭。

su -oracle -c "dbshut"

(2）进行文件的复制，请把datafile、control file和redo log file

复制到另一个目录。

cp -r 数据库文件 目录

（3）当复制完成之后，把数据库打开

su -oracle -c "dbstart"

联机备份方式

-------------------------------------------------------------------------
[09.05]：如何捕捉运行很久的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
/

[09.04]：INDEX NOLOGGING的误解

有朋友提到说ARCHIVELOG太多了，想把索引改成NOLOGGING的，意思是少产生些LOG，其实这是一种误解，索引的LOGGING是针对建索引来说的，对于一个正常使用的索引LOGGING并没有什么意义，也不会产生LOG。

[09.03]：DBMS_OUTPUT缓存溢出？

在使用DBMS_OUTPUT输出时，有时会遇到这样的错误：

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

我们可以通过dbms_output.enable(20000)设置一个更大的缓存。

[09.02]：如何让SQLLDR跳过某列？

使用关键字FILLER，如：

Load data
INFILE 'file_name.txt'
BADFILE 'file_name.bad'
DISCARDFILE 'file_name.dsc'
DISCARDMAX 2000

truncate into TABLE table_name
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(column_a DATATYPE,
ANY_NAME FILLER DATATYPE,
column_c DATATYPE )

[09.01]：RMAN-20242诊断过程？

RMAN-20242: Specification does not Match any Archivelog in the Recovery Catalog的处理

有时候我们备份时，会出现这样错误，如果确认archivelog都存在，没有其它问题，则可能是因为我们写的格式不对，如下面的例子：

初始备份脚本：

RMAN> run {
2> allocate channel c1 type disk;
3> backup
4> archivelog like 'E:/oracle/oradata/prod/archive%';
5> }

报错信息：

RMAN-03002: failure during compilation of command
RMAN-03013: command type: backup
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specificati
on does not match any archivelog in the recovery catalog

诊断过程：

SQL> select * from v$archived_log;

RECID STAMP
---------- ----------
NAME
--------------------------------------------------------------------------------
THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS FIRST_CHANGE# FIRST_TIM
---------- ---------- ----------------- --------- ------------- ---------
NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREA REGI STA ARC DEL COMPLETIO
------------ --------- ---------- ---------- ---- ---- --- --- --- ---------
1 419970034
E:/ORACLE/ORADATA/PROD/ARCHIVE/PRODT001S01524.ARC
1 1524 1 21-JAN-01 599954 25-JAN-01
600067 25-JAN-01 103 512 FGRD FGRD NO YES YES 25-JAN-01

我们发现文件名是大写的，现在将脚本改成：

RMAN> run {
2> allocate channel c1 type disk;
3> backup
4> archivelog like 'E:/ORACLE/ORADATA/PROD/ARCHIVE/PROD%'
5> delete input;
6> release channel c1;
7> }

就可以顺利完成了。

[08.29]：RMAN-06089故障处理？

有时候我们在做archive log备份的时候可能会遇到下面的错误：

RMAN-03002: failure during compilation of command
RMAN-03013: command type: backup
RMAN-06089: archived log /arch_log1/arch_1_157351.arc not found or out of sync with catalog

从字面上看，是说这个archive文件没有被发现，但事实上，我们的archive文件确实存在。

一般来说，出现这样错误的原因是因为archive log被手工移动或修改过，此时RMAN就不认识它了，而不是它真正的不存在。比如，我前面写的一篇文章，说如何处理归档日志目录满了的问题，我们需要移走没有备份的归档日志，此后，当我们再将这些归档日志复制回来，继续备份时，通常就会出现这样的错误。

其实很简单，RMAN不就是不认它们了嘛，我们可以在RMAN中手工执行“Change Archivelog All Crosscheck;” 命令，明确通知RMAN不要进行检查和确认，这样就OK了。

[08.28]：如何快速下载oracle补丁？

我们首先在Metalink中查到相应补丁的信息，一般在http下载页面可以发现FTP的下载地址：

ftp://updates.oracle.com

然后我们可以用命令方式先进入站点看看，你会发现跟目录下什么都没有：

ftp> ls
200 PORT command OK.
150 Opening data connection for file listing.
226-Listing complete. Data connection has been closed.
226-It is the policy of this server to deny all
226-requests to list from the root directory.
226-Only directories with names which match
226 bug numbers exist and there are too many to be listed.
ftp> cd 3095277   --这是9204的补丁号
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_HP64.zip
p3095277_9204_HPUX-IA64.zip
p3095277_9204_LINUX.zip
p3095277_9204_SOLARIS.zip
p3095277_9204_SOLARIS64.zip
p3095277_9204_TRU64.zip
p3095277_9204_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 237 bytes received in 0.02Seconds 11.85Kbytes/sec.
ftp>

好了，现在我们知道了具体补丁的地址，如：p3095277_9204_AIX64-5L.zip的ftp下载地址为：

ftp://updates.oracle.com/3095277/p3095244_9204_AIX64-5L.zip

知道了这个信息，我们就可以用flashget或网络蚂蚁进行下载了。需要输入的认证用户名和密码就是你metalink的用户名与密码。

[08.27]：如何生成带日期的文件名？

在UNIX上，使用`date +%y%m%d` (`这个是键盘上~所在的那个键)，例：

touch exp_table_name_`date +%y%m%d`.dmp

Windows上，使用%date:~12%，其中12是取日期，你可以改成其它你需要的数字。