Category SQL
Amazon Web Services has been gaining popularity in the last few years since cloud computing has been in the spotlight.…
DynamoDB is one of the hidden gems of the NoSQL world. It is true serverless database. It is amazing that…
— Replace duplicate values only in consecutive records with NULL [duplicate]– SELECT CASE WHEN lag(tran_id) over(order by NULL) = tran_id…
Source Database ————— ::: Directory Structure ::: ORACLE_HOME : E:\oracle\product\10.2.0\db_1 RMAN Backup : E:\rman_backup_full Control Files : E:\oradata\ecc DataFiles: E:\oradata\ECC…
This error is mostly due to a mismatch in the Java system classes. It happened to us when we cloned…
spool index_rebuild.sql select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’ from dba_indexes where owner=upper(‘SAMPLE’); spool off
Query to Select the First Day and Last Day of the Current Month select trim(to_date(last_day(sysdate),’DD/MM/YYYY’)) as lastday from dual; select…
The new PL/SQL procedure REDEF_TABLE in Oracle 12c lets you redefine a table online in a single step. It can…
The Below Script will generate output if percentage of tablespace space left free is less tan 20%. You can easily…
First Install UTL_MAIL ### To install UTL_MAIL: [code language=”sql”] sqlplus sys/**** @$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb [/code] ### Create a Reporting User ###…
Query to check sessions writing to flashcache on Exadata select se.sid, sn.name, s.value, se.program from v$sesstat s natural join v$statname…
Let’s say you have already created logfiles from Group 1 to Group 8 on a 2 node RAC cluster on…
This error occurs when an expression is divided by zero. It is considered a logical error in Oracle. In mathematics,…
— Make Table Read Only [sourcecode language=”sql”] alter table <table_name> read only; [/sourcecode] — Make Table Back to Read Write.…
— Insert statements dynamically — Select ‘insert into abc values(‘||col1||’,’||col2||’,’||col3||’,’||col4||’);’ from xyz;
Oracle allows the database to access a flat file as a table. You can have a flat file with a…
This is a script to query a table, generate the result in HTML and send an email using mailx for…
To select 10 random records from a table in Oracle SELECT * FROM ( SELECT * FROM TABLE_NAME ORDER BY…
Query to find out size of the 10 Largest Tables sitting inside an Oracle database which is greater than 1GB…
Suppose you have a query result and want to concatenate a string to that output for better readibility, it can…
Sometimes it is required to find the largest tables residing in your database. It helps to identify candidates for table…
SQL Query to check size of Table — Check Size in GB SELECT bytes/1024/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME in (‘TABLE1′,’TABLE2’);…