Nitin Agrawal
Contact -
  • Home
  • Interviews
    • Secret Receipe
    • InterviewFacts
    • Resume Thoughts
    • Daily Coding Problems
    • BigShyft
    • CompanyInterviews >
      • InvestmentBanks >
        • ECS
        • Bank Of America
        • WesternUnion
        • WellsFargo
      • ProductBasedCompanies >
        • CA Technologies
        • Model N India
        • Verizon Media
        • Oracle & GoJek
        • IVY Computec
        • Nvidia
        • ClearWaterAnalytics
        • ADP
        • ServiceNow
        • Pubmatic
        • Expedia
        • Amphora
        • CDK Global
        • CDK Global
        • Epic
        • Sincro-Pune
        • Whiz.AI
        • ChargePoint
      • ServiceBasedCompanies >
        • Altimetrik
        • ASG World Wide Pvt Ltd
        • Paraxel International & Pramati Technologies Pvt Ltd
        • MitraTech
        • Intelizest Coding Round
        • EPAM
    • Interviews Theory
  • Programming Languages
    • Java Script >
      • Tutorials
      • Code Snippets
    • Reactive Programming >
      • Code Snippets
    • R
    • DataStructures >
      • LeetCode Problems
      • AnagramsSet
    • Core Java >
      • Codility
      • Program Arguments OR VM arguments & Environment variables
      • Java Releases
      • Threading >
        • ThreadsOrder
        • ProducerConsumer
        • Finalizer
        • RaceCondition
        • Executors
        • Future Or CompletableFuture
      • Important Points
      • Immutability
      • Dictionary
      • URL Validator
    • Julia
    • Python >
      • Decorators
      • String Formatting
      • Generators_Threads
      • JustLikeThat
    • Go >
      • Tutorial
      • CodeSnippet
      • Go Routine_Channel
      • Suggestions
    • Methodologies & Design Patterns >
      • Design Principles
      • Design Patterns >
        • TemplatePattern
        • Adapter Design Pattern
        • Decorator
        • Proxy
        • Lazy Initialization
        • CombinatorPattern
        • RequestChaining
        • Singleton >
          • Singletons
  • Frameworks
    • Apache Velocity
    • Spring >
      • Spring Boot >
        • CustomProperties
        • ExceptionHandling
        • Issues
      • Quick View
    • Rest WebServices >
      • Interviews
      • Swagger
    • Cloudera BigData >
      • Ques_Ans
      • Hive
      • Apache Spark >
        • ApacheSpark Installation
        • SparkCode
        • Sample1
        • DataFrames
        • RDDs
        • SparkStreaming
        • SparkFiles
    • Integration >
      • Apache Camel
    • Testing Frameworks >
      • JUnit >
        • JUnit Runners
      • EasyMock
      • Mockito >
        • Page 2
      • TestNG
    • Blockchain >
      • Ethereum Smart Contract
      • Blockchain Java Example
    • Microservices >
      • Messaging Formats
      • Design Patterns
    • AWS >
      • Honeycode
    • Dockers >
      • GitBash
      • Issues
  • Databases
    • MySql
    • Oracle >
      • Interview1
      • SQL Queries
    • Elastic Search
  • Random issues
    • TOAD issue
    • Architect's suggestions
  • Your Views

Oracle Tips...

                                        Not able to start the database due to missing .dbf file

If database is not starting due to some .dbf file is missing or you deleted any such file by mistake.
If that .dbf file was not having any important data you want, then you can try below things else you need to look RMAN steps on internet to recover the lost file and that will only be possible if recovery is enable in your DB, but still better to look all the possible RMAN options over the Oracle Docs or internet.
So, if you deleted some .dbf file by mistake, then below scenarios -

A) If you are still connected to DB as sysdba and you can try to create the .dbf file while creating the tablespace.

B)  If you are not connected as sysdba, then connect to your DB as sysdba. Due to missing .dbf file that particular database is having issue & not the whole DB(Oracle) is down then execute the below command -
       SQL> SELECT dd.tablespace_name tablespace_name, dd.file_name file_name FROM sys.dba_data_files dd;
       And check if your missing .dbf file is being shown here, if yes then execute below command -
       SQL> alter database datafile '/opt/apps/oraclexe/demo/<dbf file name>.dbf' offline drop;
       SQL> alter database open;
       Database altered.
       SQL> drop tablespace <Tablespace_Name> including contents cascade constraints;
       Tablespace dropped.

 If still your DB is not starting-up then follow the commands in the given order -
    1. STARTUP NOMOUNT;
    2. ALTER DATABASE MOUNT;
    3. ALTER DATABASE DATAFILE '<.dbf file name including its path>' OFFLINE DROP;
    4. ALTER DATABASE OPEN;
    5. DROP TABLESPACE <tablespace using the missing .dbf file> INCLUDING CONTENTS;
 Tip: In case you cannot drop the tablespace then you can try the command without the "INCLUDING CONTENTS" after dropping all the object in the tablespace. So first drop all the objects in the tablespace and second execute:
        DROP TABLESPACE TEST;
​===============================================================================================
                                                                    Pagination in Oracle

While working in Oracle to get certain set of rows from the rows in the table, number of rows in the table need to considered
as the efficiency of your task.
If writing the query solves your requirement in hand then can consider below 2 queries -
a) Use this way if you are having many rows from which you are required to get your required set, as this doesn't require
   intermediate view for you to create & ranks to the rows are pushed using Window and Filter predicates. So it costs less.
    SELECT * FROM
     ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp)
     WHERE Row_Num BETWEEN &start and &end;
But now let us add order by also to the inner query -
        SELECT * FROM
     ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp order by Row_Num)
     WHERE Row_Num BETWEEN &start and &end;
It will not reduce the cardinality but it reduces the cost a bit, so you can try this trick.

b) If the number of rows are less or you need not to execute the query frequently then one can try below query -
     select * from (select deptno, ename, sal, rownum Row_Num from (select deptno, ename, sal from emp order by ename)) where Row_Num between &start and &end;
 
  Here one extra subquery is required because rownum < 5 kind of filter doesn't work in where clause of the query.
 
And if you are planning to fetch all the rows but in batches, may be 100 records from the million number of records in the table.
So you definitely be running any one from above queries multiple times & it will definitely take a lot of time.
Here suggestion will be to have procedure or function & fetch all the rows or as many rows as per the memory available.
And then fetch the required number of rows from the fetched data. Continue these 2 steps in the pl/sql block till you have fetched all the rows from the table. This will avoid multiple calls to the DB.
For more information check - stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle
                                               viralpatel.net/blogs/oracle-pagination-using-rownum-limiting-result-set/
​​
===============================================================================================

Lets see one normal problem good for interviews -
Ques - You need to provide the list of employees in every department but sort the names of those employees based on their
             salary in decreasing order but if 2 employees have the same salary then sort their names in increasing order.
Solution : select deptno, ename, sal from emp group by deptno, ename, sal order by deptno,sal desc, ename asc;
Picture
Another follow-up question - Give the top 2 high salaried from each department. So above we saw how to sort the employees
                                              in each department, but to have ranking system we need more support. So we will use
                                              Row_Number() function instead of Rank() or Dense_Rank(), Why? Simply don't make the things
                                              tricky for this simple requirement.
​select * from (
select Row_Number() over(partition by deptno order by sal desc) rank, deptno, ename, sal from emp group by deptno, ename, sal order by deptno,sal desc, ename asc)
where rank < 3;

Picture
===============================================================================================
There are following type of statements :-
DDL : Create, Alter, Drop
DML : Insert, Update, Delete
DQL : Select
DCL/ACL : Grant, Revoke
​TCL : Commit, Rollback, Savepoint

===============================================================================================
Have a look on various other SQL/Windows functions available in SQL, like Pivot() or explode(), Rank() etc...
​
Powered by Create your own unique website with customizable templates.