Search This Blog

Sunday 25 October 2020

AWS RDS Oracle Frequently used SQL Queries

 Recently I came across working on Amazon Oracle RDS database where I have to migrate the in-place oracle database to Amazon RDS. Here is my learning from that implementation of frequently used queries in Oracle / Amazon RDS.

1. Getting all the tablespace file from database

    select tablespace_name, bytes from dba_data_files;

2. Getting long running job status from database

    SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR, 

    TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" 

    FROM V$SESSION_LONGOPS 

    WHERE OPNAME NOT LIKE '%aggregate%' 

    AND TOTALWORK != 0 

    AND SOFAR <> TOTALWORK;

 3. How to get all the files from a directory?

select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'DATA_PUMP_DIR'));

4. Getting details from import Log files          

    select * from table (rdsadmin.rds_file_util.read_text_file(

       p_directory => 'DATA_PUMP_DIR',

        p_filename  => 'abc.log'));

5. Removing a file from a directory

    exec utl_file.fremove('DATA_PUMP_DIR','IMPORT.LOG');

6. How to kill a job in RDS Oracle

begin

rdsadmin.rdsadmin_util.kill(sid => sid, serial => serial_number);

end;

/

7. Providing Grant issues

    GRANT EXEMPT ACCESS POLICY TO "SYSTEM"

    GRANT GRANT ANY ROLE TO "SYSTEM" with admin option; 

    GRANT DEBUG ANY PROCEDURE TO "HR"

    GRANT CREATE VIEW TO "HR"

7. Granting Permissions to SYS Objects in AWS RDS

begin

rdsadmin.rdsadmin_util.grant_sys_object(p_obj_name => 'V_$SESSION',p_grantee => 'HR',p_privilege => 'SELECT');

end;

/

8. Exporting a Schema from a database in AWS RDS / Oracle

================================================================

DECLARE

hdnl NUMBER;

BEGIN

hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'ABC.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'ABC.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);

DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''ABC'')');

DBMS_DATAPUMP.START_JOB(hdnl);

END;

/

================================================================

9. Getting number of tables from a database

select count(*) from all_objects where object_type = 'TABLE';

10. How to drop a AQ Tables:

EXEC dbms_aqadm.drop_queue_table ( queue_table => 'HR.AQ$_HR_EMP_SQ_I',force=>true);


11. How to copy dump files from s3 bucket to database

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name    =>  'mys3bucketname', 
p_s3_prefix      =>  'db', 
p_directory_name =>  'DATA_PUMP_DIR') 
AS TASK_ID FROM DUAL;   

12. How to get the query of any view or table from a schema:

syntax:
select dbms_metadata.get_ddl('object_type','object_name','schema nme') from dual;
e.g:
select dbms_metadata.get_ddl('VIEW','EMPLOYEE','HR') from dual;

13. How to know when is an object created
syntax:
SELECT created FROM dba_objects  WHERE object_name = 'object_name'     AND owner =     'schema_name' AND object_type = 'object_type';

e.g:
SELECT created   FROM dba_objects  WHERE object_name =                'EMPLOYEE' AND owner =     'APPS' AND object_type = 'VIEW';

14. checking data dumping directory to check the dump files

     select * from                 
   table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order          by mtime;  

15. Creating Tablespaces

    create tablespace TEMP1 datafile size 5G autoextend on maxsize 10G;

16. creating a database in Oracle or Amazon RDS database

Sample Syntax:

create user schema_1 identified by <password>;

grant create session, resource to schema_1;

alter user schema_1 quota 100M on users;

Example:

CREATE USER HR IDENTIFIED BY HR

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION, CREATE TABLE to HR;

17. Importing Dump file in Amazon RDS

Now we could start running the import process with the following command:

================================================================

DECLARE

hdnl NUMBER;

BEGIN

hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode =>        'SCHEMA',job_name=>null);

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'HR.dmp',        directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename =>       'HR.log', directory => 'DATA_PUMP_DIR', filetype =>       dbms_datapump.ku$_file_type_log_file);

DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''HR'')');

DBMS_DATAPUMP.START_JOB(hdnl);

 END;

 /

================================================================

18. How to copy your dump files to S3 bucket from Linux Server

syntax: 
aws s3 cp /data/dumps/HR.dmp s3://s3-bucket-name

9 comments:

  1. Nice.. Thanks for sharing.

    ReplyDelete
  2. This site helps to clear your all query. bsc 1st year result 2021
    mg university bcom result 2021 This is really worth reading. nice informative article.

    ReplyDelete
  3. There are the nearby professional listings, the public catalogs, and the worldwide professional references. source

    ReplyDelete
  4. Reseller hosting can be termed as a hosting type that gives you hosting for multiple websites most probably for business purposes. Most people buy the hosting from web hosting providers, and then they resell it to other customers. The reseller hosting will not usually require any separate mal server, web server, internet speed or DNS server. As a buyer, you can have your name on the templates as a web host even though you are just reselling. Reseller hosting comes in different types including: https://onohosting.com/

    ReplyDelete
  5. Amazing post.Thanks for sharing such a worthy. keep sharing like this.....
    Benefits ofAmazon Web Service
    Advantages of AWS

    ReplyDelete
  6. Genex brings support for world-renowned relational databases and data streaming platforms on either on-prem or cloud, with 24/7/365 support from our skilled experts. Powered by the world's most popular databases, our administrative support services will help you deliver modern applications timely in the most cost-effective way without compromising the quality and security.
    https://genexdbs.com/

    ReplyDelete
  7. I read your blog frequently and I just thought I’d say keep up the amazing work! https://youstable.com/cpanel-hosting

    ReplyDelete
  8. Keep up the great work! You recognize a lot of people are searching around for this info, you could help them greatly.
    bsc 1st year result 2021 name wise

    ReplyDelete

OBIEE 12c to OAS Migration

 Hi All, I have migrated successfully few projects from BI 12c to OAS 6.4. If  anyone wants help then please reach Me! Cheers! Bose

Recent Posts