ORACLE第一章：

--查看VGA信息：

 show sga;

 select * from v$sgastat;

--可以通过以下几个动态性能视图查看信息：

 V$sysstat                系统统计信息
 V$sesstat                用户会话统计信息 
 V$pgastat               显示内存使用统计信息
 V$sql_workarea          SQL游标所用工作区的信息
 V$ sql_workarea_active    当前系统工作区的信息
data_file:dba_data_files;
control_file:从init.ora里面可以看到，D:ora92adminora92pfileinit.ora.
redo_log:v$logfile;
实际上D:ora92oradataora92里面放着很多东西。
select * from v$database;里面可以查询archivelog的信息。
段：dba_segments
区：dba_extents

--在V$process动态性能视图中可以查询到每个Oracle进程的PGA分配的内存和已使用的内存情况，
--其中PGA_used_mem表示已使用的，pag_alloc_mem表示已分配的，pga_max_men表示PGA的最大值。

 SQL> select pid,pga_used_mem,pga_alloc_mem,pga_max_mem from v$process;

--查看后台进程：

SELECT * FROM v$bgprocess WHERE paddr <> '00';

--查看所有的表空间；

 SQL> select tablespace_name from dba_data_files  order by tablespace_name;

--查看表空间的名字及大小:
 SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size    
 from dba_tablespaces t, dba_data_files d    where t.tablespace_name = d.tablespace_name   
 group by t.tablespace_name;

--创建表空间的语法是：
 CREATE TABLESPACE tablespacename
 DATAFILE 'filename' [SIZE integer [K|M]] 
 [AUTOEXTEND [OFF|ON]];
--创建有多个数据文件的表空间：
 SQL> create tablespace  SALES
  datafile  'd:salesSALES_DATA01.dbf' size 10m autoextend on next 10m maxsize 100m,
  'd:salesSALES_DATA02.dbf' size 10m  autoextend   on next 10m maxsize
  unlimited,'d:salesSALES_DATA03.dbf' size 10m;

--查看表空间的大小；

 SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

--查看表空间中数据文件存放的路径：

 SQL> SELECT    TABLESPACE_NAME,  BYTES/1024/1024   FILE_SIZE_MB,  FILE_NAME    FROM DBA_DATA_FILES;


insert into date_test values(interval '12' month,'adjf');测试interval 数据类型；


删除表空间：
SQL> drop tablespace worktbs including contents;
表空间已丢弃。

再次查询表空间会发现已经没有worktbs表空间了。
如果我们再次删除表空间会出现什么后果？
删除一个不存在的表空间：
SQL> drop tablespace worktbs including contents;
drop tablespace worktbs including contents
*
ERROR 位于第 1 行:
ORA-00959: 表空间'WORKTBS'不存在
o 为表空间增加数据文件
SQL> alter tablespace sales add datafile 'd:oracleoradata est esttablespace
sales_data04.dbf' size 10m autoextend on next 10m maxsize 100m,
'c:oracleoradata est esttablespacesales_data05.dbf' size 10m autoextend on next 10m maxsize unlimited, 'c:oracleoradata est esttablespacesales_data06.dbf' size 10m;

表空间已更改。
o 修改数据文件的大小
o 在数据文件中的可用空间未使用完时，允许对数据文件进行压缩，当压缩空间大小超过未使用空间大小时，将会产生错误。
SQL>  alter database datafile 'c:oracleoradata est esttablespaceSALES_DATA04.dbf' resize 30m;

数据库已更改。
o 关闭表空间数据文件的自动扩展属性
o alter database
o     datafile  'c:SALES_DATA04.dbf' ,
o                    'c:SALES_DATA05.dbf',
o                     'c:SALES_DATA06.dbf'
o                      autoextend  off;
o 打开表空间数据文件的自动扩展属性
o alter database
o     datafile  'c:SALES_DATA04.dbf' ,
o                    'c:SALES_DATA05.dbf',
o                     'c:SALES_DATA06.dbf'
o                      autoextend  on;

o 修改表空间属性(离线)
o alter tablespace   sales  offline;  
o 修改表空间属性(在线)
o alter tablespace   sales  online;
o 修改表空间属性(只读)
o alter tablespace   sales  read  only;
o 修改表空间属性(读写)
o alter tablespace   sales  read   write;
o 如下系统表空间不得设置为  offline   或者  read only
o system  ， temp，undo  ，undotbs 

o 移动表空间的数据文件
o 1.  使表空间脱机（系统表空间不得脱机，故不得移动）
o 2.  修改数据文件的名称
o 3.  执行  alter  tablespace  rename  datafile 命令
o 4.  使表空间联机
o 第一步：alter  tablespace    sales  offline；  
o 第二步：物理移动数据文件到目的地(可以是表空间的
o                部分数据文件,可以修改数据文件的名称)
o 第三步:    逻辑移动，修改控制文件的内容

o alter  tablespace sales  rename 
o datafile 'c:sales01.dbf' to 'd:sales02.dbf'
o --可以有多个数据文件，但是源文件
o 要在to的左边，目的文件要在to 右边,文件名称之间逗号分割.
o 第四步: 将表空间联机
o alter   tablespace  sales   online;
o 第五步：查询dba_data_files确认

o 创建用户，指定默认表空间，磁盘配额
o  create  user rose identified by rose   default
o tablespace sales quota 10m on sales;
o 给用户授权
o grant   connect,resource,dba to rose;
o 用户登录
o connect   rose/rose
o 创建表
o create table  emp(eid  number) 
o 该表默认放在表空间  sales   中
o 查询用户的存储限额dba_ts_quotas 
o 删除表空间(如果该表空间为空)
o drop tablespace sales  ;
o 删除表空间的同时删除数据文件(不为空)
o drop tablespace sales  including contents  and  datafiles;
o 创建表的同时指定该表的存储位置
o  create table mytab(tid  int ) tablespace sales;

o 删除表空间(如果该表空间为空)
o drop tablespace sales  ;
o 删除表空间的同时删除数据文件(不为空)
o drop tablespace sales  including contents  and  datafiles;
o 创建表的同时指定该表的存储位置
o  create table mytab(tid  int ) tablespace sales;


o 查看当前用户每个表占用空间的大小：
o select  segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name 
o 查看每个表空间占用空间的大小：
o select tablespace_name,sum(bytes)/1024/1024 from dba_segments group by tablespace_name 


只有用合法的用户帐号才能访问Oracle数据库
Oracle 有几个默认的数据库用户  
Scott/tiger

创建一个名称为 martin 的用户，其密码为 martinpwd
CREATE USER MARTIN IDENTIFIED BY martinpwd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT 命令可用于为用户分配权限或角色；
CONNECT角色允许用户连接至数据库，并创建数据库对象。
GRANT CONNECT TO MARTIN; 

RESOURCE角色允许用户使用数据库中的存储空间。
GRANT RESOURCE TO MARTIN; 

此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中。
GRANT CREATE SEQUENCE TO MARTIN; 

GRANT CREATE SESSION TO MARTIN;

GRANT CREATE TABLE TO MARTIN;

GRANT CREATE VIEW TO MARTIN;

GRANT CREATE SEQUENCE TO MARTIN;

 

授予用户 MARTIN 操作emp表对象的权限 ：
允许用户查询 TEST 表的记录
GRANT SELECT ON EMP TO MARTIN; 

允许用户更新 TEST 表中的记录
GRANT UPDATE ON EMP TO MARTIN; 

允许用户插入、删除、更新和查询 TEST 表中的记录
GRANT ALL ON EMP TO MARTIN; 


ALTER USER 命令可用于更改口令：
修改 MARTIN 用户的密码：
ALTER USER MARTIN IDENTIFIED BY martinpass;
DROP USER 命令用于删除用户： 
删除 MARTIN 用户模式：
DROP USER MARTIN CASCADE;


alter session set nls_date_format='yyyy-mm-dd';

create table customer(customer_id int not null,constraint pk_id primary key(customer_id));
create table salesinfo(salesid varchar2(10),customer_id int not null,primary key(salesid,customer_id),
foreign key (customer_id) references customer(customer_id) on delete cascade);

insert into date_test values(to_date('09-09-09','dd-mm-yy'),'akldjf');
alter table date_test modify current_date timestamp; 
create table timestamp_test (currenttime timestamp with time zone);
insert into timestamp_test values(to_timestamp('2006-09-09 12:12:12','yyyy-mm-dd hh:mi:ss'));

1.1.1 取前n条记录

       SQL Server：      Select top n * from xtable

       Oracle：          Select * from xtable where rownum <=n

       DB2：             Select * from xtable fetch first n rows only

1.1.2 取当前日期

       SQL Server：      Select getdate()

       Oracle：          Select sysdate from dual

       DB2：             Select current   timestamp from sysibm.sysdummy1

1.1.3 连接字符串

SQL Server：    Select Hello+Toone

Oracle：        Select   Hello||Toone from dual

DB2：           Select   Hello||Toone from sysimb.sysdummy1

1.1.4 空值转换

       SQL Server：      SELECT   userid,username,isnull(email,0) FROM AUTH_USER

       Oracle：          SELECT   userid,username,nvl(email,0) FROM AUTH_USER

       DB2：             SELECT   userid,username,value(email,0) FROM AUTH_USER

1.1.5 类型转换

       SQL Server：      SELECT convert(varchar,getdate(),20)

       Oracle：          SELECT   to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual

       DB2：             SELECT   varchar(current timestamp) from sysibm.sysdummy1

       注1：

   ■SQL   Server中转换日期格式改变Style参数：20 ；    

   ■Oracle中转换日期格式改变格式化参数：yyyy-mm-dd   hh24:mi:ss   ‘yyyy’、’mm’、’dd’、’hh12’、’hh24’、’mi’、’ss’等；     

   ■Db2中转换日期格式改变系统常量：current timestamp ‘current date’、’current   time’等；

       注2：     

   ■SQL Server数据类型改变“数据类型参数”：int、varchar等；     

   ■Oracle数据类型改变函数：to_char()、to_date()、to_number()等；     

   ■DB2数据类型改变函数：varchar()、int()、date()、time()等；

1.1.6 取值判断

SQL Server：   select   case Convert = case when g.master_type =system    then 管理员     when g.master_type =roletype then 特殊角色    else 普通用户    end from global_code g

Oracle：       select   case g.master_type    when system    then 管理员    when roletype then 特殊角色   else 普通用户 end    AS caseConvert from global_code g

DB2：          select   case g.master_type   when system    then 管理员   when roletype then 特殊角色 else 普通用户   end    AS caseConvert From global_code g

1.1.7 位置 SQL

       Server：      select charindex(E,ABCDEF) select patindex(%E%,ABCDEF)

       Oracle：      select instr(ABCDEF,E) from dual

       DB2：         Select locate(E,ABCDEF) from

       sysibm.sysdummy1

1.1.8 其他函数          SQL Server    Oracle      DB2

   长度    Len()        Length()    Length()

   取子串 Substring() Substr()    Substr()                  

1.2      

       附

1.2.1 DB2V8.

1常用命令

■   创建数据库 在服务器上执行      db2 CREATE DB oatemp

■   删除数据库 在服务器上执行      db2 DROP DB oatemp

■   创建表空间 db2 CREATE USER TEMPORARY TABLESPACE USERSPACE1 MANAGED BY SYSTEM USING ( USERSPACE1)

         ■   启动数据库 切换用户         su – db2inst1      启动数据库：          db2start

         ■   关闭数据库 切换用户         su – db2inst1      关闭数据库          db2stop [force]

         ■   创建远程管理节点      进入   DB2命令窗口      db2 CATALOG TCPIP NODE asnode REMOTE 10.1.22.176 SERVER 50000    db2 CATALOG DB oadb2 AS oadb2 AT NODE asnode

■   连接数据库          db2 CONNECT   TO oadb2 USER db2inst1 USING ibmdb2

         ■   关闭数据库连接      db2 terminate

         ■   执行脚本            db2 -td! -vf ioa2.db2 -z info.log

■   导出脚本            db2look -d oadb2 -i db2inst1 -w ibmdb2 -e -o putsql.db2 -t auth_user db2look -d oadb2 -i db2inst1 -wibmdb2 -e -o putsq.db2

         ■   查看正在使用的端口 netstat -a

         ■   查询系统表          select count(*) from SYSCAT.TABLES where TABSCHEMA=’DB2INST1’      select count(*)   from SYSCAT.PROCEDURES where PROCSCHEMA=’DB2INST1’

         ■   常用命令 DB2 SELECT DROP TALBE ||TABNAME FROM SYSCAT.TABLES WHERE T ABSCHEMA=DB2INST1 AND TYPE=T >db2droptables.db2    DB2 SELECT DROP VIEW ||TABNAME FROM SYSCAT.TABLES WHERE T ABSCHEMA=DB2INST1 AND TYPE=V >db2dropviews.db2    DB2 –vf db2droptables.db2 –z info.log db2 SELECT username FROM auth_user FETCH FIRST 3 ROWS ONLY  

         ■   启动JDBC      db2jstrt JDBC APPLET SERVER 6789     

1.1       数据库移植注意事项

1.1.1 取前n条记录

SQL Server： Select top n * from xtable

Oracle：Select * from xtable where rownum <=n

DB2：Select * from xtable fetch first n rows only

1.1.2 取当前日期

SQL Server： Select getdate()

Oracle： Select sysdate from dual

DB2： Select current timestamp from sysibm.sysdummy1

1.1.3 连接字符串

SQL Server： Select 'Hello'+'Toone'

Oracle： Select 'Hello'||'Toone' from dual

DB2： Select 'Hello'||'Toone' from sysimb.sysdummy1

1.1.4 空值转换

SQL Server：SELECT userid,username,isnull(email,'0') FROM AUTH_USER

Oracle： SELECT userid,username,nvl(email,'0') FROM AUTH_USER

DB2：SELECT userid,username,value(email,'0') FROM AUTH_USER

1.1.5 类型转换

SQL Server： SELECT convert(varchar,getdate(),20)

Oracle：SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

DB2： SELECT varchar(current timestamp) from sysibm.sysdummy1

注1：

     ■SQL Server中转换日期格式改变Style参数：20 ；

     ■Oracle中转换日期格式改变格式化参数：yyyy-mm-dd hh24:mi:ss XML:namespace prefix = o ns = "urn:schemas-microsoft-com:Office:office" />

         ‘yyyy’、’mm’、’dd’、’hh12’、’hh24’、’mi’、’ss’等；

     ■Db2中转换日期格式改变系统常量：current timestamp

         ‘current date’、’current time’等；

注2：

     ■SQL Server数据类型改变“数据类型参数”：int、varchar等；

     ■Oracle数据类型改变函数：to_char()、to_date()、to_number()等；

     ■DB2数据类型改变函数：varchar()、int()、date()、time()等；

1.1.6 取值判断

SQL Server：

select caseConvert =

      case when g.master_type ='system'    then '管理员'

           when g.master_type ='roletype' then '特殊角色'

           else '普通用户'

      end

from global_code g

Oracle：

select   case g.master_type

           when 'system'    then '管理员'

           when 'roletype' then '特殊角色'

           else '普通用户'

      end

      AS caseConvert

from global_code g

DB2：

    

select   case g.master_type

           when 'system'    then '管理员'

           when 'roletype' then '特殊角色'

           else '普通用户'

      end

      AS caseConvert

From global_code g

1.1.7 位置

SQL Server：

     select charindex('E','ABCDEF')

select patindex('%E%','ABCDEF')

Oracle：

     select instr('ABCDEF','E') from dual

DB2：

     Select locate('E','ABCDEF') from sysibm.sysdummy1

1.1.8 其他函数

      SQL Server      Oracle     DB2

长度   Len()          Length()   Length()

取子串Substring()    Substr()   Substr()

  

  

  

  

  

  

1.2       附

1.2.1 DB2V8.1常用命令

■   创建数据库在服务器上执行 db2 CREATE DB oatemp

■   删除数据库在服务器上执行db2 DROP DB oatemp

■   创建表空间

db2 "CREATE USER TEMPORARY TABLESPACE USERSPACE1 MANAGED BY SYSTEM USING (' USERSPACE1')"

■   启动数据库 切换用户 su – db2inst1

     启动数据库：db2start

■   关闭数据库 切换用户 su – db2inst1 关闭数据库 db2stop [force]

■   创建远程管理节点进入   DB2命令窗口

     db2 CATALOG TCPIP NODE asnode REMOTE 10.1.22.176 SERVER 50000

     db2 CATALOG DB oadb2 AS oadb2 AT NODE asnode

■   连接数据库

     db2 CONNECT TO oadb2 USER db2inst1 USING ibmdb2

■   关闭数据库连接

     db2 terminate

■   执行脚本

     db2 -td! -vf ioa2.db2 -z info.log

■   导出脚本

db2look -d oadb2 -i db2inst1 -w ibmdb2 -e -o putsql.db2 -t auth_user

db2look -d oadb2 -i db2inst1 -w ibmdb2 -e -o putsq.db2

■   查看正在使用的端口

     netstat -a

■   查询系统表

     select count(*) from SYSCAT.TABLES where TABSCHEMA=’DB2INST1’     select count(*) from SYSCAT.PROCEDURES where PROCSCHEMA=’DB2INST1’