Thursday, August 19, 2010

Example on interval partition (11gR1 onwards)

SQL> desc sh.sales;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)


SQL> create table sales
2 (prod_id number not null,
3 cust_id number not null,
4 time_id date not null,
5 channel_id number not null,
6 promo_id number not null,
7 quantity_sold number(10,2) not null,
8 amount_sold number(10,2) not null)
9 partition by range(time_id)
10 interval (numtoyminterval(3,'month'))
11 store in (ts1,ts2,ts3,ts4)
12 (partition sales_q1_1998 values less than
13 (to_date('01-04-1998','dd-mm-yyyy'))
14 )
SQL> /

SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a15
SQL> set lin 80
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ---------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01

SQL> insert into sales select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.


SQL> @retrieve_partition.sql
SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a30
SQL> set lin 100
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01
SYS_P21 TS2 TO_DATE(' 1998-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P22 TS3 TO_DATE(' 1998-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P23 TS4 TO_DATE(' 1999-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P24 TS1 TO_DATE(' 1999-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P25 TS2 TO_DATE(' 1999-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P26 TS3 TO_DATE(' 1999-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P27 TS4 TO_DATE(' 2000-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P28 TS1 TO_DATE(' 2000-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P29 TS2 TO_DATE(' 2000-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P30 TS3 TO_DATE(' 2000-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P31 TS4 TO_DATE(' 2001-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P32 TS1 TO_DATE(' 2001-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P33 TS2 TO_DATE(' 2001-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P34 TS3 TO_DATE(' 2001-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P35 TS4 TO_DATE(' 2002-01-01 NUMTOYMINTERVAL(3,'MONTH')

16 rows selected.

SQL>

SQL> insert into sales values
2 (14,288,sysdate,3,999,1,1259.99);

1 row created.

SQL> @retrieve_partition.sql
SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a30
SQL> set lin 100
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01
SYS_P21 TS2 TO_DATE(' 1998-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P22 TS3 TO_DATE(' 1998-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P23 TS4 TO_DATE(' 1999-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P24 TS1 TO_DATE(' 1999-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P25 TS2 TO_DATE(' 1999-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P26 TS3 TO_DATE(' 1999-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P27 TS4 TO_DATE(' 2000-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P28 TS1 TO_DATE(' 2000-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P29 TS2 TO_DATE(' 2000-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P30 TS3 TO_DATE(' 2000-10-01 NUMTOYMINTERVAL(3,'MONTH')

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SYS_P31 TS4 TO_DATE(' 2001-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P32 TS1 TO_DATE(' 2001-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P33 TS2 TO_DATE(' 2001-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P34 TS3 TO_DATE(' 2001-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P35 TS4 TO_DATE(' 2002-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P36 TS3 TO_DATE(' 2010-10-01 NUMTOYMINTERVAL(3,'MONTH')

17 rows selected.

SQL> rollback;

Rollback complete.

SQL> @retrieve_partition.sql
SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a30
SQL> set lin 100
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01
SYS_P21 TS2 TO_DATE(' 1998-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P22 TS3 TO_DATE(' 1998-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P23 TS4 TO_DATE(' 1999-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P24 TS1 TO_DATE(' 1999-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P25 TS2 TO_DATE(' 1999-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P26 TS3 TO_DATE(' 1999-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P27 TS4 TO_DATE(' 2000-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P28 TS1 TO_DATE(' 2000-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P29 TS2 TO_DATE(' 2000-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P30 TS3 TO_DATE(' 2000-10-01 NUMTOYMINTERVAL(3,'MONTH')

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SYS_P31 TS4 TO_DATE(' 2001-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P32 TS1 TO_DATE(' 2001-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P33 TS2 TO_DATE(' 2001-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P34 TS3 TO_DATE(' 2001-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P35 TS4 TO_DATE(' 2002-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P36 TS3 TO_DATE(' 2010-10-01 NUMTOYMINTERVAL(3,'MONTH')

17 rows selected.

SQL>

Wednesday, August 18, 2010

What do the "hard" and "soft" mean in the /etc/security/limits.conf?

soft: the default value, if the value not being overwritten in other place, for example (ulimit)
hard: kernel limit, user level changes can not exceed this value.

for example:

-------------------------
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
-------------------------
later we can change Oracle's nofile to any value less than 65536 using "ulimit -n". If we do not change, the default value is 1024, but we can not change the value exceed 65536.

For the maximum open file descriptor, the best estimated value is process x dbfiles + 500.

process: number of oracle process
dbfiles: number of datafiles.

And nofile shoud be less than fs.file-max in the /etc/sysctl.conf

Speed up mview refresh with atomic_refresh=>false

SQL> create materialized view mv_sales
2 refresh complete
3 enable query rewrite
4 as
5 select * from sales;

Materialized view created.

SQL> select count(*) from mv_sales;

COUNT(*)
----------
918843

SQL> exec dbms_mview.refresh(list=>'DONGHUA.MV_SALES',atomic_refresh=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:01:34.24
SQL> exec dbms_mview.refresh(list=>'DONGHUA.MV_SALES',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.09

==================================
When atomic_refresh=>true, Oracle performs deleting from MView table.
When atomic_refresh=>false, Oracle trunctates the MView table.

How to resolve "ORA-14402: updating partition key column would cause a partition change"

SQL> create table part_test
2 (id number, doc varchar2(30))
3 partition by range (id)
4 (partition p1 values less than (100),
5 partition p2 values less than (maxvalue));

Table created.

SQL> insert into part_test values(1,'donghua');

1 row created.

SQL> commit;

Commit complete.

SQL> update part_test set id=id+100;
update part_test set id=id+100
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> alter table part_test enable row movement;

Table altered.

SQL> update part_test set id=id+100;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table part_test disable row movement;

Table altered.

Monday, August 16, 2010

MySQL quick commands for Oracle DBAs

How to connect to mysql?

[root@vmxdb01 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.

[root@vmxdb01 ~]# mysql -u donghua -pora123 -h 192.168.1.21
Welcome to the MySQL monitor. Commands end with ; or \g.

How to disconnect from mysql?

mysql> exit
Bye

Do I need to commit after DML?

It’s good to commit, but by default, auto_commit=1, which means it will command after each statement.
How to list databases (database = schema)?

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

How to connect to a database?

mysql> use hr;
Database changed

How to list tables inside a database?

mysql> show tables;
+--------------+
| Tables_in_hr |
+--------------+
| employees |
+--------------+
1 row in set (0.00 sec)

How to list tables from another database?

mysql> show tables from information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
17 rows in set (0.00 sec)

How to describe a table?

mysql> desc hr.employees;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int(11) | YES | | NULL | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

How to retrieve the DDL for a table?

mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`employee_id` int(11) default NULL,
`first_name` varchar(20) default NULL,
`last_name` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to retrieve list of indexes of a table?

mysql> show index from employees;
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employees | 1 | employees_pk | 1 | employee_id | A | NULL | NULL | NULL | YES | BTREE | |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

How to create a database?

mysql> create database hr;
Query OK, 1 row affected (0.00 sec)

How to drop a database?

mysql> drop database hr;
Query OK, 0 rows affected (0.00 sec)

How to create a table?

mysql> create table employees (employee_id integer, first_name varchar(20), last_name varchar(20)) type=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

How to drop a table?

mysql> drop table employees;
Query OK, 0 rows affected (0.00 sec)

How to create an index?

mysql> create index employees_pk on employees (employee_id);

How to create a user?

mysql> create user 'donghua'@ '192.168.1.%' identified by 'ora123';
Query OK, 0 rows affected (0.00 sec)

How to change password for a user?

mysql> set password for 'donghua'@'192.168.1.%' = password('ora123');
Query OK, 0 rows affected (0.00 sec)

How to grant privilege for a user?

mysql> grant select on hr.* to 'donghua'@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

How to drop a user?

mysql> drop user 'donghua'@'172.168.1.%';
Query OK, 0 rows affected (0.01 sec)

How to create a readonly user in single command?

mysql> grant select on hr.* to 'donghua'@'172.168.1.%' identified by 'ora123';
Query OK, 0 rows affected (0.01 sec)

How to check a user’s privilege?

mysql> show grants for 'donghua'@'192.168.1.%';
+---------------------------------------------------+
| Grants for donghua@192.168.1.% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'donghua'@'192.168.1.%' |
| GRANT SELECT ON `hr`.* TO 'donghua'@'192.168.1.%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

How to retrieve list of users?

mysql> select Host,user from mysql.user;
+-----------+------+
| Host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

How to monitor mysql status?

mysql> show status;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 7 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 200 |

| Threads_running | 1 |
| Uptime | 2059 |
| Uptime_since_flush_status | 92 |
+-----------------------------------+----------+
249 rows in set (0.01 sec)


mysql> show status like '%conn%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_connects | 7 |
| Connections | 24 |
| Max_used_connections | 1 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+--------------------------+-------+
7 rows in set (0.00 sec)

How to monitor thread status? (Similar to v$process & v$session)

mysql> show processlist;
+----+---------+--------------------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+--------------------------------+------+---------+------+-------+------------------+
| 23 | donghua | vmxdb01.lab.dbaglobe.com:22931 | NULL | Query | 0 | NULL | show processlist |
+----+---------+--------------------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> show full processlist;
+----+---------+--------------------------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+--------------------------------+------+---------+------+-------+-----------------------+
| 23 | donghua | vmxdb01.lab.dbaglobe.com:22931 | NULL | Query | 0 | NULL | show full processlist |
+----+---------+--------------------------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

How to kill a process (based on ID retrieved from “show processlist”)?

mysql> kill 25;
Query OK, 0 rows affected (0.00 sec)

How to “spool” the output to a file?

mysql> tee output.txt
Logging to file 'output.txt'
mysql> select * from hr.employees;
+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 1 | donghua | luo |
+-------------+------------+-----------+
1 row in set (0.00 sec)

mysql> notee
Outfile disabled.

How to execute OS command inside mysql command prompt?

mysql> system date
Wed Jul 28 22:50:01 SGT 2010

mysql> \! date
Wed Jul 28 22:50:04 SGT 2010

How to run a SQL file inside mysql command prompt?

mysql> source test.sql

mysql> \. test.sql

How to cancel a partial finished SQL statement?

mysql> select
-> \c
mysql>

How to retrieve your session status?

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.77 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 8 min 22 sec

Threads: 2 Questions: 37 Slow queries: 0 Opens: 24 Flush tables: 1 Open tables: 18 Queries per second avg: 0.074
--------------

How to dump data into a text file?

mysql> select * from hr.employees into outfile '/tmp/employees.txt';
Query OK, 1 row affected (0.00 sec)

How to load data from text file into table?

mysql> load data infile '/tmp/employees.txt' into table hr.employees;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

How to list global variables?

mysql> show global variables;

How to list session variables?

mysql> show session variables;


How to retrieve on 1 row (or n rows) from a table?

mysql> select * from hr.employees limit 1;
+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 1 | donghua | luo |
+-------------+------------+-----------+
1 row in set (0.00 sec)

How to turn on query log and slow query log?

[root@vmxdb01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-slow-queries=/var/log/slow-query-mysqld.log
log=/var/log/mysql_query.log
long_query_time=10

[root@vmxdb01 ~]# touch /var/log/slow-query-mysqld.log
[root@vmxdb01 ~]# touch /var/log/mysql_query.log
[root@vmxdb01 ~]# chown mysql:mysql /var/log/slow-query-mysqld.log
[root@vmxdb01 ~]# chown mysql:mysql /var/log/mysql_query.log
[root@vmxdb01 ~]# service mysqld restart

How to use escape character?

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> select '\\',"\\";
+---+---+
| \ | \ |
+---+---+
| \ | \ |
+---+---+
1 row in set (0.00 sec)


Performance: How to retrieve explain plan?

mysql> explain select * from employees where employee_id=1;
+----+-------------+-----------+-------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | employees_pk | employees_pk | 5 | const | 1 | |
+----+-------------+-----------+-------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from employees;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

How to start/stop mysql in standalone environment?

[root@vmxdb01 ~]# service mysqld status
mysqld (pid 19167) is running...
[root@vmxdb01 ~]# service mysqld stop
Stopping MySQL: [ OK ]
[root@vmxdb01 ~]# service mysqld status
mysqld is stopped
[root@vmxdb01 ~]# service mysqld start
Starting MySQL: [ OK ]
[root@vmxdb01 ~]#

Wednesday, August 11, 2010

What happened to your sequence/view/procedures during the "imp" if ignore=y

Basically they will follow the same rule that if already exists, the imp process will skip them. There is no "replace" keyword to be used.

For example:

"CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREM"
"ENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE"
"CREATE FORCE VIEW "DONGHUA"."V" ("VERSION") AS "
"select 'v2' version from dual"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = PLSQL"
"CREATE procedure p as begin dbms_output.put_line('v2');end;"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = NONE"
"ALTER PROCEDURE "P" COMPILE REUSE SETTINGS TIMESTAMP '2010-08-11:22:52:46'"

The side-effect for the sequence is the cache will be cleared even it's skipped.



SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
S 1 1.0000E+28 1 N N 20
21


SQL> CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
2 ;
CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> select s.nextval from dual;

NEXTVAL
----------
21


SQL> CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
2 ;
CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> select s.nextval from dual;

NEXTVAL
----------
41

Saturday, August 7, 2010

How the server process being created

Case 1: connecting through listener, and listener fork() itself and exec() to load oracle image to replace it. the server's parent process id will be same as listener's.

SQL> conn donghua/donghua@orcl
Connected.

SQL> select spid, process clientid from v$session s, v$process p where s.sid=userenv('SID') and s.paddr=p.addr;

SPID CLIENTID
------------------------ ------------------------
10908 10818

SQL> ! ps -ef|grep -e 10908 -e 10818 -e tnslsnr |grep -v grep
oracle 4233 1 0 18:34 ? 00:00:00 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 10818 10784 0 21:00 pts/1 00:00:00 sqlplus
oracle 10908 1 0 21:02 ? 00:00:00 oracleorcl (LOCAL=NO)


Case 2, connecting server without listener
In this case, the client process(for example, sqlplus) forks the new server process and loads oracle's image.

SQL> conn donghua/donghua
Connected.
SQL> select spid, process clientid from v$session s, v$process p where s.sid=userenv('SID') and s.paddr=p.addr;

SPID CLIENTID
------------------------ ------------------------
11036 10818

SQL> ! ps -ef|grep -e 11036 -e 10818 -e tnslsnr |grep -v grep
oracle 4233 1 0 18:34 ? 00:00:00 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 10818 10784 0 21:00 pts/1 00:00:00 sqlplus
oracle 11036 10818 0 21:07 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Thursday, August 5, 2010

Do we really need tempfiles and online redo logfiles for cold backup?

Answer is "NO".

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01_nosparse.dbf

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

SQL> ! rm /u01/app/oracle/oradata/orcl/redo03.log

SQL> ! rm /u01/app/oracle/oradata/orcl/redo02.log

SQL> ! rm /u01/app/oracle/oradata/orcl/redo01.log

SQL> ! rm /u01/app/oracle/oradata/orcl/temp01_nosparse.dbf

SQL> startup mount
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 197134048 bytes
Database Buffers 318767104 bytes
Redo Buffers 5869568 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

Oracle tempfile on linux is using sparse file by default

With 12GB free space, and if without sparse file, it's impossible to create a 20GB tempfile.

SQL> ! ls -lh /u01/app/oracle/oradata/orcl/temp01.dbf
-rw-r----- 1 oracle oinstall 201M Aug 5 13:54 /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> ! df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
26G 12G 12G 50% /

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 20G;

Database altered.

Elapsed: 00:00:00.02
SQL> ! ls -lh /u01/app/oracle/oradata/orcl/temp01.dbf
-rw-r----- 1 oracle oinstall 21G Aug 5 13:56 /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> ! df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
26G 12G 12G 50% /


It's possible to pre-allocate space to tempfile by copying current tempfile to a new file with "--sparse=never" option.

SQL> ! cp --sparse=never /u01/app/oracle/oradata/orcl/temp01.dbf /u01/app/oracle/oradata/orcl/temp01_nosparse.dbf

Drop temporary tablespace hang with "enq: TS - contention"

When i drop the temporary tablespace, the SQL command hangs.

After further check, it waits for "enq: TS - contention".

SQL> select sid,event,seconds_in_wait from v$session where username='DONGHUA' and status='ACTIVE';

SID EVENT SECONDS_IN_WAIT
---------- ---------------------------------------- ---------------
44 enq: TS - contention 21

And blocked by "SMON".

SQL> select * from v$lock where request>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
CTIME BLOCK
---------- ----------
3E68104C 3E681078 44 TS 7 1 0 6
29 0


SQL> select sid from v$lock where id1=7 and id2=1;

SID
----------
13
44

SQL> select program,status from v$session where sid=13;

PROGRAM STATUS
------------------------------------------------ --------
oracle@vmxdb01.lab.dbaglobe.com (SMON) ACTIVE

SQL> select sid,event,seconds_in_wait from v$session where sid=13;

SID EVENT SECONDS_IN_WAIT
---------- ---------------------------------------- ---------------
13 smon timer 87


Check which session is still using the "TEMP2"

SQL> SELECT se.username username,
2 se.SID sid, se.serial# serial#,
3 se.status status, se.sql_hash_value,
4 se.prev_hash_value,se.machine machine,
5 su.TABLESPACE tablespace,su.segtype,
6 su.CONTENTS CONTENTS
7 FROM v$session se,
8 v$sort_usage su
9 WHERE se.saddr=su.session_addr;

USERNAME SID SERIAL# STATUS SQL_HASH_VALUE
------------------------------ ---------- ---------- -------- --------------
PREV_HASH_VALUE MACHINE
--------------- ----------------------------------------------------------------
TABLESPACE SEGTYPE CONTENTS
------------------------------- --------- ---------
DONGHUA 41 259 INACTIVE 0
2640221370 WORKGROUP\ORACLE-PC
TEMP2 LOB_DATA TEMPORARY


After kill it, the problem resloved.

SQL> alter system kill session '41,259';

System altered.