Showing posts with label sqlplus. Show all posts
Showing posts with label sqlplus. Show all posts
Friday, April 11, 2025

Solution to "ORA-12546: TNS:permission denied" when starting sqlplus

›
Problem: when trying to start sqlplus, the error below is thrown: ORA-12546: TNS:permission denied Cause: Incorrect permissions on the ora...
Friday, July 19, 2024

What do do if SET SERVEROUTPUT ON doesn't work

›
If you have an anonymous piece of pl/sql code and you have turned on serveroutput, like this: set serveroutput on You may need to enable...
Wednesday, April 17, 2024

Solution to ORA-28547: connection to server failed, probable Oracle Net admin error

›
When trying to perform sqlplus actions against one of my databases, I received oracle@oric-dbserver01:[mydb01]# sqlplus / as sysdba SQL*P...
Tuesday, April 9, 2024

How to trim away all extra output from a result using sqlplus

›
The following code can be put in a shell script and executed by root. Note the sqlplus directives which will remove all unwanted output fr...
Wednesday, January 31, 2024

How come I cannot logon to my instance as sysdba, when the ORACLE_SID variable is set correctly?

›
Here's a reminder if you use a symbolic link for your ORACLE_HOME variable: You will not be able to attach to your instance using OS a...
Wednesday, September 28, 2022

How to use the html markup option in sqlplus

›
Use the following directives in sqlplus: set timing on set trimspool on set markup html on spool on head MYHEADLINEHERE table "width=...
Tuesday, March 29, 2022

How to connect via sqlplus with the connect string

›
To connect as sys using the entire connect string, use the following syntax: sqlplus /nolog SQL>connect sys@'(DESCRIPTION=(ADDRESS...
Thursday, June 17, 2021

How to generate dbms_application_info settings together with DDL in a sql script

›
Many DBAs forget to incoporate usage of dbms_application_info into their own scipts. This is particulary important if you start long-runni...
Thursday, June 10, 2021

How to extract all mountpoints for oracle data files in a database

›
To extract all unique mount points used for datafiles in a database, used the query below: SELECT distinct SUBSTR(FILE_NAME, 1, ...
Thursday, January 31, 2019

How to solve SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

›
Logged in as the user owning the objects, you are getting the following error when trying to use the autotrace feature: SP2-0618: Cannot ...
Tuesday, July 11, 2017

Create a tablespace using input variables

›
Here is a script to help set up tablespaces based on the path used for the system tablespace: SET LINES 200 -- NEW_VALUE in sqlplus specifi...
Tuesday, March 15, 2016

How to add a line feed to your spool file

›
If you want to spool out a file from you sqlplus session, and need a line feed in your string, you can use the function char( int ) to conc...
Friday, January 23, 2015

What is the difference between a BEQUEATH connection and an IPC connection?

›
A bequeath connection runs on your local host bypasses the listener the protocol creates the server process for you directly An IPC (I...
1 comment:
Tuesday, October 7, 2014

How to create the PLUSTRACE role in order to use the AUTOTRACE feature in sqlplus

›
Create the PLAN_TABLE in the schema you want to use it for (in this example, SCOTT): CONNECT SCOTT @$ORACLE_HOME/rdbms/admin/utlxplan.sq...
Wednesday, June 11, 2014

How to deal with ORA-00020: maximum number of processes (%s) exceeded

›
I recently had a situation where access to the database was completely blocked because of the infamous error message ORA-00020: maximum nu...
5 comments:
Thursday, May 22, 2014

How to use the glogin.sql file to set global sqlplus attributes

›
The file $ORACLE_HOME/sqlplus/admin/glogin.sql can be used to define global attributes for users of sqlplus on that particular host. For e...
Thursday, January 16, 2014

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

›
In TOAD you are trying to drop a partition, as follows: alter table segment_sizes drop partition SYS_P41; But you get ORA-14006: invalid...
Wednesday, January 15, 2014

How to use the sqlplus "autotrace" facility

›
SET AUTOT ON Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace on") SET AUTOT TRACE...
Tuesday, November 19, 2013

How to hide the input from the user when using the accept directive in sqlplus

›
accept sch prompt "Schema : " accept pwd prompt "Password (not displayed) : " hide
Friday, November 15, 2013

How to use the PRODUCT_USER_PROFILE (PUP) table to prevent SQL*Plus access to database

›
This article is relevant to versions documentation In cases where you have an oracle client installed on a user's PC, skilled users c...