Showing all posts tagged #sql:


PostgreSQL pgSQL 終止沒有回應的 Query

Posted on November 24th, 2016


由於對較大的 Table 執行 update SQL 可能因為網路環境等因素, 造成沒有回應,
所以不用 GUI 操作, 用 CLI 操作, 當然有時候還是會卡住, 結果即使用 psql.exe 還是每次都卡住...

問題可能是因為 網路?

C:\pgAdminIII\1.22\psql.exe -U ihergo -h 60.199.195.113 -d ihergo -a -f a.sql -L a.log

另外, 可以透過 SQL 來查詢目前正在執行的 Query

SELECT * FROM pg_stat_activity ;

可以看 datname, application_name, state=Idle 欄位值



查詢後, 如果遇到想要中止的 Query 可以:


SELECT pg_cancel_backend(pid); 或 SELECT pg_terminate_backend(pid); // like force

MSSQL varchar, nvarchar 預設長度 30

Posted on April 15th, 2016

MSSQL varchar, nvarchar 預設長度 30

nvarchar, varchar 不給長度時, 有預設 30 的限制

以後記得, 所有對資料庫欄位的 update, 都應該要備份, 以確保不會有慘劇發生…

  • 方法1: 新增一個備份用的欄位, 將值 copy 過去
  • 方法2: 直接對資料庫備份
2016/04/15 某案子得到的教訓…

Oracle 資料庫密碼過期相關指令

Posted on February 26th, 2015

  1. 密碼過期時就得修改密碼 , 否則該帳號將無法使用:

    ALTER USER "使用者名稱" IDENTIFIED BY "新密碼" REPLACE "舊密碼"

  2. 查詢 Default user profile 的密碼期限: (預設是 180 天)
    SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

  3. 查詢 user 是哪一種 profile: (預設應該都是 default)
    SELECT username,PROFILE FROM dba_users;

  4. 修改 default user profile 密碼過期的時間 (這邊改為無限制: UNLIMITED)
    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

注意:

  • 按照 第4點 的修改 user profile 會降低帳號的安全性, 因為原本預設是 180 天密碼到期, 期至便須修改密碼, 改無限制意思是永遠不會過期. (所以我只用在開發用的機器上, 產品或客戶的資料庫不要用)

參考資料:

  1. Oracle 11g密碼過期問題 ORA-28002

  2. Oracle 11g存在密碼過期問題


透過 SQL 查詢樹狀結構某節點下的所有節點

Posted on December 26th, 2014

透過 SQL 查詢 Oracle 樹狀結構某節點下的所有節點

  • ORACLE 限定方法: START WITH ... CONNECT BY PRIOR ...
  • Oracle ex:
SELECT Id
FROM Category START WITH Id=2 CONNECT BY
PRIOR Id=Parentid

透過 SQL 查詢 MSSQL 樹狀結構某節點下的所有節點

  • MSSQL 似乎沒有直接的查詢語法(?), 但有 with
  • MSSQL ex:
WITH n (id, name) AS
    (SELECT id, name
    FROM category
    WHERE id = 2 --父節點 id
    UNION ALL SELECT child.id, child.name
    FROM Category AS child, n
    WHERE n.id = child.parentId)

SELECT * FROM n

如果要透過 n 再查詢, 例如查詢分類樹底下所有 binder
(假設 BinderCategory 透過 Binder_Category 來記錄關聯性)

WITH n (id, name) AS (SELECT id, name FROM category WHERE id = 2 --父節點 id UNION ALL SELECT child.id, child.name FROM Category AS child, n WHERE n.id = child.parentId) SELECT * FROM Binder bb LEFT JOIN Binder_Category bc ON bc.binder_id = bb.id WHERE bc.cat_id IN (SELECT id FROM n)

(感謝 April 大大教學)


MSSQL Server 對已經存在的 table 增加識別 id 欄位 (從1開始)

Posted on November 7th, 2014

Sql Server add auto increment primary key to existing table

No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:

ALTER TABLE dbo.YourTable
  ADD ID INT IDENTITY

and then you can make it the primary key:

ALTER TABLE dbo.YourTable
  ADD CONSTRAINT PK_YourTable
  PRIMARY KEY(ID)

REF: http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table


SQL Server:匯入資料遭遇“文字已截斷”錯誤

Posted on November 7th, 2014

匯入大檔, 錯誤大概都會長這樣...

原文告訴大家改用一般檔案來源, 且匯入時對應的欄位型態要修改!

以下整理自原文的建議:

  1. 資料來源選擇: 一般檔案來源

  2. 選好欲匯入的檔案,左側點選 進階

  3. 將文字欄位的 DataType 改成 文字資料流[DT_TEXT]

  4. 將整數欄位的 DataType 改成 二位元組不帶正負號的整數[DT_UI2]

圖片來源: http://lanfar.pixnet.net

REF: SQL Server:匯入資料遭遇“文字已截斷"錯誤 from 蘭花❉心得報告


SQL Server Drop All The Tables

Posted on September 17th, 2014

這就是 Stored Procedures, 不過這似乎要有權限才能完成!

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

How to fetch the row count for all tables in a SQL SERVER database

Posted on September 16th, 2014

The following SQL will get you the row count of all tables in a database:

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

The output will be a list of tables and their row counts.
If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

REF:
Fetch_row_count_for_all_tables_in_a_SQL_SERVER by adrianbanks from stackoverflow.com


在 SQL Server 2005 中取得所有欄位定義的方法(含備註欄位)

Posted on June 5th, 2014



SQL 這個算超好用的... 如果要請客戶幫忙 debug

結果會把所有 table 以及各個欄位的資訊放在一起輸出


REF: [The Will Will Web](http://blog.miniasp.com/post/2007/11/05/How-to-get-detailed-Data-Dictionary-in-SQL-Server-2005.aspx)



sql指令取得table內的欄位名稱

Posted on June 5th, 2014

###MSSQL
``select name from syscolumns where id=object_id('tablename')``

###Oracle
```
select COLUMN_NAME from all_tab_columns
where owner='OWNER' --需要大寫,否則找不到
and table_name='DIS_PHON_ACC' --需要大寫,否則找不到
order by column_id
```

REF: [sql指令取得table內的欄位名稱](http://blog.roodo.com/fionscenery/archives/19070448.html)

Liu@llen

Notes from my experience.