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>
Thursday, August 19, 2010
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
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.
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.
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 ~]#
[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
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)))
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.
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
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".
And blocked by "SMON".
Check which session is still using the "TEMP2"
After kill it, the problem resloved.
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.
Subscribe to:
Posts (Atom)