个人整理sql语句（每天更新中……）

------------------------------------------------------

非常有用的sql 语句整理

-----------------------------------------------------

1、select tablespace_name from dba_tablespaces;  -------查询表空间名

2、SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.TABLESPACE_NAME) from DBA_TABLESPACES TS;--------查询建表空间 的sql语句

3、show parameter spfile;            ----------查询参数文件的目录及名称

4、alter system set parameter_name=values scope=both;----------------修改系统设置的参数值

 首先通过这条语句 ：select 'alter system set' || name from V$parameter; ----------查询出具体要修改的参数名称

5、select * from gv$instance;--------查询出整个服务器上有几个实例 （即有几个RAC节点）

6、show parameter instance_name;----------查看连接数据库的实例名

7、 select file_name,online_status from dba_data_files;----------查看数据文件是否是online状态
8、ps -ef | grep ora_---------查看oracle 数据实例后台进程是否都启动
9、sql>set timing on      ------------打开sql语句执行的时间

10、show parameter dump_dest;---------查看告警日志目录，告警日志是此目录下的alert_orcl.log，告警日志记录系统的日志，比如切换日志信息，修改系统参数等
11、rman>delete archivelog all completed before 'sysdate-7';>---------删除7天前的归档日志

       rman>crosscheck archivelog all;----------检查控制文件和实际物理文件的差别

       rman>delete expired archivelog all;--------------同步控制文件的信息和实际物理文件的信息

12、RMAN>report obsolete;-------------查看过期备份

         RMAN>delete obsolete;-------------删除过期备份

13、sql>select table_name from dict where table_name like '%DBA_DATA%';  --------------查找数据字典中包含DBA_DATA的表

14、sql>alter system set  control_file='/u01/oracle/oradata/orcl/control01.ctl' scope=both;--------修改控制文件

15、如何建立一个备份控制文件？

首先让数据库运行在archivelog模式下，然后open数据库：

SQL>alter database backup controlfile to '/mnt/backup_ora/controlfile' ;或者是：alter database backup controlfile to trace;
搞定

16、 如何判断数据库的时区?

　　解答：SELECT DBTIMEZONE FROM DUAL;

17、如何进行强制LOG SWITCH?　　解答：ALTER SYSTEM SWITCH LOGFILE;

18、查看表空间的大小（单位换算成M）：SQL> select file_name ,bytes/(1024*1024) as sizeM from dba_data_files;

19、查看表空间的剩余大小： sql>select tablespace_name,bytes/(1024*1024) as sizeM from dba_free_space;
20、查看数据库是否处于归档模式：sql>select name,log_mode,force_logging from v$database;或者是：sql>archive log list;     ---不仅仅可以查看是否处于归档模式，还可以查看归档日志存放目录；具体改变归档日志目录的sql>alter system set log_archive_dest_1='location=/archivelog' scope=spfile;

如log_mode值为ARCHIVELOG，force_logging值为yes，则表明数据库目前已运行在归档模式下，并已启用强制归档。如运行在非归档模式下，则需将数据库切换为归档模式，并启用强制归档。操作方法：

a.创建归档日志目录：在默认情况下，如不自定义归档日志目录，则归档日志保存在FRA(Flash Recovery Area)中，默认的FRA大小为2G。故日常使用中，推荐在操作系统中创建自定义的归档日志目录，并修改属主及组信息为oracle:dba：

mkdir–p /usr/oracle/oradata/arch

chown –R oracle.dba /usr/oracle/oradata/arch

b.修改数据库相关参数，指定归档日志存放路径，并将数据库切换至归档模式运行，启用强制归档，上述操作在sqlplus下进行：

alter system set log_archive_dest_1=’location=/usr/oracle/oradata/arch’;

shutdown immediate;

startup mount;

alter database archivelog; --切换到归档模式

alter database force logging; --强制写日志，保证standby一致

alter database open;

变更完成后，再次在sqlplus下执行：

select name,log_mode,force_logging from v$database;

用于查看数据库是否已经运行在归档模式下并强制归档。确认已经启用归档后，在sqlplus下执行如下语句：

alter system switch logfile; //日志强制切换

执行成功后，切换至自定义的归档日志目录，查看归档日志是否已经生成。

21、alter database flashback on;         ------------打开闪回

22、alter database force logging;         -------------打开强制日志，根据日志文件同步数据

23、Explain plan for select 语句；Select * from table(dbms_xplan.display);         -------------直接解析SQL语句

具体格式：

explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ] FOR < sql_statement >
24、与SQL调优有关的几个数据字典
1v$session
2V$sqltext
3V$sql_plan
4V$sql_plan_statistics
5V$sql_plan_statistics_all
6User_table_histograms
7User_tab_statistics
8User_tab_col_statistics
9User_ind_statistics

25、如何获取语句的执行计划？
1 直接解析SQL语句.
Explain plan for XXX；
Select * from table(dbms_xplan.display);

2 Set autotrace on /off；-----每写一条sql语句都会显示执行计划；
3 从视图v$sql_plan中获取。
4 pl/sql工具

25、alter system switch logfile;        ----------手动切换日志

        alter database drop logfile group 1;     ---------删除日志组

        alter system checkpoint;            ----------改变系统检查点

        alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02a.log';     -----删除日志成员

删除日志组成员有如下限制:
    current不能删,active可以删
    最后一个有效成员不能删  
    在删除联机重做日志成员时并未删除操作系统文件
26、select pool,sum(bytes)
FROM v$sgastat where pool is not null
group by pool
UNION ALL
select name,bytes
FROM v$sgastat where pool is null； ---------通过v$sgastat计算SGA中每个组件分配内存大小

27、 create public database link ts connect to scott identified by seker using 'ORCL'; --------------数据库连接 DB_LIKE，访问远程数据库资源的一种方法，    确保网络畅通

          select owner,username,host,created from dba_db_links;      -----------查看创建的dblink
          drop public database link ts;         -------删除dblink
28、drop tablespace  test   INCLUDING CONTENTS AND DATAFILES; ------------------删除表空间以及表空间里面的数据文件
29、env | grep ORA           --------查看oracle的环境变量

30、scp /u01/oracle/oradata/*.dbf oracle@172.2.95.200:/u01/oracle/oradata/  ----------从一台主机拷贝文件到另外一台主机

31、SQL> startup mount pfile='/u01/oracle/spfileyaofang.ora'  --------------指定参数文件启动数据库

32、getconf WORD_BIT 或者 uname-a           ----查看你的linux 系统是多少位的

33、 lsb_release -a ---------查看你的linux 系统的版本

34、show parameter background_dump_dest ---查看告警日志目录

tail -f alert_sid.log -------查看告警日志一直变化的信息

35、备份控制文件：

（1）热备：

alter database backup controlfile to trace; ---------- 备份控制文件

alter database backup controlfile to '/u01/oracle/control.bak'; -----必须在归档模式下

alter database backup controlfile to trace as '/u01/oracle/control.txt'; ----生成重建控制文件的脚本

rman 备份：

rman>backup current controlfile;

rman>backup database include current controlfile;

restore controlfile ;

recover database;

(2)rman>configure controlfile autobackup on; ---------打开rman自动备份控制文件

RMAN>restore controlfile from autobackup; -------从自动备份恢复控制文件

rman>recover database;

36、启动自动归档：archive log start;

37、rman 全库压缩备份：

RMAN> backup as compressed backupset full database include current controlfile plus archivelog delete all input;

rman 0级（全库）备份：

rman>backup incremental level 0 database include current controlfile plus archivelog;

rman 1 级备份（增量备份）：

rman>backup incremental level 1 database ;

38、rman调整备份介质保留7天：

rman>configure retention policy to recovery window of 7 days;

配置控制文件自动备份目录和格式：

RMAN>configure controlfile autobackup format for device type disk to '/u01/app/oracle/backup/control/cf_%F';

RMAN>configure controlfile autobackup on; 启动控制文件自动备份

创建RMAN默认备份介质保存目录:

RMAN>configure channel device type disk format'/u02/app/oracle/backup/DB_%U';

显示配置后RMAN环境变量 :RMAN> show all;

39、配置sqlnet.ora 可以控制采用何种方式连接数据库：sqlnet.ora 的存放目录在：$ORACLE_HOME/network/admin/  

在最后加上一句：SQLNET.AUTHENTICATION.SERVICES=NONE | ALL 

参数none : 表示不能通过操作系统认证来连接数据库，必须使用密码文件  eg:conn /as sysdba    这样是连接不上的。

参数all: 表示本地不能通过远程方式来连接数据库  eg:conn system/oracle@orcl  肯定是连接失败的。

40、增加日志组：sql>alter database add logfile group 4 '/oradata/oracle/oradata/oracle_sid/redo04.log' size 100M；

        增加日志组成员： sql>alter database add logfile member '/oraback/logbackup/redo01_b.log' to group 1; ------日志成员不能设置大小

       删除日志组：ALTER DATABASE DROP LOGFILE GROUP N； ----组内成员也将被删除

 41、可以从内存中创建spfile(11g 新出来的指令，之前的版本没有) ：sql>create spfile from memory;

42、删除某一参数（置为默认值）：sql>alter system reset parameter_name scope=spfile sid='*' | 'sid'; 

        oracle 中隐藏的参数：x$ksppinm 和 x$ksppcv 这两个视图中可以查询

43、转储控制文件的内容：

第一种方法：

sql>alter session set events 'immediate trace name CONTROLF level 10'; ------level 10 表示转储全部内容

  转储到哪了？ 可以使用： sql>show parameter user_dump_dest; 查找出最新的文件打开

第二种方法： sql>oradebug setmypid; 

                        sql>oradebug dump controlf 3;

44、减少控制文件的个数： alter system set control_files='/u01/oracle/oradata/orcl/control01.ctl' scope=spfile; ------就剩下control01.ctl 一个控制文件

         增加控制文件的个数：alter system set control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/oradata/orcl/control02.ctl','/u01/oracle/oradata/orcl/control03.ctl' scope=spfile;

 此时的控制文件的个数是增加了，但是版本号不一致会导致数据库无法启动到mount ，我们可以通过拷贝高版本的控制文件到你新增加的几个控制文件，具体做法(确保control01 的版本号是最高的): 

①cp /u01/oracle/oradata/orcl/control01.ctl  /u01/oracle/oradata/orcl/control02.ctl 

②cp /u01/oracle/oradata/orcl/control01.ctl  /u01/oracle/oradata/orcl/control03.ctl  此时就可以正常启动数据库了！

45、SQL> select property_name,property_value from database_properties where property_name like '%TBS%';   --------查看表空间是大还是小文件
------------------------------------------------------------------------------------------------

PROPERTY_NAME                                    PROPERTY_VALUE

DEFAULT_TBS_TYPE                               SMALLFILE