Sequences (Autonumber) Drop, Create, Alter commands.

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. Oracle SQL Syntax : SELECT last_number FROM all_sequences WHERE sequence_owner = […]

ORA-01555 or Alter _highthreshold_undoretention integer

hello, Setting either of this parameter will limit high value of undo retention like as ; Description: _highthreshold_undoretention integer : high threshold undo_retention in seconds connect as sysdba alter system set “_HIGHTHRESHOLD_UNDORETENTION=21600” SCOPE=spfile; you need to close and open database !! SQL> show parameter undo; NAME TYPE ———————————— ——————————– VALUE —————————— _highthreshold_undoretention integer 17200 undo_management string […]

AWR Report on the Oracle Database

Hello, to get awr report : connect to oracle db as sysdba percula40: / home / oracle # sqlplus / as sysdba SQL * Plus: Release 11.2.0.3.0 Production on Tue Mar 27 13:44:46 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With […]

Add UNDO tablespace autoextend tips

Hello,   I need to alter by UNDO tablespace tpo make it autoextend on.  How do I change my UNDO tablespace to allow for autoextend? Show Undo Tablespace in Oracle Database scripts ; CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘+ XDSLBAUNDATA’ SIZE 33554416K AUTOEXTEND OFF, ‘+ XDSLBAUNDATA’ SIZE 33554416K AUTOEXTEND OFF, ‘+ XDSLBAUNDATA’ SIZE 33554416K AUTOEXTEND OFF, ‘+ […]

DBMS_SESSION.CLOSE_DATABASE_LINK

Hello, The CLOSE_DATABASE_LINK procedure is used to close an open but inactive database link in the session. Determining Which Links Are in the Database The following views show the database links that have been defined at the local database and stored in the data dictionary: DBA_DB_LINKS Lists all database links in the database. ALL_DB_LINKS Lists […]

Oracle Database Import

Using power of Oracle Data Pump If you haven’t used oracle Data Pump and still using old IMP / EXP, it’s time for change. It’s very simple to use and at least 10 times faster (my experience) than IMP/EXP. Simple steps to configure 1. CREATE OR REPLACE DIRECTORY EXPIMP AS ‘{path}’; 2. GRANT READ, WRITE […]

Show Oracle DB Name and SID name

Hello To find the current DB_NAME and ORACLE_SID: Query the views v$database and v$thread. V$DATABASE gives DB_NAME V$THREAD gives ORACLE_SID If ORACLE_SID = DB_SID and db_name = DBNAME: To find the current value of ORACLE_SID: SVRMGR> select instance from v$thread; INSTANCE —————- DB_SID To find the current value of DB_NAME: SVRMGR> select name from v$database; […]