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
36 views

PUBLIC privilege potential security threat - Oracle database

Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only ...
0
votes
1answer
39 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 ...
0
votes
0answers
18 views

Why does Oracle text catsearch function need to follow with > 0?

Why does Oracle Text catsearch function need to follow with "> 0" ? Even I use > 10000, > 100 nothing different. Example: CATSEARCH(PRODUCTNAME, 'NOTEBOOK A4','') = 10000000000000000000000 ...
2
votes
1answer
67 views

Oracle Min - ORA-00937 not a single-group group function

I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command; SQL> select tickets.bus_id bus, tickets.fare fare, ...
1
vote
1answer
61 views

Oracle: Query gives different answer when running from procedure

I guess my problem is somehow related with grant permissions. I have an "ADMIN" user, I grant it "dba" permissions like this: grant dba to admin Now in the "ADMIN" schema I have 2 tables: The ...
1
vote
1answer
59 views

Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups

I'd like to stand up a database which offers a single tablespace for NOLOGGING operations (all other perm tablespaces set with FORCE_LOGGING=YES). A conventional weekend full-backup + daily ...
2
votes
2answers
44 views

Is an Oracle 10g instance dependent on the windows account authentication?

I have created an Oracle 10g (10.2.0.4) instance on a Windows 2003 Server with a domain user xxx having domain administrator level privileges. Now I wish to change xxx's password. Will it impact the ...
1
vote
1answer
43 views

SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is: SQL Error: ORA-12991: column is reference ...
1
vote
2answers
70 views

Read from SQL Server into Oracle

Is there a way for Oracle to read from SQL Server? We are basically trying to read from a SQL Server table and update the information in Oracle. We used to have a database link in Oracle that takes ...
1
vote
1answer
52 views

Troubleshooting / traceroute within Oracle Instant Client?

I am troubleshooting a freshly reimaged backup server Win2000 / Oracle 10.1.0.5.0. Here's what I did: Installed Oracle 10g by copying Basic Instant Client package and ODBC package into same ...
0
votes
1answer
40 views

Oracle RAC 11g creation

I would like to play with RAC configuration. Using virtual area (VMware or VirtualBox): RAC1 (Windows 2003 Server) RAC2 (Windows 2003 Server) Shared storage (Openfiler) DNS server (for using SCAN ...
0
votes
1answer
71 views

How to convert decimal number to IP address?

I need a function or query to convert a decimal number to ann IP address. Presently my ip_address column looks like this: select ip_address from test_ip; ip_address 173015430 ...
1
vote
1answer
29 views

finding out the metadata and joins

Suppose I have a database with a large number of tables. Is there any command that I can type in PL/SQL which display only the field names of a given table? Given any table is there any command ...
1
vote
2answers
65 views

Delete rows from a table with inner join conditions

I have a query that selects data from a table based on some inner joins: select * from table1 p inner join table2 e on e.col1='YU' and e.username=p.username inner join table3 d on p.col2=d.col3 and ...
0
votes
1answer
52 views

How to grant privileges to use DBMS_OUTPUT in Oracle?

SET SERVEROUTPUT ON, DBMS_OUTPUT work perfect in my user, System or DBA. But when i create new user, it's not work! How to grant privileges to use this?
0
votes
1answer
43 views

SQL: Pivot + Group by in one go

I wish to pivot INFO2 into 2 columns. INFO3 data should adjusted accordingly after the PIVOT. The challenge is I also have a date column, which I want to take the MAX of. The MAX(Date) should also ...
0
votes
1answer
45 views

Can I install Oracle 12c and NOT use pluggable databases?

I've just installed Oracle 12c. I did not select any of the fancy new options, I just wanted an Oracle instance. I'm fairly new to Oracle but I was used to 11.2.0.4. Every command I try now fails ...
0
votes
2answers
58 views

List expiration dates of Oracle backups

Is it too much to ask Oracle to provide expiration dates with a list of backups? I am not looking for expired backups. I am looking for a list of expiration dates for each backup.
0
votes
1answer
71 views

ORA-00942: table or view does not exist- Can it be solved by granting privileges to user?

I am creating a Java application that connects to an Oracle Database. In the application I connect to the DB as User A, which works as intended. I then try to access tables in Schema 1 and Schema ...
2
votes
1answer
71 views

Problem using pivot to display data

I currently have a table called sample, the structure of my attendance table is: create table sample ( Student_ID VARCHAR2 (10), Late_Attendance number (10) Default 3, Attendance_Date Date, ...
0
votes
1answer
69 views

Trigger to keep account unlock history

How can create trigger that will insert username and date to a table,when oracle user account unlocked to keep account lock history
0
votes
1answer
43 views

Multiple counts in a SQL statement

Is it possible to have more than one count in a SQL statement such as: SELECT student_id, ( COUNT (ATTENDANCE_ID) from attendance where attendance_id IN ('ABSENT') ...
0
votes
2answers
67 views

How to get past account lock information in oracle database?

Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.
0
votes
1answer
57 views

How to select sub query in pl/sql? [closed]

I want to select subquery in pl/sql by count where UPI debit card and VISA debit cards as screenshot below: Please helps to check script code as below : for r in (select BRANCHCODE,BRANCHNAME,cntupi ...
0
votes
1answer
31 views

How to download or place a copy of original tables with locked-permissions via remote connection in Oracle SQL Developer?

N00b question here: I'm still learning Oracle SQL Developer. Although I understand how to query databases/tables remotely, I'm unable to manipulate the data due to permissions-basically I would like ...
1
vote
1answer
100 views

Gap resolution of Logical Standby Database via Archive Log Backups

Due to reasons outside the scope of this question, we had to flashback our Logical Standby Database (at the VM level) to the previous day. So, now i'm trying to resolve gaps in missing archivelogs ...
0
votes
1answer
38 views

Updating data from subpartition when the subpartition name is unknown

Thanks to JSapkota who helped me pull the subpartition name as per below query select subobject_name from user_objects where data_object_id IN (select dbms_rowid.rowid_object(rowid) from TABLE ...
1
vote
1answer
40 views

Is there a way to find the partition or subpartition name where my ID belongs

I know if I need to select from any partition I need to provide partition name SELECT * FROM TABLE PARTITION(P1); What if I need to know which partition a particular value belongs using a sql query ...
1
vote
1answer
67 views

Why can't I SPOOL an EXPLAIN PLAN? [closed]

Why can't I spool the explain plan to a txt file? SPOOL EXPLAIN_PLAN_1.txt set heading off set echo off set pages 999 set long 90000 select plan_table_output from ...
0
votes
1answer
54 views

Help interpreting Oracle RMAN results

I am extremely new to Oracle and had to take a backup on an 11g database that is in NOARCHIVELOG mode. Below is the output. It claims that the backup failed but I have a file of appropriate size where ...
0
votes
1answer
48 views

Why is installing ORACLE ODBC client a complex task? [closed]

I'm following the ODBC driver instructions in the link below. I think it would help me deal with issues if I understood why this has to be so complex. Cannot be that ORACLE would not make it easier if ...
-5
votes
1answer
34 views

how to data dictionary? [closed]

How to data dictionary owner and table name. Additionally, the output should add the comments on the rows. Input: owner and table name. Output: columns for Name, Null?, Type, Comments focusing on a ...
0
votes
1answer
28 views

Oracle : Is my DISTINCT function working properly?

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...
1
vote
1answer
37 views

Oracle Wallet Connection setup for SSIS

How to configure SSIS package to connect to oracle using oracle wallet? Specifically for using OLEDB to connect to oracle and load data to oracle tables.
0
votes
1answer
42 views

Oracle 11G, Move indexes from one datafile to another

I have an Oracle 11G DB installed on Windows Server 2008 SP2 64bit OS. Below is some data files usage statistics: As you can see, there are two files: index01.dbf and index02.dbf belong to the same ...
-1
votes
2answers
57 views

Count txn date in oracle database [closed]

We want to count transaction date/time as result screenshot above, Please helps check code as below, and the code as below is when run the result as screenshot below, Thanks all guy. for r ...
1
vote
2answers
94 views

Why query plan changes by number of rows

I execute following two queries on an oracle database: select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48507 which returns result in less than a second and with following ...
1
vote
1answer
76 views

Run Oracle queries on Sublime Text 3

I'm trying to run some queries in Sublime Text3. I downloaded this plugin, and I set up the settings: { "connections": { "Connection 1": { "type" : "oracle", ...
-3
votes
1answer
37 views

Converting SQL Syntax: Oracle to MySQL [closed]

I have the following SQL code written in Oracle SQL developer: CREATE TABLE students ( student_id INT NOT NULL, username VARCHAR2(30), email VARCHAR2(80), password VARCHAR2(30), f_name VARCHAR2(30), ...
-1
votes
2answers
85 views

Oracle audit log to other server?

How can I configure audit log records to other server? Because: DB Server is full because it is very big. Our IT team manages DB server and our team (security team) is going to get audit log in our ...
0
votes
1answer
52 views

Oracle: How can I create a where clause programmatically?

Suppose I have a table like: TBL_CONDITIONS | ID | WHERE_CLAUSE | |----|---------------------------------------| | 1 | fruit = 'apple' | | 2 | ...
0
votes
1answer
68 views

Is catclust.sql really required? What is the importance

We are in a process of upgrading RAC Databases, and when validating the installed components, we saw that some of the databases aren't displaying the RAC component, but still the RAC works normally ...
0
votes
1answer
120 views

Can we expdp using JOINS in QUERY clause

I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?
0
votes
3answers
83 views

How to make Database to support Multiple Languages

How to make the database to support national language characters if there are any special characters in languages other than English it is showing as ?. For example :AQUAFRESH Мой первый зубик is ...
0
votes
1answer
146 views

Does PL/SQL code run differently in sql plus vs sql developer?

Today, I stumbled upon a question someone posted about the / character and ; character. The Answer doesn't seem to be down-voted, so I questioned it a little more as to why it wasn't down-voted. ...
0
votes
1answer
42 views

Identify rman files and delete them

I need to clean up space and do a full backup of database. Old backups are safe on tape. My backups are on 2 specifics directories (2 channels). So I did: DELETE EXPIRED BACKUP; DELETE OBSOLETE; ...
0
votes
1answer
94 views

How to commit transaction on an after update event trigger?

I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance. I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that ...
1
vote
1answer
67 views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
0
votes
0answers
48 views

how to track lob changes?

this question continues this one: I have table that simulate a file system or directory of text files with the following columns: file_name varchar2 not null primary key, file_text clob, ...
1
vote
1answer
62 views

Selective data extract at Schema level

I am attempting to achieve the following within a single data pump export: SCHEMA1 = Structures only SCHEMA2 = Structures only SCHEMA3 = Structures and data The answer in this question only works ...