Home/Database/Page 2
  1. Asked: March 28, 2020In:

    How to copy a table without data in oracle?

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

    To copy a table without copying its data, you can simply use this SQL statement: CREATE TABLE xx_oraask_tbl_bkp AS SELECT * FROM xx_oraask_tbl WHERE 1=0; But there is a limitation to using the above statement that you have to pay attention to: The following database objects are not copied to the newRead more

    To copy a table without copying its data, you can simply use this SQL statement:

    CREATE TABLE xx_oraask_tbl_bkp AS SELECT * FROM xx_oraask_tbl WHERE 1=0;

    But there is a limitation to using the above statement that you have to pay attention to:

    The following database objects are not copied to the new version of your table

    • Sequences
    • Triggers
    • Indexes
    • Identity column
    • Some constraints
    • Partitioning

    Or you can use another way to do this:

    SELECT dbms_metadata.get_ddl( 'TABLE', 'xx_oraask_tbl' ) FROM DUAL;

    In the above statement, you will get the DDL statement of a specified table, and then you can easily change the table name, the indexes, etc.

    See less
  2. Asked: August 9, 2021In:

    HTTP request failed ORA-29270: too many open HTTP requests

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

    This error is thrown because there is a limit of 5 open HTTP connections per session in the Oracle database server. Usually, the application intends to open one connection at a time. However, this error might occur due to the application not closing the connection properly after being finished. So,Read more

    This error is thrown because there is a limit of 5 open HTTP connections per session in the Oracle database server.

    Usually, the application intends to open one connection at a time. However, this error might occur due to the application not closing the connection properly after being finished.

    So, it’s recommended to review your code and to make sure you are ending the response after you have finished as well as in the exception part also, ending the request like the below code.

    Catch too many requests exception to handle it by closing the request and response:

    EXCEPTION
WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
UTL_HTTP.END_REQUEST(req);
UTL_HTTP.END_RESPONSE(resp);

    Ending the request at the end of your program

    UTL_HTTP.END_REQUEST(req);
    See less
  3. Asked: September 18, 2021In:

    ORA-01417: a table may be outer joined to at most one other table

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

    Before Oracle database release 12c, the error ORA-01417 is raised when you have more than one table on the left-hand side of an outer join. To overcome this limit, we can convert the join to an ANSI syntax, e.g.: SELECT * FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID = E.DEPARTMENT_IDRead more

    Before Oracle database release 12c, the error ORA-01417 is raised when you have more than one table on the left-hand side of an outer join. To overcome this limit, we can convert the join to an ANSI syntax, e.g.:

    SELECT *
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
LEFT JOIN LOCATIONS L ON (L.LOCATION_ID = D.LOCATION_ID);

    From the 12c version, Oracle has supported having multiple tables on the left-hand side of the join.

    See less
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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