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 […]
User and Schema

How to Duplicate a Schema from One to Another

Posted on
Suppose you are planning to duplicate user01 schema into user02 schema. Tools like TOAD, SQL Developer can be helpful. But you may also like to know the formal way provided by Oracle to finish your job. Yes, it’s Data Pump.Estimate the size of dump file.[oracle@test ~]$ expdp “/ as sysdba” schemas=user01 content=all estimate_only=yAs you may […]