using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;
using System.Reflection;
using System.ComponentModel;
using Harlinn.Common;
namespace Harlinn.DBTool.DataSources.Oracle.DB
{
public class TableReader : Reader
{
private static readonly log4net.ILog sfLog = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
private static void LogException(Exception exc, System.Reflection.MethodBase method)
{
Logger.LogException(sfLog, exc, method);
}
public const string FULL_SELECT = "SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES, CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY, NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ROW_MOVEMENT, GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED, READ_ONLY, SEGMENT_CREATED, RESULT_CACHE FROM ALL_TABLES";
public const int OWNER = 0;
public const int TABLE_NAME = 1;
public const int TABLESPACE_NAME = 2;
public const int CLUSTER_NAME = 3;
public const int IOT_NAME = 4;
public const int STATUS = 5;
public const int PCT_FREE = 6;
public const int PCT_USED = 7;
public const int INI_TRANS = 8;
public const int MAX_TRANS = 9;
public const int INITIAL_EXTENT = 10;
public const int NEXT_EXTENT = 11;
public const int MIN_EXTENTS = 12;
public const int MAX_EXTENTS = 13;
public const int PCT_INCREASE = 14;
public const int FREELISTS = 15;
public const int FREELIST_GROUPS = 16;
public const int LOGGING = 17;
public const int BACKED_UP = 18;
public const int NUM_ROWS = 19;
public const int BLOCKS = 20;
public const int EMPTY_BLOCKS = 21;
public const int AVG_SPACE = 22;
public const int CHAIN_CNT = 23;
public const int AVG_ROW_LEN = 24;
public const int AVG_SPACE_FREELIST_BLOCKS = 25;
public const int NUM_FREELIST_BLOCKS = 26;
public const int DEGREE = 27;
public const int INSTANCES = 28;
public const int CACHE = 29;
public const int TABLE_LOCK = 30;
public const int SAMPLE_SIZE = 31;
public const int LAST_ANALYZED = 32;
public const int PARTITIONED = 33;
public const int IOT_TYPE = 34;
public const int TEMPORARY = 35;
public const int SECONDARY = 36;
public const int NESTED = 37;
public const int BUFFER_POOL = 38;
public const int FLASH_CACHE = 39;
public const int CELL_FLASH_CACHE = 40;
public const int ROW_MOVEMENT = 41;
public const int GLOBAL_STATS = 42;
public const int USER_STATS = 43;
public const int DURATION = 44;
public const int SKIP_CORRUPT = 45;
public const int MONITORING = 46;
public const int CLUSTER_OWNER = 47;
public const int DEPENDENCIES = 48;
public const int COMPRESSION = 49;
public const int COMPRESS_FOR = 50;
public const int DROPPED = 51;
public const int READ_ONLY = 52;
public const int SEGMENT_CREATED = 53;
public const int RESULT_CACHE = 54;
public TableReader( )
: base(CreateReader())
{
}
public TableReader(string tableOwner)
: base(CreateReader(tableOwner))
{
}
public TableReader(string tableOwner, string tableName)
: base(CreateReader(tableOwner, tableName))
{
}
public TableReader(OracleDataReader dataReader)
: base(dataReader)
{
}
private static OracleDataReader CreateReader()
{
try
{
OracleConnection connection = ConnectionManager.Connection;
OracleCommand command = connection.CreateCommand();
using (command)
{
command.CommandText = FULL_SELECT + " ORDER BY OWNER, TABLE_NAME";
OracleDataReader result = command.ExecuteReader(CommandBehavior.SingleResult);
return result;
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
private static OracleDataReader CreateReader(string tableOwner)
{
try
{
OracleConnection connection = ConnectionManager.Connection;
OracleCommand command = connection.CreateCommand();
using (command)
{
command.CommandText = FULL_SELECT + " WHERE OWNER = :tableOwner ORDER BY TABLE_NAME";
command.Parameters.Add(":tableOwner", OracleDbType.Varchar2).Value = tableOwner;
OracleDataReader result = command.ExecuteReader(CommandBehavior.SingleResult);
return result;
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
private static OracleDataReader CreateReader(string tableOwner, string tableName)
{
try
{
OracleConnection connection = ConnectionManager.Connection;
OracleCommand command = connection.CreateCommand();
using (command)
{
command.CommandText = FULL_SELECT + " WHERE OWNER = :tableOwner AND TABLE_NAME = :tableName";
command.Parameters.Add(":tableOwner", OracleDbType.Varchar2).Value = tableOwner;
command.Parameters.Add(":tableName", OracleDbType.Varchar2).Value = tableName;
OracleDataReader result = command.ExecuteReader(CommandBehavior.SingleResult);
return result;
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public string Owner
{
get
{
try
{
string result = GetString(OWNER);
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
public string Name
{
get
{
try
{
string result = GetString(TABLE_NAME);
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
//TABLESPACE_NAME VARCHAR2(30)
[Description("Name of the tablespace containing the table")]
[DisplayName("Tablespace Name")]
public string TablespaceName
{
get
{
if (IsDBNull(TABLESPACE_NAME) == false)
{
return GetString(TABLESPACE_NAME);
}
return null;
}
}
//CLUSTER_NAME VARCHAR2(30)
[Description("Name of the cluster, if any, to which the table belongs")]
[DisplayName("Cluster Name")]
public string ClusterName
{
get
{
if (IsDBNull(CLUSTER_NAME) == false)
{
return GetString(CLUSTER_NAME);
}
return null;
}
}
//IOT_NAME VARCHAR2(30)
[Description("Name of the index-only table, if any, to which the overflow or mapping table entry belongs")]
[DisplayName("IOT Name")]
public string IOTName
{
get
{
if (IsDBNull(IOT_NAME) == false)
{
return GetString(IOT_NAME);
}
return null;
}
}
//STATUS VARCHAR2(8)
[Description("Status of the table will be UNUSABLE if a previous DROP TABLE operation failed, VALID otherwise")]
[DisplayName("Status")]
public string Status
{
get
{
if (IsDBNull(STATUS) == false)
{
return GetString(STATUS);
}
return null;
}
}
//PCT_FREE NUMBER
[Description("Minimum percentage of free space in a block")]
[DisplayName("% Free")]
public double PctFree
{
get
{
if (IsDBNull(PCT_FREE) == false)
{
return Convert.ToDouble(GetDecimal(PCT_FREE));
}
return -1;
}
}
//PCT_USED NUMBER
[Description("Minimum percentage of used space in a block")]
[DisplayName("% Used")]
public double? PctUsed
{
get
{
if (IsDBNull(PCT_USED) == false)
{
return Convert.ToDouble(GetDecimal(PCT_USED));
}
return null;
}
}
//INI_TRANS NUMBER
[Description("Initial number of transactions")]
[DisplayName("Ini Trans")]
public int IniTrans
{
get
{
if (IsDBNull(INI_TRANS) == false)
{
return Convert.ToInt32(GetDecimal(INI_TRANS));
}
return -1;
}
}
//MAX_TRANS NUMBER
[Description("Maximum number of transactions")]
[DisplayName("Max Trans")]
public int MaxTrans
{
get
{
if (IsDBNull(MAX_TRANS) == false)
{
return Convert.ToInt32(GetDecimal(MAX_TRANS));
}
return -1;
}
}
//INITIAL_EXTENT NUMBER
[Description("Size of the initial extent in bytes")]
[DisplayName("Initial Extent")]
public int InitialExtent
{
get
{
if (IsDBNull(INITIAL_EXTENT) == false)
{
return Convert.ToInt32(GetDecimal(INITIAL_EXTENT));
}
return -1;
}
}
//NEXT_EXTENT NUMBER
[Description("Size of secondary extents in bytes")]
[DisplayName("Next Extent")]
public int NextExtent
{
get
{
if (IsDBNull(NEXT_EXTENT) == false)
{
return Convert.ToInt32(GetDecimal(NEXT_EXTENT));
}
return -1;
}
}
//MIN_EXTENTS NUMBER
[Description("Minimum number of extents allowed in the segment")]
[DisplayName("Min Extents")]
public int MinExtents
{
get
{
if (IsDBNull(MIN_EXTENTS) == false)
{
return Convert.ToInt32(GetDecimal(MIN_EXTENTS));
}
return -1;
}
}
//MAX_EXTENTS NUMBER
[Description("Maximum number of extents allowed in the segment")]
[DisplayName("MaxExtents")]
public int MaxExtents
{
get
{
if (IsDBNull(MAX_EXTENTS) == false)
{
return Convert.ToInt32(GetDecimal(MAX_EXTENTS));
}
return -1;
}
}
//PCT_INCREASE NUMBER
[Description("Percentage increase in extent size")]
[DisplayName("% Increase")]
public double? PctIncrease
{
get
{
if (IsDBNull(PCT_INCREASE) == false)
{
return Convert.ToDouble(GetDecimal(PCT_INCREASE));
}
return null;
}
}
//FREELISTS NUMBER
[Description("Number of process freelists allocated in this segment")]
[DisplayName("Freelists")]
public int? Freelists
{
get
{
if (IsDBNull(FREELISTS) == false)
{
return Convert.ToInt32(GetDecimal(FREELISTS));
}
return null;
}
}
//FREELIST_GROUPS NUMBER
[Description("Number of freelist groups allocated in this segment")]
[DisplayName("Freelist Groups")]
public int? FreelistGroups
{
get
{
if (IsDBNull(FREELIST_GROUPS) == false)
{
return Convert.ToInt32(GetDecimal(FREELIST_GROUPS));
}
return null;
}
}
//LOGGING VARCHAR2(3)
[Description("Logging attribute")]
[DisplayName("Logging")]
public bool Logging
{
get
{
if (IsDBNull(LOGGING) == false)
{
return GetString(LOGGING).ToUpper() == "YES";
}
return false;
}
}
//BACKED_UP VARCHAR2(1)
[Description("Has table been backed up since last modification?")]
[DisplayName("Backed Up")]
public bool BackedUp
{
get
{
if (IsDBNull(BACKED_UP) == false)
{
return GetString(BACKED_UP).ToUpper() == "Y";
}
return false;
}
}
//NUM_ROWS NUMBER
[Description("The number of rows in the table")]
[DisplayName("Num Rows")]
public int NumRows
{
get
{
if (IsDBNull(NUM_ROWS) == false)
{
return Convert.ToInt32(GetDecimal(NUM_ROWS));
}
return -1;
}
}
//BLOCKS NUMBER
[Description("The number of used blocks in the table")]
[DisplayName("Blocks")]
public int Blocks
{
get
{
if (IsDBNull(BLOCKS) == false)
{
return Convert.ToInt32(GetDecimal(BLOCKS));
}
return -1;
}
}
//EMPTY_BLOCKS NUMBER
[Description("The number of empty (never used) blocks in the table")]
[DisplayName("Empty Blocks")]
public int EmptyBlocks
{
get
{
if (IsDBNull(EMPTY_BLOCKS) == false)
{
return Convert.ToInt32(GetDecimal(EMPTY_BLOCKS));
}
return -1;
}
}
//AVG_SPACE NUMBER
[Description("The average available free space in the table")]
[DisplayName("Avg. Space")]
public int AvgSpace
{
get
{
if (IsDBNull(AVG_SPACE) == false)
{
return Convert.ToInt32(GetDecimal(AVG_SPACE));
}
return -1;
}
}
//CHAIN_CNT NUMBER
[Description("The number of chained rows in the table")]
[DisplayName("Chain Count")]
public int ChainCount
{
get
{
if (IsDBNull(CHAIN_CNT) == false)
{
return Convert.ToInt32(GetDecimal(CHAIN_CNT));
}
return -1;
}
}
//AVG_ROW_LEN NUMBER
[Description("The average row length, including row overhead")]
[DisplayName("Avg. Row Length")]
public int AvgRowLen
{
get
{
if (IsDBNull(AVG_ROW_LEN) == false)
{
return Convert.ToInt32(GetDecimal(AVG_ROW_LEN));
}
return -1;
}
}
//AVG_SPACE_FREELIST_BLOCKS NUMBER
[Description("The average freespace of all blocks on a freelist")]
[DisplayName("Avg. Space Freelist Blocks")]
public int AvgSpaceFreelistBlocks
{
get
{
if (IsDBNull(AVG_SPACE_FREELIST_BLOCKS) == false)
{
return Convert.ToInt32(GetDecimal(AVG_SPACE_FREELIST_BLOCKS));
}
return -1;
}
}
//NUM_FREELIST_BLOCKS NUMBER
[Description("The number of blocks on the freelist")]
[DisplayName("Num. Freelist Blocks")]
public int NumFreelistBlocks
{
get
{
if (IsDBNull(NUM_FREELIST_BLOCKS) == false)
{
return Convert.ToInt32(GetDecimal(NUM_FREELIST_BLOCKS));
}
return -1;
}
}
//DEGREE VARCHAR2(10)
[Description("The number of threads per instance for scanning the table")]
[DisplayName("Degree")]
public string Degree
{
get
{
if (IsDBNull(DEGREE) == false)
{
return GetString(DEGREE).Trim();
}
return null;
}
}
//INSTANCES VARCHAR2(10)
[Description("The number of instances across which the table is to be scanned")]
[DisplayName("Instances")]
public string Instances
{
get
{
if (IsDBNull(INSTANCES) == false)
{
return GetString(INSTANCES).Trim();
}
return null;
}
}
//CACHE VARCHAR2(5)
[Description("Whether the table is to be cached in the buffer cache")]
[DisplayName("Cache")]
public string Cache
{
get
{
if (IsDBNull(CACHE) == false)
{
return GetString(CACHE).Trim();
}
return null;
}
}
//TABLE_LOCK VARCHAR2(8)
[Description("Whether table locking is enabled or disabled")]
[DisplayName("Table Lock")]
public string TableLock
{
get
{
if (IsDBNull(TABLE_LOCK) == false)
{
return GetString(TABLE_LOCK);
}
return null;
}
}
//SAMPLE_SIZE NUMBER
[Description("The sample size used in analyzing this table")]
[DisplayName("Sample Size")]
public int SampleSize
{
get
{
if (IsDBNull(SAMPLE_SIZE) == false)
{
return Convert.ToInt32(GetDecimal(SAMPLE_SIZE));
}
return -1;
}
}
//LAST_ANALYZED DATE
[Description("The date of the most recent time this table was analyzed")]
[DisplayName("Last Analyzed")]
public DateTime LastAnalyzed
{
get
{
if (IsDBNull(LAST_ANALYZED) == false)
{
return GetDateTime(LAST_ANALYZED);
}
return DateTime.MinValue;
}
}
//PARTITIONED VARCHAR2(3)
[Description("Is this table partitioned?")]
[DisplayName("Partitioned")]
public bool Partitioned
{
get
{
if (IsDBNull(PARTITIONED) == false)
{
return GetString(PARTITIONED).ToUpper() == "YES";
}
return false;
}
}
//IOT_TYPE VARCHAR2(12)
[Description("If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL")]
[DisplayName("IOT Type")]
public string IOTType
{
get
{
if (IsDBNull(IOT_TYPE) == false)
{
return GetString(IOT_TYPE);
}
return null;
}
}
//TEMPORARY VARCHAR2(1)
[Description("Can the current session only see data that it place in this object itself?")]
[DisplayName("Temporary")]
public bool Temporary
{
get
{
if (IsDBNull(TEMPORARY) == false)
{
return GetString(TEMPORARY).ToUpper() == "Y";
}
return false;
}
}
//SECONDARY VARCHAR2(1)
[Description("Is this table object created as part of icreate for domain indexes?")]
[DisplayName("Secondary")]
public bool Secondary
{
get
{
if (IsDBNull(SECONDARY) == false)
{
return GetString(SECONDARY).ToUpper() == "Y";
}
return false;
}
}
//NESTED VARCHAR2(3)
[Description("Is the table a nested table?")]
[DisplayName("Nested")]
public bool Nested
{
get
{
if (IsDBNull(NESTED) == false)
{
return GetString(NESTED).ToUpper() == "Y";
}
return false;
}
}
//BUFFER_POOL VARCHAR2(7)
[Description("The default buffer pool to be used for table blocks")]
[DisplayName("Buffer Pool")]
public string BufferPool
{
get
{
if (IsDBNull(BUFFER_POOL) == false)
{
return GetString(BUFFER_POOL);
}
return null;
}
}
//FLASH_CACHE VARCHAR2(7)
[Description("The default flash cache hint to be used for table blocks")]
[DisplayName("Flash Cache")]
public string FlashCache
{
get
{
if (IsDBNull(FLASH_CACHE) == false)
{
return GetString(FLASH_CACHE);
}
return null;
}
}
//CELL_FLASH_CACHE VARCHAR2(7)
[Description("The default cell flash cache hint to be used for table blocks")]
[DisplayName("Cell Flash Cache")]
public string CellFlashCache
{
get
{
if (IsDBNull(CELL_FLASH_CACHE) == false)
{
return GetString(CELL_FLASH_CACHE);
}
return null;
}
}
//ROW_MOVEMENT VARCHAR2(8)
[Description("Whether partitioned row movement is enabled or disabled")]
[DisplayName("Row Movement")]
public string RowMovement
{
get
{
if (IsDBNull(ROW_MOVEMENT) == false)
{
return GetString(ROW_MOVEMENT);
}
return null;
}
}
//GLOBAL_STATS VARCHAR2(3)
[Description("Are the statistics calculated without merging underlying partitions?")]
[DisplayName("Global Stats")]
public bool GlobalStats
{
get
{
if (IsDBNull(GLOBAL_STATS) == false)
{
return GetString(GLOBAL_STATS).ToUpper() == "YES";
}
return false;
}
}
//USER_STATS VARCHAR2(3)
[Description("Were the statistics entered directly by the user?")]
[DisplayName("User Stats")]
public bool UserStats
{
get
{
if (IsDBNull(USER_STATS) == false)
{
return GetString(USER_STATS).ToUpper() == "YES";
}
return false;
}
}
//DURATION VARCHAR2(15)
[Description("If temporary table, then duration is sys$session or sys$transaction else NULL")]
[DisplayName("Duration")]
public string Duration
{
get
{
if (IsDBNull(DURATION) == false)
{
return GetString(DURATION);
}
return null;
}
}
//SKIP_CORRUPT VARCHAR2(8)
[Description("Whether skip corrupt blocks is enabled or disabled")]
[DisplayName("Skip Corrupt")]
public string SkipCorrupt
{
get
{
if (IsDBNull(SKIP_CORRUPT) == false)
{
return GetString(SKIP_CORRUPT);
}
return null;
}
}
//MONITORING VARCHAR2(3)
[Description("Should we keep track of the amount of modification?")]
[DisplayName("Monitoring")]
public bool Monitoring
{
get
{
if (IsDBNull(MONITORING) == false)
{
return GetString(MONITORING).ToUpper() == "YES";
}
return false;
}
}
//CLUSTER_OWNER VARCHAR2(30)
[Description("Owner of the cluster, if any, to which the table belongs")]
[DisplayName("Cluster Owner")]
public string ClusterOwner
{
get
{
if (IsDBNull(CLUSTER_OWNER) == false)
{
return GetString(CLUSTER_OWNER);
}
return null;
}
}
//DEPENDENCIES VARCHAR2(8)
[Description("Should we keep track of row level dependencies?")]
[DisplayName("Dependencies")]
public string Dependencies
{
get
{
if (IsDBNull(DEPENDENCIES) == false)
{
return GetString(DEPENDENCIES);
}
return null;
}
}
//COMPRESSION VARCHAR2(8)
[Description("Whether table compression is enabled or not")]
[DisplayName("Compression")]
public string Compression
{
get
{
if (IsDBNull(COMPRESSION) == false)
{
return GetString(COMPRESSION);
}
return null;
}
}
//COMPRESS_FOR VARCHAR2(12)
[Description("Compress what kind of operations")]
[DisplayName("Compress For")]
public string CompressFor
{
get
{
if (IsDBNull(COMPRESS_FOR) == false)
{
return GetString(COMPRESS_FOR);
}
return null;
}
}
//DROPPED VARCHAR2(3)
[Description("Whether table is dropped and is in Recycle Bin")]
[DisplayName("Dropped")]
public bool Dropped
{
get
{
if (IsDBNull(DROPPED) == false)
{
return GetString(DROPPED).ToUpper() == "YES";
}
return false;
}
}
//READ_ONLY VARCHAR2(3)
[Description("Whether table is read only or not")]
[DisplayName("Read Only")]
public bool ReadOnly
{
get
{
if (IsDBNull(READ_ONLY) == false)
{
return GetString(READ_ONLY).ToUpper() == "YES";
}
return false;
}
}
//SEGMENT_CREATED VARCHAR2(3)
[Description("Whether the table segment is created or not")]
[DisplayName("Segment Created")]
public bool SegmentCreated
{
get
{
if (IsDBNull(SEGMENT_CREATED) == false)
{
return GetString(SEGMENT_CREATED).ToUpper() == "YES";
}
return false;
}
}
//RESULT_CACHE VARCHAR2(7)
[Description("The result cache mode annotation for the table")]
[DisplayName("Result Cache")]
public string ResultCache
{
get
{
if (IsDBNull(RESULT_CACHE) == false)
{
return GetString(RESULT_CACHE);
}
return null;
}
}
}
}
Submit an article or tip