1.数据库备份，恢复(load后数据库处于offonline状态，需用online database dbname 做online)
1.注意事务日志问题syslogs
2.dump database "sdl" to ""
load database "sdl" from ""
3.dump transaction dbname to " " with no_truncate
load transaction dbname from " "
注意：如果总用dump database命令，不用dump transaction,事务日志将不会被删除，会变得越来越大。因此对于一般的数据库，每次运行dump database 与 dump tracsaction with no_truncate之后，应当运行dump tracsaction with truncate only删除日志
2.设备建立，数据库建立，数据库扩容，添加设备到数据库（会造成数据库offline)
3.用户管理
1.添加用户，删除用户，用户权限授权、撤权管理 sp_login name,"password"
2.
4.系统性能：sp_monitor sp_who sp_lock sp_configure sp_cacheconfig
kill
5.建、修、删数据库或表，SQl语句
6.绑定缓存区至数据库或表或字段sp_bindcache （注：绑定立即生效，无需要重启服务）
1.sp_bindcache pub_cache,guoyihong,oss,id
2.sp_bindcache pub_cache,guoyihong,syslogs 绑定至事务日志表
7.常用命令：
1.查看数据库,表 sp_helpdb sp_help
2.查看服务 sp_helpserver
3.设备数据库与对应用设备 select name,phyname from sysdevices sp_helpdevice
4.查看系统版本 select @@version
5.查看表占用空间sp_spaceused tablename
6.检查数据库表原整性 dbcc checktable(tablename)
8.数据库服务管理
1.运行，停止 startserver SYBBASE /shutdown SYBASE
2.
1> use master
2> go
1> load database guoyihong from "c:/guoyihong.bat"
2> go
Backup Server session id is: 6. Use this value when executing the 'sp_volchang
ed' system stored procedure after fulfilling any volume change request from the
Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'oyihong073650D0DA' section number 1 moun
ted on disk file 'c:/guoyihong.bat'
Backup Server: 4.58.1.1: Database guoyihong: 5638 kilobytes LOADed.
Backup Server: 4.58.1.1: Database guoyihong: 11270 kilobytes LOADed.
Backup Server: 4.58.1.1: Database guoyihong: 11278 kilobytes LOADed.
Backup Server: 3.42.1.1: LOAD is complete (database guoyihong).
Started estimating recovery log boundaries for database 'guoyihong'.
Completed estimating recovery log boundaries for database 'guoyihong'.
Started ANALYSIS pass for database 'guoyihong'.
Completed ANALYSIS pass for database 'guoyihong'.
Started REDO pass for database 'guoyihong'. The total number of log records to p
rocess is 4.
Redo pass of recovery has processed 1 committed and 0 aborted transactions.
Completed REDO pass for database 'guoyihong'.
Use the ONLINE DATABASE command to bring this database online; SQL Server will n
ot bring it online automatically.
1> use guoyihong
2> dbcc checktable(syslogs)
3> go
Msg 950, Level 14, State 1:
Line 1:
Database 'guoyihong' is currently offline. Please wait and try your command again later.
Checking syslogs: Logical pagesize is 2048 bytes
The total number of data pages in this table is 97.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 1987 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> disk mirror
2> name="gyh1_dev",
3> mirror="c:/gyh1.dat"
4> go
Msg 5155, Level 16, State 1:
Line 1:
The command 'DISK MIRROR' cannot be executed since disk mirroring has been disabled. In order to enable disk mirroring, use: sp_configure 'disable disk mirroring',0 and restart the server.
1> sp_helpdevice
2> go
gyh1_dev1 c:/sybase/data/gyh1.bat
special, dsync on, physical disk, 1.20 MB //注意，扩大了一陪空间
(6 rows affected)
(return status = 0)
1> disk resize
2> name="gyh1_dev1",
3> size=612
4> go
1> sp_helpdevice
2> go
gyh1_dev1 c:/sybase/data/gyh1.bat
special, dsync on, physical disk, 2.39 MB
//将设备滔添加到数据库guoyihong
1> alter database guoyihong on gyh1_dev1
2> go
Extending database by 512 pages (1.0 megabytes) on disk gyh1_dev1
Database guoyihong which is currently offline has been altered from size 5632 pages to 6144 pages.
1> use guoyihong
2> go
Msg 950, Level 14, State 1:
Line 1:
Database 'guoyihong' is currently offline. Please wait and try your command again later.
1> online database guoyihong
2> go
Started estimating recovery log boundaries for database 'guoyihong'.
Completed estimating recovery log boundaries for database 'guoyihong'.
Started ANALYSIS pass for database 'guoyihong'.
Completed ANALYSIS pass for database 'guoyihong'.
Recovery of database 'guoyihong' will undo incomplete nested top actions.
Database 'guoyihong' is now online.
1> select name,phyname from sysdevices
2> go
name phyname
------------------------------ ----------------------------------------------------------------------------------
gyh1_dev1 c:/sybase/data/gyh1.bat
gyh_dev1 c:/sybase/data/gyh.bat
master C:/sybase/data/master.dat
sysprocsdev C:/sybase/data/sysprocs.dat
tapedump1 //./TAPE0
tapedump2 //./TAPE1
(6 rows affected)
1> sp_helpdb guoyihong
2> go
name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ -------------- ---------------------------
guoyihong 12.0 MB sa 4 Dec 31, 2007 offline, mixed log and data
(1 row affected)
device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------- ----------------
master 11.0 MB data and log Dec 31 2007 2:40PM 9962
gyh1_dev1 1.0 MB data and log Jan 1 2008 12:53AM 1020
(return status = 0)
//帐号名检查
1> sp_checknames
2> go
Looking for non 7-bit ASCII characters in the system tables of database: "master"
===============================================================
Table.Column name: "syslogins.password"
The following logins have passwords that contain non 7-bit
ASCII characters. If you wish to change them use "sp_password";
Remember, only the sa and the login itself may examine or change
the syslogins.password column:
suid name
----------- ------------------------------
1 sa
2 probe
3 mon_user
4 gyh9711
5 test
(return status = 1)
1> sp_configure
2> go
Group: Configuration Options
Group: Backup/Recovery
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
allow remote access 1 0 1 1 switch dynamic
max concurrently recovered db 0 0 0 0 number dynamic
number of checkpoint tasks 1 0 1 1 number dynamic
print recovery information 0 0 0 0 switch dynamic
recovery interval in minutes 5 0 5 5 minutes dynamic
tape retention in days 0 0 0 0 days dynamic
(1 row affected)
Group: Cache Manager
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
global async prefetch limit 10 0 10 10 percent dynamic
global cache partition number 1 0 1 1 number static
memory alignment boundary 2048 0 2048 2048 bytes static
number of index trips 0 0 0 0 number dynamic
number of oam trips 0 0 0 0 number dynamic
total data cache size 0 8774 0 8774 kilobytes NULL
Group: Component Integration Services
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
cis bulk insert array size 50 0 50 50 rows dynamic
cis bulk insert batch size 0 0 0 0 rows dynamic
cis connect timeout 0 0 0 0 seconds dynamic
cis cursor rows 50 0 50 50 number dynamic
cis packet size 512 0 512 512 bytes dynamic
cis rpc handling 0 0 0 0 switch dynamic
enable cis 1 0 1 1 switch static
enable file access 0 0 0 0 switch dynamic
enable full-text search 0 0 0 0 switch dynamic
enable snmp 0 0 0 0 switch dynamic
max cis remote connections 0 84 0 0 number dynamic
Group: DTM Administration
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
dtm detach timeout period 0 0 0 0 minutes dynamic
dtm lock timeout period 300 0 300 300 seconds dynamic
enable DTM 0 0 0 0 switch static
enable xact coordination 1 0 1 1 switch static
number of dtx participants 500 146 500 500 number dynamic
strict dtm enforcement 0 0 0 0 switch static
txn to pss ratio 16 856 16 16 number dynamic
xact coordination interval 60 0 60 60 seconds dynamic
Group: Diagnostics
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
average cap size 200 0 200 200 bytes static
caps per ccb 50 0 50 50 number static
dump on conditions 0 0 0 0 switch dynamic
maximum dump conditions 10 0 10 10 number static
number of ccbs 0 0 0 0 number static
Group: Disk I/O
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
allow sql server async i/o 1 0 1 1 switch static
disable disk mirroring 1 0 1 1 switch static
disk i/o structures 256 38 256 256 number dynamic
number of devices 10 #10 10 10 number dynamic
number of large i/o buffers 6 3112 6 6 number dynamic
page utilization percent 95 0 95 95 percent dynamic
Group: Error Log
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
event log computer name LocalSystem 0 LocalSystem LocalSystem name dynamic
event logging 1 0 1 1 switch dynamic
log audit logon failure 0 0 0 0 switch dynamic
log audit logon success 0 0 0 0 switch dynamic
Group: Extended Stored Procedure
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
esp execution priority 8 0 8 8 id dynamic
esp execution stacksize 34816 0 34816 34816 bytes static
esp unload dll 0 0 0 0 switch dynamic
start mail session 0 0 0 0 switch dynamic
xp_cmdshell context 1 0 1 1 switch dynamic
Group: General Information
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
configuration file 0 0 0 C:/sybase/C name dynamic
Group: Java Services
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
enable enterprise java beans 0 0 0 0 switch dynamic
enable java 0 0 0 0 switch static
number of java sockets 0 0 0 0 number dynamic
size of global fixed heap 150 0 150 150 memory pages(2k) dynamic
size of process object heap 1500 0 1500 1500 memory pages(2k) dynamic
size of shared class heap 1536 0 1536 1536 memory pages(2k) dynamic
Group: Languages
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
default character set id 2 0 2 2 id static
default language id 0 0 0 0 id dynamic
default sortorder id 50 0 50 50 id static
disable character set conversi 0 0 0 0 switch static
enable unicode conversions 0 0 0 0 switch dynamic
Group: Lock Manager
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
deadlock checking period 500 0 500 500 milliseconds dynamic
deadlock retries 5 0 5 5 number dynamic
lock address spinlock ratio 100 0 100 100 ratio static
lock hashtable size 2048 51 2048 2048 bytes static
lock scheme allpages 0 allpages allpages name dynamic
lock spinlock ratio 85 0 85 85 ratio static
lock table spinlock ratio 20 0 20 20 ratio static
lock wait period 2147483647 0 2147483647 2147483647 seconds dynamic
number of locks 5000 754 5000 5000 number dynamic
page lock promotion HWM 200 0 200 200 number dynamic
page lock promotion LWM 200 0 200 200 number dynamic
page lock promotion PCT 100 0 100 100 percent dynamic
print deadlock information 0 0 0 0 switch dynamic
read committed with lock 0 0 0 0 switch dynamic
row lock promotion HWM 200 0 200 200 number dynamic
row lock promotion LWM 200 0 200 200 number dynamic
row lock promotion PCT 100 0 100 100 percent dynamic
Group: Memory Use
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
additional network memory 0 0 0 0 bytes dynamic
allocate max shared memory 0 0 0 0 switch dynamic
allow resource limits 0 #4 0 0 switch static
audit queue size 100 2 100 100 number dynamic
average cap size 200 0 200 200 bytes static
caps per ccb 50 0 50 50 number static
deadlock pipe max messages 0 0 0 0 number dynamic
default network packet size 512 #148 512 512 bytes static
disk i/o structures 256 38 256 256 number dynamic
dynamic allocation on demand 1 0 1 1 switch dynamic
enable rep agent threads 0 0 0 0 switch dynamic
errorlog pipe max messages 0 0 0 0 number dynamic
event buffers per engine 100 #10 100 100 number static
executable codesize + overhead 0 8542 0 8542 kilobytes NULL
heap memory per user 4096 0 4096 4096 bytes dynamic
lock hashtable size 2048 51 2048 2048 bytes static
lock spinlock ratio 85 0 85 85 ratio static
max SQL text monitored 0 4 0 0 bytes static
max memory 33792 67584 33792 33792 memory pages(2k) dynamic
max number network listeners 5 458 5 5 number dynamic
max online engines 1 113 1 1 number static
memory per worker process 1024 4 1024 1024 bytes dynamic
number of alarms 40 4 40 40 number dynamic
number of aux scan descriptors 200 #206 200 200 number dynamic
number of ccbs 0 0 0 0 number static
number of devices 10 #10 10 10 number dynamic
number of dtx participants 500 146 500 500 number dynamic
number of java sockets 0 0 0 0 number dynamic
number of large i/o buffers 6 3112 6 6 number dynamic
number of locks 5000 754 5000 5000 number dynamic
number of mailboxes 30 6 30 30 number dynamic
number of messages 64 6 64 64 number dynamic
number of open databases 12 1394 12 12 number dynamic
number of open indexes 500 592 500 500 number dynamic
number of open objects 500 685 500 500 number dynamic
number of remote connections 20 33 20 20 number static
number of remote logins 20 22 20 20 number static
number of remote sites 10 918 10 10 number static
number of user connections 25 2290 25 25 number dynamic
number of worker processes 0 0 0 0 number dynamic
open index hash spinlock ratio 100 0 100 100 ratio dynamic
open index spinlock ratio 100 0 100 100 ratio dynamic
partition groups 1024 860 1024 1024 number dynamic
partition spinlock ratio 10 0 10 10 ratio dynamic
permission cache entries 15 #196 15 15 number dynamic
plan text pipe max messages 0 0 0 0 number dynamic
procedure cache size 3271 6928 3271 3271 memory pages(2k) dynamic
process wait events 0 0 0 0 switch dynamic
remote server pre-read packets 3 #32 3 3 number static
size of global fixed heap 150 0 150 150 memory pages(2k) dynamic
size of process object heap 1500 0 1500 1500 memory pages(2k) dynamic
size of shared class heap 1536 0 1536 1536 memory pages(2k) dynamic
size of unilib cache 0 120 0 0 bytes dynamic
sql text pipe max messages 0 0 0 0 number dynamic
stack guard size 16384 #1184 16384 16384 bytes static
stack size 41984 #3034 41984 41984 bytes static
statement cache size 0 0 0 0 memory pages(2k) dynamic
statement pipe max messages 0 0 0 0 number dynamic
total data cache size 0 8774 0 8774 kilobytes NULL
total logical memory 33792 36858 18429 18429 memory pages(2k) read-only
total physical memory 0 36858 0 18429 memory pages(2k) read-only
txn to pss ratio 16 856 16 16 number dynamic
user log cache size 2048 0 2048 2048 bytes static
user log cache spinlock ratio 20 0 20 20 ratio dynamic
wait event timing 0 0 0 0 switch dynamic
Group: Meta-Data Caches
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
number of open databases 12 1394 12 12 number dynamic
number of open indexes 500 592 500 500 number dynamic
number of open objects 500 685 500 500 number dynamic
open index hash spinlock ratio 100 0 100 100 ratio dynamic
open index spinlock ratio 100 0 100 100 ratio dynamic
open object spinlock ratio 100 0 100 100 ratio dynamic
partition groups 1024 860 1024 1024 number dynamic
partition spinlock ratio 10 0 10 10 ratio dynamic
Group: Monitoring
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
SQL batch capture 0 0 0 0 switch dynamic
deadlock pipe active 0 0 0 0 switch dynamic
deadlock pipe max messages 0 0 0 0 number dynamic
enable monitoring 0 0 0 0 switch dynamic
errorlog pipe active 0 0 0 0 switch dynamic
errorlog pipe max messages 0 0 0 0 number dynamic
max SQL text monitored 0 4 0 0 bytes static
object lockwait timing 0 0 0 0 switch dynamic
per object statistics active 0 0 0 0 switch dynamic
performance monitoring option 0 0 0 0 switch dynamic
plan text pipe active 0 0 0 0 switch dynamic
plan text pipe max messages 0 0 0 0 number dynamic
process wait events 0 0 0 0 switch dynamic
sql text pipe active 0 0 0 0 switch dynamic
sql text pipe max messages 0 0 0 0 number dynamic
statement pipe active 0 0 0 0 switch dynamic
statement pipe max messages 0 0 0 0 number dynamic
statement statistics active 0 0 0 0 switch dynamic
wait event timing 0 0 0 0 switch dynamic
Group: Network Communication
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
additional network memory 0 0 0 0 bytes dynamic
allow remote access 1 0 1 1 switch dynamic
allow sendmsg 0 0 0 0 switch dynamic
default network packet size 512 #148 512 512 bytes static
max network packet size 512 0 512 512 bytes static
max number network listeners 5 458 5 5 number dynamic
number of remote connections 20 33 20 20 number static
number of remote logins 20 22 20 20 number static
number of remote sites 10 918 10 10 number static
remote server pre-read packets 3 #32 3 3 number static
send doneinproc tokens 1 0 1 1 switch dynamic
syb_sendmsg port number 0 0 0 0 id static
tcp no delay 1 0 1 1 switch static
Group: O/S Resources
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
max async i/os per engine 2147483647 0 2147483647 2147483647 number static
max async i/os per server 2147483647 0 2147483647 2147483647 number static
o/s file descriptors 0 0 0 100000 number read-only
tcp no delay 1 0 1 1 switch static
Group: Parallel Query
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
max parallel degree 1 0 1 1 number dynamic
max scan parallel degree 1 0 1 1 number dynamic
memory per worker process 1024 4 1024 1024 bytes dynamic
number of worker processes 0 0 0 0 number dynamic
Group: Physical Resources
Group: Physical Memory
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
additional network memory 0 0 0 0 bytes dynamic
allocate max shared memory 0 0 0 0 switch dynamic
dynamic allocation on demand 1 0 1 1 switch dynamic
heap memory per user 4096 0 4096 4096 bytes dynamic
lock shared memory 0 0 0 0 switch static
max memory 33792 67584 33792 33792 memory pages(2k) dynamic
shared memory starting address 0 0 0 0 not applicable static
total logical memory 33792 36858 18429 18429 memory pages(2k) read-only
total physical memory 0 36858 0 18429 memory pages(2k) read-only
Group: Processors
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
max online engines 1 113 1 1 number static
number of engines at startup 1 0 1 1 number static
Group: Rep Agent Thread Administration
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
enable rep agent threads 0 0 0 0 switch dynamic
Group: SQL Server Administration
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
SQL Perfmon Integration 1 0 1 1 switch static
abstract plan cache 0 0 0 0 switch dynamic
abstract plan dump 0 0 0 0 switch dynamic
abstract plan load 0 0 0 0 switch dynamic
abstract plan replace 0 0 0 0 switch dynamic
allow backward scans 1 0 1 1 switch dynamic
allow nested triggers 1 0 1 1 switch static
allow resource limits 0 #4 0 0 switch static
allow updates to system tables 0 0 0 0 switch dynamic
cpu accounting flush interval 200 0 200 200 clock ticks dynamic
cpu grace time 500 0 500 500 clock ticks dynamic
deadlock retries 5 0 5 5 number dynamic
default database size 2 0 2 2 megabytes dynamic
default exp_row_size percent 5 0 5 5 percent dynamic
default fill factor percent 0 0 0 0 percent dynamic
enable DTM 0 0 0 0 switch static
enable HA 0 0 0 0 switch static
enable housekeeper GC 1 0 1 1 switch dynamic
enable job scheduler 0 0 0 0 switch dynamic
enable sort-merge join and JTC 0 0 0 0 switch dynamic
enable webservices 0 0 0 0 switch dynamic
enable xml 0 0 0 0 switch dynamic
event buffers per engine 100 #10 100 100 number static
housekeeper free write percent 1 0 1 1 percent dynamic
i/o accounting flush interval 1000 0 1000 1000 number dynamic
i/o batch size 100 0 100 100 number dynamic
i/o polling process count 10 0 10 10 number dynamic
identity burning set factor 5000 0 5000 5000 number static
identity grab size 1 0 1 1 number dynamic
job scheduler interval 1 0 1 1 seconds dynamic
job scheduler tasks 32 0 32 32 number dynamic
license information 25 0 25 25 number dynamic
maximum job output 32768 0 32768 32768 bytes dynamic
number of alarms 40 4 40 40 number dynamic
number of aux scan descriptors 200 #206 200 200 number dynamic
number of histogram steps 20 0 20 20 number dynamic
number of large i/o buffers 6 3112 6 6 number dynamic
number of mailboxes 30 6 30 30 number dynamic
number of messages 64 6 64 64 number dynamic
number of open databases 12 1394 12 12 number dynamic
number of open objects 500 685 500 500 number dynamic
number of pre-allocated extent 2 0 2 2 number dynamic
number of sort buffers 500 0 500 500 number dynamic
page lock promotion HWM 200 0 200 200 number dynamic
page lock promotion LWM 200 0 200 200 number dynamic
page lock promotion PCT 100 0 100 100 percent dynamic
percent database for history 20 0 20 20 percent dynamic
percent database for output 30 0 30 30 percent dynamic
percent history free 30 0 30 30 percent dynamic
percent output free 50 0 50 50 percent dynamic
print deadlock information 0 0 0 0 switch dynamic
procedure cache size 3271 6928 3271 3271 memory pages(2k) dynamic
row lock promotion HWM 200 0 200 200 number dynamic
row lock promotion LWM 200 0 200 200 number dynamic
row lock promotion PCT 100 0 100 100 percent dynamic
runnable process search count 2000 0 2000 2000 number dynamic
sampling percent 0 0 0 0 percent dynamic
size of auto identity column 10 0 10 10 bytes dynamic
sql server clock tick length 100000 0 100000 100000 microseconds static
statement cache size 0 0 0 0 memory pages(2k) dynamic
text prefetch size 16 0 16 16 logical pages dynamic
time slice 100 0 100 100 milliseconds dynamic
upgrade version 12500 0 12500 12500 id dynamic
Group: Security Related
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
allow procedure grouping 1 0 1 1 switch dynamic
audit queue size 100 2 100 100 number dynamic
auditing 0 0 0 0 switch dynamic
check password for digit 0 0 0 0 switch dynamic
curread change w/ open cursors 1 0 1 1 switch dynamic
current audit table 1 0 1 1 id dynamic
enable ldap user auth 0 0 0 0 not applicable dynamic
enable row level access 0 0 0 0 switch dynamic
enable ssl 0 0 0 0 switch static
maximum failed logins 0 0 0 0 number dynamic
minimum password length 6 0 6 6 bytes dynamic
msg confidentiality reqd 0 0 0 0 switch dynamic
msg integrity reqd 0 0 0 0 switch dynamic
secure default login guest 0 guest guest not applicable dynamic
select on syscomments.text 1 0 1 1 switch dynamic
suspend audit when device full 1 0 1 1 switch dynamic
systemwide password expiration 0 0 0 0 days dynamic
unified login required 0 0 0 0 switch dynamic
use security services 0 0 0 0 switch static
Group: Unicode
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
default unicode sortorder binary 0 binary binary name static
enable surrogate processing 1 0 1 1 switch dynamic
enable unicode conversions 0 0 0 0 switch dynamic
enable unicode normalization 1 0 1 1 switch dynamic
size of unilib cache 0 120 0 0 bytes dynamic
Group: User Environment
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------
default network packet size 512 #148 512 512 bytes static
number of user connections 25 2290 25 25 number dynamic
permission cache entries 15 #196 15 15 number dynamic
stack guard size 16384 #1184 16384 16384 bytes static
stack size 41984 #3034 41984 41984 bytes static
user log cache size 2048 0 2048 2048 bytes static
user log cache spinlock ratio 20 0 20 20 ratio dynamic
(return status = 0)
1>
1> sp_dboption
2> go
Settable database options.
database_options
----------------------------
abort tran on log full
allow nulls by default
async log service
auto identity
dbo use only
ddl in tran
disable alias access
identity in nonunique index
no chkpt on recovery
no free space acctg
read only
select into/bulkcopy/pllsort
single user
trunc log on chkpt
trunc. log on chkpt.
unique auto_identity index
(16 rows affected)
(return status = 0)
1> SYB_BACKUP...sp_who
2> go
spid status loginame hostname blk cmd
----------- ---------- ------------------------------ ------------ --- ----------------
0 runnable NULL NULL 0 NULL
2 runnable NULL NULL 0 CONNECT HANDLER
3 sleeping NULL NULL 0 DEFERRED HANDLER
8 runnable NULL NULL 0 SITE HANDLER
9 running sa COMMONOR-5E* 0 NULL
(5 rows affected)
1> sp_reportstats
2> go
Name Since CPU Percent CPU I/O Percent I/O
------------------------------ ----------- ----------- ----------- ----------- -----------
sa Jan 1 2008 0 0.0000% 0 0.0000%
probe Jan 1 2008 0 0.0000% 0 0.0000%
mon_user Jan 1 2008 0 0.0000% 0 0.0000%
gyh9711 Jan 1 2008 0 0.0000% 0 0.0000%
test Jan 1 2008 0 0.0000% 0 0.0000%
(5 rows affected)
Total CPU Total I/O
--------------- ---------------
1.0 1.0
(1 row affected)
(return status = 0)
1> sp_who
2> go
fid spid status loginame origname hostname blk_spid dbname cmd block_xloid
------ ------ ------------ ------------ ------------ ---------- -------- ---------- ---------------- -----------
0 2 sleeping NULL NULL 0 master DEADLOCK TUNE 0
0 3 sleeping NULL NULL 0 master MIRROR HANDLER 0
0 4 sleeping NULL NULL 0 master SHUTDOWN HANDLER 0
0 5 sleeping NULL NULL 0 master ASTC HANDLER 0
0 6 sleeping NULL NULL 0 master CHECKPOINT SLEEP 0
0 7 sleeping NULL NULL 0 master HK WASH 0
0 8 sleeping NULL NULL 0 master HK GC 0
0 9 sleeping NULL NULL 0 master HK CHORES 0
0 10 sleeping NULL NULL 0 master DTC COMMIT SVC 0
0 11 sleeping NULL NULL 0 master PORT MANAGER 0
0 12 sleeping NULL NULL 0 master NETWORK HANDLER 0
0 13 sleeping NULL NULL 0 master HBC HANDLER 0
0 17 running sa sa COMMONOR-5 0 master SELECT 0
(13 rows affected)
(return status = 0)
1> sp_lock 2
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 rows affected)
(return status = 0)
1>
9.数据一致性检查dbcc命令
1> dbcc checktable ("guoyihong..oss")
2> go
Checking guoyihong..oss: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> dbcc checkdb //默认检查当前数据库
2> go
Checking current database: Logical pagesize is 2048 bytes
Checking sysobjects: Logical pagesize is 2048 bytes
The total number of data pages in this table is 2.
Table has 27 data rows.
Checking sysindexes: Logical pagesize is 2048 bytes
The total number of data pages in this table is 5.
Table has 48 data rows.
Checking syscolumns: Logical pagesize is 2048 bytes
The total number of data pages in this table is 11.
Table has 374 data rows.
Checking systypes: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 35 data rows.
Checking sysprocedures: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking syscomments: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking syssegments: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 3 data rows.
Checking syslogs: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own dev
Table has 13 data rows.
Checking sysprotects: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 26 data rows.
Checking sysusers: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 11 data rows.
Checking sysalternates: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking sysdepends: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking syskeys: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 32 data rows.
Checking sysgams: Logical pagesize is 2048 bytes
The total number of data pages in this table is 8.
Checking sysusermessages: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking sysreferences: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking sysconstraints: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking systhresholds: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 1 data rows.
Checking sysroles: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 9 data rows.
Checking sysattributes: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 30 data rows.
Checking syspartitions: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Checking systabstats: Logical pagesize is 2048 bytes
The total number of data pages in this table is 4.
Table has 44 data rows.
Checking sysstatistics: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
The total number of empty pages (with all deleted rows) in this table is 1.
Checking sysxtypes: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
The total number of empty pages (with all deleted rows) in this table is 1.
Checking sysjars: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
The total number of empty pages (with all deleted rows) in this table is 1.
Checking sysqueryplans: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
The total number of empty pages (with all deleted rows) in this table is 1.
Checking oss: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
9. optdiag应用实例
C:/sybase/ASE-12_5/bin>optdiag statistics guoyihong -Usa -Pgyhchair
OptDiag/12.5.1/EBF 11428/P/NT (IX86)/OS 4.0/ase1251/1823/32-bit/OPT/Wed Sep 17 11:30:02 2003
Adaptive Server Enterprise/12.5.1/EBF 11522/P/NT (IX86)/OS 4.0/ase1251/1824/32-bit/OPT/Mon Sep 29 21:41:30 2003
Server name: ""
Specified database: "guoyihong"
Specified table owner: not specified
Specified table: not specified
Specified column: not specified
Table owner: "dbo"
Table name: "oss"
Statistics for table: "oss"
Data page count: 1
Empty data page count: 0
Data row count: 0.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 0.0000000000000000
Data page CR count: 0.0000000000000000
OAM + allocation page count: 0
First extent data pages: 0
Data row size: 55.5000000000000000
Derived statistics:
Data page cluster ratio: 1.0000000000000000
Space utilization: 0.0000000000000000
Large I/O efficiency: 1.0000000000000000
No statistics for remaining columns: "describe"
(default values used) "id"
"name"
"passwd"
"year"
Optdiag succeeded.