Oracle 23c New Features for DBA's1. What DBA activities did you to do today?
2. What is your typical day like?
3. What other parts of your organization do you interact with and how?
4. Do you consider yourself a development DBA or a production DBA and why?
5. Are you a nuts-n-bolts DBA or a tools-n-props DBA
Looking for Core DBA Questions with practical exmaples read here
Hope this helps!!!!
Unix Scheduling?
1. How do you schedule in unix
Can you provide me how to schedule a job in unix for every day, at 9.30AM and 10.30PM a job should run.
Ans:
crontab -e
* * * * 9.30,22.30 /opt/oracle/bin/scriptname.sh
2. how do you list the process in unix command
ANS: ps -eaf
3. what does ps -uax does
Ans: its also provide same kind of process list
4> how do you clear inodes usage when file system is full
ANS: go to file location and clean up files, inodes usage is basically the more files in the filesystem than the descriptors has
5. How do you sort the list of files given by date
ls -ltr
or
ls -lt | sort
6. What does rev command does
7. Can you demonstrate cut command
ls | cut -d
8. IN shell script how to print a any string
ANS echo "here"
9. How to get yesterday's date
date -d "1 days ago"
10. How can i remove all lines in a file having string named 'suresh'
sed -i s/'suresh'//g filename
11. How can remove all lines except string anmes suresh
sed -i s/'suresh'//d! filename
12. How do you check lenght of a file
# sed –n 'n p' file.txt | wc –c
13.Can you tell me the various stages of a Linux process, it passes through?
Answer : A Linux process normally goes through four major stages in its processing life.
Here are the 4 stages of Linux process.
Waiting: Linux Process waiting for a resource.
Running : A Linux process is currently being executed.
Stopped : A Linux Process is stopped after successful execution or after receiving kill signal.
Zombie : A Process is said to be ‘Zombie’ if it has stopped but still active in process table.
7. What is the use of cut command in Linux?
Answer : A ‘cut’ is a very useful Linux command which proves to be helpful when we need to cut certain specific part of a file and print it on standard output, for better manipulation when the field of the file and file itself is too heavy.
For example, extract first 10 columns of a text file ‘txt_tecmint‘.
# cut -c1-10 txt_tecmint
To extract 2nd, 5th and 7th column of the same text file.
# cut -d;-f2 -f5 -f7 txt_tecmint
8. What is the difference between commands ‘cmp’ and ‘diff’?
Answer : The command ‘cmp’ and ‘diff’ means to obtain the same thing but with different mindset.
The ‘diff‘ command reports the changes one should make so that both the files look the same. Whereas ‘cmp‘ command compares the two files byte-by-byte and reports the first mismatch.
10. Utilities to monitor unix performance
ANS: vmstat, prstat, sar, iostat, top
12. difference between vmstat and sar
ANS: sar can be historical also
13. How to find a problematic or bottlenec in I/O
14. How to find which command is taking more cpu in unix
top
and then C
15. In a shell script how to read a variable from prompt?
use read
ex: read $1 will read a input variable from the prompt
16) What is wrong with this interactive shell script?
echo What month is this?
read $month
echo $month is as good a month as any.
17) how do you install pacakges in linux?
YUM, rpm, apt
18. What does make do?
make compile the binaries
19. Sar examples
CPU usage of all CPUs. “1 3″ reports for every 1 seconds a total of 3 times.
sar -u 1 3
Memory usage
sar -r 1 3 Swap Usage
sar -S 1 3 20. HOw do you clear swap usage? by clearing memory intensive programs or reboot machine 22. HOw do disable and enable network interface. by service network stop, ifdown eth0 23.
SQL Questions
1. What database you have worked with?
2. What kind of work you have done in databases?
Creatingusers? creaating databases, monitor transaction kind of?
3. Have you know about SQL and queries?
If yes,
then proceed
1. Define Join and explain different type of joins?
Another frequently asked SQL Interview Questions on Joins. In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related tables. "Join" return rows when there is at least one match in both table. Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the left table.
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the right table.
Full Join
Return rows when there is a match in one of the tables.
24. What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.
25. What is Cross Join?
Cross Join will return all records where each row from the first table is combined with each row from the second table.
Database Views Interview Questions
26. What is a view?
The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
9. What is a database transaction?
Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
10. What are properties of a transaction?
Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience. Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
11. What is a Database Lock ?
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.
12. What are the type of locks ?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read the item, but can't write into it.
2. Exclusive Lock
When an exclusive lock is applied on data item, other transactions can't read or write into the data item.
9. What is a database transaction?
Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
10. What are properties of a transaction?
Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience. Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
11. What is a Database Lock ?
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.
12. What are the type of locks ?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read the item, but can't write into it.
2. Exclusive Lock
When an exclusive lock is applied on data item, other transactions can't read or write into the data item.
4. What are the different type of SQL's statements ?
This is one of the most frequently asked SQL Interview Questions for freshers. SQL statements are broadly classified into three. They are
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.
2. DML – Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as a limited version of the DML, since it can't change the data in the database. But it can perform operations on data retrieved from the DBMS, before the results are returned to the calling function.
3. DCL – Data Control Language
DCL is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example - Grant, Revoke access permission to the user to access data in the database.
5. How ot update all rows in a table
update t set empno=somenum will update all rows
5. How to delete only specific roes in table
delete from t where tname='string';
6. how to delete rows in table having name starts with 'suresh'
delete from t where ename like 'suresh%';
7. Find out employees who joined on jan 1990
select * from emp where hiredate='01-Jan-1990'
8. Provide a count of employes location wise
select location,count(*) from emp group by locaiton;
What is the difference between inner and outer join? Explain with example.
Inner Join
Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).
Inner join returns rows when there is at least one match in both tables
If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.
SELECT dept.name DEPARTMENT, emp.name EMPLOYEE
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id
What is the difference between JOIN and UNION?
SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.
UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.
SELECT * FROM EMP1
UNION
SELECT * FROM EMP2;
What is the difference between UNION and UNION ALL?
UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.
In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.
SELECT * FROM EMPLOYEE WHERE ID = 5
UNION ALL
SELECT * FROM EMPLOYEE WHERE ID = 5
How to select first 5 records from a table?
This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:
In Oracle,
SELECT *
FROM EMP
WHERE ROWNUM <= 5;
Generic solution,
I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table.
For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.
SELECT name
FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
Follow Me!!!