When we give SELECT * FROM EMP; How does oracle respond:

whenever you want to retrieve  all columns data  from emp table then you can secify this.

when ever we want to see emp table,then we will give select*from emp query

It Display the all records of emp table

Oracle first parsed that SQL then make a execution plan for that which will take minimum CPU I/O and Memory and internally it's declare a implicit cursor which will return all the record from that particular table EMP by using set theory of cartesdian product.

This will results as FULL TABLE scan and oracle will return all the columns from this table.

Select command executes and retrieves all of the coulumns values are in table EMP. means you will see columns name and related data for every column from the table EMP

When this query will be submitted to oracle it goes into different stages .ie. parse,bind,execute and fetch the result set.

Following this as an example let us see how the select execution happens in
database.



SELECT ename,sal,job FROM emp

WHERE job='clerk'

ORDER BY sal;



How does the query execution occur?

1. SQL*plus checks the syntax on client side.


2. If syntax is correct the query is stamped as a valid SQL statement and
   encrypted into OCI (Oracle Call Interface) packets and sent via LAN using TCP
   to the server.


3. Once the packets reach the server the server process will rebuild the
   query and again perform a syntax check on server side.


4. Then if syntax is correct SP will continue execution of the query.


5. The SP will go to the library cache. The L.C. will keep the recently
   executed SQL statements along with their execution plan.


6. In the library cache the server process will search from the MRU (Most
   Recently Used) end to the LRU (Least Recently Used) end for a match for the
   SQL statement. It does this by using a hash algorithm that returns a hash
   value. If the hash value of the query we have written matches with that of the
   query in L.C. Then SP need not generate an execution plan (soft parsing) but
   if no match is found then SP has to proceed with the generation of execution
   plan (hard parsing).


7. Parsing is the process undertaken by Oracle to generate an execution plan.


8. The first step in parsing involves performing a semantic check. This is
   nothing but check for the existence of the obj and its structure in the
   database.


9. This check is done by SP in the data dictionary cache. Here SP will ask
   for the definition of the object, if already available within the DDC , SP
   will process the check. If not available then SP will retrieve the required
   information from the system tablespace.


10. After this SP will approach the optimizer, who will read the SQL statement
    and generate the execution plan of the query.


11. After generation of the e-plan's the SP will pick the best e-plan and go
    to the L.C.


12. SP will keep the e-plan in the L.C. Along with the original SQL text.


13. At this point in time the parsing ends and the execution of the SQL
    statement will begin.


14. SP will then go to the database cache and checks whether the data required
    by the query is already available or not in the cache.


15. If available that data can be returned to the client else it brings the
    data from the database files.


16. If sorting and filtering is required by the query then the PGA is utilized
    along with the temporary tablespace for performing sort run.


17. After sort run the data is returned to the client and SQL*plus client will
    show the data to the users.

when ever a query is executed in oracle it will undergo different stages parse,bind,execute,fetch. when this code is being executed oracle will fetch all the data is present in the particular table

Code
SELECT*FROM tablename

when ever this query is executed in oracle it will fetch all the data is present in the particular table but it will take more time. when we mentioned column names it will reduce the processing time.
Note:- we will need to execute this type of queries when we don't know the column names only.

When we write sql query .... The oracle will do these following steps initially.

1.Syntactic check.
2.semantic Check.
3.Plan execution.
4.hard parsing.
Soft Parsing.

and then it will fetch data from particular table.

answer for the select * from emp is : Oracle responds to the total content of emp table will be selected and displayed this is the answer for this question

whenever we hit a query on any table it go through from many stage
1.syntax
2.semantic
3.plan execution
4.hard parsing
5.if query is running 2nd time its come in soft parsing
6.then fetch all data from table.

Oracle responds it will select all the columns from the emp table and display output to the client.

How to remove the top record when we know some record value

it display all records in the table as a output

This is good question. You have to understand it properly.
When You Write Select * from EMP;

First Oracle try to find weather this Object (EMP) is there in the data base or not , If it is not there then it will through the error message.
Ok ,If EMP is there , then it will load the full table to the system table space which is called the temp table space.After that oracle came to know that user want to see all the record from the table (*) . Then it display all the records at the Output window.

I thing you got the answer ..

First it will check the syntax and semantics in library cache, after that it will create execution plan. Already data in the buffer cache it will directly return to the client. If not it write the fetched to the dB buffer cache after that it will send server. Server sends to the client.