Category: SQL

oracle storage blocks

Oracle stored data in blocks , blocks are usually 8k in size , you can change this , but it’s best to leave this as the default size. Blocks make up extents and extents make up segments which is the primary unit while working with partitions and tables.

Blocks consider headers and as expected is located at the start of the block and row data which starts at the bottom and works its way back up.

PCTFREE is associated with how much of the space in the block can be used before it is considered full. its purpose is to reserve free space for future updates to the row. This ensures that there is no row migration when updates happen.

ROWID defines how the database has to look up a row, it consists of the data object number

sp_help

one of the ways you can quickly lookup the stored procedure definition in SQL server is using sp_helpText procname . This will print lines each with 255 character that show the code for the stored procedure

if you are trying from a remote machine try this instead 

EXEC  [ServerName].[DatabaseName].dbo.sp_HelpText 'ProcName'

Another help stored procedure is sp_help . You can inspect a table by running this and passing a table name as a parameter

note when you drag and drop in ssms it may give you a syntax like this

[procfwk].[AlertOutcomes]

you want to change it to [procfwk.AlertOutcomes] for it to work

sp_help [procfwk.AlertOutcomes]

this gives a detailed layout of the table

you can also find out dependencies using sp_depends

sp_depends [procfwk.AlertOutcomes]

sp_who or sp_who2 shows all , but sp_who active shows active users