create user fly_rain identified by
fly_rain
default tablespace 'usr'
quota on .......................................
.............................
-_-
fly_rain
default tablespace 'usr'
quota on .......................................
.............................
-_-
默认安装完Oracle后，初学者应该了解的一些SQL语句1、连接
SQL*Plus system/manager2、显示当前连接用户
SQL> show user3、查看系统拥有哪些用户
SQL> select * from all_users;4、新建用户并授权
SQL> create user a identified by a;（默认建在SYSTEM表空间下）
SQL> grant connect,resource to a;5、连接到新用户
SQL> conn a/a6、查询当前用户下所有对象
SQL> select * from tab;7、建立第一个表
SQL> create table a(a number);8、查询表结构
SQL> desc a9、插入新记录
SQL> insert into a values(1);10、查询记录
SQL> select * from a;11、更改记录
SQL> update a set a=2;12、删除记录
SQL> delete from a;13、回滚
SQL> roll;
SQL> rollback;14、提交
SQL> commit;
http://expert.csdn.net/Expert/topic/1208/1208670.xml?temp=4.423159E-02
这里拿分。
http://www.oraclefan.net/ocp/sql/dba_monitor.txtset echo on
spool user_DBA_report.txt
set pages 333 lin 96
rem 这是一个DBA_Monitor.sql程序，目的是日常监测分析数据库之用。
rem 使用时服务器并不一定要设置为timed_statistics=true。
rem 我希望在你们这里可以发表一个月后，我收集意见后再修改为正式版。
rem 看过的朋友一定提更改意见噢!
rem 主要参考《Oracle8 DBA Handbook》,《Oracle8 Tuning》。
rem
rem
rem ---------------------------------------------------
rem 执行说明：本程序第一部分需要DBA权限,
rem 第二部分针对实际用户,它们拥有表,索引,Source等。
rem # cat> ding92sql
rem sqlplus system/passwd@stamex <<EOFa1
rem @dba_monitor.sql
rem connect stamexxx/passwd@stamex
rem @getuser_objects.sql
rem EOFa1
rem exit
rem 数据库性能调整包括三方面的内容:(OS,DB,App)
rem OS=操作系统，分别从CPU,Memory,Disk,NetWork
rem 查询数据表的:|名称|行数.变化|列数|Initail|Cache|TSpace|
rem 查询数据表索引的:|名称|表名|列数|类型|Initial|
rem 查询数据表约束:|名称|表名|相关性|类型|
rem 查询SQL的频率和效率v_$sqlarea,跟踪到sqls表中
rem 生成并定期执行:index1rebld.sql, index2rebld.sql
rem ORACLE数据库情况统计分析程序, 2001.02--2001.05, Ding Jugang
rem 1、 参数dbwr_io_slave等三个从属进程可以分别设置为 40:12:6
rem 2、 参数process 一般是实际进程数据的1.5倍, 是为限制进程总数.
rem process过大将降低系统的性能，可适当考虑降低该数值到600
rem 3、 当LOG FILE SWITCH时出现等待时，建议加大REDO LOG FILE,一般是30分钟
rem 一次切换。目前是128MB,配合4MB LOG_BUFFER已经可以了(8M也没用).
rem 4、 在整个系统较繁忙时检测SHARED_POOL(一般情况下应该空余1/4)
rem 5、 SGA应该小于整个物理内存的一半，太大会导致内存换页出现(PI/PO)
rem 6、 MTS对于网站应用是理想选择，但在过分繁忙的客户端压力下，MTS会自动失败
rem 而重启专用进程(例如过多的PHP连接)，估计是应用类型不兼容。
rem 7、 命令instat,vmstat,top,w 能从OS级评估系统负荷。
rem 监测下面语句的执行结果的变化率，能得出数据库级的硬盘读写流量。
rem select count(FILE#),sum(PHYRDS),sum(PHYWRTS),sum(PHYBLKRD) from sys.V_$filestat;rem================================================================
rem 创建SQL跟踪表SQLS
rem create table SQLS as select * from sys.v_$sqlarea where disk_reads>100;rem 择机(有性能怀疑时)执行跟踪, 并执行随后的查询：
rem insert into sqls select * from sys.v_$sqlarea where disk_reads>10 and executions<10;
rem 1)最高频率的SQL
rem select disk_reads,executions,rows_processed,sql_text from SQLS
rem where executions> 99 ; rem 2)查询性能最差的SQL：
rem select disk_reads,executions,rows_processed,first_load_time,sql_text from SQLS
rem order by first_load_time;
select disk_reads,executions,rows_processed,first_load_time,sql_text
from sys.v_$sqlarea where disk_reads>10 and executions <10 order by first_load_time;
rem ========================================================================
rem 567890123456789_1234567890123456789_1234567890123456789_1234567890123456
rem ==RowCache,LibraryCache 依赖于Shared_pool,参看sys.v_$sgastat ===
rem 此二者当达到95%，现已经达到99%
select (sum(pins - reloads))/ sum(pins) "lib cache" from sys.v_$librarycache;
select (sum(gets-getmisses-usage-fixed)) / sum(gets) from sys.v_$rowcache;
rem ==================== SGA ==============================================
rem ======= sys.v_$sgastat,SGA中详细说明 =====================
rem 剩余共享池: 保留Free Memory 大于25%
column name format A46
column value format 999999,999,999
select * from sys.v_$sgastat where rownum<5;
rem =================== SYS ===============================================
rem ========= sys.v_$SYSstat 详细列表,下面是几个指标的算法=======
rem 数据缓冲命中率：1- 40#/(39#+38#) = 99.8% > 95%
rem 内存排序成功率：1- 162#/(161#) = 99.4% > 92%
rem 脏缓冲区平均长度(oracle8i已废除之): 41#/42#=0.06<db_block_size/4
rem 应用效率：全表扫描<1%, 140#long/(139#short+140#long)
column class format 99999
column value format 999999,999,999
select * from sys.v_$sysstat
where STATISTIC# in (38,39,40,41,42,43,139,140,141,106,161,162,163);rem 日志缓存要满足空间请求极小,每日300,还可更小：
rem LOG_BUFFER=4MB,还可再大。
rem select * from sys.v_$sysstat where name like 'redo%';
rem
rem ==================回滚段1==============================================
rem 回滚段有效率：waits/gets<1% 即: 无等待命中率NoWait_Hit_Ratio接近于1
rem 回滚段数据量在4---100个,同样规格大小,尽可能稳定不变。
rem alter TABLESPACE RBS DEFAULT STORAGE
rem (INITIAL 1M NEXT 1M MINEXTENTS 8 PCTINCREASE 0);
rem CREATE PUBLIC ROLLBACK SEGMENT RB21 TABLESPACE RBS;
rem ALTER ROLLBACK SEGMENT RB21 storage( minextents 4 optimal 8M);
rem CREATE PUBLIC ROLLBACK SEGMENT RB55 TABLESPACE RBS2 storage(minextents 8 optimal 8M);column Ratios? format 99.9999
select count(*), sum(waits)/sum(gets) from sys.v_$rollstat;rem rssize>=最优保留值optimal,shrinks是动态收缩次数,每小时2次是允许的。
rem 例如，统计信息：rssize=8M,extents=8, waits和shrinks 小于每天2次select usn,extents,gets,writes,rssize,waits,shrinks
from sys.v_$rollstat;rem =======****** 回滚段2 ****===========
rem 这里是回滚段的汇总统计,分析其扩展段之定义。其命中率统计见v_$roolstat
rem dba_rollback_segs 下面验证一致性。select owner,initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,count(*),
TABLESPACE_NAME,STATUS
from dba_rollback_segs
group by owner,initial_extent,NEXT_EXTENT,min_extents,
MAX_EXTENTS,TABLESPACE_NAME,STATUS;rem dba_rollback_segs --> detail 定义
column SEGMENT_name format a6
column TABLESPACE_NAME format a12
select SEGMENT_name,owner,initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,
TABLESPACE_NAME,STATUS from dba_rollback_segs;
rem ======================== 闩 ===========================================
rem 闩-Oracle内部锁，无等待命中率NoWait_Hit_Ratio接近于1
column name format A30
select name, immediate_gets "Imme_gets", immediate_misses "Imme_Mis",
round(immediate_gets/(immediate_gets+immediate_misses),3) "nowait_hit_ratio"
from sys.v_$latch where immediate_gets+immediate_misses != 0 order by name;
rem 闩-Oracle内部锁，命中率HITRATIO(即misses/gets)接近于0
rem sleeps, immediate_gets "Imme_gets", 为了易于阅览,不要换行
column Mis/Get? format 99.999;
select name, gets, misses, misses/gets "Mis/Get?",
immediate_misses "Imme_Mis"
from sys.v_$latch where gets > 0 order by name;
rem ================== 系统等待 ===========================================
rem 系统等待累计次数和累计时间
column class format A30
select * from sys.v_$waitstat ;
column event format A28
select * from sys.v_$system_event order by TOTAL_WAITS;
rem =======================================================================
rem ================ v_$session_wait
GRANT ALTER ANY INDEX TO "user_id"
GRANT "dba" TO "user_id";
ALTER USER "user_id" DEFAULT ROLE ALL
创建用户:
CREATE USER "user_id" PROFILE "DEFAULT" IDENTIFIED BY " DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "user_id";
用户密码设定:
ALTER USER "CMSDB" IDENTIFIED BY "pass_word"
表空间创建:
CREATE TABLESPACE "table_space" LOGGING DATAFILE 'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M
创建用户:CREATE USER User_Name IDENTIFIED BY Default_Pass DEFAULT TABLESPACE users
用户密码设定:ALTER USER User_Name IDENTIFIED BY New_Pass
表空间创建:CREATE TABLESPACE Table_Name DATAFILE 'File_Path' SIZE 1234m
角色创建:CREATE ROLE Roles_Name
SQL> select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
/
dba_free_space --表空间剩余空间状况
dba_data_files --数据文件空间占用情况
4、查看现有回滚段及其状态 SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS; 5、查看数据文件放置的路径 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; 6、显示当前连接用户 SQL> show user 7、把SQL*Plus当计算器 SQL> select 100*20 from dual; 8、连接字符串 SQL> select 列1||列2 from 表1;
SQL> select concat(列1,列2) from 表1; 9、查询当前日期 SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual; 10、用户间复制数据 SQL> copy from user1 to user2 create table2 using select * from table1; 11、视图中不能使用order by，但可用group by代替来达到排序目的 SQL> create view a as select b1,b2 from b group by b1,b2; 12、通过授权的方式来创建用户 SQL> grant connect,resource to test identified by test; SQL> conn test/test 13、查出当前用户所有表名。select unique tname from col;
alter table alist_table add address varchar2(100);/* 修改字段 属性 字段为空 */
alter table alist_table modify address varchar2(80);/* 修改字段名字 */
create table alist_table_copy as select ID,NAME,PHONE,EMAIL,
QQ as QQ2, /*qq 改为qq2*/
ADDRESS from alist_table;drop table alist_table;
rename alist_table_copy to alist_table
/* 修改表名 */
空值处理
有时要求列值不能为空
create table dept (deptno number(2) not null, dname char(14), loc char(13)); 在基表中增加一列
alter table dept
add (headcnt number(3)); 修改已有列属性
alter table dept
modify dname char(20);
注：只有当某列所有值都为空时，才能减小其列值宽度。
只有当某列所有值都为空时，才能改变其列值类型。
只有当某列所有值都为不空时，才能定义该列为not null。
例：
alter table dept modify (loc char(12));
alter table dept modify loc char(12);
alter table dept modify (dname char(13),loc char(12));
查找未断连接
select process,osuser,username,machine,logon_time ,sql_text
from v$session a,v$sqltext b where a.sql_address=b.address;
select * from user_tables;
2.以ALL_开始的数据字典视图包含ORACLE用户所拥有的信息,
查询用户拥有或有权访问的所有表信息:
select * from all_tables;3.以DBA_开始的视图一般只有ORACLE数据库管理员可以访问:
select * from dba_tables;4.查询ORACLE用户：
conn sys/change_on_install
select * from dba_users;
conn system/manager;
select * from all_users;5.创建数据库用户：
CREATE USER user_name IDENTIFIED BY password;
GRANT CONNECT TO user_name;
GRANT RESOURCE TO user_name;
授权的格式: grant (权限) on tablename to username;
删除用户(或表):
drop user(table) username(tablename) (cascade);
6.向建好的用户导入数据表
IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:\EXPDAT.DMP COMMIT = Y
7.索引
create index [index_name] on [table_name]("column_name")
基本关键字：
操作语句：select、delete、insert、update
定义语句：create、drop、alter创建永久表空间：
CREATE TABLESPACE "TEST"
LOGGING
DATAFILE 'D:\ORACLE\ORADATA\PDMS\TEST.ora' SIZE 50M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
（创建永久表空间TEST、启动重做日志、指定物理位置、初始空间50M）创建临时表空间
CREATE TEMPORARY TABLESPACE "TEST_TEMP" TEMPFILE
'D:\ORACLE\ORADATA\PDMS\TEST_TEMP.ora' SIZE 10M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M
（创建临时表空间TEST_TEMP、初始大小10M）创建用户：
CREATE USER "TEST"
IDENTIFIED BY "test" DEFAULT TABLESPACE "TEST"
TEMPORARY TABLESPACE "TEST_TEMP"
ACCOUNT UNLOCK;
(创建用户test，数据表空间test、临时表空间test_temp，账户未锁定)为用户授与角色（权限）
GRANT "CONNNECT" TO "TEST";
GRANT "RESOURCE" TO "TEST";
ALTER USER "PDMS" DEFAULT ROLE ALL
(把CONNECT/RESOURCE角色授予test用户)
每个数据库最少有一个控制文件（建议3个，分别放在不同的磁盘上） 每个数据库最少有一个表空间（SYSTEM表空间）
建立SYSTEM表空间的目的是尽量将目的相同的表存放在一起，以提高使用效率 每个数据库最少有两个联机日志文件
一个数据文件只能属于一个表空间
一个数据文件一旦被加入到一个表空间中，就不能再从这个表空间中移走，也不能再加入到其他表空间中 建立新的表空间需要建立新的数据文件
数据文件被ORACLE格式化为ORACLE块，ORACLE块的大小是在第一次创建数据库时设定的，并且以后不能改变 单独一个事务不能跨越多个回滚段
索引表不含ROWID值
拥有不同大小的回滚段没有任何益处
ORACLE在初始安装时建立一个缺省数据库，实例名为ORCL 一个块的最大长度为16KB(有2K、4K、8K、16K)
每个数据库最大文件数（按块大小）
2K块 20000个文件
4K块 40000个文件
8K块或以上 65536个文件
oracle server可以同时启动多个数据库
一套操作系统上只能安装一个版本的ORACLE数据库系统
一套ORACLE数据库系统中可以有多个ORACLE数据库
每个ORACLE数据库拥有一个数据库实例（INSTANCE）
所以，一套操作系统上同时可以有多个oracle数据库实例启动
*******************Transact_SQL********************
--语 句 功 能
--数据操作
SELECT --从数据库表中检索数据行和列
INSERT --向数据库表添加新数据行
DELETE --从数据库表中删除数据行
UPDATE --更新数据库表中的数据
--数据定义
CREATE TABLE --创建一个数据库表
DROP TABLE --从数据库中删除表
ALTER TABLE --修改数据库表结构
CREATE VIEW --创建一个视图
DROP VIEW --从数据库中删除视图
CREATE INDEX --为数据库表创建一个索引
DROP INDEX --从数据库中删除索引
CREATE PROCEDURE --创建一个存储过程
DROP PROCEDURE --从数据库中删除存储过程
CREATE TRIGGER --创建一个触发器
DROP TRIGGER --从数据库中删除触发器
CREATE SCHEMA --向数据库添加一个新模式
DROP SCHEMA --从数据库中删除一个模式
CREATE DOMAIN --创建一个数据值域
ALTER DOMAIN --改变域定义
DROP DOMAIN --从数据库中删除一个域
--数据控制
GRANT --授予用户访问权限
DENY --拒绝用户访问
REVOKE --解除用户访问权限
--事务控制
COMMIT --结束当前事务
ROLLBACK --中止当前事务
SET TRANSACTION --定义当前事务数据访问特征
--程序化SQL
DECLARE --为查询设定游标
EXPLAN --为查询描述数据访问计划
OPEN --检索查询结果打开一个游标
FETCH --检索一行查询结果
CLOSE --关闭游标
PREPARE --为动态执行准备SQL 语句
EXECUTE --动态地执行SQL 语句
DESCRIBE --描述准备好的查询
---局部变量
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
---全局变量
---必须以@@开头
--IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
--CASE
use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
--WAITFOR
--例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay ’01:02:03’
select * from employee
--例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time ’23:08:00’
select * from employee
***SELECT***
select *(列名) from table_name(表名) where column_name operator value
ex:(宿主)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
stockname like '[^F-M]%' --------- (^排除指定范围)
--------- 只能在使用like关键字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
order by 1,2 --------- by列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层select只返回一个行的值，
--------- 否则应在外层where子句中用一个in限定符
select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组,指定列中有相同的值
having count(*) = 2 --------- having选定指定的组
select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
table1.id =* table2.id -------- 右外部连接
select stockname from table1
union [all] ----- union合并查询结果集，all-保留重复行
select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value为select语句
***update***
update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3
truncate table_name ----------- 删除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全删除表
***alter table*** --- 修改数据库表结构
alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法（创建新表）
alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
***function(/*常用函数*/)***
----统计函数----
AVG --求平均值
COUNT --统计数目
MAX --求最大值
MIN --求最小值
SUM --求和
--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
--MAX
--求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
--STDEV()
--STDEV()函数返回表达式中所有数据的标准差
--STDEVP()
--STDEVP()函数返回总体标准差
--VAR()
--VAR()函数返回表达式中所有值的统计变异数
--VARP()
--VARP()函数返回总体变异数
/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression) --返回<=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据
--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
PI() --返回值为π 即3.1415926535897936
RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数----字符串函数----
ASCII() --函数返回字符表达式最左端字符的ASCII 码值
CHAR() --函数用于将ASCII 码转换为字符
--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER() --函数把字符串全部转换为小写
UPPER() --函数把字符串全部转换为大写
STR() --函数把数值型数据转换为字符型数据
LTRIM() --函数把字符串头部的空格去掉
RTRIM() --函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX() --函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
--0 两个SOUNDEX 函数返回值的第一个字符不同
--1 两个SOUNDEX 函数返回值的第一个字符相同
--2 两个SOUNDEX 函数返回值的第一二个字符相同
--3 两个SOUNDEX 函数返回值的第一二三个字符相同
--4 两个SOUNDEX 函数返回值完全相同
QUOTENAME() --函数返回被特定字符括起来的字符串
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
REVERSE() --函数将指定的字符串的字符排列顺序颠倒
REPLACE() --函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
SPACE() --函数返回一个有指定长度的空白字符串
STUFF() --函数用另一子串替换字符串指定位置长度的子串----数据类型转换函数----
CAST() 函数语法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
------------------------------ ------------
199 Jan 15 2000
----日期函数----
DAY() --函数返回date_expression 中的日期值
MONTH() --函数返回date_expression 中的月份值
YEAR() --函数返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
--函数返回两个指定日期在datepart 方面的不同之处
DATENAME(<datepart> , <date>) --函数以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>) --函数以整数值的形式返回日期的指定部分
GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME() --函数返回当前执行的应用程序的名称
COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>) --函数返回表中指定字段的名称即列名
DATALENGTH() --函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) --函数返回数据库的编号
DB_NAME(database_id) --函数返回数据库的名称
HOST_ID() --函数返回服务器端计算机的名称
HOST_NAME() --函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换
ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';2、Oracle启动时，监听器不启动或打开出错？ 软件环境：
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为：C:\ORANT错误现象：
1、ORACLE启动时，监听器不启动或打开出错
2、服务器端：用username/password登录正常，但用username/password@alias登录不成功
3、客户端：用username/password@alias登录不成功解决方法：
1、如果是因为修改了NT的机器名，则把listener.ora文件中的host参数全部改为新的NT机器名，
重新启动OracleTNSListenr80服务即可。
例如:
LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = NT_Name)(Port = 1521))
(ADDRESS = (PROTOCOL = TCP)(Host = NT_Name)(Port = 1526))
)
2。使用监听器实用程序：LSNRCTL80.exe
3。检查监听器参数文件：c:\orant\NET80\admin\listener.ora
4。检查监听器日志文件：c:\orant\NET80\log\listener.log
5。Tnsnames.ora文件配置错误，重新配置。
6。检查client 端 Tnsnames.ora3、 查询数据库内被锁的对象
如果仅仅是lock，以下语句可以发现所有用户锁
select s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,s.terminal,s.machine,s.program,s.osuser
from v$session s,v$lock l,dba_objects o
where s.sid=l.sid
and o.object_id=l.id1
and s.username is not null如果有锁等待或死锁，可以通过以下语句发现
select lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
order by o.object_id desc4、察看数据库的大小，和空间使用情况
SQL> col tablespace format a20
SQL> select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
/
dba_free_space --表空间剩余空间状况
dba_data_files --数据文件空间占用情况5、手工创建数据库的全部脚本极其说明
·系统环境：
1、操作系统：Windows 2000 Server，机器内存128M
2、数据库： Oracle 8i R2 (8.1.6) for NT 企业版
3、安装路径：D:\ORACLE
·建库步骤：
·1、手工创建相关目录
·D:\Oracle\admin\test
·D:\Oracle\admin\test\adhoc
·D:\Oracle\admin\test\bdump
·D:\Oracle\admin\test\cdump
·D:\Oracle\admin\test\create
·D:\Oracle\admin\test\exp
·D:\Oracle\admin\test\pfile
·D:\Oracle\admin\test\udump
·
·D:\Oracle\oradata\test
·D:\Oracle\oradata\test\archive
·
·2、手工创建初始化启动参数文件：D:\Oracle\admin\test\pfile\inittest.ora，内容：
·
·3、手工创建D:\Oracle\Ora81\DATABASE\inittest.ora文件，
·
· 内容：IFILE='D:\Oracle\admin\test\pfile\inittest.ora'
·
·4、使用orapwd.exe命令，创建D:\Oracle\Ora81\DATABASE\PWDtest.ora
·
· 命令：D:\Oracle\Ora81\bin\orapwd file=D:\Oracle\Ora81\DATABASE\PWDtest.ora password=ORACLE entries=5
·
·
·5、通过oradim.exe命令，在服务里生成一个新的实例管理服务，启动方式为手工
· set ORACLE_SID=test
· D:\Oracle\Ora81\bin\oradim -new -sid test -startmode manual -pfile "D:\Oracle\admin\test\pfile\inittest.ora"
·
·6、生成各种数据库对象
·D:\>svrmgrl
·
·--创建数据库
·connect INTERNAL/oracle
·startup nomount pfile="D:\Oracle\admin\test\pfile\inittest.ora"
·CREATE DATABASE test
·LOGFILE 'D:\Oracle\oradata\test\redo01.log' SIZE 2048K,
· 'D:\Oracle\oradata\test\redo02.log' SIZE 2048K,
· 'D:\Oracle\oradata\test\redo03.log' SIZE 2048K
·MAXLOGFILES 32
·MAXLOGMEMBERS 2
·MAXLOGHISTORY 1
·DATAFILE 'D:\Oracle\oradata\test\system01.dbf' SIZE 58M REUSE AUTOEXTEND ON NEXT 640K
·MAXDATAFILES 254
·MAXINSTANCES 1
·CHARACTER SET ZHS16GBK
·NATIONAL CHARACTER SET ZHS16GBK;
·
·控制文件、日志文件在上面语句执行时生成
·
·
·connect INTERNAL/oracle
·--修改系统表空间
·ALTER TABLESPACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
·ALTER TABLESPACE SYSTEM MINIMUM EXTENT 64K;
·
·--创建回滚表空间
·CREATE TABLESPACE RBS DATAFILE 'D:\Oracle\oradata\test\rbs01.dbf' SIZE 256M REUSE
·AUTOEXTEND ON NEXT 5120K
·MINIMUM EXTENT 512K
·DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);
·
·--创建用户表空间
·CREATE TABLESPACE USERS DATAFILE 'D:\Oracle\oradata\test\users01.dbf' SIZE 128M REUSE
·AUTOEXTEND ON NEXT 1280K
·MINIMUM EXTENT 128K
·DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
·
·--创建临时表空间
·CREATE TABLESPACE TEMP DATAFILE 'D:\Oracle\oradata\test\temp01.dbf' SIZE 32M REUSE
·AUTOEXTEND ON NEXT 640K
·MINIMUM EXTENT 64K
·DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;
·
·--创建工具表空间
·CREATE TABLESPACE TOOLS DATAFILE 'D:\Oracle\oradata\test\tools01.dbf' SIZE 64M REUSE
·AUTOEXTEND ON NEXT 320K
·MINIMUM EXTENT 32K
·DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
·
·--创建索引表空间
·CREATE TABLESPACE INDX DATAFILE 'D:\Oracle\oradata\test\indx01.dbf' SIZE 32M REUSE
·AUTOEXTEND ON NEXT 1280K
·MINIMUM EXTENT 128K
·DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
·
·--创建回滚段
·CREATE PUBLIC ROLLBACK SEGMENT RBS0 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS1 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS2 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS3 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS4 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS5 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS6 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS7 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS8 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS9 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS10 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS11 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS12 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS13 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS14 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS15 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS16 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS17 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS18 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS19 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS20 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS21 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS22 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS23 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·CREATE PUBLIC ROLLBACK SEGMENT RBS24 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
·
GRANT "dba" TO "user_id";
ALTER USER "user_id" DEFAULT ROLE ALL
创建用户:
CREATE USER "user_id" PROFILE "DEFAULT" IDENTIFIED BY " DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "user_id";
用户密码设定:
ALTER USER "CMSDB" IDENTIFIED BY "pass_word"
表空间创建:
CREATE TABLESPACE "table_space" LOGGING DATAFILE 'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M
1。查找a表中有，而在b表中没有的记录
实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /表已建立.
SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111');
SQL> insert into a values('1115','1111');
SQL> create table b as select * from a where 1=2;表已建立.
SQL> insert into b values('1111','1111');
SQL> insert into b values('1112','1111');
SQL> insert into b values('1113','1111');
SQL> insert into b values('1114','1111');
SQL> commit;
完全提交.
SQL> select * from a;BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111
1115 1111SQL> select * from b;BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111SQL> select bm from a where not exists (select bm from b where a.bm=b.bm);BM MC
---- --------------------
1115 11112。删除a表中和b表相同的数据
问题提出：在做数据转储业务的时候，如果发生操作错误，有可能出现主表和副表中都有同一种数据，
这样结算的结果就有可能发生错误。实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /表已建立.SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111');
SQL> insert into a values('1115','1111');
SQL> create table b as select * from a where 1=2;
表已建立.
SQL> insert into b values('1111','1111');
SQL> insert into b values('1112','1111');
SQL> insert into b values('1113','1111');
SQL> insert into b values('1114','1111');
SQL> commit;
完全提交.
SQL> select * from a;
BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111
1115 1111
SQL> select * from b;
BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111方法一：exists子句SQL> delete from a where exists (select 'X' from b where a.bm=b.bm and a.mc=b.mc);删除4个记录.where条件：如果两个表中都拥有相同字段的主键（primary key），则只需比较两个主键就可以了方法二：in子句SQL> delete from a where (bm,mc) in (select bm,mc from b);删除4个记录.SQL> select * from a;BM MC
---- --------------------
1115 1111实际测试结论：
在表不是很大时，用in子句速度还可以忍受，而如果记录量很多时（十万条以上），in子句简直让人难以人忍受，速度奇慢。