Table

How Big a Table Can Be

Posted on
Maximum Size Limit of a Table According to Oracle Logical Database Limits, tables can have at most 1000 columns and no limit on rows and space, so theoretically, there’s no space limit on a table. But not so fast, we should consider other factors. Since one ordinary table can only be stored in one tablespace, […]
Table

How to Check Oracle Table Size

Posted on
Although we have listed the top 10 biggest tables in our Oracle database, we also can check single table size by similar ways. Theoretical Table Size We used NUM_ROWS and AVG_ROW_LEN (in byte) in DBA_TABLES to calculate how many bytes that active rows of the table are used. The result can not be very accurate […]
Segment

Segments vs Schema Objects

Posted on
Segments vs Schema Objects No only showing all tables in Oracle, for better understanding the nature of table, you also need to know the definitions of segment and schema object in Oracle world. Segments A segment in an Oracle database is a set of extents that contains all the data for a specific logical storage […]
Index

How to Resolve ORA-08102: index key not found

Posted on
ORA-08102 Found some ORA-08102 errors during executions of SQL statements like this: ERROR at line 1: ORA-08102: index key not found, obj# 98830, file 69, block 120164 (2) ERROR at line 1: ORA-08102: index key not found, obj# 98832, file 69, block 120172 (2) ERROR at line 1: ORA-08102: index key not found, obj# 98831, […]
SQL

How to Resolve ORA-00903: invalid table name

Posted on
In this post, I will introduce 3 main error patterns about ORA-00903, they are: Violating Naming Rules Missing Quotes Incorrect Syntax ORA-00903 due to Violating Naming Rules Without complying with database object naming rules, we can’t create a table with unusual strings in normal way. Let’s see some cases that throw ORA-00903. First of all, […]
Data Dictionary View

How to Find Biggest Segments in Oracle

Posted on
Find Biggest Segments A segment in Oracle database is a set of extents that contains all the data for a specific logical storage structure within a tablespace. Which means segments contain not only metadata but real data. Normally, segments include tables, indexes and large objects, etc. Let’s see what biggest segments are in our database. […]
Trigger

How to Kill Session in Logon Trigger

Posted on
Here I introduce a trigger which can log logon information on specific schemas. It will kill their sessions if they are not from specific subnet. Create a table for storing logon records CREATE TABLE LOGON_EXCEPTION_RECORD (    USERNAME     VARCHAR2 (30 BYTE),    LOGON_TIME   DATE,    IP_ADDRESS   VARCHAR2 (15 BYTE)    ); Create a trigger […]
User and Schema

Who AM I in Oracle

Posted on
Sometimes, you switched the connection to another schema for more operations and forgot where you are. Here are some ways to know who am I (whoami). If you’re in sqlplus, you check it with the short cut. SQL> show user USER is “SCOTT” If you’re not in sqlplus, you can also check the information by […]