Questions tagged with Oracle

Hi, Logged on as the m aster user on a Oracle 19 RDS database, I would like to execute the following: GRANT EXECUTE ON XDB.DBMS_XMLSCHEMA TO appl_owner; This fails with ORA-01031: insufficient privileges. I tried this with RDSADMIN package: EXECUTE rdsadmin.rdsadmin_util.**grant_sys_object**( p_obj_name=>'DBMS_XMLSCHEMA', p_grantee=>'FWD_OWNER', p_privilege=>'EXECUTE'); But that want to grant privileges on SYS objects, so it doesn't find objects in schema XDB. How can I grant privileges on objects in different schema (xdb) to o different user/schema? Thankslg...

 Hello AWS, I have a Client requirement to create a USER and Grant ALL PRIVILEGES to this newly created USER. These SQL Queries are working on my on-premises Oracle Database but when executing the same in AWS's Oracle RDS instance, the Query is not working for my newly created user it shows the privileges error! I'm unable to give ALL PRIVILEDGES for newly created USER in RDS. Have pasted and attached the following queries and the Error faced. alter session set "_ORACLE_SCRIPT"=true; create user mendix_so_8_10 identified by 1; GRANT ALL PRIVILEGES TO mendix_so_8_10; Kindly Requesting your team to give us a working solution to our problem. Awaiting your reply at the earliest. Thanks & Regards, Madhumitha. Blg...

So, have some DMP files that was exported from a oracle on premise, using expdp. And now from the linux EC2 instance i need import those dumps that is already there if is possible using the impdp but since there is no access to directory in the RDS instance how to do that? On linux i hace oracle installed and i can connect by sqlplus in the target db.lg...

I need to execute sys.validate_apex in order to make SPATIAL works on Oracle APEX. I tried the following code: ``` begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'VALIDATE_APEX', p_grantee => 'SNW_SYSTEM', p_privilege => 'EXECUTE'); end; / ``` With the consequent error: ``` Error report - ORA-20199: Error in rdsadmin_util.grant_sys_object. ORA-20900: You do not have permission to grant: EXECUTE to SYS object: VALIDATE_APEX ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 255 ORA-20900: You do not have permission to grant: EXECUTE to SYS object: VALIDATE_APEX ORA-06512: at "SYS.RDS_SYS_UTIL", line 513 ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 252 ``` The root problem is caused by adding SPATIAL option after APEX-DEV option. Any page in my apex applictaion related to SPATIAL datatypes returns error: ``` ORA-06592: No se ha encontrado la opción CASE al ejecutar la sentencia CASE ``` I tried editing the Option parameter group so it could trigger the procedure without success. Any help is welcome. Thanks!lg...

Hello, I try to create an oracle database instance but I have this error with cloudformation and I can't find a solution. Can anyone help with this problem. Resource handler returned message: "Access Denied to API Version: API20120917 (Service: Rds, Status Code: 400, Request ID: 172bd747-8158-4b30-8222-efaca96267e2)" (RequestToken: 5d0b9b3a-4185-1b76-e6d3-9aab9896c076, HandlerErrorCode: InvalidRequest)lg...

I have already enabled RDS minor version upgrade and scheduled Friday at 10 pm. Even though there is a minor engine version upgrade available that I noticed 2 days ago, an auto-upgrade did not happen. What am I doing wrong? RDS is a managed service, I shouldn't do the patching.lg...

Hello Community, I am currently learning for the aws database specialty exam/certificate. In my online courses and in the aws documentation for the rds option groups it specifies that you cannot remove permanent options from an option group. ``` https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithOptionGroups.html Permanent options, such as the TDE option for Oracle Advanced Security TDE, can never be removed from an option group ``` But In my test I was able to delete that permanent option "TDE" from the (disassociated) option group. Is that by design for unused option groups or is the documentation not specific enough?   Thanks in advance. Heikolg...

Hi, We are using Glue to read data from on-premise Oracle DB table. The table is large and has 40+ columns. We are interested in data from only 3 columns. Does AWS Glue support reading from Oracle views? Has anyone tried and tested it? Please assistlg...

Ia Amazon going to provide some way to upgrade in the future as the easy way of doing it involves operations using SYS which we don't have an access to? Feedback would be appreciated on what clients are expected to do once you stop supporting version 19.lg...

To Connect to AWSRDS Oracle I usually type sqlplus on the EC2 instance and I get a prompt user-name and I enter all the connection string and can connected Example [oracle@ip-xx-xx-xx-xx eeop]$ sqlplus SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 27 15:45:52 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter user-name: Enter user-name: bhardwar/xxxxxxx@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oxxxx-yxxxxxx.xxxxxxx.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxx))) Last Successful login time: Fri Jan 27 2023 15:13:27 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 When I use sqlldr command I get an error from the EC2 Instance sqlldr user_id=bhardwar/xxxxxxx@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx-xxxx-xxxxx-xxxx.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxx))) control=/app/loaddata/eeop/load_ocom_data.ctl log=/app/loaddata/eeop/load_ocom_data.log [oracle@ip-xx-xx-xx-xx]$ sqlldr user_id=bhardwar/xxxxxxx@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx-xxxx-xxxxx-xxxx.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxx))) control=/app/loaddata/eeop/load_ocom_data.ctl log=/app/loaddata/eeop/load_ocom_data.log -bash: syntax error near unexpected token `(' How to use SQL LOADER to load data into an AWS RDS Oracle database? Thanks Rajanlg...

Hi, I have a some AWS Lambda functions which connect to a Postgres SQL database hosted on EC2 and and Oracle DB hosted out of AWS. I have been facing some intermittent connectivity challenges in connecting to DB hosted outside AWS. Also, basis some research I came across articles stating that directly connecting Lambda to a DB is not ideal for production workloads as Lambda cant maintain a connection pool. There is an option for AWS RDS proxy to handle this but that appears to be only working for MYSQL hosted on RDS. Any pointers on best practices to connect Lambda to a relational database both within and outside AWS network? Regards, dbeingslg...

I am trying to execute a SQL script from AWS RDS Oracle instance. The steps I followed 1) CREATE D A Directory and GRANTED READ WRITE EXEC rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'deployment_directory'); GRANT READ,WRITE ON DIRECTORY DEPLOYMENT_DIRECTORY TO rdsadmin; 2) Downloaded the file to AWS RDS instance from the S3 bucket SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'ojp-bvp-dev-01', p_directory_name => 'DEPLOYMENT_DIRECTORY') AS TASK_ID FROM DUAL; 3) The file deploy-1.sql got downloaded to the AWS RDS database in the DEPLOYMENT_DIRECTORY that was just created SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'DEPLOYMENT_DIRECTORY')); FILENAME TYPE FILESIZE MTIME ---------------------------------------------------------------------- ---------- ---------- --------- 01/ directory 4096 22-JAN-23 deploy-1.sql file 674 22-JAN-23 4) The path of the directory SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DEPLOYMENT_DIRECTORY'; DIRECTORY_PATH -------------------------------------------------------------------------------- /rdsdbdata/userdirs/01 5) I try to execute the SQL script and I get an error SQL> spool /rdsdbdata/userdirs/01/deploy-1.log SP2-0606: Cannot create SPOOL file "/rdsdbdata/userdirs/01/deploy-1.log" SQL> @/rdsdbdata/userdirs/01/deploy-1.sql SP2-0310: unable to open file "/rdsdbdata/userdirs/01/deploy-1.sql" How do I execute sql scripts in AWS RDS Oracle? Is there a utility that can be used? Thanks Rajanlg...