找尋檔案中的字串-find / grep 指令

在linux底下，找尋目錄及子目錄的檔案的字串：

要利用 find 的功能來協助，就嘗試寫寫看囉！我的寫法如下：

# find ./ -type f -name \*.php |xargs grep 'localhost'

# grep -r --include='*.php' localhost .

Read More

Create partition Table/Index

Create partition Table/Index：

1.      確認是否需要新增Table/Index Tablespace

2.      確認空間是否足夠

3.      Create Partition table

4.      Split Partition

5.      Create Partition Index

6.      確認建立的Partition(找出最大的Partition)

一、產生新的 Table/index Tablespace語法(自行確認是否要新增)：

CREATE TABLESPACE STGC5_TRG_2015Q1 DATAFILE

  '/rptdata1/c5prpt/STGC5_TRG_2015Q1_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M,

  '/rptdata2/c5prpt/STGC5_TRG_2015Q1_02.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M,

  '/rptdata3/c5prpt/STGC5_TRG_2015Q1_03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M,

  '/rptdata4/c5prpt/STGC5_TRG_2015Q1_04.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

二、確認Tablespace空間大小：

select t.tablespace_name ,avg(d.bytes/d.maxbytes)*100 as Used_Rate,sum(d.maxbytes)/1024/1024/1024 as max_g,sum(d.bytes)/1024/1024/1024 as Used_g,

       (sum(d.maxbytes)-sum(d.bytes))/1024/1024/1024 as free_space_g

FROM DBA_TABLESPACES T,DBA_DATA_FILES D

WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 group by t.tablespace_name

ORDER BY Free_space_g

三、增加Partition Table:

1.日期格式：

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' ADD PARTITION P2015Q1 VALUES LESS THAN (''20150401 000000'') TABLESPACE DWC5_TBL;'

from dba_tab_partitions t where table_owner='DWC5' AND PARTITION_NAME='P2014Q1';

結果：

ALTER TABLE DWC5.CUT_TIME_WIP ADD PARTITION P2015Q1 VALUES LESS THAN ('20150401 000000') TABLESPACE DWC5_TBL;

2.時間格式：

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' ADD PARTITION P2015Q1 VALUES LESS THAN TO_DATE('' 2015-04-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'') TABLESPACE DWC5_TBL;'

from dba_tab_partitions t where table_owner='DWC5' AND PARTITION_NAME='P2014Q1';

結果：

ALTER TABLE DWC5.CUT_TIME_WIP ADD PARTITION P2015Q1 VALUES LESS THAN TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TABLESPACE DWC5_TBL;

四、Split Partition

SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' SPLIT PARTITION CTHI_MAXVALUE (TO DATE(''20170101 000000'') INTO (PARTITION '

||PARTITION_NAME|| ', PARTITION CTHI_MAXVALUE TABLESPACE) ' ||TABLESPACE_NAME|| ';' from dba_tab_partitions t

where table_owner='C5PROD' AND PARTITION_NAME LIKE '%2015Q4';

結果：

ALTER TABLE C5PROD.FWAMTCOMPTRACKHISTORY SPLIT PARTITION CTHI_MAXVALUE (TO DATE('20170101 000000') INTO (PARTITION CTHI_2015Q4, PARTITION CTHI_MAXVALUE) TABLESPACE C5_PTT_CTHI10;

五、增加Partition Index

每季增加：

SELECT * FROM (SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD PARTITION P2015Q4 TABLESPACE= ' || TABLESPACE_NAME || ';'  from dba_segments 

where tablespace_name='DWC5_IDX' AND PARTITION_NAME='P2014Q4');

結果：

ALTER INDEX DWC5.XPKCUT_TIME_WIP_P REBUILD PARTITION P2015Q4 TABLESPACE= DWC5_IDX;

一次找出一整年(Partition table要先建立好)：

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD PARTITION '||PARTITION_NAME|| ' TABLESPACE '||TABLESPACE_NAME|| ';'

FROM ALL_IND_PARTITIONS WHERE INDEX_OWNER='STGC5' AND PARTITION_NAME LIKE '%2014Q%';

結果：

ALTER INDEX IDX_COMPDLTYPETIME_600A REBUILD PARTITION P2014Q1 TABLESPACE STGC5_TRG_IDX_2014Q1;

六、找出最大的Partition：

SELECT INDEX_NAME,max(PARTITION_NAME) FROM ALL_IND_PARTITIONS WHERE INDEX_OWNER='STGC5'  group by INDEX_NAME order by max(PARTITION_NAME);

參考資料：

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm

Read More

Linux下安裝xrdp

Linux下安裝xrdp

使用rdp協定訪問遠端Linux桌面

Read More

被動收入-分類

一般來說被動收入有兩種方式:
1.透過資本賺被動收入(較適合有一桶金以上的人)
2.透過技能專長興趣賺被動收入(較適合時間較多的人)

Read More

DB File/Tablespace Size Monitor

查看每個File的使用率
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES/1024/1024/1024 as USED_G,D.MAXBYTES/1024/1024/1024 AS MAX_G,D.STATUS,(d.bytes/d.maxbytes)*100 AS UseRate
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 ORDER BY UseRate desc

查看Tablespace的平均使用率及剩餘空間
select t.tablespace_name ,avg(d.bytes/d.maxbytes)*100 as Used_Rate,sum(d.maxbytes)/1024/1024/1024 as max_g,sum(d.bytes)/1024/1024/1024 as Used_g,
       (sum(d.maxbytes)-sum(d.bytes))/1024/1024/1024 as free_space_g
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 group by t.tablespace_name
ORDER BY Free_space_g

Read More

DB常用指令

Create Function:

CREATE INDEX DWPIA.IDX_SAP_STB_MTIDX ON DWPIA.SAP_STB
(MTIDX, RDATE)
LOGGING
TABLESPACE DWC5_IDX
NOPARALLEL online;

CREATE TABLESPACE C5_SYS_DATA DATAFILE '+DBDATA/c5tasm/datafile/C5_SYS_DATA.dbf' SIZE 5M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

CREATE USER C5TST3 IDENTIFIED BY C5TST3 ACCOUNT UNLOCK;

Drop Function:

DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES;

Grant Function:

GRANT RESOURCE TO C5TST3;
GRANT CREATE SESSION TO C5TST3

Read More

Oracle ASM To File File:：DBMS_FILE_TRANSFER

Oracle ASM To File File:：DBMS_FILE_TRANSFER
建立Directory：
SQL> create directory souredir as '+DBDATA/c5tasm/datafile';
SQL> create directory destdir as '/orasys';
Directory created

確認directories:
SQL>select * from dba_directories;

確認File的Status:
SQL>select file_name, file_id, online_status from dba_data_files order by File_id;

Tablespace C5OPI Offline:
SQL> alter tablespace C5OPI offline;
Tablespace altered.

做資料轉換：
SQL>begin
  dbms_file_transfer.copy_file(source_directory_object => 'destdir',
                               source_file_name => 'C5OPI_DATA_01.DBF',
                               destination_directory_object =>'souredir',
                               destination_file_name => 'C5OPI_DATA_01.DBF');
end;
/

把位置rename一下：
SQL> alter database rename file '/orasys/C5OPI_DATA_01.DBF' to  '+DBDATA/c5tasm/datafile/c5opi_data_01.dbf';
Database altered.

Recover datafile:
SQL> alter database recover datafile 6;

把Tablespace轉Online:
SQL> alter tablespace c5opi online;

確認最後狀況：
SQL>select file_name, file_id, online_status from dba_data_files;


Reference:
11gr2轉換:透過ASMCMD
DBMS_FILE_TRANSFER包方法
RMAN轉換
http://blog.itpub.net/17203031/viewspace-1065094/
================================================================
This e-mail and any files transmitted with it are CONFIDENTIAL and intended solely for the use of the intended recipient.  If you are not the intended recipient or the named addressee, please notify the sender immediately and delete this e-mail and any files transmitted with it from your system; you should not disseminate, distribute, or copy this e-mail and any files transmitted with it, or take any action in reliance on the contents of the said e-mail and files.
================================================================

Read More

Linux Multipath (Storage I/O)

Linux Multipath (Storage I/O)

l         Multipath說明

儲存裝置到主機有多條路徑, 這就叫作 Multipath, 一般在外接 Storage 上較常看到

Multipath 一般常看到的是在 Storage 上有一個以上的 Controller 或是 供外部主機存取的介面, 這個情況下你會發現, 我在 Storage 上明明只切了一塊空間給主機, 但是再主機上卻看到兩個空間

Read More