Oracle Database Directories

System Privileges Needed

GRANT create any directory TO <user_name>;
GRANT drop any directory TO <user_name>;

Create A Directory

CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>';

conn / as sysdba

desc dba_directories

set linesize 121
col owner format a15
col directory_name format a20
col directory_path format a70

SELECT * FROM dba_directories;

CREATE OR REPLACE DIRECTORY temp AS '/opt/app/user/';

SELECT * FROM dba_directories;

Grant Read On A Directory

GRANT READ ON DIRECTORY <directory_name> TO <schema_name>

col grantor format a20
col grantee format a20
col table_schema format a20
col table_name format a20
col privilege format a10

SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'EMP';

GRANT READ ON DIRECTORY temp TO testuser;

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Grant Write On A Directory

GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name>

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Revoke Read On A Directory

REVOKE READ ON DIRECTORY <directory_name> FROM <schema_name>

Revoke Write On A Directory REVOKE WRITE ON DIRECTORY <directory_name> FROM <schema_name>

Drop A Directory

DROP DIRECTORY <directory_name>;

SELECT * FROM dba_directories;

DROP DIRECTORY temp;

SELECT * FROM dba_directories;

ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed 6786000K

Got below error while running utlrp

ERROR at line 1:
ORA-12801: error signaled in parallel query server P619, instance (2)
ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed
6786000K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large
pool","unknown object","large pool","PX msg pool")
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4


As it was a “PX” (parallelism) error, and my system has a lot of processors, the problem was that my default parallel_max_servers was 970!
This number would be acceptable if I had enough free memory (specially pga) to accommodate all of them, but I didn’t. My MEMORY_TARGET was the minimal.

So to resolve it, I had to change this parameter to a lower value:

SQL> show parameter parallel_max_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     970


Brought this down to 50

SQL> alter system set parallel_max_servers=50 scope=both sid='*';

System altered.

This stopped the error!!.

Hope this works for you

Oracle : Start Database in No archive log mode.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/app/dbname/oraflsh01/archivelog
Oldest online log sequence     9
Next log sequence to archive   34
Current log sequence           34

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5.9861E+10 bytes
Fixed Size                  2267792 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         4.8989E+10 bytes
Redo Buffers              131936256 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/app/dbname/oraflsh01/archivelog
Oldest online log sequence     9
Current log sequence           34

Oracle : Clearing Redo Logs

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 4:

ALTER DATABASE CLEAR LOGFILE GROUP 4;

This statement overcomes two situations where dropping redo logs is not possible:

i) If there are only three log groups
ii)The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;

This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.

If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover.

Note:
If you clear an unarchived redo log file, you should make another backup of the database.

If you want to clear an unarchived redo log that is needed to bring an offline tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement.

If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery. Note that tablespaces taken offline normal do not require recovery.

ORACLE : How to generate SQL_ID information

Session related Queries

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15

select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
--  and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/

SQLs Running from longtime
--------------------------
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
      ,opname
      ,target
      ,round(sofar/totalwork*100,2)   as percent_done
      ,start_time
      ,last_update_time
      ,time_remaining
from
       v$session_longops
/

Active Sessions running for more than 1 hour
---------------------------------------------
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

Session details associated with SID and Event waiting for
---------------------------------------------------------
set pages 50000 lines 32767
col EVENT for a40

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

Session details associated with Oracle SID
-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on

Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

Session details from Session longops
-------------------------------------
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;


Session details with SPID
-------------------------
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid')
/
To find Undo Generated For a given session
------------------------------------------
select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid='&sid';

To list count of connections from other machines
------------------------------------------------
select count(1),machine from gv$session where inst_id='&inst_id' group by machine;

To get total count of sessions and processes
--------------------------------------------
select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext thru sqladdress
-------------------------------
select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalue
-------------------------------------------
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

To list long running forms user sessions
----------------------------------------
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username
---------------------------------------------
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs
------------------------------------
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');

To find Sql Text given SQLHASH & SQLADDR
----------------------------------------
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where  ADDRESS ='&addr' order by piece;

Oracle : Running SQLTXPLAIN.sql

Installation:

SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:

Uninstall a prior version (optional).
This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql
Execute installation script sqlt/install/sqcreate.sql connected as SYS.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql
During the installation you will be asked to enter values for these parameters:

Optional Connect Identifier (mandatory when installing in a Pluggable Database)
In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. Entering nothing is the most common setup.
The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.

SQLTXPLAIN password.
Case sensitive in most systems.

SQLTXPLAIN Default Tablespace.
Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

SQLTXPLAIN Temporary Tablespace.
Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

Optional Application User.
This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.

Licensed Oracle Pack. (T, D or N)
You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

Running SQLTXPLAIN

For brief instructions and feedback, please refer to the INSTRUCTIONS.TXT file included on same SQLT.zip downloaded file.

Start by uncompressing latest file SQLT.zip into one dedicated directory from where you can connect into SQL*Plus.

The file sql.txt, whose filename is provided as an inline parameter when SQLTXPLAIN.SQL is executed, has some restrictions and characteristics explained below.  If you were not provided with one sql.txt file, create it and place under same dedicated directory where you placed all SQLT scripts.  SQLTXPLAIN.SQL will try to open file <sql.txt> under same directory where you placed all SQLT scripts.

Restrictions and characteristics for file sql.txt:

It is a plain text file (flat file) with one and only one valid SQL Statement to be explained (SELECT, INSERT, UPDATE or DELETE),
It cannot have empty lines (blank lines),
At the very end of the file, after the very last character of the SQL Statement, one and only one 'carriage return' ('enter' or 'line feed') should be provided, with no spaces before or after it (review file sql0.txt provided as an example),
The SQL Statement should NOT have a semicolon ';' at the end,
If you get an error similar to 'Bind variable "b2" not declared', you have empty lines within the SQL Statement, or at the end (review sql0.txt provided as a correct example),
Do NOT replace bind variables with literals.  Since the SQLTXPLAIN.SQL script does not execute the SQL Statement provided on the <sql.txt> file, there is no need to replace the bind variables on it.  Actually, by replacing the bind variables with literals, the resulting Explain Plan can change substantially and may lead to confusion or false conclusions,
The filename <sql.txt> is NOT hard-coded.  Therefore, if multiple SQL Statements are being diagnosed, use filenames sql1.txt, sql2.txt, sql3.txt, etc.; or any other set of names,
File sql.txt is usually created out of the TKPROF by extracting a specific expensive SQL Statement.  File sql.txt is normally created with a simple Cut&Paste OS command into one new flat file,
File sql0.txt is provided as an example only (use it to test SQLTXPLAIN.SQL on APPS databases)
To execute the SQLTXPLAIN.SQL script, login into SQL*Plus.  If using Oracle APPS, login with APPS USER and password.  If using on a non-APPS database, connect into SQL*Plus with same USER that CAN execute the SQL Statement provided within the <sql.txt> file.  Be aware that the USER executing the SQLTXPLAIN.SQL script must have access to the objects specified in the sql.txt file, PLUS to the 'ALL_', 'DBA_' and 'V$' views (see also SQLTGRANT.SQL).

# sqlplus apps/<apps_pwd>
SQL> START SQLTXPLAIN.SQL sql.txt;
SQLTXPLAIN.SQL creates three files: SQLTXPLAIN.LOG, SQLT_<statement_id>.TXT and a Raw SQL Trace.  The first two are the log file and the actual report.  Use any text editor to review them (TextPad and WordPad work fine).  Be sure your lines are not wrapping around.  These two files are usually created under the same directory from where SQLTXPLAIN.SQL was executed.  On NT, these files may get created under $ORACLE_HOME/bin instead.

In most cases, the *.TXT report is enough to understand a specific Explain Plan.  In some cases, two additional files may be required:

Raw SQL Trace generated by SQLTXPLAIN.SQL.  Generated always under the 'udump' directory on the database server.  Filename and exact location can be found on the report or log files.  Please do not TKPROF this particular raw SQL Trace.
Export file of the table SQLT$STATTAB, which contains the CBO Stats related to the SQL Statement <sql.txt>.  See Migrating CBO Stats across Similar Instances below for details on its use.  To export this table SQLT$STATTAB, use ORACLE_HOME 8.0.6 (if using Oracle APPS), and execute command below.  Keep in mind that exported file is always BINARY and should be treated as such (if XFR as ASCII, this BINARY file gets corrupted).

ORA-01012: not logged on

$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 6 16:44:14 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
SQL> startup mount
ORA-01012: not logged on

Solution : Shutdown Abort the database and start it again to solve ora-01012

Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit 
Disconnected

$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 6 16:52:19 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.