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 userUSER is “SCOTT”If you’re not in sqlplus, you can also check the information by querying the environmental function […]
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 […]
User and Schema

How to Stop Account Password Expired Notice in Oracle

Posted on
Make sure that you’re in the right databaseSQL> select name from v$database;NAME—————————ORCLCheck what profile the user is applied.SQL> select profile from dba_users where username = ‘STEVE’;PROFILE——————————————————————————–DEFAULTWe set unlimited password_life_time on profile default.SQL> alter profile default limit password_life_time unlimited;Profile altered.