ORACLE: Tablespaces

Tablespaces are where Oracle stores data logically. Each tablespace is consists of datafiles. Datafiles are the physical storage of the data. Please see below common scripts used for tablespace management.


***********************Create Tablespace********************************

CREATE TABLESPACE tablespaceName DATAFILE '<datafile>' SIZE xxM



*******************checked all tablespace usage****************************

select b.tablespace_name, b.total "TOTAL SPACE (MB)",
round((sum(a.bytes)/(1024 * 1024)),2) "Free Space (MB)",
round((sum(a.bytes)/(1024 * 1024))/b.total*100,2) "% Free"
from dba_free_space a,
(select tablespace_name, sum(bytes/1024/1024) total
from dba_data_files group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
group by b.tablespace_name, b.total order by 4;

******************tablespace status****************************************

col file_name format a60;

set lines 150;

set pages 50;

select FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where ONLINE_STATUS='RECOVER';

***********Determine space percentage available for specific tablespace***********

select b.tablespace_name , b.total "Mb Total",  

sum( a.bytes/(1024 * 1024)) "Mb Free",

sum( a.bytes/(1024 * 1024))/b.total*100 "%Free"

from dba_free_space a ,

(select tablespace_name, sum(bytes/1024/1024) total  

from dba_data_files group by tablespace_name) b

where b.tablespace_name = 'tablespaceName'

and a.tablespace_name(+)=b.tablespace_name

group by b.tablespace_name, b.total 

order by 4;

******************Check the datafiles of a tablespace*************************

set linesize 140

col FILE_NAME format a60

select file_name, bytes/1024/1024 as Mb from dba_data_files where tablespace_name = 'tablespaceName' order by 1;

****************Add Datafile to a tablespace********************************

alter tablespace tablespaceName add datafile '<datafile>' size xxM;

************************Resize datafile************************************

alter database datafile '<datafile>'  resize xxM; 

MYSQL: Users - Create, Grants, Revoke, and Drop

Now that you already have your databases, you need to have users. In this guide, we will learn how to create user, grant/revoke privileges and drop user.

CREATING NEW USER

To create new user, use below command:

CREATE USER '[username]@'localhost' IDENTIFIED BY '[password]';

Here is an example:

CREATE USER 'DBuser@'localhost' IDENTIFIED BY 'P@ssw0rd123';

You can then check if the user has been created

 mysql> select user, host from mysql.user;
+----------+---------------+
| user     | host          |
+----------+---------------+
| DBuser   | localhost     |
| root     | localhost     |
+----------+---------------+
2 rows in set (0.01 sec)


GRANTING/REVOKING PRIVILEGES

Now that you have a user, you then need to grant privileges on it.

GRANT [type of permission] ON [database name].[table name] TO 
‘[username]’@'localhost’;

Here are the list of some basic privileges that you may need to grant your user.

CREATE - allows the user to create new databases or tables
SELECT - allows the user to use select command
UPDATE - allows the user to update table rows
INSERT - allows the user to insert rows into tables
DROP - allows the user to delete databases or tables
DELETE - allows the user to delete rows from tables
GRANT OPTION - allows the user to grant/remove other users' privileges

If you need to grant ALL on the user, you can use below command

GRANT all ON [database_name].* TO '[username]'@'localhost';

If you want to REVOKE privileges to a user:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

After privileges are granted or revoked, you need to run below command for the changes to take effect.

FLUSH PRIVILEGES;


DROPPING USER

Lastly, to drop a user, use below command:

DROP USER '[username]@'localhost'


For more MYSQL guides, here! 

MYSQL: How to Create and Delete Database

Before creating a database, we need first to be able to login to mysql. Here is how you can login.

mysql -u[username] -p[password] -h[hostname] -P [port]

CREATING NEW DATABASE

After you are able to login, you can list all the databases that are currently present in your MYSQL server.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

You can then create a new database as easy as this:

create database [dbname];

Here is an example:

create database newDB;

Then you can check it by listing all your databases.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| test               |
| newDB              |
+--------------------+
5 rows in set (0.01 sec)


You can then start working on that particular database using below command:

use database newDB;


DELETING DATABASE;

Here is a very simple way to delete your database:

drop database [dbname];

Here is an example:

drop database newDB;

After deleting the database, you can again verify it with the SHOW DATABASES command.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)


Hopefully you've learned from this very short guide. Click here to for more MYSQL guides.

ORACLE: Shutting Down Your Database

Databases needs to be shut down for several reasons. There are three methods to shutdown your Oracle database. These are as follows:

• Shutdown (Normal)
• shutdown Immediate
• Shutdown Abort

As to which will be used among these three will depend upon your situation.

1. Shutdown (Normal)

This is used when you shutdown your database on normal condition. This might take long as this shutdown method will wait for all active users to disconnect their sessions before the oracle instance is shut down.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


2. Shutdown Immediate

With this shutdown method, all active users will be disconnected and oracle will rollback all active transaction. This method is used when database is already needed to be shut down immediately and cannot wait for active users to disconnect their session.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Shutdown Abort

This shutdown method should only be used on emergency cases. This method will immediately disconnect users and end all transactions without rollback. Shutdown abort will then shut down Oracle instance. You may use this option when normal shutdown is not an option and shutdown immediate is not working.

SQL> shutdown abort
ORACLE instance shut down.
SQL>

To summarize, you may use these three methods on the following situations:

Shutdown (normal) - when you have ample time to wait for all the users to disconnect their sessions and for all transactions to be committed.

Shutdown Immediate - when you need to have the instance shut down but cannot wait for all the users to disconnect.

Shutdown Abort - used for emergency cases when the other two options are not working.

ORACLE: Starting up your database

In starting up your database, there are three thing that you would actually want to do to have your database usable.
First is to start your instance. Second is to mount your database. Third is to open your database.

This can all be done using the command STARTUP.


SQL> startup;
ORACLE instance started.

Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL>


With the startup command, the database is now open and can be accessible to your users. There are different stages in starting up your database. Below are some start up methods and their purpose.

1. Startup Nomount
When startup nomount is used, only your Oracle instance will be started. Oracle will read the initialization parameter file to start the instance according to how it is configured. It will then allocate memory for the database instance. Also, since the instance is already started in this phase, Oracle background processes will also be started.

SQL>startup nomount;

Startup Nomount is used when creating database for first time. It is also used for some recovery procedures or when recreating your control files.


2. Startup Mount
When startup mount is used, it will now read the control files. The control file contains entries that specifies the physical structure of the database. This will include the Database name and the locations of the datafiles and redo log files.

SQL>startup mount;

Database should be in mount state when doing some administrative tasks on your database. Some of these are;

• renaming/moving datafile
• changing archive log mode
• database full recovery

When database was already started nomount, you can change to mount state using below command.

SQL>alter database mount;

Note: Databases that are used as standby are also in mount state.

3. Startup (OPEN)
When you did not specify whether the startup type and execute only STARTUP, this will then start the database in open mode, which is the 3rd stage after mounting the database. Only in this phase can we consider that the database is finally operational. Applications and users can now start reading and writing data on the database. During this stage, database and redo log files will be open. If there are corrupted or missing in any of the required files, the startup command will return an error.

SQL> startup;

When the database is already mounted, you can then open the database with below command.

SQL> alter database open;


To summarize the stages of starting up your database:

1. no mount - instance started, reads your parameter file
2. mount - control files are read
3. open - database file and redo log files are open


Feel free to comment below for anything that can make this post a better one.

Sharing simple DB tutorials for newbies like me.

Hello! Since I have already decided to follow the path of being a DBA, I have decided to make a blog of random DB-related things that I will learn along the way.

Hope my blogs will be useful to you :)