ITeye博客

BURO#79 xxd

xxd · 共 18 篇文章

Import/Export between Oracle and MySQL

把从Oracle数据库导出的数据导入到MySql数据库中 1. 使用默认的结束符号导入到MySql数据库中: LOAD DATA LOCAL INFILE 'd:\oracle.txt' IGNORE INTO TABLE alarmLog 此时Fields的结束符默认的就是'\t'制表符,而不是"\t"字符串. 2. 当设定Fields结束符导入到MySql数据库中: LOAD DATA LOCAL INFILE 'd:\oraclea.txt' IGNORE INTO TABLE alarmLog FIELDS TERMINATED BY '~' 以上两种情况使用的数据文件oracle.…

2009-03-04 00:58 · 评论(0)

T-SQL - Using Trigger to monitor DML changes

1. Log table IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HotelOrder_OrderStatus_Log]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[HotelOrder_OrderStatus_Log]( [OrderStatusLogID] [int] IDENTITY(1,1) NOT NULL, [AppName] [nvarchar](50) NOT NULL, [EventInfo] [nvarch…

2009-03-04 00:22 · 评论(0)

T-SQL - Sync Table

/* --1---Create table---- */ create table Sync_Table ([id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Table_Name] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Sync_Table] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] /* --2---Inse…

2009-03-03 00:23 · 评论(0)

MySQL_Style - INSERT ... ON DUPLICATE KEY UPDATE

MySQL Performance Blog Par example, on vais creater une table pour noter le traffic de l'Internet, il y a une table avec le traffic et le numero de package de le port et l'IP de address. la table comme: Create Table: CREATE TABLE `ipstat` ( `ip` int(10) unsigned NOT NULL, `hits` int(10) unsigned NO…

2009-02-26 19:45 · 评论(0)

T-SQL - sp_executesql语法

--方法1查询表改为动态 select * from sysobjects exec('select ID,Name from sysobjects') exec sp_executesql N'select ID,Name from sysobjects'--多了一个N为unicode --方法2:字段名,表名,数据库名之类作为变量时,用动态SQL declare @FName varchar(20) set @FName='ID' exec('select '+@FName+' from sysobjects where '+@FName+'=5' ) declare @s varcha…

2009-02-25 00:41 · 评论(0)

T-SQL - 分页查询(ROW_NUMBER() OVER 语句)

DECLARE @rows_per_page AS INTEGER DECLARE @current_page AS INTEGER DECLARE @total_pages AS INTEGER DECLARE @start_item AS INTEGER DECLARE @items_count AS INTEGER -- 设置每页的行数 SET @rows_per_page = 10 -- 设置要显示的页号(从1开始) SET @current_page = 3 SELECT @total_pages = COUNT(*) / @rows_per_page + 1, @items_co…

2009-02-25 00:31 · 评论(0)

T-SQL - 拼脚本语句索引监控

use profiler to capture a server status for 24 hrs, the result stored into table Conn_AdminIII_2009_02_10 0. create index Create index idx_starttime on Conn_AdminIII_2009_02_10(starttime) 1.SQL query: select * from Conn_AdminIII_2009_02_10 where starttime between '2009-02-20 00:00:05.680' and '2009…

2009-02-25 00:29 · 评论(0)

MySQL shell(ETPASS): M/S status, Sendmail

This script is actually another slow log detector, but it will also send mail instead of writing logs only. 1. monitor MySQL, if the connection >= 30 then call the sendmail function --------------------------------------------------------- $ more master_mysql_monitor.sh #!/bin/sh flag=30 //more tha…

2009-01-19 22:18 · 评论(0)

Remotely disconnect a terminal services session

c:\ mstsc /v:Server IP /admin // on local c:\ tsadmin.exe // on target server, to kill some session, then reconnect by Remote Desktop You have to cp tsadmin.exe from windows 2003's system32 to XP Ref: http://thebackroomtech.com/2008/04/09/howto-remotely-disconnect-a-terminal-services-session/

2009-01-08 21:22 · 评论(0)

T-SQL creating folder

" This StoredProcedure is using master.sys.xp_dirtree and master.sys.xp_create_subdir USE Master; GO SET NOCOUNT ON -- 1 - Variable declaration DECLARE @DBName sysname DECLARE @DataPath nvarchar(500) DECLARE @LogPath nvarchar(500) DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT) -- 2 …

2009-01-06 19:53 · 评论(0)

T-SQL - Last Job Run Duration

select job_name, run_datetime, SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' + SUBSTRING(run_duration, 5, 2) AS run_duration from ( select job_name, DATEADD(hh, -7, run_datetime) as run_datetime, run_duration = RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6) fro…

2009-01-05 01:37 · 评论(0)

MySQL Index is slowing down the selecting actions

**Index is slowing down the selecting actions ** Description the problem: Table size 30G, Ram size 16G mysql> select * from program_access_log where program_id between 1 and 4000; very slow Try to select the top 500,000 records: mysql> select * from program_access_log where id between 1 and 500000 …

2008-12-25 23:27 · 评论(0)

T-SQL - While Vs Cursor

Example 1: SELECT * INTO #t1 FROM dbo.HorseBase DECLARE @id int DECLARE @t2 table (id int) WHILE (exists(SELECT HorseNumber FROM #t1)) BEGIN SELECT TOP 1 @id=HorseNumber FROM #t1 INSERT INTO @t2 SELECT HorseNumber FROM #t1 WHERE HorseNumber=@id DELETE FROM #t1 WHERE HorseNumber=@id END DROP Table #…

2008-12-23 00:30 · 评论(0)

T-SQL - Delete&Truncate tables

1. Delete all tables DECLARE @tablename varchar(50) DECLARE @truncatesql varchar(255) DECLARE TrCun_Cursor CURSOR FOR select [name] from sysobjects where type = 'U' -- or select [name] from sysobjects where type = 'U' and name 'table_you_do_NOT_want_to_delete' OPEN TrCun_Cursor FETCH TrCun_Cursor I…

2008-12-23 00:19 · 评论(0)

T-SQL - Using Trigger to monitor DDL changes

CREATE TABLE [DatabaseLog] ( ID INT PRIMARY KEY IDENTITY(1,1), Command NVARCHAR(1000), PostTime NVARCHAR(24), [Event] NVARCHAR(100), [Schema] NVARCHAR(100), LoginName NVARCHAR(100), XmlEvent xml, [object] NVARCHAR(100), [login] NVARCHAR(100), hostname NVARCHAR(100), spid NVARCHAR(24)) CREATE TRIGGE…

2008-12-23 00:19 · 评论(0)