Home/Database/Oracle SQL/Page 2
  1. Asked: March 6, 2022In:

    Oracle SQL Query to Find User Permissions

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    To check current user permission in oracle execute the following queries: First Query: SELECT * FROM USER_SYS_PRIVS WHERE USERNAME = USER; The result of this query would be: USERNAME PRIVILEGE ADMIN_OPTION HR CREATE VIEW NO HR UNLIMITED TABLESPACE NO HR CREATE DATABASE LINK NO HR CREATE SEQUENCE NORead more

    To check current user permission in oracle execute the following queries:

    First Query:

    SELECT *
FROM USER_SYS_PRIVS
WHERE USERNAME = USER;

    The result of this query would be:

    USERNAME PRIVILEGE ADMIN_OPTION
    HR CREATE VIEW NO
    HR UNLIMITED TABLESPACE NO
    HR CREATE DATABASE LINK NO
    HR CREATE SEQUENCE NO
    HR CREATE SESSION NO
    HR ALTER SESSION NO
    HR CREATE SYNONYM NO

    Second Query:

    SELECT *
FROM USER_TAB_PRIVS
WHERE GRANTEE = USER;

The result of this query would be:
    GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
    HR SYS DBMS_STATS SYS EXECUTE NO NO

    Third Query:

    SELECT *
FROM USER_ROLE_PRIVS
WHERE USERNAME = USER;

The result of this query would be:
    USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
    HR RESOURCE NO YES NO

    hope this help.

    See less
  2. Asked: September 18, 2021In:

    ORA-01427: single-row subquery returns more than one row

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    Hello Garcia, The message of the ORA error is so descriptive it's pointing the query that causing the issue and the issue it self which is nothing but the sub query returned more than one row and this is not correct specially that you are using "=" operator not logical operator like "IN" or "NOT IN"Read more

    Hello Garcia,

    The message of the ORA error is so descriptive it’s pointing the query that causing the issue and the issue it self which is nothing but the sub query returned more than one row and this is not correct specially that you are using “=” operator not logical operator like “IN” or “NOT IN”. It’s up to your requirement.

    See less
  3. Asked: February 19, 2020In:

    ORA-01017 invalid username password logon denied

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    Please make sure of the credentials, because this error is quite clear enough that either the username or password is incorrect. Note : In Oracle 11g the credentials are CASE sensitive it's a default feature of newly oracle 11g database creation. Now if you want to make sure whether this feature isRead more

    Please make sure of the credentials, because this error is quite clear enough that either the username or password is incorrect.

    Note : In Oracle 11g the credentials are CASE sensitive it’s a default feature of newly oracle 11g database creation.

    Now if you want to make sure whether this feature is enabled or not you can go to sqlplus then execute the following SQL command.

    SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

    if you get ” TRUE ” value then the the password is case sensitive otherwise it isn’t.

    you can also disable this feature by executing the following SQL command :

    SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.
    See less
  4. Asked: August 26, 2021In:

    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    ORA-00054 referring to the table you are trying to update it is already locked by another session that made a query for an update on the same table and not committed or rolled back yet. It could be a form or another session. The action you could take to solve the problem is to commit or roll back thRead more

    ORA-00054 referring to the table you are trying to update it is already locked by another session that made a query for an update on the same table and not committed or rolled back yet. It could be a form or another session.

    The action you could take to solve the problem is to commit or roll back the changes from the other session, causing the lock before performing the current action. There is a query also to help you identify which session that causing the lock on your table so that you can kill the session immediately.

    SELECT S.USERNAME
,S.SID
,S.SERIAL#
,T.USED_UBLK
,T.USED_UREC
,RS.SEGMENT_NAME
,R.RSSIZE
,R.STATUS
FROM V$TRANSACTION T
,V$SESSION S
,V$ROLLSTAT R
,DBA_ROLLBACK_SEGS RS
WHERE S.SADDR = T.SES_ADDR
AND T.XIDUSN = R.USN
AND RS.SEGMENT_ID = T.XIDUSN
ORDER BY T.USED_UBLK DESC;

    Above query has been taken from oracle-base then from the output of above query you can use SID and SERIAL# to kill the session by executing the following statement.

    alter system kill session 'sid,serial#' IMMEDIATE;  
    See less
  5. Asked: July 19, 2017In:

    Which one do better performance NOT IN vs NOT EXISTS ?

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    Hi @Maran Firstly It’s not about the difference in performance only because they are not the same when dealing with nulls in the data. for example, we want to check how many employees are not managers of other employees by using NOT IN SELECT COUNT (*) FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN (SELECTRead more

    Hi Maran Firstly

    It’s not about the difference in performance only because they are not the same when dealing with nulls in the data.

    for example, we want to check how many employees are not managers of other employees by using NOT IN

    SELECT COUNT (*)
FROM EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES);

    the result will be :

    COUNT(*)
----------
0

    This means all employees are managers in this case.

    let’s then take the same example but using NOT EXISTS

    SELECT COUNT (*)
FROM EMPLOYEES EMP_1
WHERE NOT EXISTS
(SELECT NULL
FROM EMPLOYEES EMP_2
WHERE EMP_2.MANAGER_ID = EMP_1.EMPLOYEE_ID);

    the result will be :

    COUNT(*)
----------
89

    So here, out of 101 employees, there are 89, not managers.

    The most crucial thing is NULL represented in the manager_id column for the first employee 101 (Steven King). So wherever null is there while using NOT IN or IN, the evaluation of the inner query will be either FALSE or NULL and will return no records.

    So now, when it comes to performance, it depends on the amount of data that both subquery and the outer query returns. If they are small, then IN is typically more appropriate. And vise versa. But remember, we have assumed that there are no nulls in the subquery result.

    Regards

    See less
  6. Asked: July 19, 2017In:

    How to select a random row in SQL& MySQL?

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    Hi Sam I don't know what the case you need this to, but anyway you can use the following query to get random row from employees table as an example. 1- Oracle : SELECT * FROM (SELECT * FROM EMPLOYEES ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM = 1 2- MySQL : SELECT column FROM table ORDER BY RAND() LIMRead more

    Hi Sam

    I don’t know what the case you need this to, but anyway you can use the following query to get random row from employees table as an example. 1- Oracle :

    SELECT *
FROM (SELECT *
FROM EMPLOYEES
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM = 1

    2- MySQL :

    SELECT column FROM table
ORDER BY RAND()
LIMIT 1

    The idea here in both examples is to get all rows in employees table but in random orders then using ROWNUM in Oracle and LIMIT clause in MySQL to return one row from them.

    See less
  7. Asked: February 19, 2021In:

    How to grant read and write on directory in oracle ?

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    To give a particular user permission on oracle directory we can use the following commands: — Grant read permission to oraask user GRANT READ on DIRECTORY &directory_name to oraask; — Grant write permission to oraask user GRANT WRITE on DIRECTORY &directory_name to oraask; — Grant read/writeRead more

    To give a particular user permission on oracle directory we can use the following commands:

    — Grant read permission to oraask user

    GRANT READ on DIRECTORY &directory_name to oraask;

    — Grant write permission to oraask user

    GRANT WRITE on DIRECTORY &directory_name to oraask;

    — Grant read/write permissions to oraask user at one command

    GRANT READ,WRITE on DIRECTORY &directory_name to oraask;

    Hope this helpful.

    See less
  8. Asked: January 31, 2021In:

    ORA-29275 partial multibyte character

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    Hello Rain , Oracle Descritption for this error is given below: ORA-29275: partial multibyte character Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input. Action: Ensure that the complete multibyte character is sent from theRead more

    Hello Rain ,

    Oracle Descritption for this error is given below:
    ORA-29275:
    partial multibyte character
    Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
    Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.

    Mainly reason for this type or ora error is that there is a junk/invisible or special character available ,but unfortunately the cause and action that oracle give it to us is not that much descriptive, so to investigate more and figure out the problem and fix it let’s try this:

    First try to select columns individually like below example:

    SELECT COL1 FROM TABLENAME;
SELECT COL2 FROM TABLENAME;

    until reach to the column that causing your problem and raise this ORA error.

    so far so good for now that we identified the column that has junk/invisible character we can fetch the column data and overcome this issue by using CONVERT function in oracle like below example :

    SELECT CONVERT(COL1, ‘US7ASCII’, ‘WE8ISO8859P1’) FROM TABLENAME;

    Hope that this answer was helpful. 

    See less
  9. Asked: April 13, 2017In:

    Using between on sysdate instead of TRUNC

    Ahmed_Shmes
    Ahmed_Shmes Junior ahmedshmes21@gmail.com

    Use TO_DATE Function. SELECT sum(column3) from table1 where column1 = ‘XXXXXAA12’ and column2 between to_date (<yor val>,<your format>) and to_date (<yor val>,<your format>);

    Use TO_DATE Function.

    SELECT sum(column3)
    from table1
    where column1 = ‘XXXXXAA12’
    and column2 between to_date (<yor val>,<your format>) and to_date (<yor val>,<your format>);

    See less
  10. Asked: July 14, 2017In:

    How to order by number inside of character for a query like ’25 AB’ ,’30 MT’ ?

    Ahmed_Shmes
    Ahmed_Shmes Junior ahmedshmes21@gmail.com
    This answer was edited.

    Use REGEXP_SUBSTR  Syntax : REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] ) code : SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col FROM test_table t Order by sort_by_col ; Result : application2 25 AB 30 AB 3125 50 50000 AS740TRead more

    Use REGEXP_SUBSTR 

    Syntax :
REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
code :
SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col
  FROM test_table t
Order by sort_by_col ;
Result :
application2
25 AB
30 AB
3125
50
50000
AS740TRN
QAS897ZCS
oraask
test
    See less