Making some space in the tablespace:

2 ways:

Increase size of the datafile:

For 8 kb size, the maxsize of the data file would be 32 GB. Check the current size of the datafile, if it doesn't reached maximum size, the increase the size.

Add a new data file:

If the maxsize of the data file has reached 32 GB, the add one more datafile.

To shift a table from one tablespace to another tablespace:

alter table table_name move tablespace tablespace_name;

Types of data blocks in database buffer cache:

How to check invalid objects in the database:

Invalid objects:

There are many kinds of objects in database like tables, views, indexex, synonyms.

Lets take a view. What is a view? A view is a logical table, basically its a select statement saved as a defination, every time i use the view it runs the statement on the base table and give me the output. So its depended on the base table. If the base table's structure changes like any field got deleted, or its name got changed, the view will be invalid.

It is a existing sql script to check all the invalid objects on the database.

?/rdbms/admin/utilrp.sql

(?) is oracle home

Delete:

Deletes the data (can be all the data, or can be specified by where clause), the structure remains same and the high water mark doesn't change so those datablocks doesn't get free to reuse. It can be rollbacked.

Truncate:

Deletes all the data, frees all the data blocks by lowering the high water mark, but the table structure is preserved. Cannot be rollbacked as it is DDL and auto comit.

Drop:

Cannot be rollbacked as it is DDL and auto comit. The table structure is destroyed. It elemenate the high water mark along with the object defination.

User's details and password stored in:

system tablespace. Why?

Because system holds the whole metedata of the database, so it will also store that metedata about users like id, password, name, creation time etc.

User's data like the objects they created all of this will be stored in their default tablespace.

In industry production database we keep a limit on the datafile's size with maxsize (like 10 GB first, then when 90% of the storage is used up, we can resize it to add more space upto 32 GB the mazsize of a small data file with block size of 8 KB), when we need more space there are many processes, so if we don't do keep a limit before hand, then we will not know beforehand we reach the maximum 32 GB size, so that at the last second there will be chaos, and if more storage cannot be managed at the last second, we will not be able to add another datafile in the tablespace and no more inserts will be done in the database resulting in bussiness loss.

DBA gets a request to create an user, what are the things the DBA should keep in mind before creating the user?

We have to be aware of the username and password, what will be the default tablespace for the user, what will be the quota assigned to the user, what will the profile, privileges, check the database status (like whether it is in the right condition

Autiding: A feature or a tool to monitor what an user is doing in the database, like the DDL(Create, Alter Drop, Truccate), DML (Insert, Update, Delete), DQL(Select) executed by each and every user, we can see that. All of this is stored in audit trail file.

FRA: Control file 2nd location, archive logs, by default backup location of RMAN (autobackup and backup location), flashback logs

Audump: Location of audit trail file.

Scenario:

How will you take cold backup from RMAN?

Cold backup is backup taken while the database is down. To take this, we need to take the database in mount state and take the backup.

What to check when an user can't connect to the database?

Is the liistener working?, does the user have create session prilivilege?, user is entering right password or not?, account lock status whether is the user is locked?, check alert log, check if the database is down or not?

What to check if an user can't insert any data in a table?

the user's existing quota on that tablespace is over? the user doesn't any have quota in that tablespace, the user doesn't have the required object privileges (like insert privilege on that table), that table's row currently has a row level lock, the object become invalid, the table maybe be deleted by mistake, the user

How to start the database without spfile and pffile without using rman:

In the dbs directory under oracle home there is a file init.ora, it is a template file for the pfile. We can use that to create the pfile for our database to start the database.

Explain the connection between an user and database, like what is happening in the backend when an user connects with the database?

When an user connect with the database using network (listener and tnsname)

PMON checks for the ip adress of the tnsname, port number, checks the user name and password, check the database name, checks if the user has create session privilege or not, after checking all of these things process monitor registers the connection of the user with the database and the user process will be connected with the server process.

PGA:

2 types a server process:

dedicated server process: each user will have a separate server process.

shared server process: multple user or multiple sessions will use the same server process

Only with dedicated server process PGA got created.

Comonents of PGA:

sort area, sql area, bitmap area, session memory, user global area, sql run area.

sorting area is used for group by and order by basically aggregrations. Its is present in both PGA and temp data file. So when an user do an aggagation it will first go to the PGA. If the PGA memory is not enough because the data where the aggragrations is required is huge, then it will move to the temp datafile. Priority is always PGA for aggragrations, and if the data is small then PGA will successfully do the aggragrations, only if the data is too much big for the PGA then temp data file will be used.

If the temp tablespace is missing then who will do the aggragrations?

Well first it will go to PGA, if it is unable to do this then normally it will go to temp tablespace. Now if the temp tablespace is missing, then it will go to system tablespace. For the time temp tablespace is missing the system tablespace will do the calclations on behalf of temp tablespace. When new temp tablespace is created then temp will again do the calculations by itself.

PGA:Program global area or private global area, created outside of SGA, When the server process is dedicated only PGA got created.
Session memrory, hash value area, bitmap area, sorting area, hash join, sql work are sql run area, UGA

Visualization of dedicated and shared server process:

When the user process gets connected to the server process the PGA is created, it gets created for each server process. Compoenet is session memory, sorting area

UGA: UGA is a part of PGA when the server process is dedicated. When the server process is shared UGA is part of SGA.

For example a store has one employee, that one employee has to handle all the customers. That one employee who servs all the customer is the shared server process.

But what if there were many employees, one employee per customer, that is dedicated server process.

Now in case how shared server process that employee has to know which customer needs which thing. That information stays in User global area.

Now in case of dedicated server process the UGA again has the same thing, here one server process serving one user, so UGA stores that information.

Backup both logical and physical ones happens in the large pool area of SGA.

Datapump worker and Datapump master these background processes helps in expdp backup.