Sybase总结

链接：http://www.360doc.com/content/12/0523/23/4152160_213259047.shtml

一、 系统数据库 

安装Sybase数据库时自动生成的下列系统数据库： 
● 主数据库master； 
● 模型数据库model； 
● 系统过程数据库sybsystemprocs； 
● 临时数据库tempdb。 
也可选择下列数据库： 
● 安全审核数据库sybsecurity； 
● 示例数据库pubs2； 
● 命令语法数据库sybsyntax。 

（1） master数据库 
包含许多系统表和系统过程，从总体上控制用户数据库和SQL Server的操作，构成了SYBASE系统的数据字典。MASTER数据库主要记录信息为： 
● 登录帐号（SQL服务器用户名）；syslogins,sysremolelogins 
● 正在执行的过程；sysprocesses 
● 可修改的环境变量；sysconfigures 
● 系统错误信息；sysmessages 
● SQL服务器上每个数据库的信息；sysdatabases 
● 每个数据库占用的存储空间；sysusages 
● 系统上安装的磁带及磁带信息；sysdevices 
● 活动锁信息；syslocks 

（2） model数据库 
提供了新用户数据库的初型。当我们每次用CREATE DATABASE命令 时，SQL Server都产生一个model数据库的拷贝，然后把它扩展到命令要求的尺寸。如果我们修改了model数据库，那么以后新创建的数据库都将随它而改变。下面是几个通常用到的对model数据库的改变： 
● 增加用户数据库类型、规则或缺省； 
● 可存取SQL Server上所有数据库的用户用sp_adduser增加到model数据库上； 
● 缺省权限（特别是guest帐号）可在model中建立； 
● 诸如select into/bulkcopy那样的数据库选项可在model中设置。这些设置反映到所有新创建的数据库中。它们在model中最原始的值是关（off）。 
在一般情况下，大多数用户无权修改model数据库，又没被授权读取。因为model中所有内容已拷贝到新数据库中，授权读model没什么意义。 
1> use model 
2> go 
3> sp_help 
4> go 
Name Owner Object_type 
--------------- --------------- ---------------------- 
sysalternates dbo system table 
syscolumns dbo system table 
syscomments dbo system table 
sysconstraints dbo system table 
sysdepends dbo system table 
sysgams dbo system table 
sysindexes dbo system table 
syskeys dbo system table 
syslogs dbo system table 
sysobjects dbo system table 
sysprocedures dbo system table 
sysprotects dbo system table 
sysreferences dbo system table 
sysroles dbo system table 
syssegments dbo system table 
systhresholds dbo system table 
systypes dbo system table 
sysusermessages dbo system table 
sysusers dbo system table 

（3） sybsystemprocs数据库 
库中存储系统过程，当用户执行一存储过程（以sp_开始）时，SQL 服务器首先在用户当前数据库中搜索指定的存储过程，如果没有相应过程，则到系统过程数据库中寻找，若没有，再到MASTER数据库中寻找。 

（4）临时数据库tempdb 
该数据库为临时表和临时工作提供了一个存储区域。临时数据库是SQL Server上所有数据库共享的工作空间。由于这些表都是临时的，所以每当用户退出或系统失败，这些表都将消失。 

（5）安全审核数据库sybsecurity 
数据库中保存系统的安全审核信息，它可跟踪记录每个用户的操作情况，为维护系统安全提供控制手段。 

（6）示例数据库pubs2 
为用户提供一个学习SYBASE的数据库模型。 

（7）命令语法数据库sybsyntax 
库中保存SYBASE数据库的命令语法及支持的语言库。 

二、用户数据库 
用户数据库是指用Create database 命令创建的数据库。 所有新的用户数据库都要在master数据库中创建，也就是说，不能存取master数据库的用户是无权创建新的数据库的。 SA可以将创建数据库的权限授予其他用户。新建数据库中存在一些系统表，在sysusers表中至少有一条记录，既该数据库的创建者。数据库创建时，创建者既为该数据库的 owner, 当然创建者可以将这一地位或这一所有权用系统过程授予别的用户。 

三、数据库对象 
（1）表(Tables) 
在一个关系数据库中，表是最重要的数据库对象,对应于关系数据库理论中关系，与DBASE或FOXPRO中的DBF文件是类似。一切数据都存放在表中。其它数据库对象都是为了用户很好地操作表中的数据。表都以一张二维表的形式存在。其中，每一列称之为一个字段，或一个域；而每一行称之为一个记录，也就是一项数据。 
有一类表，它们的名字都是以sys开头的，这些表称为系统表，系统表记录着有关SQL Server的信息。在master数据库中的表大部分为系统表，这些表是随着master数据库的创建而建立的。另外，每个用户数据库在创建时都有一个系统表的子集。 
例如，只有在master数据库中才有的系统表有：sysconfigures、sysdatabases、sysdevices、syslogins等；
在用户数据库和系统数据库中都有的系统表有：sysalternates、syscolumns、sysindexs、syslogs、sysobjects、sysusers等。 
例： 
1> use master 
2> go 
3> sp_help 
4> go 
Name Owner Object_type 
------------------------------ ----------------------------------------------- 
spt_committab dbo user table 
spt_datatype_info dbo user table 
spt_datatype_info_ext dbo user table 
spt_monitor dbo user table 
spt_server_info dbo user table 
spt_values dbo user table 
sysalternates dbo system table 
syscharsets dbo system table 
syscolumns dbo system table 
syscomments dbo system table 
sysconfigures dbo system table 
sysconstraints dbo system table 
syscurconfigs dbo system table 
sysdatabases dbo system table 
sysdepends dbo system table 
sysdevices dbo system table 
sysengines dbo system table 
sysgams dbo system table 
sysindexes dbo system table 
syskeys dbo system table 
syslanguages dbo system table 
syslisteners dbo system table 
syslocks dbo system table 
sysloginroles dbo system table 
syslogins dbo system table 
syslogs dbo system table 
sysmessages dbo system table 
sysmonitors dbo system table 
sysobjects dbo system table 
sysprocedures dbo system table 
sysprocesses dbo system table 
sysprotects dbo system table 
sysreferences dbo system table 
sysremotelogins dbo system table 
sysroles dbo system table 
syssegments dbo system table 
sysservers dbo system table 
syssrvroles dbo system table 
systestlog dbo system table 
systhresholds dbo system table 
systypes dbo system table 
sysusages dbo system table 
sysusermessages dbo system table 
sysusers dbo system table 
sp_configure dbo stored procedure 
sp_dboption dbo stored procedure 
sp_getmessage dbo stored procedure 
sp_procxmode dbo stored procedure 
sp_prtsybsysmsgs dbo stored procedure 
sp_validlang dbo stored procedure 

（2）视图(Views) 
视图是查看一张或几张表中的数据的一种方式。通过将一张或几张表中的一部分数据进行组合得到视图。视图看上去与表非常相象，但与表还是有着本质的区别。通过视图看到的数据实际上都是存放在表中的，在数据库中仅存在视图的定义。 
使用视图的好处是： 
● 操作方便 
● 安全性 

（3）索引(Index) 
索引是对字段生成的，用于加快数据的检索。在Sybase数据库中，索引是由系统自动管理的，也就是说，Sybase的索引操作对用户是透明的。表的索引一旦建立，系统会自动对其进行更新维护以使它与相应表对应一致；操作时，用户无须指定索引，系统会自动确定是否使用索引进行操作。 
Sybase支持三种不同类型的索引，一是复合索引，即包含几个列的索引；二是唯一性索引，即任意两行中的索引列不能有相同的取值；三是簇类索引（Clustered indexes）和非簇类索引(Nonclustered indexs)，簇类索引使SQL服务器不断重排表行的物理顺序以使其与逻辑索引顺序相一致，非簇类索引则不需要表行的物理顺序与逻辑顺序一致。每个表最多只能建立一个簇类索引，非簇类索引则可建立多个。与非簇类索引相比，簇类索引的检索速度要快。应当在建立任何非簇类索引以前建立簇类索引。 

（4）存储过程(Stored procedures) 
存储过程是用T-SQL语言编写成的SQL子例程，它存储于SQL服务器上供用户调用执行。与一般的SQL语句和批处理语句不同的是，存储过程是经过预编译的。当首次运行一个存储过程时，SQL Server的查询处理器将对其分析，并产生最终的执行方案。由于查询处理的大部分工作已经完成，所以以后执行存储过程时速度将会很快。执行存储过程时可带参数并可调用其他存储过程，执行完毕后返回信息以指示是否成功完成相应操作。存储过程有两种：一种是SQL服务器安装时自动建立的系统存储过程（系统过程），另一种是用户自己创建的存储过程。 
系统过程是用于系统管理，并且为用户提供了从事数据库管理的一种途径。这些系统过程都是以sp_开头的，它们都放在master数据库中且隶属于sa(系统管理员) 。也有很多可以在任一个数据库中运行的系统过程。 
常见的系统过程有： 
Sp_addgroup 在当前数据库中建立一个数据库用户组 
Sp_addlogin 建立一个SQL服务器用户 
Sp_adduser 在当前数据库中增加一个用户 
sp_changegroup 改变数据库用户组 
Sp_dboption 查询或改变数据库系统设置 
Sp_dropdevice 删除设备 
Sp_dropgroup 删除组 
Sp_droplogin 删除帐号 
Sp_help 查询数据库对象及所有数据库信息 
Sp_helpdb 查询数据库信息 
Sp_helpdevice 查询设备信息 
Sp_helpgroup 查询组信息 
Sp_helpindex 查询给定表信息 
Sp_helpuser 查询用户信息 
Sp_lock 查询当前加锁信息 
Sp_monitor 查询SQL服务器统计信息 
Sp_password 改变登录帐号口令 
Sp_spaceused 查询表中的行数、数据页数及空间大小 
Sp_who 查询当前用户及过程信息 
Sp_syntax 查询操作语法 
Sp_configure 配置系统参数 

例： 
1> sp_helpdb 
2> go 
name db_size owner dbid created status 
-------------------------------------------------------------------------- 
master 3.0 MB sa 1 Jan 01, 1900 no options set 
model 2.0 MB sa 3 Jan 01, 1900 no options set 
sybsystemprocs 10.0 MB sa 4 Oct 24, 1997 trunc log on chkpt 
tele114 370.0 MB sa 5 Oct 24, 1997 select into/bulkcopy, trunc log on chkpt 
tempdb 22.0 MB sa 2 May 05, 1998 select into/bulkcopy 
(0 rows affected, return status = 0) 
例： 
1> sp_monitor 
2> go 
last_run current_run seconds 
-------------------------- -------------------------- ----------- 
May 5 1998 4:09PM May 5 1998 4:13PM 224 

(0 rows affected) 
cpu_busy io_busy idle 
------------------------- ------------------------- ------------------------- 
17(1)-0% 5(0)-0% 923(223)-99% 

(0 rows affected) 
packets_received packets_sent packet_errors 
------------------------- ------------------------- ------------------------- 
21(9) 51(23) 0(0) 

(0 rows affected) 
total_read total_write total_errors connections 
------------------- ------------------- ------------------- ------------------ 
947(1) 595(113) 0(0) 3(1) 

(0 rows affected, return status = 0) 

（5）触发器(Triggers) 
触发器是一种特殊的存储过程，用来维护不同表中的相关数据的一致性。当在一张表中插入、删除和修改数据时，触发器就会触发另一个存储过程，从而保持数据的一致性。 

（6）缺省与规则(Defaults and rules) 
缺省是在数据录入时，若用户没有输入数据，SQL Server自动输入的值。 
规则是可以理解为对数据库、某一列、某用户数据类型的限制。

四、启动和关闭数据库，查看表结构 
sp_helpdb   查看所有数据库信息
use test   使用一个库 
sp_helpdb test         --(查看test数据库详细信息) 
sp_helpserver        查看ASE的远程服务器 
sp_help tableName     查看表结构 

查看用户下所有表名 
select name from sysobjects where type='U' 
或 
sp_tables 
 
启动数据库 
[sybase@rosan128main install]$ pwd 
/opt/sybase/ASE-12_5/install 
[sybase@rosan128main install]$ startserver -f RUN_rosantech 


关闭数据库 
先要看启动了什么服务 
1> select srvname from sysservers 
2> go 
 srvname                         
 ------------------------------   
 SYB_BACKUP                      
 loopback                        
 rosanljw_BS                     
 rosantech                       
(4 rows affected) 
关闭备份服务 
1> shutdown SYB_BACKUP 
2> go 
关闭 
1> shutdown   
2> go 
如果关不上 
1> sp_lock 
2> go 
The class column will display the cursor name for locks associated with a cursor 
for the current user and the cursor id for other users. 
 fid    spid   loid        locktype                     table_id    page         row    dbname          class                           context                       
 ------ ------ ----------- ---------------------------- ----------- -----------  ------ --------------- ------------------------------  ----------------------------   
      0     57         114 Sh_intent                       32000114           0  0      master          Non Cursor Lock                
                                       
(1 row affected) 
(return status = 0) 
1> kill 57（spid只可以杀除自己会话以外的会话） 杀掉再shutdown就好了   
！！！如果关不动，可以用以下命令，慎用 
shutdown with nowait 

五、用户管理 

当SQL SERVER创建注册用户后，该用户就能合法进SQL SERVER，该注册用户信息会放在master数据库中的 syslogins表中。
但只有注册用户成为某一数据库用户，并且对该用户赋予某些权限时，该注册用户才能在限制条件下使用数据库中的表。
sa为sybase系统管理员; 
addlogin：增加登陆用户，使你能通过isql登陆；
sp_addgroup：创建组，为该组授权；缺省组为public；
adduser：增加数据库用户，使你能use database；

1.创建登陆用户（login） 
1> sp_addlogin ljw1911,rosantech,test    --依次为用户名，密码，默认数据库 
2> go 
Password correctly set. 
Account unlocked. 
New login created. 
(return status = 0) 
2.删除登陆用户（login） 
1> sp_droplogin ljw1911 
2> go 
3.创建数据库用户（user） 
1> sp_adduser ljw1911 
2> go 
New user added. 
(return status = 0) 
4.删除数据库用户（user） 
1> sp_dropuser ljw1911 
2> go 


六、常用操作
查看数据库对象的信息 
sp_help            --数据库对象名字 
sp_helpdevice        --设备名字 
sp_helpdb 
sp_helptext        --存储过程名字 
sp_spaceused         --查看一个当前库(需要use 数据库)或表占所占用的空间 
sp_recompile （+usertable）    --重新编译存储过程和触发器。与该表相关联的存储过程和触发器在第一次运行时，自动重新编译 

几个常用的存储过程和命令 
sp_who            (查看用户进程) 
sp_lock            (查看数据库锁状况) 
sp_help            (查看对象信息) 
sp_helpdb        (查看数据库信息) 
sp_helpdevice        (查看设备信息) 
sp_spaceused        (查看表占用的空间大小) 
select @@version    (查看版本号) 
dbcc sqltext(@spid)    (查看@spid执行的sql语句) 
sp_showplan @spid,null,null,null        (查看@spid的执行计划) 
dscp   修改server启动地址及端口

常用dbcc命令 
dbcctraceon(3604)                     --(随后的dbcc命令结果输出到屏幕) 
dbcctraceon(3605)                     --(随后的dbcc命令结果输出到错误日志文件) 
dbcccheckalloc[ (database_name[, fix | nofix] ) ]     --(检查分配页) 
dbcccheckcatalog[ (database_name) ]             --(检查系统表) 
dbcccheckdb[ (database_name[, skip_ncindex] ) ]     --(检查数据库) 
dbccchecktable( { table_name| table_id}         --(检查表) 
[, skip_ncindex] ) 
dbcctablealloc( { table_name| table_id}         --(检查表分配页) 
[, { full | optimized | fast | null } 
[, fix | nofix] ] ) | 

常用系统表 
sysdatabases        --记录所有数据库基本信息 
sysusages        --数据库空间分配情况 
sysdevices        --数据库设备信息 
syslogins        --数据库服务器登录信息 
sysusers        --数据库用户信息 
sysobjects        --数据库对象表(U 用户表，P 存储过程) 
sysprocesses        --进程表 

数据库操作授权 
grant 命令序列 to 用户名 
A. 系统管理员可以授予其他用户CREATE DATABASE的权限，使其他用户可以成为数据库所有者。 
B. 数据库所有者在他所拥有的数据库中，可以授予其他用户的权限有： 
l CREATE TABLE ------------------- 建表 
lCREATE DEFAULT ------------------ 建缺省 
lCREATE RULE ------------------- 建规则 
lCREATE PROCedure ------------------ 建存储过程 
lCREATE VIEW ------------------- 建视图 
lDUMP DATABASE ------------------- 备份数据库 
lDUMP TRANsaction ------------------ 备份日志 
C. 数据库对象所有者可以授予其他用户的操作权限有： 
l SELECT 
l UPDATE 
l INSERT 
l EXECute 
l DELETE 
l REFERENCE 

例：授予zhang1在数据库tele114上建表，建视图，建存储过程;对表students有select,reference权；对name,native字段有update权。 
1> use tele114 
2> go 
3> grant creat table,create procedure ,create view to zhang1 
4> go 
5> grant select,reference on students to zhang1 with grant option 
6> go 
7> grant update on students(name,native) to zhang1 
8> go 
9> sp_helprotect 
10> go

七、sybase ASE参数配置 
（1）服务器级配置 
使用存储过程sp_configure配置ASE (和oracle的show parameter很像) 
1. 语法 
sp_configure [configname[, configvalue] | group_name | non_unique_parameter_fragment] 
2. 作用 
查询服务器运行的当前值 
设置服务器运行参数 
用 "sp_configure mem" 可以查看带有mem关键字的参数的具体配置，然后用 "sp_configure 参数, 值" 来配置新 
例1. 配置内存 
1)数据库使用内存 
指定ASE拥有的总的共享内存大小 
sp_configure“max memory”,nnn(单位为2K）( HP平台推荐物理内存的75%，其他平台80%) 
sp_configure‘allocate max shared mem’,1（数据库服务启动时就分配内存）(推荐为1) 
2)配置缺省数据缓存的大小和分区 
sp_cacheconfig‘default data cache’,’xxxM’ 
sp_cacheconfig‘default data cache’,’cache_partition=n’ 
(根据CPU个数以及内存大小确定，需为2的N次方) 
3)配置存储过程的缓存大小 
sp_configure“procedure cache size”,nnn(单位为2K) 
例2. 配置CPU 
sp_configure‘max online engines’,n(cpu个数> 2时推荐CPU数目减1) 
sp_configure‘number of engines at startup’,n 
3. 其他 
1). number of devices (用户自己确定) 
指定ASE的可创建和打开的数据库设备的最大号数 
2). number of locks (用户自己确定) 
指定ASE可同时打开的锁的最大数目 
3). number of open objects (推荐8000-20000) 
ASE的对象描述的缓存数 
4)number of open indexes (推荐2000-5000) 
ASE的索引描述的缓存数 
5) stack size (如果有超长SQL和多层嵌套，推荐*2) 
数据库堆栈的大小 
6). default character set id(推荐使用cp936 –id 171) 
数据库服务器使用的字符集 
7). allow updates to system tables 
指定系统管理员是否可以更改系统表中的数据 
8)row lock promotion HWM，row lock promotion LWM 
行锁升级为表锁的阀值 
9)page lock promotion HWM，page lock promotion LWM 
页锁升级为表锁的阀值 
10) lock scheme 
缺省的对表加的锁类型(缺省为页锁) 

（2）数据库级配置 
使用存储过程sp_dboption更改数据库的数据库选项 
1. 语法sp_dboption[dbname, optname, {true | false}] 
2. 作用 
罗列出所有的数据库选项 
更改某个数据库的数据库选项 
3. 常见的数据库选项 
trunclog on chkpt自动清日志(不能再做增量备份) 
select into/bulkcopy允许快速BCP,selectinto 
ddlin tran允许事务中创建对象 
修改完成以后可以通过sp_helpdb查看到 
例子： 
1> use master 
2> go 
1> sp_dboptionuserdb, "trunclog on chkpt", true 
2> go 
数据库‘userdb'的数据库选项'trunclog on chkpt'被打开。 
在被改变数据库中运行CHECKPOINT命令。 
(return status = 0) 
1> use userdb 
2> go 
1> checkpoint 
2> go 

八、日常常规维护(性能优化) 
1.更新统计信息 
update statistics (+usertable)    --不会对表上锁，不影响业务，但比较耗资源 (optdiag) 
2.整理数据库垃圾空间 
重建表的聚簇索引 
对行锁表执行reorg 
如何加快上述操作 (配置I/O，并行) 
注意：以上操作对表上锁，另外，必须有足够的空间来执行(剩余空间必须为最大表的1.2倍左右) (sp_spaceusedusertable) 
3.重新编译存储过程和触发器 
sp_recompile (+usertable)    --与该表相关联的存储过程和触发器在第一次运行时，自动重新编译 

九、备份和恢复 
备份：dump database dbname to "d:\backup\dbname.bak" 
导入：load database dbname from "d:\backup\dbname.bak" online database dbname

备份的命令 
1.全库备份 
dump database userdb to stripe_device1   
stripe on stripe_device2 
2.增量备份并删除日志 
dump transaction userdb to stripe_device 
3.仅删除日志(没有备份) 
dump transaction userdb with truncate_only 
4、日志满且truncate_only无法删除日志时 
dump transaction userdb with no_log(慎用) 
恢复的命令 
1. load database database_name from stripe_device 
恢复整个数据库 
2. load transaction database_name from stripe_device 
恢复日志