Oracle Corporations version 10 of its flagship RDBMS product.

learn more… | top users | synonyms (1)

0
votes
0answers
6 views

Oracle Drivers Install Error - Missing and Unknown files

I'm trying to run a VBScript on a machine that doesn't have Oracle installed; though I just needed the driver to use a connection string that does not use a TSNames.ora file (connecting to a remote ...
0
votes
1answer
25 views

Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error: ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1" ORA-06512: ...
0
votes
2answers
25 views

Purge ARCHIVELOG automatically

I have an Oracle 10g XE installation (exclusively used for development) which has accumulated so far 14 GB of files at ...\oraclexe\app\oracle\flash_recovery_area\XE\ARCHIVELOG. I do need that space ...
1
vote
1answer
53 views

Modify LISTAGG query for 10g

below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated. SELECT ...
1
vote
0answers
168 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
0
votes
2answers
73 views

Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication. MacBook:~ mangeles$ ssh oracle@10.10.1.64 oracle@10.10.1.64's password: Last ...
1
vote
1answer
54 views

Generate rows based on value in 2 columns. Bug?

I have the following table in Oracle: ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302 I want to generate as many rows and every ...
0
votes
1answer
44 views

Critical tablespace usage UNDOTBS (98%)

This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning: Tablespace usage critical: UNDOTBS (96%) I also executed this command: select sum (bytes/1048576) MB FROM DBA_SEGMENTS ...
0
votes
1answer
27 views

Right way to get references to an object in Oracle?

I want to find all the objects referencing my object. I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that ...
3
votes
1answer
62 views

What is the difference between public_dependency and dba_dependencies?

I know that PUBLIC_DEPENDENCY has only the objects and references Ids, and the DBA_DEPENDENCIES does not have the Ids, but the Type, Owner, Name, etc of them. But beyond that, what is the difference ...
1
vote
1answer
33 views

Critical tablespace usage UNDOTBS (96%)

This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning: Tablespace usage critical: UNDOTBS (96%) I also executed this command: select sum (bytes/1048576) MB FROM ...
0
votes
0answers
37 views

Can we Audit Synonym Usage in Oracle?

When Auditing is enabled in Oracle, is there a way to differentiate Private Synonyms access from actual object access? For example If I have Table: S1.My_TABLE Private Synonym: S2.My_TABLE ...
0
votes
0answers
35 views

Create table with select Query on DB link

I'm trying to execute the following query, it is taking more than usual time. I'm retrieving data from db link. Without the create table statement the query is giving results. For Create table also if ...
0
votes
0answers
29 views

non-default connect roles and proxy authentication

I've been reading up on proxy authentication and found the following to bits of Oracle documentation that I can't seem to implement. Page 8 of this PDF of new forms features: ...
0
votes
0answers
68 views

How to downgrade Oracle Database from 11gr2 to 10g?

In certain situation i have to downgrade from 11gr2 to 10g but coudn't find right way to downgrade. Can you please give a right direction and possible causes to Database.!
0
votes
2answers
79 views

Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit ...
0
votes
0answers
28 views

What types of files can be stored on raw device in oracle database?

What are the other file types that one can store on a raw device in addition to placing CRD controlfiles,redologs,datafiles in oracle database.....
0
votes
2answers
26 views

Adding counts inside a COALESCE

So I have a continuation of this question. New requirements have thrown in a third table. So here's the breakdown of how the tables relate: TabA(TabA_id, desc) TabB(TabB_id, TabA_id) TabC(TabC_id, ...
1
vote
0answers
91 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
0
votes
1answer
62 views

Getting sum result for last year divided quarterly

I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks. Also, if it is possible, I want to have the type as a separate column with the type as ...
0
votes
2answers
93 views

Oracle custom week of the year numbering based on first Monday of the year

I have following sql for generating dates, I wanted to change week of the year numbering based on 'First Monday of the Year'. Hence numbering would be, 01-05-15 ---> 01 01-12-15 ---> 02 ...
0
votes
2answers
99 views

Access Oracle GTT table data from another session?

I have using Oracle GTT tables with condition ON COMMIT DELETE ROWS. Now i know GTT tables data is only session specific. 1 session cannot see the GTT data of other session in General. But is their ...
1
vote
1answer
220 views

How to find out the segment behind a corrupted block when the database cannot be opened?

Short Version: alter database open fails with ORA-00600. alert.log and dbv both point out that one data block (type: 6) is corrupted. I cannot select from dba_extents as that requires an opened ...
1
vote
2answers
400 views

Restoring an Oracle Backup Properly

I've been trying to do a proper restore of a database for weeks now and I'm convinced that I'm doing something simple wrong. I took a cold backup of the database before an update to an application ...
0
votes
1answer
65 views

Error while migrating SQL Server database to Oracle

I am getting this error message while loading database capture script output in Oracle SQL Developer. The source is SQL Server 2000 and destination is Oracle 11g. java.sql.BatchUpdateException: ...
0
votes
1answer
169 views

Rman controlfile and rotation

I got an Oracle 10g server with a bash script running every night which extract each datafile and create an rman script, which is launched from the bash script itself and make backups. CONFIGURE ...
0
votes
1answer
335 views

Dynamic ORDER BY clause in PL/SQL cursor

I am not sure if this is simply impossible or if I need to approach this from a different angle. Simplified, here is what I am trying: create or replace FUNCTION someFunction(SortCol1 varchar2 ...
0
votes
1answer
114 views

ORA-08102 while creating a view

I am learning PL/SQL programming. I'm trying to create a view with the following statement: create or replace view viewName as select ID from employees; The view creation throws the following ...
1
vote
2answers
226 views

Unexpectively slow data loading into Oracle

Big amount (4.000.000+) of files (1-4Mb each) are loading into Oracle 10g for about several days. Table is allocated in big tablespace, which was created on special volume (labeled BIG_VOL) over ...
1
vote
2answers
103 views

SQLPlus : default define settings

My workstation is Ubuntu 14.04. I have Oracle Express 11g installed. Whenever i run the define command in SQLPlus, is see the define _editor = "ed". Is there a way to change that permanently to gedit ...
1
vote
1answer
364 views

Can we migrate oracle database 10gR2 to cross platform

I want to know can we migrate Linux based oracle database 10gR2 (10.2.0.1.0) to windows server 2003 with oracle database 10gR2 (10.2.0.3.0). I have seen the documentation related to database migration ...
0
votes
0answers
453 views

How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

I have three fields in a table named table_family. One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son. Other field is "age" and it contains the ages of all the ...
1
vote
1answer
65 views

Oracle performance loss from 10.1.0.2 to 10.2.0.5

I am testing an application on two database servers with the same data : 10.1.0.2 on Windows (for development) 10.2.0.5 on Solaris (for validation). We have a script which recreates a huge table ...
0
votes
2answers
304 views

update column based on sum of data from another table

I have two tables : Table1 (col1, col2, col3, col4, val1, status) Table2 (col1, col2, col3, col4, val2) For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key. ...
2
votes
1answer
98 views

Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Background I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements. The search queries the database restricting the data to a single month. Most ...
1
vote
1answer
468 views

ORA-27100: shared memory realm already exists oracle

I'm trying to start our db after performing a shutdown abort on a oracle 10g server, but atm i'm getting the following message: ORA-27100: shared memory realm already exists Linux Error: 17: ...
0
votes
2answers
37 views

Create a test enviroment in Oracle 10g

I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually ...
0
votes
1answer
397 views

Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?

I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script: set serveroutput on; declare v1 tbl_plant.plant_id%type := 0; ...
1
vote
0answers
90 views

How can I optimize a hierarchical query with multiple paths from child to root Oracle SQL?

I am trying to build a query that gives me the hierarchical history of events for a given child event_id. The events are related to each other by some from_id and to_id and the order is determined by ...
0
votes
0answers
159 views

How to debug ORA-42016 with DBMS_REDEFINITION?

I am writing a number of scripts to transform some tables using DBMS_REDEFINITION. Is there an easy way to debug the "ORA-42016: shape of interim table does not match specified column mapping" error? ...
0
votes
0answers
29 views

Accessing values of dymanic cloumn

I have a table which has 16 fields whose of alternate name to city. Below is my table structure: CREATE TABLE CITY_DATA_TO_CHK ( COUNTRY_CODE VARCHAR2 (100), REGION ...
0
votes
0answers
190 views

Is it possible to alias a column with a constant variable defined in the package header of a PL/SQL package?

I am trying to alias some columns that are being used to interface with a 3rd party data aggregation system. This company has stated that they might change their naming scheme in the near future, so I ...
0
votes
0answers
319 views

Sorting and Grouping by year, month and sums

Im new at Oracle, and am trying to write a query to show sums of Hours for the last 3 months. First Id like to display the year/month, then the sum of a specific type of hour worked for that ...
0
votes
1answer
49 views

Compare sysdate month with a column data

im application developer and we are using Oracle database for this project. I never used oracle before so i want some help to build a simple query. I have a CARD table with these columns EXPIREMONTH ...
0
votes
0answers
57 views

Error While Restoring Standby Database from RMAN Backup of Primary Database

My Standby Database was corrupted. So i took primary database backup using RMAN. Transferred to my standby database. On standby, on startup nomount, restored standby control file then on mount stage, ...
2
votes
1answer
202 views

How would I implement interprocess communication from Oracle (10g R1) trigger using DBMS_ALERT?

Let me state up front that I'm not proficient in Oracle at all, MS SQL yes but not Oracle; which is why I'm asking this. Current Setup Oracle 10g Release 1 running on Red Hat Ent. 3 (no support) ...
0
votes
0answers
130 views

Performance degradation after partioning tables using DBMS_REDEFINITION package

We have created partitions on per-existing tables in my application using DBMS_REDEFINITION package. just for the sake of improving performance. Now , We are facing an issue in the performance ...
1
vote
1answer
205 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
0
votes
1answer
41 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
0
votes
0answers
91 views

How to populate Oracle 10g tables related via foreign key?

As the title says, how do i fill random data in tables, which have foreign key references within each other. I want to then practice all the SQL commands, like, SELECT commands, JOINS, SET FUNCTIONS ...