Welche Sybase devices beinhaltet welche Table

- Anzeigen welche sybase Device sind in der Datenbank vorhanden?
1> sp_helpdb SAS

2> go

 name                     db_size       owner      dbid   created        status 
 ------------------------ ------------- ---------- ------ -------------- -----------------
 SAS                        117712.0 MB sa       4 Mar 14, 2007   select into/bulkcopy/pllsort,
trunc log on chkpt, abort tran on log full  

(1 row affected)

 device_fragments               size          usage                free kbytes      
 ------------------------------ ------------- -------------------- -------------
[……]
applicable   
 saslog                         2048.0 MB     log only             not applicable 
 tempdevice7                    6144.0 MB     data only                     6291456                                                              

 -------------------------------------------------------------- 
 log only free kbytes = 4128958   
 device                         segment                                                                                   
 ------------------------------ ------------------------------------------------
[…]
 sasindex3                      indexsegment                                                                               
 sasindex4                      indexsegment                                                                               
 saslog                         logsegment                                                                                 
 tempdevice7                    default                                                                                    

- Welche sybase Segmente sind vorhanden?

1> use SAS

2> go

1> sp_helpsegment

2> go

 segment name                           status 
 ------- ------------------------------ ------ 
       0 system                              0 
       1 default                             1 
       2 logsegment                          0 
       3 indexsegment                        0 
       4 datasegment                         0 
(return status = 0)

- Welche Table beinhaltet das Segment "datasegment"

1> sp_helpsegment datasegment

2> go

 segment name                           status 
 ------- ------------------------------ ------ 
       4 datasegment                         0 
 device                         size                   free_pages  
 ------------------------------ ---------------------- ----------- 
 sasdata11                      5120.0MB                   2434213 
 sasdata2                       16384.0MB                   116282 
 sasdata6                       2048.0MB                    319322 
 sasdata7                       5120.0MB                     27342 
 sasdata8                       5120.0MB                     32830 
 sasdata9                       5120.0MB                     40231 

 table_name                     index_name                     indid  
 ------------------------------ ------------------------------ ------ 
 T_EXTREME_MARKET_MOVEMENTS     T_EXTREME_MARKET_MOVEMENTS          0 
 T_EXTREME_MARKET_MOVEMENTS     tT_EXTREME_MARKET_MOVEMENTS       255 
 dataset                        dataset                             0 
 ipo                            ipo                                 0 
 ipo_default                    ipo_default                         0 
 log_return_20100910            log_return_20100910                 0 
 log_return_20100913            log_return_20100913                 0 
 log_return_20100914            log_return_20100914                 0 
 log_return_20100915            log_return_20100915                 0 
 log_return_20100916            log_return_20100916                 0 
 log_return_20100917            log_return_20100917                 0 
 log_return_20100920            log_return_20100920                 0 
 log_return_20100921            log_return_20100921                 0 
 log_return_20100922            log_return_20100922                 0
 ping                           ping                                0 
 proxy_main                     proxy_main                          0 
 ref                            ref                                 0 
 ref_shift                      ref_shift                           0 
 shift                          shift                               0
 synonym_request                synonym_request                     0 
 vola                           vola                                0 

 total_size             total_pages free_pages  used_pages 
 ---------------------- ----------- ----------- ----------- 
 38912.0MB                 19922944     2970220    16952724 
(return status = 0)

- Erstellen einer Temporäre Segment "tmpseg" und hinzufügen des "tempdevice7"

1> sp_addsegment tmpseg,SAS,tempdevice7

2> go

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.                                                                                                                                                     
(return status = 0)

1> sp_helpsegment

2> go

 segment name                           status 
 ------- ------------------------------ ------ 
       0 system                              0 
       1 default                             1 
       2 logsegment                          0 
       3 indexsegment                        0 
       4 datasegment                         0 
       5 tmpseg                              0 
(return status = 0)

- welche Table beinhaltet das "tmpseg", in unseren beispiel, ist keine Table vorhanden.

1> sp_helpsegment tmpseg

2> go

 segment name                           status 
 ------- ------------------------------ ------ 
       5 tmpseg                              0 

 device                         size                   free_pages  
 ------------------------------ ---------------------- ----------- 
 tempdevice7                    6144.0MB                   3133440 

 total_size             total_pages free_pages  used_pages  
 ---------------------- ----------- ----------- ----------- 
 6144.0MB                   3145728     3133440       12288 

(return status = 0)

-  dropen der "tmpseg" Segment.

1> sp_dropsegment tmpseg,SAS

2> go

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment dropped.
WARNING: There are no longer any segments referencing device 'tempdevice7'.
This device will no longer be used for space allocation.
(return status = 0)