WHEN TO REORGANIZE TABLES AND REBUILD INDEXES.

When to reorganize tables and rebuild indexes:

Table fragmentation:When rows are not stored contiguously or if rows are split auto more than one block,performance decrease because these rows required additional block accesses.
Note that table fragmentation is different from file fragmentation.When a lot of DML operations are applied on a table,the table will become fragmentation because DML does not release free space from the table below the HWM.
HWM is an indicator of used blocks in the database.Blocks below the high water mark (used blocks) have at least once contained data.This data might have been deleted.Since oracle knows that blocks before the high water mark didn't have data,it only reads block up to the high water when doing a full table scan.
*DDL statements always resets the HWM.

Table size (with fragmentation)
Sql>exec dbms_stat.gather_table_stats('SCOTT','BIG1');
Sql>select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name='BIG1'
output of the command:
table_name size
BIG1 72952 kb

Actual data in table
Sql>select table_name,round((num_rows*avg_row_len/1024),||'kb' "size" from user_tables where table_name='BIG1';
output
table_name size
BIG1 30604.2 kb

72952-30604=42348 kb is wasted space in table
The difference between two values is 60% and pct free 10%(default)-so,the table has 50% extra space which is wasted becuase there is no data.
How to reset HWM/remove fragmentation ?
4 options:
1)alter table .. move to another tablespace.
2)export,truncate or drop import.
3)CTAS method
4)dbms_redifinition

To check indexes
sql>select status,index_name from user_indexes where table_name='BIG1';
output
status index_name
unusable bigidx
sql>alter index bigidx rebuild;
select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) from index_stats where name='';
you may decide that index should be rebuilt if more than 20% of its rows are deleted.

How can you determine if an index needs to be dropped and rebuilt?
Level: Intermediate
Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio
BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3

To check the compress or not compress mode:
Sql>select compression from dba_tables where table_name in <'tablename'>;
If some times show actual size is greater than the table size it is due to in compress mode of table
If table is compress make this to no compress mode
Sql>alter table move nocompress;

MYSQL_DBA_BEGINNER.

MYSQL DOCUMENTATION

To install Mysql
#rpm -ivh mysql-server-community_x86-64.rpm

After installing immediately give password
#/usr/bin/mysqladmin -u root -p password "root123"

To start and stop mysql server

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

To connect mysql
#mysql -u root -p

To check databases and use database
Mysql>show databases;
Mysql>use ;

To check tables and its contents and its use
Mysql>show tables;
Mysql>desc ;
Mysql>select * from ;

To create user
Mysql>create user 'khan'@'localhost' identified by 'khan';

To give Privileges
Mysql>grant select,insert,update,delete on *.* to 'khan';

To drop user
Mysql>drop user 'khan'@'localhost';

To check which database to connected
Mysql>select database ();

To check which user is connected
Mysql>select user ();

To check how many users have
Mysql>select distinct grantee from user_privileges;

Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON *.* syntax:

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Only two rows information need give limit 2 in your query

In mysql by default database create in /var/lib/mysql

To create table
Mysql>create table oolaala(name varchar(20));

To check mysql version
#rpm -qa|grep -i mysql

Try this after relocating files if errors getting
#setsebool -p mysqld_disable_trans=1

Note:If not available my.cnf file or removed or '/home/oracle/data/three/redo01a.log' size 20m,lost we can copy the same file available in /usr/share/mysql (my_large.cnf)

MYISAM INNODB (engines of mysql)

By default tables create in MYISAM

To create tables in INNODB
set in my.cnf (uncomment#innodb parameter)

Mysql>create table pepsi(name varchar(10)) engine=innodb;

MYISAM is for OLAP and INNODB is for OLTP

To check connection errors or any errors
#/var/log/messages or /var/log/sys/logs of /var/lib/mysql/mysqld.log

How to rename and relocate on datafiles
1.Stop service
2.copy data directory on another mount point
3.vi /etc/my.cnf
#The MySQL server
[mysqld]
datadir=/home/mysql/data (five here different mount point)
user=mysql

To migrate from Oracle to Mysql
1.PhpAdmin tool use or
2.$mysql -u -p < abc.sql 3.mysql>source abc.sql

It helps to migrate from oracle to mysql

To create tables in oracle
set echo off
set heading off
spool t1_insert.txt
create table t1 (c1 number, c2 varchar2(10), c3 date);
insert into t1 values (1,'one', sysdate);
insert into t1 values (2,'two', sysdate);
commit;
select 'insert into t1 (c1, c2, c3) values (' || c1 || ', ' || c2 || ', ' || c3 || ');' from t1;
select 'insert into t1 (c1, c2, c3) values (' || c1 || ', ''' || c2 || ''', ''' || to_char(c3,'yyyy-mm-dd hh24:mi:ss') || ''');' from t1;
spool off

To create tables in Mysql
create table t1 (c1 int, c2 varchar(10), c3 datetime);
select '' || c1 || ', ''' || c2 || ''', ''' || to_char(c3,'yyyy-mm-dd hh24:mi:ss') || '''' from t1;

make t1_insert.txt to t1_insert.sql

to migrate
mysql>source t1_insert.sql

To take logical backup dump of mysql

#mysqldump -uroot -padmin --all-database >testmysql.sql
it generates file that we can read for import on mysql or oracle. For oracle change the data format and import by running sql file.

site to download mysql software
http://dev.mysql.com/