Sybase

Sybase

Поленые ф-и

Информация по базе

sp_helpdb db_name

Место в базе:

sp_spaceused

See also Commands create database, disk init, drop database, load database System procedures sp_addsegment, sp_dropsegment, sp_helpdb, sp_helpsegment, sp_logdevice, sp_renamedb, sp_spaceused

Создать "утройство" (файл или утройство для данных)

disk init
   name = "cmm_db_data_2",
   physname = "/sybase_devices/cmm_db_data_2.dat",
   size = "5000M"

Добавить это устройство к базе.

 
ALTER database cmm_db  on cmm_db_data_2 = "5000M"

Размеры устройств:

select 
t1.name,
t1.phyname,
t1.status,
t2.lstart,
t2.size,
t2.unreservedpgs
from 
master..sysdevices t1
inner join 
master..sysusages t2 on t1.vdevno = t2.vdevno
--where
--dbid =db_id('audit_db')
order by 1,2,4

Изменение размера устройства:

disk resize name='data_device',
size='10G',
skip_alloc=false

Размеры таблиц

CREATE PROCEDURE dbo.CalcFreeSpace
AS
    BEGIN
    DECLARE @CurrentTableName NVARCHAR(100)
  
CREATE TABLE #TableList
  (TableName NVARCHAR(100)
   , RowsCount INT
   , KBReserved VARCHAR(15)
   , KBData VARCHAR(15)
   , KBIndex VARCHAR(15)
   , KBUnused VARCHAR(15)
   )
  
-- Run a cursor through all of the tables
DECLARE result_cursor CURSOR FOR
SELECT  name  FROM dbo.sysobjects WHERE type = 'U'
  
OPEN result_cursor
FETCH NEXT FROM result_cursor INTO @CurrentTableName
WHILE @@FETCH_STATUS = 0
BEGIN
   exec sp_spaceused @CurrentTableName
FETCH NEXT FROM result_cursor INTO @CurrentTableName
END
--end loop
  
--clean up
CLOSE result_cursor
DEALLOCATE result_cursor

--UPDATE #TableList
--  SET KBReserved = REPLACE(KBReserved, ' KB', '')
 --   , KBData = REPLACE(KBData, ' KB', '')
  --  , KBIndex = REPLACE(KBIndex, ' KB', '')
  --  , KBUnused = REPLACE(KBUnused, ' KB', '')
  
/* Adaptive Server has expanded all '*' elements in the following statement */ 
SELECT 
#TableList.TableName, #TableList.RowsCount, #TableList.KBReserved, #TableList.KBData, #TableList.KBIndex, #TableList.KBUnused 
FROM 
#TableList 
ORDER BY TableName
DROP TABLE #TableList    
    END

Дамп и восстановление

Для дампа и восстановления ТАБЛИЦ используется утилита bcp .
Подробнее почитать тут http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/10086;pt=8630/*

Для дампа базы целиком используется isql:

isql
1> dump database cmm_db
2> to "./cmm_db.dump"
3> go
Backup Server session id is:  70.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /DBAPP1/sybase/./cmm_db.dump.
Backup Server: 6.28.1.1: Dumpfile name 'cmm_db111080E7CC ' section number 1
mounted on disk file '/DBAPP1/sybase/./cmm_db.dump'
Backup Server: 4.188.1.1: Database cmm_db: 105464 kilobytes (1%) DUMPED.
...
Backup Server: 4.188.1.1: Database cmm_db: 8601706 kilobytes (73%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database cmm_db: 8663424 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database cmm_db).

При этом дамп будет сохранен на сервере БД а не на том хосте где запускался isql. Обратить внимание на права файла, ина компрессию (если с местом проблема.)

Для загрузки, соответвенно

load database test
from "/path/to/dump/test.dump"

Размеры объектов (исправить

select	o.name name,
	sum(convert(int, rowcnt(i.doampg))) rownum,
	sum(convert(int, reserved_pgs(i.id, i.doampg) +
		reserved_pgs(i.id, i.ioampg)) *
		(d.low / 1024)) reserved_kb,
	sum(convert(int, data_pgs(i.id, i.doampg)) * (d.low / 1024)) data_kb,
	sum(convert(int, data_pgs(i.id, i.ioampg)) * (d.low / 1024)) index_kb,
	sum(convert(int, reserved_pgs(i.id, i.doampg) +
			reserved_pgs(i.id, i.ioampg) -
			data_pgs(i.id, i.doampg) - data_pgs(i.id, i.ioampg))
		* (d.low / 1024)) unused_kb
from	sysobjects o, sysindexes i, master.dbo.spt_values d
where	i.id = o.id
	and d.number = 1 and d.type = "E"
group by o.name
order by reserved_kb desc

Ссылки

• http://blog.sybdoc.com/