Browsed by
Tag: Oracle

Oracle: tablespace size

Oracle: tablespace size

Tablespace size and freespace can be checked with a following query:

Oracle: fetch DDL for objects

Oracle: fetch DDL for objects

Getting schema (DDL) for Oracle objects (for example a table) is simple.

Valid arguments for function get_dll are: TABLE VIEW FUNCTION TABLESPACE Further information: psoug.org

Oracle: clear buffer cache

Oracle: clear buffer cache

The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA). ALTER SYSTEM FLUSH BUFFER_CACHE; This clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points. Caution: This clause is intended for use only on a test database. Do not use this clause on a production database, because as a result of this statement, subsequent queries will have no hits, only misses. Source:…

Read More Read More

Oracle: killing sessions

Oracle: killing sessions

Sometimes when things go wrong it is necessary to kill  user session. There are multiple ways to do it. First of all, you need to identify your session with &sid and &serial numbers. select * from v$session where lower(username) = 'my_user_name';   KILL SESSION command asks the session to kill itself. ALTER SYSTEM KILL SESSION '&sid,&serial';   DISCONNECT SESSION command kills the dedicated server process. Syntax is following: ALTER SYSTEM DISCONNECT SESSION '&sid,&serial' POST_TRANSACTION; The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session. ALTER SYSTEM DISCONNECT SESSION '&sid,&serial' IMMEDIATE; The IMMEDIATE clause disconnects the session and ongoing…

Read More Read More

Oracle: create database link

Oracle: create database link

Database link is a schema object in one database that enables you to access objects on another database. You can create dblink using following syntax:

Example:

Now you can easily copy data from tables from one database to another.