Database | Proc | Application | Created | Links |
sybsystemprocs | sp_extendsegment ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */ 3 /* 5.0 14.2 11/12/91 sproc/src/extendsegment */ 4 5 /* 6 ** Messages for "sp_extendsegment" 17550 7 ** 8 ** 17260, "Can't run %1! from within a transaction." 9 ** 17520, "There is no such segment as '%1!'." 10 ** 17281, "The specified device is not used by the database." 11 ** 17283, "'%1!' is reserved exclusively as a log device." 12 ** 17280, "No such device exists -- run sp_helpdb to list the devices for the current database." 13 ** 17550, "Segment extended." 14 ** 17551, "Device '%1!' is now exclusively used by '%2!'" 15 ** 17715, "The last-chance threshold for database %1! is now %2! pages." 16 ** 17716, "Could not update the last-chance threshold for database %1!" 17 ** 17552, "This command has been ignored. Extending the log segment on device '%1!' would leave no space for creating objects in database '%2!'." 18 ** 17590, "The specified database does not exist." 19 ** 17288, "You must execute this procedure from the database 20 ** in which you wish to %1! a segment. Please execute 21 ** 'use %2!' and try again." 22 ** 17289, "Set your curwrite to the hurdle of current database." 23 ** 19572, "A segment with a virtually hashed table exists of device %1!." 24 ** 19571, "You cannot extend a segment with a virtually hashed table on device %1!, because this device has other segments." 25 */ 26 27 create procedure sp_extendsegment 28 @segname varchar(30), /* segment name */ 29 @dbname varchar(30), /* database name */ 30 @devname varchar(30) /* device name to put segment on */ 31 as 32 33 declare @dbid smallint /* id of the database */ 34 declare @segbit int /* this is the bit to turn on in sysusages */ 35 declare @lct int /* log new last-chance threshold value */ 36 declare @msg varchar(1024) 37 declare @new_msg varchar(100) 38 declare @returncode int 39 declare @procval int 40 declare @bitdesc varchar(30) /* bit description for the db */ 41 declare @tempdb_mask int /* All database status bit for a tempdb */ 42 declare @isatempdb int /* Is this a temp. db ? */ 43 declare @isamaster int 44 declare @segmap int /* segment map */ 45 declare @tmp_segmap int 46 declare @segid int /* id of the segment */ 47 declare @status int /* status of the segment */ 48 49 /* 50 ** If we're in a transaction, disallow this since it might make recovery 51 ** impossible. 52 */ 53 if @@trancount > 0 54 begin 55 /* 56 ** 17260, "Can't run %1! from within a transaction." 57 */ 58 raiserror 17260, "sp_extendsegment" 59 return (1) 60 end 61 else 62 begin 63 set chained off 64 end 65 66 set transaction isolation level 1 67 68 /* 69 ** Only the Database Owner (DBO) or 70 ** Accounts with SA role can execute it. 71 ** if user had SA role he would be the dbo hence check only 72 ** whether user is DBO. 73 */ 74 if (user_id() = 1) 75 begin 76 /* If user has sa role audit this as a successful sa 77 ** command execution. 78 */ 79 if charindex("sa_role", show_role()) > 0 80 select @procval = proc_role("sa_role") 81 end 82 else 83 begin 84 /* user_id() is not DBO hence user does not have SA role 85 ** audit this as a failed sa command execution. 86 */ 87 select @procval = proc_role("sa_role") 88 return (1) 89 end 90 91 /* 92 ** Make ure the database exists 93 */ 94 if not exists (select * from master.dbo.sysdatabases 95 where name = @dbname) 96 begin 97 /* 17590, "The specified database does not exist." */ 98 raiserror 17590 99 return (1) 100 end 101 102 /* 103 ** Make sure that we are in the database specified 104 ** by @dbname. 105 */ 106 if @dbname != db_name() 107 begin 108 /* 109 ** 13233, "update" 110 ** 17288, "You must execute this procedure from the database 111 ** in which you wish to %1! a segment. Please execute 112 ** 'use %2!' and try again." 113 */ 114 declare @action varchar(30) 115 select @action = description 116 from master.dbo.sysmessages 117 where error = 13233 and langid = @@langid 118 if @action is null 119 select @action = description 120 from master.dbo.sysmessages 121 where error = 13233 and langid is null 122 if @action is null select @action = "update" 123 raiserror 17288, @action, @dbname 124 return (1) 125 end 126 127 select @dbid = db_id(@dbname) 128 129 /* 130 ** Check to see if the segment exists. 131 */ 132 if not exists (select * 133 from syssegments 134 where name = @segname) 135 begin 136 /* 137 ** 17520, "There is no such segment as '%1!'." 138 */ 139 raiserror 17520, @segname 140 return (1) 141 end 142 143 /* 144 ** See if the device exists. 145 */ 146 if not exists (select * 147 from master.dbo.sysdevices 148 where name like @devname) 149 begin 150 /* 151 ** 17280, "No such device exists -- run sp_helpdb to list the devices for the current database." 152 153 */ 154 raiserror 17280 155 156 157 158 return (1) 159 end 160 161 /* 162 ** Now see if the @dbname uses the @devname 163 */ 164 165 if not exists (select * 166 from master.dbo.sysusages u, master.dbo.sysdevices d 167 where d.name = @devname 168 and u.vdevno = d.vdevno 169 and u.dbid = @dbid) 170 begin 171 /* 172 ** 17281, "The specified device is not used by the database." 173 */ 174 raiserror 17281 175 return (1) 176 end 177 178 /* 179 ** Check to see if the device is marked as a log device. 180 ** If so, print error. 181 */ 182 if exists (select * 183 from master.dbo.sysusages u, master.dbo.sysdevices d 184 where d.name = @devname 185 and u.vdevno = d.vdevno 186 and u.dbid = @dbid 187 and segmap = 4) 188 begin 189 /* 190 ** 17283, "'%1!' is reserved exclusively as a log device." 191 */ 192 raiserror 17283, @devname 193 return (1) 194 end 195 196 /* 197 ** Get segment map of device on which segment is extended. 198 */ 199 select @segmap = segmap 200 from master.dbo.sysusages u, master.dbo.sysdevices d 201 where d.name = @devname 202 and u.vstart between d.low and d.high 203 and u.dbid = db_id(@dbname) 204 select @status = status 205 from syssegments where name = @segname 206 207 /* 208 ** Keep only the status bit regarding whether 209 ** vhash table is there or not on this segment. 210 */ 211 select @status = @status & 2 212 213 if @status = 2 214 begin 215 /* 216 ** vhash table is there on this segment. This is an 217 ** exclusive segment. It can only be extended on a 218 ** device having no other segment. Thus, segmap 219 ** should be 0. 220 */ 221 if @segmap > 0 222 begin 223 /* 224 ** 19571, You cannot extend a segment with a virtually hashed table on device %1!, 225 ** because this device has other segments. 226 */ 227 raiserror 19571, @devname 228 return (1) 229 end 230 end 231 else if @segmap > 0 232 begin 233 /* 234 ** This is not an exclusive segment. Also there 235 ** are other segments on the device. Thus, we 236 ** need to check that none of these is an 237 ** exclusive segment. 238 */ 239 select @tmp_segmap = @segmap - 1 240 select @tmp_segmap = @segmap & @tmp_segmap 241 if @tmp_segmap = 0 242 begin 243 /* 244 ** Only one bit is set in segmap. 245 ** Thus, there is only one segment 246 ** existing on device. Now we need 247 ** to check if that segment has vhash 248 ** table. As if it has a vhash table 249 ** it will be a exclusive segment and 250 ** another segment cannot be created 251 ** on this device. 252 */ 253 select @segid = log(@segmap) / log(2) 254 if exists (select * from syssegments 255 where segment = @segid and 2 = status & 2) 256 begin 257 /* 258 ** 19572, A segment with a virtually hashed table exists of device %1!. 259 */ 260 raiserror 19572, @devname 261 return (1) 262 end 263 end 264 end 265 /* 266 ** Get the segment number for @segname. 267 */ 268 select @segbit = segment 269 from syssegments 270 where name = @segname 271 272 /* 273 ** Determine if we are dealing with a temporary database. 274 */ 275 select @tempdb_mask = number 276 from master.dbo.spt_values 277 where type = "D3" and name = "TEMPDB STATUS MASK" 278 279 if (@dbid = 2) or exists (select * from master.dbo.sysdatabases 280 where dbid = @dbid 281 and (status3 & @tempdb_mask) != 0) 282 begin 283 select @isatempdb = 1 284 end 285 else 286 begin 287 select @isatempdb = 0 288 end 289 290 select @isamaster = 0 291 if ((@dbid = db_id("master")) 292 or (@dbid = db_id("master_companion"))) 293 begin 294 select @isamaster = 1 295 end 296 297 298 299 /* Encapsulate sysusages/anchor update in a transaction */ 300 if (@isamaster = 1) 301 begin transaction sysusg_upd 302 303 /* 304 ** If @segbit is 2 then it's the logsegment and the logsegment 305 ** doesn't share devices with other segments. In this case, 306 ** don't OR the @segbit in but just set segmap to 4. 307 ** Also, if we are extending the log space and this is not 308 ** a temporary database ... 309 */ 310 if @segbit = 2 and @isatempdb = 0 311 begin 312 /* 313 ** ... check single user bit (4096). 314 ** Database must be in single user mode so that no log allocation 315 ** or deallocation take place while recalculing the free space counter 316 */ 317 select @bitdesc = null 318 select @bitdesc = v.name 319 from master.dbo.spt_values v, master.dbo.sysdatabases d 320 where d.dbid = @dbid 321 and v.type = "D" 322 and d.status & v.number = 4096 323 if @bitdesc is null 324 begin 325 /* 326 ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'." 327 */ 328 raiserror 17793, @dbname, "sp_extendsegment" 329 return (1) 330 end 331 332 /* 333 ** Extending the logsegment means we won't be able to 334 ** create any more objects on this device. (See comment above.) 335 ** Do it only if non-log space is available on other devices. 336 */ 337 if not exists (select * 338 from master.dbo.sysdevices d, 339 master.dbo.sysusages u 340 where u.vdevno = d.vdevno 341 and u.dbid = @dbid 342 and u.segmap != 4 343 and d.name != @devname 344 and d.cntrltype = 0) 345 begin 346 /* 347 ** 17552, "This command has been ignored. Extending the log 348 ** segment on device '%1!' would leave no space for creating 349 ** objects in database '%2!'." 350 */ 351 raiserror 17552, @devname, @dbname 352 return (1) 353 end 354 355 update master.dbo.sysusages 356 set segmap = 4 357 from master.dbo.sysusages u, 358 master.dbo.sysdevices d 359 where d.name = @devname 360 and u.vdevno = d.vdevno 361 and u.dbid = @dbid 362 end 363 364 else 365 begin 366 if (@segbit < 31) 367 select @segbit = power(2, @segbit) 368 else 369 /* 370 ** Since this is segment 31, power(2, 31) will 371 ** overflow since segmap is an int. We'll grab the 372 ** machine-dependent bit mask from spt_values to set 373 ** the right bit. 374 */ 375 select @segbit = low 376 from master.dbo.spt_values 377 where type = "E" 378 and number = 2 379 /* 380 ** Add the segment to @devname in sysusages. 381 */ 382 update master.dbo.sysusages 383 set segmap = segmap | @segbit 384 from master.dbo.sysusages u, 385 master.dbo.sysdevices d 386 where d.name = @devname 387 and u.vdevno = d.vdevno 388 and u.dbid = @dbid 389 end 390 391 /* End the transaction */ 392 if (@isamaster = 1) 393 begin 394 if (@@error != 0) 395 begin 396 rollback transaction sysusg_upd 397 return (1) 398 end 399 400 dbcc dbrepair(@dbname, "upd_usg") 401 if (@@error != 0) 402 begin 403 rollback transaction sysusg_upd 404 return (1) 405 end 406 407 commit transaction sysusg_upd 408 end 409 410 /* 411 ** Get the segment number for @segname. 412 */ 413 select @segbit = segment 414 from syssegments 415 where name = @segname 416 417 /* 418 ** Now we need to activate the new segment map. 419 */ 420 dbcc dbrepair(@dbname, remap, NULL, - 1, @segname) 421 422 /* 423 ** If we are adding log space, we might be going onto a segment that 424 ** used to contain user data, so we need to look for stranded objects. 425 */ 426 if @segbit = 2 427 begin 428 dbcc dbrepair(@dbname, findstranded, NULL, @isatempdb) 429 end 430 431 /* 432 ** Now that dbrepair has remapped the database, we can 433 ** recalculate the log's last-chance threshold. 434 */ 435 if @segbit = 2 436 begin 437 select @lct = lct_admin("lastchance", @dbid) 438 if @lct > 0 439 begin 440 exec sp_getmessage 17715, @msg out 441 print @msg, @dbname, @lct 442 end 443 else 444 begin 445 raiserror 17716, @dbname, @lct 446 end 447 end 448 449 /* 450 ** 17550, "Segment extended." 451 */ 452 exec sp_getmessage 17550, @msg output 453 print @msg 454 455 /* 456 ** If we are extending the logsegment, print out a message about what it 457 ** means to do so. 458 */ 459 if @segbit = 2 460 begin 461 /* 462 ** 17551, "Device '%1!' is now exclusively used by '%2!'" 463 */ 464 exec sp_getmessage 17551, @msg output 465 print @msg, @devname, @segname 466 end 467 468 return (0) 469
exec sp_procxmode 'sp_extendsegment', 'AnyMode' go Grant Execute on sp_extendsegment to public go
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysusages (1) ![]() reads table sybsystemprocs..syssegments ![]() calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table master..sysdatabases (1) ![]() reads table master..spt_values (1) ![]() reads table master..sysdevices (1) ![]() reads table master..sysmessages (1) ![]() |