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
Thursday, August 5, 2010
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.
Wednesday, June 23, 2010
Troubleshooting MySQL Replication with mysqlbinlog
[root@vmxdb01 mysql]# mysqlbinlog mysql-bin.000002
SET TIMESTAMP=1277302367/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
insert into employees values('a','b',1)
/*!*/;
# at 1053
#100623 22:33:07 server id 1 end_log_pos 1154 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1277303587/*!*/;
insert into employees values('b','c',2)
/*!*/;
# at 1154
#100623 22:42:34 server id 1 end_log_pos 1255 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1277304154/*!*/;
insert into employees values('b','c',3)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@vmxdb01 mysql]# mysql -h vmxdb01 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select from_unixtime(1277304154);
+---------------------------+
| from_unixtime(1277304154) |
+---------------------------+
| 2010-06-23 22:42:34 |
+---------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
Tuesday, June 8, 2010
LOBSEGMENT defragmentation
Method 1: Shrink space, which is slow
Method 2: "Move" LobSegment
Method 2: "Move" LobSegment
[oracle@vmxdb01 ~]$ sqlplus donghua/ora123
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 8 15:02:14 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set echo on
SQL> @lob_test.sql
SQL> col segment_name for a30
SQL> col index_name for a30
SQL> set lin 90
SQL> col mbytes for 99999999999
SQL> set timing on
SQL>
SQL> drop table tbl_lob_test purge;
Table dropped.
Elapsed: 00:00:01.70
SQL>
SQL> CREATE TABLE tbl_lob_test
2 (
3 col1 CHAR (2000),
4 col2 CHAR (2000),
5 col3 CHAR (2000),
6 message CLOB
7 )
8 LOB (message) STORE AS (TABLESPACE users DISABLE STORAGE IN ROW);
Table created.
Elapsed: 00:00:00.21
SQL>
SQL> insert into tbl_lob_test
2 select owner, object_name,object_name, object_name from dba_objects;
72458 rows created.
Elapsed: 00:00:56.44
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME from dba_lobs
2 where table_name='TBL_LOB_TEST' and owner='DONGHUA';
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000074592C00004$$ SYS_IL0000074592C00004$$
Elapsed: 00:00:00.14
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 4194304
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 603979776
Elapsed: 00:00:02.15
SQL>
SQL> delete from tbl_lob_test;
72458 rows deleted.
Elapsed: 00:01:13.64
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 11534336
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 603979776
Elapsed: 00:00:02.62
SQL>
SQL> insert into tbl_lob_test
2 select owner, object_name,object_name, object_name from dba_objects;
72458 rows created.
Elapsed: 00:01:26.81
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 14680064
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 1207959552
Elapsed: 00:00:01.68
SQL>
SQL> alter table tbl_lob_test modify lob (message) (shrink space);
Table altered.
Elapsed: 00:02:29.18
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 14680064
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 603979776
Elapsed: 00:00:01.09
SQL>
SQL> alter table tbl_lob_test
2 move lob (message) STORE AS tbl_lob_test_lob_msg
3 (TABLESPACE users
4 index tbl_lob_test_lob_msg_idx (tablespace users) );
Table altered.
Elapsed: 00:01:06.87
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 4194304
TBL_LOB_TEST 603979776
TBL_LOB_TEST_LOB_MSG 603979776
Elapsed: 00:00:00.05
SQL>
SQL> truncate table tbl_lob_test;
Table truncated.
Elapsed: 00:00:02.10
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 65536
TBL_LOB_TEST 65536
TBL_LOB_TEST_LOB_MSG 65536
Elapsed: 00:00:00.06
SQL>
SQL> drop table tbl_lob_test purge;
Table dropped.
Elapsed: 00:00:00.49
SQL>
SQL> CREATE TABLE tbl_lob_test
2 (
3 col1 CHAR (2000),
4 col2 CHAR (2000),
5 col3 CHAR (2000),
6 MESSAGE CLOB
7 )
8 LOB (
9 MESSAGE)
10 STORE AS
11 tbl_lob_test_lob_msg (
12 TABLESPACE users
13 DISABLE STORAGE IN ROW
14 INDEX tbl_lob_test_lob_msg_idx ( TABLESPACE users ));
Table created.
Elapsed: 00:00:00.18
SQL>
SQL> insert into tbl_lob_test values (1,2,3,4);
1 row created.
Elapsed: 00:00:00.06
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;
SEGMENT_NAME MBYTES
------------------------------ ------------
TBL_LOB_TEST_LOB_MSG_IDX 65536
TBL_LOB_TEST 65536
TBL_LOB_TEST_LOB_MSG 65536
Elapsed: 00:00:00.05
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME from dba_lobs
2 where table_name='TBL_LOB_TEST' and owner='DONGHUA';
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
TBL_LOB_TEST_LOB_MSG TBL_LOB_TEST_LOB_MSG_IDX
Elapsed: 00:00:00.04
Friday, May 14, 2010
Useful SQL commands during the database mirroring / log shipping
ALTER DATABASE TESTDB SET
SINGLE_USER WITH Rollback Immediate
Go
/****** Object: Database [TESTDB] Script Date: 05/12/2010 14:21:21 ******/
CREATE DATABASE [TESTDB_SS] ON PRIMARY
( NAME = N'TESTDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SDC\MSSQL\DATA\TESTDB.ss' )
AS SNAPSHOT OF TESTDB
GO
ALTER DATABASE testdb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
GO
SINGLE_USER WITH Rollback Immediate
Go
/****** Object: Database [TESTDB] Script Date: 05/12/2010 14:21:21 ******/
CREATE DATABASE [TESTDB_SS] ON PRIMARY
( NAME = N'TESTDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SDC\MSSQL\DATA\TESTDB.ss' )
AS SNAPSHOT OF TESTDB
GO
ALTER DATABASE testdb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
GO
Wednesday, March 31, 2010
Difference with "traceonly explain", "autotrace on" and "traceonly statistics"
Explain plan is supported by default:
But reporting statistics requires plustrace role.
SQL> set autotrace traceonly explain
But reporting statistics requires plustrace role.
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> set autotrace traceonly statistics
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> get ?\sqlplus\admin\plustrce.sql
1 --
2 -- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
3 --
4 -- NAME
5 -- plustrce.sql
6 --
7 -- DESCRIPTION
8 -- Creates a role with access to Dynamic Performance Tables
9 -- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
10 -- After this script has been run, each user requiring access to
11 -- the AUTOTRACE feature should be granted the PLUSTRACE role by
12 -- the DBA.
13 --
14 -- USAGE
15 -- sqlplus "sys/knl_test7 as sysdba" @plustrce
16 --
17 -- Catalog.sql must have been run before this file is run.
18 -- This file must be run while connected to a DBA schema.
19 set echo on
20 drop role plustrace;
21 create role plustrace;
22 grant select on v_$sesstat to plustrace;
23 grant select on v_$statname to plustrace;
24 grant select on v_$mystat to plustrace;
25 grant plustrace to dba with admin option;
26* set echo off
27
Monday, March 15, 2010
Partition by range with Interval example
SQL> select min(time_id) from sh.sales;
MIN(TIME_ID)
--------------------
1998-JAN-01 00:00:00
SQL> create table sales
2 partition by range (time_id)
3 interval (numtoyminterval(1,'MONTH'))
4 (
5 partition p199801 values less than (to_date('1998-02-01','yyyy-mm-dd'))
6 )
7 as select * from sh.sales;
Table created.
SQL> create index sales_n1 on sales(time_id) local;
Index created.
SQL> exec dbms_stats.gather_table_stats('','sales');
PL/SQL procedure successfully completed.
SQL> set lin 120
SQL> col partition for a10
SQL> col high_value for a80
SQL> col num_rows for 99999999
SQL> set pages 999
SQL> select partition_name,high_value,num_rows
2 from user_tab_partitions
3 where table_name='SALES';
PARTITION_ HIGH_VALUE NUM_ROWS
---------- -------------------------------------------------------------------------------- ---------
P199801 TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 15132
SYS_P69 TO_DATE(' 1998-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14307
SYS_P70 TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14248
SYS_P71 TO_DATE(' 1998-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11818
SYS_P72 TO_DATE(' 1998-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 12309
SYS_P73 TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11631
SYS_P74 TO_DATE(' 1998-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 16257
SYS_P75 TO_DATE(' 1998-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17199
SYS_P76 TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17059
SYS_P77 TO_DATE(' 1998-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19878
SYS_P79 TO_DATE(' 1998-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14612
SYS_P78 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14384
SYS_P80 TO_DATE(' 1999-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20437
SYS_P81 TO_DATE(' 1999-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 24122
SYS_P82 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19627
SYS_P83 TO_DATE(' 1999-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17004
SYS_P84 TO_DATE(' 1999-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19213
SYS_P85 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 18016
SYS_P86 TO_DATE(' 1999-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21889
SYS_P87 TO_DATE(' 1999-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22225
SYS_P88 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 23024
SYS_P89 TO_DATE(' 1999-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22256
SYS_P91 TO_DATE(' 1999-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21259
SYS_P90 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 18873
SYS_P92 TO_DATE(' 2000-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22135
SYS_P93 TO_DATE(' 2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20609
SYS_P94 TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19453
SYS_P95 TO_DATE(' 2000-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17481
SYS_P96 TO_DATE(' 2000-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20046
SYS_P97 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17988
SYS_P98 TO_DATE(' 2000-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 18534
SYS_P99 TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20369
SYS_P100 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20047
SYS_P101 TO_DATE(' 2000-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21542
SYS_P102 TO_DATE(' 2000-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19709
SYS_P103 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14733
SYS_P104 TO_DATE(' 2001-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20739
SYS_P105 TO_DATE(' 2001-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19185
SYS_P106 TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20684
SYS_P107 TO_DATE(' 2001-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21667
SYS_P108 TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19188
SYS_P109 TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22437
SYS_P110 TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21860
SYS_P112 TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 23330
SYS_P111 TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20579
SYS_P115 TO_DATE(' 2001-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 24537
SYS_P113 TO_DATE(' 2001-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22403
SYS_P114 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22809
48 rows selected.
Subscribe to:
Posts (Atom)
