oracle杂七杂八

smon也是Oracle的进程。


$ !ps -ef|grep sqlplus        查看Oracle进程（操作系统产生）
（！表示在sqlplus模式下查看操作系统的信息）


$ !ps -ef|grep 4629（4629由上一个命令得出的进程id(sqlplus对应字段)）

然后得出 4630（第二列字段），这个就是Oracle的spid(系统进程id),然后select * from v$process where spid=4630;   

然后 由该进程 产生一个Oracle数据库的会话session（有可能产生多个session）

 这个session和Linux系统Oracle进程相对应

v$process          与进程相关的动态性能视图

v$session          与会话相关的视图

它们之间有一个相同的字段     v$process的addr  等于  v$session的paddr   (会话进程地址)

看着美观：

SQL> set lines 150 pages 9999

SQL>/


一般用的时候查看(与进程相关的)
select addr,spid,username,terminal,program from v$process where spid=4630;



一般用的时候查看(与会话相关的)
select SID,SERIAL#，USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE,TERMINAL,PROGRAM from v$session

SID           会话id
SERIAL#       多个会话序列编号




创建一张表（测试用)

create table test as select * from dual;  (创建表test，表结构用的dual的表结构)



v$sql         动态sql视图   （只有当有sql语句执行的时候，里面才会有数据）
它和v$session中有相同的字段     v$session.sql_id =  v$sql.sql_id
                                v$session.hash_value =  v$sql.hash_value



几乎每天都用的sql语句 （主要是查看影响性能的sql语句）

select s.sid,s.serial#,s.status,s.USERNAME,q.sql_text from v$process p,v$session s,v$sql q
where p.addr=s.paddr and s.sql_id=q.sql_id and
s.sql_hash_value=q.hash_value and spid=4515;




5-13

sql>renam 表1 to 表2   // 把表1重命名为表2     （一般用在恢复数据时）
sql>set timing on      //返回sql操作所用的时间

truncate和truncate的删除原理不一样（删除速度不一样）
DDL语句不需要提交    如  truncate table 表名；(快)
DML语句需要提交      如  truncate from 表名；  （慢）


一般测试用的比较大的表  dba_objects;    如 create table test as select * from dba_objects;

spool  t.sql   // spool一个t.sql文件    缓冲到一个文件  默认是.list后缀
这中间是sql语句
spool off

可以vi t.sql 编辑需要的内容后
运行@t.sql  就可以执行这个脚本文件了  （用于批量的sql操作）






SQL>EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);  --去除SEGMENT属性（包括storage属性
）
SQL>EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);             --去除storage属性

------------------去除storage等多余参数 （导出建表语句的时候，去除后面的语句用）



SQL>set long 99999   //设置显示行的长度


SQL>select dbms_metadata.get_ddl(object_type,object_name,owner) from dba_objects where object_type='TABLE' and owner='SCOTT';
上面的意思是 获取scott的建表语句



拼接sql

select 'drop table '||owner||'.'||table_name||';' from dba_tables where owner='CHEESE';



恢复oracle的回收站的所有的表


1 使用sys as sysdba 进入到sqlplus的控制界面
   sqlplus / as sysdba
 
2 执行相关的命令，自动生成一个脚本文件

   spool  d:/a.sql
   select 'flashback table '||owner||'.'||original_name||' to before drop'|| ';' from dba_recyclebin;
   spool off

3 执行完这些命令以后会生成一个 a.sql的文件，对文件进行修改后保存，只保留我们需要执行的sql语句

4 执行 @d:/a.sql 进行批量操作


如何删除表中重复记录

DELETE FROM table_name a
WHERE rowid > ( SELECT min(rowid) FROM table_name b WHERE b.pk_column_1 = a.pk_column_1 and b.pk_column_2 = a.pk_column_2 );





commit的数据恢复

select count(*) from cheese.136pub as of timestamp sysdate-5/60/24;    --查询5分钟之前的t_136pub表里的数据。
sysdate-1/60/24   --1分钟之前的时间
sysdate-1/24   --1小时之前的时间
sysdate-1   --1天之前的时间




查询报错（Linux下用）
oerr ora 600


设置日期格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


为建立重复数据，让temp2自己复制本表数据
Insert into temp2 select * from temp2;
多运行几次上述命令，造成大量重复数据




一些设置 ：
set echo on：显示文件中的每条命令及其执行结果；
set echo off：不显示文件中的命令，只显示其执行结果。

term命令：
    当和SPOOL命令联合使用时，可以取消SQLPLUS输出，查询结果仅仅存在于假脱机文件中
    set term on：查询结果既显示于假脱机文件中，又在SQLPLUS中显示；
    set term off：查询结果仅仅显示于假脱机文件中。

set heading off——不显示每行的列名
set echo off——不重复显示所要执行的sql语句
set line 200——设置每行显示200字符
set pages 0——不进行分页显示
set feed off——不显示sql语句的运行结果（selected N rows）




闪回  ----   只能用于DML操作     （增、删、改） 但是truncate不能这样操作（truncate操作的是DDL语句）


Linux 中的 vi命令中 运用   :%s/tmp/136pub/g       //  %s代表替换的意思，就是把tmp替换成136pub,后面的g代表全局（整个文件中的tmp替换成136pub），

                           :%x                    //  %x代表保存的意思




SQL>list
SQL>save  a.sql               //保存SQL buffter里的信息到a.sql  

SQL>start a.sql或者@a.sql或者run a.sql      //执行这个a.sql



SQL>get n.sql                 //把外面的一个n.sql文件调入到SQL buffter里面

SQL>/                        //执行


关于count（）统计函数：

count(1)，其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段，而是表示一个固定值。
其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.
同理，count(2)，也可以，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。



col object_name for a30         //  'col 列名 for a数字' 表示设定列的显示格式，col column_name for a30的意思是将column_name 以20字符宽度进行显示，
                                     等同于column column_name format a20.     此命令只对当前设置的列名生效








oracle连接“监听程序当前无法识别连接描述符中请求的服务”解决方案：（listener.ora中少了中间一段代码）


安装好了之后可以通过这个路径找到一个文本文件：
oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora 还一个在同路径下的tnsnames.ora文件，这两个文本中的一些信息要一样的。
  你看看我的这两个文本文件的信息：

listener.ora
# listener.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
     (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (SID_NAME = orcl)
    )
 
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

tnsnames.ora
# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

提问者评价

    灰常感谢！！！！原来是少了第一个的中间那段。







oracle 创建表空间和用户


create temporary tablespace escs_temp
tempfile '/opt/oracle/oradata/orcl/escs_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;




create tablespace escs
logging
datafile '/opt/oracle/oradata/orcl/escs.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;




create user escs identified by escs123
default tablespace escs
temporary tablespace escs_temp;



grant dba,connect,resource to escs;




Linux 下修改Oracle 的字符集：WE8ISO8859P1 修改为 ZHS16GBK   

首先 sqlplus登录后，关闭oracle数据库
(先执行，后导库)
//第一种方法
1.shutdown immediate ;    
2.startup mount ;    
3.alter  system enable restricted session ;     
4.alter  system  set  JOB_QUEUE_PROCESSES=0;     
5.alter  system  set  AQ_TM_PROCESSES=0;     
6.alter   database   open  ;     

7.alter database character set INTERNAL_USE ZHS16GBK;
8.shutdown immediate ;    
9.startup ;


//第二种方法

1.shutdown immediate ;    
2.startup restrict   ;
3.alter  system enable restricted session ;     
4.alter  system  set  JOB_QUEUE_PROCESSES=0;     
5.alter  system  set  AQ_TM_PROCESSES=0;     
6.alter   database   open  ;     
7. alter database character set INTERNAL_USE ZHS16GBK;     
8.shutdown immediate ;    
9.startup ;



CKPT进程触发DBWR进程，而CKPT的触发时间由参数FAST START MTTR TARGET而定。
确切的说commit操作并不会触发DBWR进程（纠正了大家的误导），而是由CKPT进程触发。


$ipcs -m         //查看Oracle的共享内存



查看Oracle段名字，段类型，表空间名字，块大小，区大小，段大小
SQL> select segment_name,segment_type,tablespace_name,blocks,extents,bytes/1024/
1024 from user_segments where segment_name='T';




Oracle数值类型  number

number（p,s） --------------p是精度（有效位），s代表刻度(s【通常】是指小数点后保留几位)
                            如果s是正数，那么我们需要保留小数点后面的位；如果s是负数，那么可以四舍五入到个位、十位、百位。
int是number(*,s)演变来的，其中s=0，说明不保留小数点后面的值
说明不保留小数点后面的值，那么你转换成建表语句后，会发现字段类型不是INT,而是NUMBER(*,0)，P可变的值，可以在0-38的范围内。

例子：数字15.6，如果这个数字存储到number(*,0)类型的字段中，存储后的结果变成什么？
      答案是：16            因为四舍五入的位置在小数点的位置，小数点后一位大于等于5，则进位。
                            
     如果数据是15.125，存储到number(n,-2),则是15.13
     如果数据值是12345.678，存储到number(n,-2)，则是12300

假设我们类型是NUMBER（3,2)，那么我们输入值15.123，最终结果是什么？
  先用S进行转换，得到15.12，对吧（3被舍弃），而15.12有4个有效位，有效位=4
而P=3，P溢出了，所以你这个值15.123是无法插入到NUMBER（3,2)字段的。

有效值怎么去判断，如果有0就比较复杂，比如经过S运算后，得到1234.3210，大家说，这个值几个有效数？

7个，为什么不是8个呢？因为最后这个0是没有用的，无效的。

--------为什么银行卡金额不能设置成浮点呢？因为浮点和NUMBER类型区别之一是精度，而银行卡的金额对精度要求很高，
银行卡的金额需要很长的有效位，浮点计算不精确
-------由精度引申出两种重要的数值类型：number和float类型，早期的版本，浮点类型用float来表示，到了oracle 10g新引进了两种数值类型binary_float 和binary_double，
大家要记一下，这两种都是浮点类型，非常有用。（一个是单精度浮点类型，一个是双精度的浮点类型）

binary_double精度很高，但性能不比binary_float差，我们说精度越低,存储的空间越小,性能越高，浮点精度低，想来应该更节约空间？但是空间节约对于性能的提高是非常有限的。
浮点类型还有更重要的因素来影响性能，浮点运算机制跟number的运算机制是完全不同的，浮点的性能要高很多。
那我们要从浮点的内部机制来说起，浮点数是一个有理数子集中一个数的数字表示，通常用于在计算机上近似一个任意的实数，浮点是依赖硬件设备的，大家说,用硬件来计算数据快,还是软件来计算快？
当然是硬件，因为软件其实最终也是靠硬件来计算的，等于多了一道，最终都需要CPU来计算。
那CPU来计算数值快还是ORACLE软件来计算数值快？必须是CPU速度快，CPU是硬件，是最快的计算设备，由于允许在硬件（CPU、芯片）上执行运算，而不是在Oracle 子例程中运算，所以在多种不同类型的应用中都很有用


总结下：
a: 浮点是硬件来计算的
b: NUMBER类型，是由软件来计算的

他们两者的性能差至少在1个数量级。

有同学问，我们的DBA没有注意这些细节，如果我们的业务表已经设置成NUMBER类型了，我又不想把表结构的NUMBER类型转换成浮点类型，又想提高计算效率，该怎么办？
我们自己强制在语句级转换类型，而不在表结构级上转换，我们是不是可以在SELECT语句中将number类型的字段强制转换成浮点类型-----使用cast函数，它是一个类型转换函数，他是一个万能的转换函数
什么类型都可以转换，select sum(ln(cast( num_type as binary_double ) )) from number_tuning;应该能在0.0几秒完成。




pls_integer是10g新增的类型，是一种硬件计算的整型。PLS_INTEGER和BINARY_INTEGER唯一区别是在计算当中发生溢出时，BINARY_INTEGER型的变量会被自动指派给一个NUMBER型而不会出错，PLS_INTEGER型的变量将会发生错误。

大部分直接用pls_integer都能满足业务需求，别用int。






Oracle有关子查询：
在FROM后面的两个子SELECT，是子查询，因为结果集是中间结果，所以这种视图称为INLINE VIEW，凡是SELECT被括号括起来的，就是子查询。而最外层的SELECT，我们叫父查询，或者叫主查询
我先介绍下子查询概念:子查询按类型分：
a 标准子查询----------  标准子查询是没有关联别SELECT语句的子查询    (标量子查询每个SELECT语句都是相对独立的), 只要去看下子查询的WHERE语句的连接条件，就可以判断是不是关联还是独立的.

b 关联子查询----------关联子查询是子查询跟外部查询之间有关联条件
c inline view----------inline view称为内联视图，子查询跟在from后面  (凡是在from后面的子查询，并生成中间结果集的，称为内联视图)