DBA Blogs

trigger body

Tom Kyte - Sun, 2016-11-06 10:26
Hi Tom, I want to search a trigger body for some particular text and would like to know the names of all such triggers... select * from user_triggers where trigger_body like '%CONSULTATION%' My Oracle Version is 10G Rel 1. Now my problem i...
Categories: DBA Blogs

ORA-04031 Unable to Allocate Shared Memory

VitalSoftTech - Sat, 2016-11-05 02:38
When executing a procedure I am getting "ORA-04031: unable to allocate shared memory" error. How do I avoid this error?
Categories: DBA Blogs

Mechanism to migrate and two-way sync database

Tom Kyte - Fri, 2016-11-04 22:46
Hi Tom I have two schemas in different physical servers. The data structures and data are different because of poor data design. I want to migrate data from one server to another server and then two-way sync databases. In two-way sync, I pla...
Categories: DBA Blogs

Listener working without listener.ora

Tom Kyte - Fri, 2016-11-04 22:46
I have an oracleDB working en production for more than a year. I have my listener working but NO listener.ora file. <code>[oracle@base admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST...
Categories: DBA Blogs

Ora 22288 while trying to wirte an XML file to a clob column

Tom Kyte - Fri, 2016-11-04 22:46
Hi Tom, I am trying to put the contents of an xml into a clob column of a table in database using a sql query and am not being able to do so. I am getting <ORA-22288: file or LOB operation FILEOPEN failed ORA-06512: at "SYS.XMLTYPE", line 296 OR...
Categories: DBA Blogs

Unsafe behavior when using functions results in SELECTs and Views

Tom Kyte - Fri, 2016-11-04 22:46
Hello, I post you my issue after Oracle support says there is no 'great' workaround. It seems to be non documented and only oracle performance teams where informed of this 'expected' behavior (in their opinion). To summarize the problem : W...
Categories: DBA Blogs

bind variables

Tom Kyte - Fri, 2016-11-04 22:46
Hello, I created bind variables in a dataset and would like to reference them in a second dataset using "where 1=1". what do I need to put after each variable in the first for the reference to hold?
Categories: DBA Blogs

loop in plsql

Tom Kyte - Fri, 2016-11-04 22:46
Hi, Is loop (for,while) creates implicit or explicit cursor ? To me, it looks like a implicit cursor because it open the memory area implicitly. But the definition of implicit cursor is any SELECT and DML operation defined a implicit cursor. C...
Categories: DBA Blogs

Scope of Savepoint

Tom Kyte - Fri, 2016-11-04 22:46
1. How to access savepoint which is created in another procedure ? 2. Can we rollback only the records processed between two save points ?
Categories: DBA Blogs

Row Locking on Insert

Tom Kyte - Fri, 2016-11-04 22:46
Hello team, I want to discuss an Issue of Locking occur on Insert in my Table Issue is that I have a Table A with Primary Key on Column 1, foreign key on Nullable Column 2 with out any Index. Table B is having foreign key of Table A P.key w...
Categories: DBA Blogs

Partner Webcast – Build your Digital Business with Oracle Cloud

Becoming a digital business is now an imperative for all organizations. The key to this transformation are the productivity tools and services that employees rely on to deliver business value....

We share our skills to maximize your revenue!
Categories: DBA Blogs

Running SQL*Plus from a Python script

Bobby Durrett's DBA Blog - Fri, 2016-11-04 16:08

I needed to write a new script that was running on a Red Hat Linux 6 virtual machine and that would connect to databases using SQL*Plus. I was going to write a bash shell script but decided to use Python instead to see if I could do it using the Python that came with this version of Linux. I wont paste the entire script here but the key was to run SQL*Plus from Python instead of a shell script. Here is a simple example showing how I did it:

$ cat test.py
import subprocess

"""

Example of running a sqlplus script from python 2.6.6.

"""

def run_sqlplus(sqlplus_script):

    """

    Run a sql command or group of commands against
    a database using sqlplus.

    """

    p = subprocess.Popen(['sqlplus','/nolog'],stdin=subprocess.PIPE,
        stdout=subprocess.PIPE,stderr=subprocess.PIPE)
    (stdout,stderr) = p.communicate(sqlplus_script)
    stdout_lines = stdout.split("\n")

    return stdout_lines

sqlplus_script="""
connect test/test
select * from dual;
exit

"""

sqlplus_output = run_sqlplus(sqlplus_script)

for line in sqlplus_output:
    print line

Here is the output:

$ python test.py

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 4 15:44:30 2016

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

SQL> SQL> Connected.
SQL>
D
-
X

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

The function run_sqlplus takes a multi-line string as input. This is the text of a SQL*Plus script. It runs sqlplus /nolog to get a SQL*Plus prompt and then passes the strings in as the lines typed at the prompt. The function returns SQL*Plus’s output as a list of strings.

Anyway, this is just an example. I built a real script using these concepts. In this case we are using Python 2.6.6 without cx_Oracle installed so I couldn’t connect directly to an Oracle database. Instead I just ran SQL*Plus from Python.

Bobby

Categories: DBA Blogs

Sql scripts

Tom Kyte - Fri, 2016-11-04 04:26
Hi , can we call sql scripts containing procedures and packages without a SQL*plus. As of now we are running some scripts through sql*plus command line. So if we want to run these scripts from a system where they don't have sql plus installed is t...
Categories: DBA Blogs

SQL QUERY

Tom Kyte - Fri, 2016-11-04 04:26
hello, i have two tables DEPARTMENT_DETAILS(deptno,dname,loc) and EMPLOYEE_DETAILS(empno,ename,job,sal,mgr,comm,deptno) with having data 5 records in department_details and 14 records in employee table my query is that.... i wnat to display th...
Categories: DBA Blogs

SCAN IP

Tom Kyte - Fri, 2016-11-04 04:26
Can SCAN resolve to more than three IP address ?If so why we use only three ?What are the theory behind keeping these three IPs?
Categories: DBA Blogs

DBMS_COMPRESSION.get_compression_ratio ERROR on NON EXA platform

Tom Kyte - Fri, 2016-11-04 04:26
Hello Tom, I'm trying to use DBMS_COMPRESSION.get_compression_ratio on my AIX box to estimate HCC storage savings if I move to an Exadata platform. MOS notes & many public links says it is possible to use the advisor on NON EXA platforms like my ca...
Categories: DBA Blogs

Deletion from a atble having millions of records

Tom Kyte - Fri, 2016-11-04 04:26
Hi Tom, I have a table having 115 columns and 25 foreign key constraints having millions of records. I am running a deletion script from the table based on one field in that table. An index is created for the same field and also the table is non-p...
Categories: DBA Blogs

How to do migrate Oracle RAC standard to enterprise edition to a new server?

Tom Kyte - Fri, 2016-11-04 04:26
My current database server is Oracle RAC standard edition. I need to migrate my current database to Oracle RAC enterprise edition on a new server. How to do this? Could I use RMAN backup from standard edition and restore to enterprise edition on th...
Categories: DBA Blogs

How to syncronize Active-Active-Active databases

Tom Kyte - Fri, 2016-11-04 04:26
The customer need a system archietecture with 3 sites with all ACTIVE. My question here is how to do Real Time synchronization of Active-Active-Active topology. Active-Active we do based on archive logs. But with 3 Active it is very complex. Could ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs