20 votes
Accepted

How to see profile assigned to a user

In order to find out the users and the profile assigned you can use the commands below. DESC DBA_USERS; This will show you all the fields name for which you want to query SELECT USERNAME, ...
user avatar
14 votes
Accepted

PL/SQL function to receive a number and return its binary format

This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm. Here is an example: https://www.orafaq.com/wiki/Binary CREATE OR REPLACE FUNCTION dec2bin (N in number) ...
user avatar
  • 38.1k
9 votes
Accepted

Using Oracle12C Client with Oracle11.g database

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required) Oracle ...
user avatar
  • 9,621
8 votes
Accepted

Commit and rollback in Oracle stored procedure

You can do this by using a save point. CREATE OR REPLACE PROCEDURE SPTest AS BEGIN -- We create a savepoint here. SAVEPOINT sp_sptest; INSERT INTO emptest(empid, empname, deptno) ...
user avatar
  • 455
8 votes

Result on execution of any SQL is number 2

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have ...
user avatar
  • 38.1k
7 votes
Accepted

Delete all the data from all tables

Generate the statements you need select 'DELETE FROM '||table_name||' ;' from user_tables; Even better would be using TRUNCATE instead of DELETE. That would prevent generating extra redo vectors in ...
user avatar
  • 1,525
7 votes
Accepted

Composite primary key from multiple tables / multiple foreign keys

Short version: CREATE TABLE Relation ( stu_id INT NOT NULL REFERENCES Student, par_id INT NOT NULL REFERENCES Parent, PRIMARY KEY (stu_id, par_id) ) ; Long version: Why use short forms for ...
user avatar
7 votes

Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file. Your entry in ...
user avatar
7 votes

How to create an Oracle Database Link when the password has special characters

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';
user avatar
  • 38.1k
7 votes

How to find foreign keys that don't have corresponding indexes?

Oracle SQL Developer ships with a report to find these unindexed columns. Here's the SQL behind this report: select a.owner "Owner", a.table_name "...
user avatar
7 votes
Accepted

Best way to get current year minus 5 years

There are different ways to achieve the goal. SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual; OR SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'...
user avatar
  • 8,406
6 votes
Accepted

Duration of adding versus dropping a column

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered. (...
user avatar
  • 9,621
6 votes
Accepted

Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to ...
user avatar
6 votes

ORA-02270: no matching unique or primary key for this column-list

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table. Why do you need to duplicate these columns in the child table? EDIT: ...
user avatar
  • 21.3k
6 votes
Accepted

ORA-02270: no matching unique or primary key for this column-list

The error has occurred because you are referencing a column in another table which is not unique. The good answers are already given by Lennart and Balazs Papp. I would like to explain why do we ...
user avatar
  • 8,406
6 votes
Accepted

How to get hierarchical data from two tables in oracle?

Use grouping sets. You can try it here. select cat1, cat2, cat3, sum(val) as val from t1 join t2 on t1.f_id = t2.f_id group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3)) order by cat1, ...
user avatar
  • 736
6 votes

Unable to see list of tables in Oracle SQL Developer

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details. If you want to see 'the tables' - you need to expand ...
user avatar
6 votes
Accepted

Inserting year with zero

SQL> create table t1 (c1 date); Table created. SQL> insert into t1 values (date'0000-01-05'); 1 row created. SQL> commit; Commit complete. SQL> select c1, to_char(c1, 'dd/MM/yyyy'), ...
user avatar
  • 38.1k
5 votes

ORA-03113 when starting up an Oracle 11g database

From ParnassusData: This error indicates that a mismatch has been detected between redo records and rollback (undo) records. ARGUMENTS: Arg [a] - Maximum Undo record number in Undo ...
user avatar
  • 7,441
5 votes

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I just wanted to chime in and share what got me past this error. In my case (Ora 11.2.0.3 on Win 7) I found I needed the trailing '\' in my directory path. Replaced: CREATE OR REPLACE DIRECTORY ...
user avatar
  • 151
5 votes
Accepted

aggregate column of nvarchar2 and obtain result in nvarchar2 too

As the documentation describes: The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2. So unless you pass the column as RAW, the result will be ...
user avatar
  • 38.1k
5 votes
Accepted

Is it possible to perform skip N rows and fetch M rows without full scan

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees ...
user avatar
5 votes
Accepted

Oracle return the first row, not returning the first row

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them): The order by has ties. There is not one "first row" but 3 of them. So, when the 2nd query ...
user avatar
5 votes

How to run a query in PL/SQL with Serializable transaction isolation

Alter the session: declare a number; begin EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE'; select count(*) into a from item w where w.Item_num='MOH601' ; ...
user avatar
  • 30.8k
5 votes

oracle 11g Grant on dbms_lock not persisting

From Oracle documentation- When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role. SQL> conn system Password: SQL> select * from ...
user avatar
  • 8,406
5 votes

Cascade primary key update to all referencing foreign keys in oracle

With DEFERRABLE foreign key constraints: create or replace procedure cascade_update_primary ( p_table_name in varchar2, p_old_value in number, p_new_value in number ) as l_column_name ...
user avatar
  • 38.1k
5 votes

Lock out of Oracle based on Windows username

The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege. The ADMINISTER DATABASE TRIGGER privilege allows you to create ...
user avatar
4 votes

External authentication using Oracle Standard

I'm afraid it is practically impossible. In Oracle's terminology external authentication means: "If users's name on client machine is identical to user's username in database, then user does not have ...
user avatar
  • 1,474
4 votes

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

Firstly, the oracle docs are VERY clear that you shouldn't use data pump as SYS: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL824: Do not invoke Export as SYSDBA, ...
user avatar
  • 1,877
4 votes

Oracle query optimization

Why do you use this view at all for this query? The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items ...
user avatar
  • 38.1k

Only top scored, non community-wiki answers of a minimum length are eligible