Memory usage for AMM RAC database and changing RAC database to ASMM

SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
        1804.448437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from  v$sgainfo    where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
    2592.84766
SQL> show parameter memory_max_target;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 1600M
SQL> select (value/1024/1024) Current_PGA_IN_USE_in_MB from v$pgastat where name = 'total PGA inuse';
CURRENT_PGA_IN_USE_IN_MB
------------------------
          788.078938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
         1567.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB    from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
         480





Memory usage of exisitng AMM: 
 - memory reserved  for PGA/SGA: 8 GB 
 - current PGA size 790 MB
 - current SGA size 1804 MB
 - free memory for future PGA/SGA usage: ~ 2.5 GB

For switching ASMM this can be translated into 
  SGA_MAX_SIZE             : 3 GB
  SGA_TARGET               : 3 GB
  PGA_AGGREGATE_TARGET     :  2 GB

For further tuning check : V$PGA_TARGET_ADVICE

Execute the  following commands.
Disable AMM
  SQL> alter system reset memory_max_target scope=spfile  sid='*';
  SQL> alter system reset memory_target  scope=spfile  sid='*';

Enable ASMM
  SQL> alter system set SGA_MAX_SIZE=3G scope=spfile  sid='*';
  SQL> alter system set SGA_TARGET=3G scope=spfile  sid='*'; 
  SQL> alter system set PGA_AGGREGATE_TARGET=2G scope=spfile  sid='*';  

Reboot database and verify that we have switched from AMM to ASMM
SQL> show parameter memory
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 0
memory_target                 big integer 0
--> AMM disabled 

SQL> show parameter sga
NAME                     TYPE          VALUE
------------------------ ----------- ------------------------------
sga_max_size             big integer 3G
sga_target               big integer 3G

SQL> show parameter pga
NAME                     TYPE     VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 2G

--> ASMM enabled !

Disk Consumption with Average Incremental Value

SELECT b.tsname tablespace_name ,
  MAX(b.used_size_mb) cur_used_size_mb ,
  ROUND(AVG(inc_used_size_mb),2)avg_increas_mb
FROM
  (SELECT a.days,
    a.tsname ,
    used_size_mb ,
    used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM
    (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,
      ts.tsname ,
      MAX(ROUND((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    FROM DBA_HIST_TBSPC_SPACE_USAGE tsu ,
      DBA_HIST_TABLESPACE_STAT ts ,
      DBA_HIST_SNAPSHOT sp,
      DBA_TABLESPACES dt
    WHERE tsu.tablespace_id    = ts.ts#
    AND tsu.snap_id            = sp.snap_id
    AND ts.tsname              = dt.tablespace_name
    AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
      ts.tsname
    ORDER BY ts.tsname,
      days
    ) a
  ) b
GROUP BY b.tsname
ORDER BY b.tsname;

To add a new user to a MySQL database from the Linux command line

This method should work from any operating system that uses the MySQL client application from a terminal.
First we need to log into MySQL on a server so lets do that right now.

mysql -u root -p

Then enter your password and hit enter. Now we want to create a database which our new user will have privileges on. If you already have a database you can skip this step.

mysql > create database new_database;

Now with our new database in place called new_database we can move on and set up a user for this schema.

mysql > grant usage on *.* to new_database_user@localhost identified by user_password';

Now we want the new user to be able to do almost everything in the new database so we run:

mysql > grant all privileges on new_database.* to new_database_user@localhost;

Now to make sure all the settings we entered stick we run:

mysql > flush privileges;

Now you should be able to easily log in to the database called new_database on the localhost. If you wanted the user to be able to access the database from any location than you would run the following line instead of the one above:

mysql > grant usage on *.* to new_database_user@'%' identified by 'user_password';

Hope this helps people remember how to add users really is in MySQL on the command line.

Query to find and replace a specific word in SQL Database without affecting the data along with it

update table
set column_name = replace(column_name,'TextToBeReplaced','TextToBeReplacedBy')

Mysqladmin Commands in Linux

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

1.To set MySQL Root password:

# mysqladmin -u root password YOURNEWPASSWORD

2.to Change MySQL Root password?

#mysqladmin -u root -p123456 password 'xyz123'

3.To check MySQL Server is running?

# mysqladmin -u root -p ping

4.To Check which MySQL version I am running?

# mysqladmin -u root -p version

5.To Find out current Status of MySQL server?

#mysqladmin -u root -ptmppassword status 

6.To check status of all MySQL Server Variable’s and value’s?

# mysqladmin -u root -p extended-status

7.To see all MySQL server Variables and Values?

# mysqladmin  -u root -p variables

8.To check all the running Process of MySQL server?

#mysqladmin -u root -p processlist

9.To create a Database in MySQL server?

# mysqladmin -u root -p create databasename

10.To drop a Database in MySQL server?

# mysqladmin -u root -p drop databasename

11.To reload/refresh MySQL Privileges?

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh;

The reload command tells the server to reload the grant tables. The refresh command

 flushes all tables and reopens the log files.

12.To shutdown MySQL server Safely?

#mysqladmin -u root -p shutdown 

other commands:

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13.Some useful MySQL Flush commands

# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

14.To kill Sleeping MySQL Client Process?

to see: # mysqladmin -u root -p processlist

Run the following id with process id: 

# mysqladmin -u root -p kill 5 

15.To run multiple mysqladmin commands together?

#mysqladmin  -u root -p processlist status version 

16.To Connect remote mysql server:

# mysqladmin  -h 172.16.25.126 -u root -p

17.To execute command on remote MySQL server:

# mysqladmin  -h 172.16.25.126 -u root -p status

18.To start/stop MySQL replication on a slave server?

# mysqladmin  -u root -p start-slave

# mysqladmin  -u root -p stop-slave

19.To store MySQL server Debug Information to logs?

# mysqladmin  -u root -p debug

20.To view mysqladmin options and usage:

# mysqladmin --help 

Source 

To get autorefresh resultset of Sql query

The following query does not full fill the requirements as it does not auto refresh 

the results. Each time it updates the screen with the new resultset.

SELECT GETDATE()              --your query to run
raiserror('',0,1) with nowait --to flush the buffer
waitfor delay '00:00:10'      --pause for 10 seconds
GO 5                          --loop 5 times

The above query will run  5 times, pausing for 10 seconds between each run.

Copy an existing MySQL table to a new table

To make a copy of the table tableOriginal which is in a different database called production into a new table called tableDuplicate in the currently selected database, use these two commands:

CREATE TABLE tableDuplicate LIKE production.tableOriginal ; 

INSERT into tableDuplicate SELECT * FROM production.tableOriginal ;

The first command creates the new table recipes_new by duplicating the structure of the existing table. The second command copies the data from old to new.

Query to get Table Info of SQL server

Query to get Table info from a SQL server database is as:

select COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table_name'

Case Sensative Database Query

Normally, the information kept in a cell of a database is not case sensative. For the query as below

Select * from tblShopInfo where shopName='bata'
Select * from tblShopInfo where shopName='batA'
Select * from tblShopInfo where shopName='Bata'

we will get same results for the all of the above queries. But if we are in need to get the information of case sensative partameter. Then we need to make the query as below:

Select * from tblShopInfo where shopName COLLATE Latin1_General_CS_AS ='baTa'

The above query will return only the specific info we need.