All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

0
votes
1answer
3k views

how can I insert a long string into a blob using an insert statement?

ORA-06502: PL/SQL: numeric or value error: raw variable length too long ORA-06512: at "SYS.UTL_RAW", line 224 the datatype is BLOB here's my insert statement insert into system_value (type, ...
0
votes
2answers
248 views

Oracle remote OS authentication, security risks with domain users

What security risks can be with domain users? create user "OPS$KA\USERNAME" identified externally. grant create session to "OPS$KA\MARIK"; Then MARIK that is in KA domain, can logon to the ...
1
vote
1answer
190 views

Run stored procedures from specific package/with specific tag at lower priority

I have this requirement to run "Maintainance Jobs" for a web application. These jobs will just call a stored procedure from a set of specific packages. Running these jobs can consume quite a bit of ...
1
vote
0answers
54 views

Row level security and full text searching

How do you approach the problem of resolving access permissions when matching tens of thousands of users with tens of millions of documents containing unstructured data? How do you even build and ...
0
votes
1answer
783 views

Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

TLDR: The CTXCAT index is supposed to be transactional, but Before Insert triggers that modify the indexed column don't seem to induce an update. I'm running on Oracle 9i. (Actually, I'm running on ...
0
votes
1answer
160 views

Select * from (every table in an subselect)

I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this? (SELECT a.table_name FROM all_cons_columns a JOIN all_constraints c ...
2
votes
3answers
1k views

Storing file into oracle db

I'm developing a java web-app for the acquisition of contracts, and that have to receive some file from an external system. For each new contract i recieve some information about the new customer and ...
2
votes
3answers
1k views

exporting DDL of all views Oracle

How can I go about exporting DDL of all my schema views in single .sql file ? I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case. I know how to ...
4
votes
1answer
397 views

'ORA-00059: maximum number of files exceeded' on Oracle 12c

I have a test database. Every night a script drops and recreates a number of pluggable databases that are used for our automated test runs. Recently, I have started getting 'ORA-00059: maximum ...
0
votes
1answer
48 views

Writing a trigger for every time a user logs in

I want to write a trigger which runs every time a user logs in and saves the name of each user, for example. I have written trigger in this way: create or replace TRIGGER LOGON_TRG AFTER LOGON ON ...
4
votes
1answer
92 views

Oracle - physical writes to an index

We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value. ...
0
votes
2answers
5k views

Boolean fields in Oracle, best practice?

I faced again the serious problem, that Oracle simply doesn't have a boolean field type. I need to use char(1), or smallint, or some other. What is the best practice, how to emulate boolean values in ...
0
votes
1answer
208 views

Combining data of multiple rows in view

I have a table that holds timetracking data of users and looks somewhat like this: projectid activityid worklocationid year week day hours 1 22 21 2014 37 09/...
0
votes
0answers
406 views

Oracle Recovery fails

I am quite new to DB-Administration and accidently deleted a tablespace-file manually (via OS operations) without deleting the tablespace itself... Now I am trying to open the database again and ...
0
votes
3answers
4k views

I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing ...
0
votes
0answers
186 views

Selectively load data using Oracle 11g r2 External Table Preprocessor

I am using the Preprocessor feature in Oracle 11g r2 to load several data files in a table. However, the raw files seem to have certain rows that shouldn't be imported with the rest. For example: ...
0
votes
0answers
1k views

Installing Oracle Database 11g on Mac

Does anyone know how to install Oracle 11G on a Mac? I believe some people did it using VirtualBox. I have been googling online for tutorials. Any help/link in appreciated.
0
votes
1answer
7k views

Can't connect to a Oracle db instance via SQLDeveloper

When I set the connecting information on the GUI in SQLDeveloper I get the following error (everytime!) Status : Failure -Test failed: IO Error: The Network Adapter could not establish the ...
1
vote
1answer
1k views

What is the fastest way to clone/copy oracle databases on the same machine?

I want to clone oracle 11g databases on the same machine and I need to change user etc. from e.g. PROD to DEV. For that I use expdp/impdp, but this tends to be slow with increasing database size. I ...
2
votes
2answers
176 views

What is the fastest way to clone/copy oracle databases on the same machine?

I want to clone oracle databases on the same machine and I need to change user etc. from e.g. PROD to DEV. For that I use expdp/impdp, but this tends to be slow with increasing database size. I need ...
0
votes
0answers
99 views

Explain plan shows a table not in the query

In trying to optimize a 2 minute query against an SAP R3 Oracle database, I examined the Explain Plan in Toad and found that most of the query's time is spent on a table that isn't even in the query. ...
0
votes
1answer
112 views

Add range of values under a column using rowid

How do i add range of values in a column based on row id? For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5. Additionally is it possible to divide the then ...
0
votes
0answers
95 views

How to import data into Oracle 11g r2 daily

I need to batch load several log data files (comma delimited but they don't have a .csv extension in the file name) once each day into an Oracle database. I'm sure this is a very common use case, but ...
1
vote
1answer
1k views

How to know when a query was executed?

How to find out when a query was executed? I ran a query couple of days ago, I'm not sure about the exact time. I want to know at what date and time the query was executed. Is it possible to get that ...
-3
votes
1answer
198 views

Selecting a row and row above [closed]

I need to select a row from a table if it has "PART_NO=2" and the row just above it. What should be the query. I'm using Toad for Oracle. Thanks. **Data** *Title------Part_seq_no------*End_time ----...
2
votes
2answers
1k views

Oracle Data Pump Import imports no records

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported. Processing object type ...
0
votes
1answer
32 views

Connecting to Standalone/RAC

I would like to know if SERVICE_NAME is always configured for a Oracle standalone and RAC setup or it's optional to set up. In my code I would like to connect to Database using SERVICE_NAME. But if ...
1
vote
2answers
444 views

Notification email when deadlock is encountered in Oracle alert log

How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log) Node 1: Mon Sep ...
2
votes
2answers
3k views

Repopulating Oracle tables using data from Excel/CSV file

I'm developing a Java EE application that utilizes Hibernate/JPA. Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle ...
0
votes
2answers
1k views

Oracle service_name vs database name

I am reading about Oracle and I am confused with the two names: service_name and database name. Do they both refer to the database name used while creating a database with the CREATE DATABASE xxxxxx ...
0
votes
0answers
254 views

Debug network latency when getting data from oracle

I have an application which needs to connect to Oracle database to bring my instance up. Oracle server is running on U.S server, While I am accessing it from India Sadly it takes an hour to get ...
2
votes
0answers
66 views

Master and Read Only Oracle Database synch is unacceptable [closed]

Not a DBA, I am a developer, currently my team uses a Database set up created by the DBAs which we have little control over. All write's go to a master and then from there it is synched out to read ...
0
votes
3answers
6k views

comparing values between two tables

how to Compare values between two different tables I have two tables as shown below Table 'A' sql_id OPERATION OPTIONS 63kf3an2j0pk SELECT STATEMENT 63kf3an2j0pkc TABLE ACCESS FULL ...
0
votes
2answers
184 views

Oracle GROUP BY question

I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use. ...
0
votes
2answers
610 views

inconsistent type after querying search_condition column in all_constraints table Oracle

I'm trying to get the not null constraints in my tables, so I'm using the following sql request : select * from all_constraints where owner='userx' and constraint_type='C' and ...
0
votes
2answers
71 views

Filtering of sql output

I want to migrate the database. When I disable the constraints before adding the data to the output file I get extra lines: SQL> spool disable_constraints.sql; SQL> select 'alter table '||...
0
votes
1answer
37 views

Choosing the Most Recently Entered Non-Blank Value When Many Values Exist

I have run into a problem where some IDs have multiple names associated with them. That is, if I have a location ID from one table and need to pull the location's name from another table based on the ...
0
votes
0answers
3k views

PL/SQL Error bad bind variable

Where is the problem with my code. It should work, but Compiler says: Error(5,14): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,1): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,25)...
3
votes
2answers
34k views

How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?

I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping. Here's the error I get: ...
2
votes
1answer
177 views

Select the max from two max values

I need to find the airport(departure or arrival) that had the most passengers in the last year. The tables are: PASSENGER(id,name,surname) TAKES(id_passenger,id_flight) FLIGHT(id,departure_id,...
1
vote
2answers
4k views

How to restore operational Oracle 11g Database from full backup?

Imagine that things gone bad and all we have is a fresh Windows Server and full cold backup of our Oracle database. In case of SQL Server 2000/2005/2008/2012 it was really a matter of few mouse ...
0
votes
1answer
276 views

Connect Oracle database using AD account via OID

We have configured Oracle Internet Directory. We have integrated OID with AD. We have also registered Oracle database with OID. And we are now trying to configure authentication to the database with ...
1
vote
0answers
322 views

Database Migration with Liquibase using different DBMS

Our customer is using Oracle and we want to use MySQL for development. Therefore our MySQL Scheme has to be in sync with Oracle. I tried to use Liquibase, but I get problems at the time of applying a ...
1
vote
1answer
816 views

Stop NLS settings in init.ora getting overridden by environment variable

We have a databases server that is used for development which has hundreds of database instances for different branches/builds of our application. And we needed an special instance that has ...
2
votes
1answer
86 views

Is data warehouse monitoring same as database monitoring? [closed]

I have a requirement, where data warehouse population is to be monitored. I am in confusion, and needed a clear picture. Is data warehouse different than database? eg. For monitoring database we ...
0
votes
0answers
61 views

Oracle 11 table design for better performance for reports?

We have a requirement to design a report page. A few factors we have are: number of fields in report is 70 computed fields is going to around 14*8 (based on toggles) a filter is going to have ...
2
votes
0answers
61 views

Importing data into Oracle 12

I got a set of files from a client which are, as far as I can tell, copies of a database in Oracle 11. Each folder has the following structure: - init<DB>.ora - orapw<DB> - arch - dbf ...
0
votes
0answers
50 views

Oracle Buffer Pool TARGET_SIZE is different from CURRENT_SIZE, but RESIZE_STATE is STATIC

According to the documentation for V$BUFFER_POOL, TARGET_SIZE shows that target size when resizing, but should be the same as CURRENT_SIZE if RESIZE_STATE is STATIC. However, on one of our databases ...
1
vote
1answer
430 views

PL/SQL operating system call

I'm trying to lauch a system call from Oracle Apex, directly or indirectly. It seems to only accept PL/SQL procedures, so I iterated towards this: begin :p3_request_id := sequence.nextval; ...
0
votes
1answer
112 views

The materialized view is already optimal and cannot be tuned any further

I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error: Error report: ORA-13600: error encountered in Advisor QSM-03116: The ...