1. Tạo Profiles

Các lệnh SQL cơ bản hay sử dụng

1. Tạo Profiles

create PROFILE APPS_PROFILES LIMIT
    FAILED_LOGIN_ATTEMPTS UNLIMITED
    PASSWORD_LIFE_TIME UNLIMITED
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_GRACE_TIME UNLIMITED;  

2. Tạo Tablespace

CREATE TABLESPACE APPS01_DEFAULT
DATAFILE '/home/oracle/datafile/APPS01_DEFAULT_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/home/oracle/datafile/APPS01_DEFAULT_02.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
commit;

CREATE TABLESPACE APPS01_DATA
DATAFILE '/home/oracle/datafile/APPS01_DATA_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'/home/oracle/datafile/APPS01_DATA_02.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
commit;

3. Tạo User

CREATE USER APPS01 PROFILE APPS_PROFILES IDENTIFIED BY Apps20160720
DEFAULT TABLESPACE APPS01_DEFAULT 
TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CONNECT TO APPS01;
GRANT RESOURCE TO APPS01;
commit;

4. Tạo bảng Partitions, Procedures

- Partition theo tháng:

create table ACTION_LOGS(   
MODULE  VARCHAR2(50),
LOG_TIME    TIMESTAMP(7),
IP_ADDRESS    VARCHAR2 (20),
ACTION    VARCHAR2 (1500),
RESULT    VARCHAR2 (1500),
USERNAME    VARCHAR2 (50),
USER_TYPE    NUMBER)
tablespace APPS01_DATA
PARTITION BY RANGE (LOG_TIME)
    INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION M201306 VALUES LESS THAN (TO_DATE('01-07-2013','DD-MM-YYYY')) TABLESPACE "APPS01_DATA",
    PARTITION M201307 VALUES LESS THAN (TO_DATE('01-08-2013','DD-MM-YYYY')) TABLESPACE "APPS01_DATA",
    PARTITION M201308 VALUES LESS THAN (TO_DATE('01-09-2013','DD-MM-YYYY')) TABLESPACE "APPS01_DATA", 
    PARTITION M201309 VALUES LESS THAN (TO_DATE('01-10-2013','DD-MM-YYYY')) TABLESPACE "APPS01_DATA", 
    PARTITION M201310 VALUES LESS THAN (TO_DATE('01-11-2013','DD-MM-YYYY')) TABLESPACE "APPS01_DATA", 
    PARTITION M201311 VALUES LESS THAN (TO_DATE('01-12-2013','DD-MM-YYYY')) TABLESPACE "APPS01_DATA",
    PARTITION M201312 VALUES LESS THAN (TO_DATE('01-01-2014','DD-MM-YYYY')) TABLESPACE "APPS01_DATA" 
); 

- Partition theo ngày

CREATE TABLE subscription_logs
(msisdn                         VARCHAR2(20) NOT NULL,
sub_service_name               VARCHAR2(50) NOT NULL,
tranid                         VARCHAR2(50),
action                         VARCHAR2(500),
result                         NUMBER,
datetime                       TIMESTAMP (6))
  TABLESPACE  APPS01_DATA
PARTITION BY RANGE (datetime)
INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(
PARTITION D20140413 VALUES LESS THAN (TO_DATE('13-04-2014','DD-MM-YYYY')) TABLESPACE APPS01_DATA,
PARTITION D20140414 VALUES LESS THAN (TO_DATE('14-04-2014','DD-MM-YYYY')) TABLESPACE APPS01_DATA,
PARTITION D20140415 VALUES LESS THAN (TO_DATE('15-04-2014','DD-MM-YYYY')) TABLESPACE APPS01_DATA
);  

- Tạo bảng log

CREATE TABLE vlogger

    (logcontent                     VARCHAR2(200),

    logcomment                     VARCHAR2(200),

    writelogtime                   TIMESTAMP (6),

    object                         VARCHAR2(50),

    schema                         VARCHAR2(30),

    error_code                     VARCHAR2(20),

    log_code                       VARCHAR2(500) NOT NULL);

CREATE INDEX ind_vlogger ON vlogger
  (
    "WRITELOGTIME" DESC
  ) ;

alter index ind_vlogger rebuild;

- Tạo Procedure write log

CREATE PROCEDURE VWRITELOG (    
    LOGMSG IN VARCHAR2,
    LOGCOMMENT IN VARCHAR2,
    OBJ IN VARCHAR2)
AS
v_schema varchar2(30);
v_errorcode varchar2(30);
v_log_code varchar2(100);
BEGIN
v_errorcode:=substr(LOGMSG,0,9);
SELECT username INTO v_schema FROM user_users;
select v_schema||to_Char(sysdate,'yymmddhh24miss')||v_errorcode||OBJ into v_log_code from dual;

INSERT INTO VLOGGER (logcontent, logcomment, writelogtime, object,schema,error_code,log_code)
VALUES(LOGMSG,LOGCOMMENT,SYSDATE,OBJ,v_schema,v_errorcode,v_log_code);
COMMIT;
END; 

- Mẫu Procedure

create or replace 
PROCEDURE PR_SMSGW_INS_MT 
(
P_MSISDN    VARCHAR2,
P_SHORT_CODE  VARCHAR2,
P_ALIAS    VARCHAR2,
P_CONTENTS    VARCHAR2,
P_USER    VARCHAR2,
P_DATETIME    TIMESTAMP
)
AS
   ERRMSG VARCHAR2(200);  
   p_id  VARCHAR2 (5); 

BEGIN
INSERT INTO SMSGW_MT_LOGS (
      MSISDN,
      SHORTCODE,
      ALIAS, 
      CONTENT, 
      USERNAME, 
      DATETIME
              )
  VALUES   (
                    P_MSISDN, P_SHORT_CODE, P_ALIAS, P_CONTENTS, P_USER, P_DATETIME
              );
        COMMIT;
        EXCEPTION
      WHEN OTHERS THEN -- HANDLES ALL OTHER ERRORS
      ERRMSG := SUBSTR(SQLERRM, 1, 200); 
      VWRITELOG(ERRMSG,'EXCEPTION:PR_SMSGW_INS_MT','PR_SMSGW_INS_MT');
RAISE;
END;

5. Tạo jobs

BEGIN
DBMS_JOB.isubmit (
job => 771,
what => 'BEGIN CHKTBLSPACE; COMMIT; END;',
next_date => SYSDATE + 0.5/24,
interval => 'SYSDATE + 0.5/24');
COMMIT;
END;

6. Các lệnh thường dùng

- Resize Tablespace
alter database datafile /u01/oracle/oradata/booktst_users_02.dbf' resize 10G;

- ADD Datafile
alter tablespace APPS01 add datafile /u01/oracle/oradata/booktst_users_02.dbf' size 150M autoextend on;

- Size of db
select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)","Free_Space(GB)"
from(
select 
(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",
(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"
from dual
);

- Size of object
select sum(bytes)/1024/1024/1024 size_in_megabytes, segment_name
from user_extents
where segment_name = 'IDX_BILLING_CDR_HIS'
group by segment_name

- Kiểm tra tham số DB
show parameter process
show parameter cursor
show parameter sessions

- Thiết lập tham số
alter system set processes = 5000 scope=spfile;
alter system set open_cursors = 5000 scope=both;
ALTER SYSTEM SET processes=3000 scope=spfile sid='*';
ALTER SYSTEM SET sessions=2000 scope=spfile sid='*';

- Đổi charset cho DB
Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';

UPDATE PROPS$
SET VALUE$ = 'AL32UTF8'
WHERE NAME = 'NLS_CHARACTERSET';

- Một số lệnh import/export:

expdp system/****** SCHEMAS=MDMV1 DUMPFILE=mdmv119DEC2011.dmp NOLOGFILE=y

impdp system/***** SCHEMAS=MDMV1 DUMPFILE=mdmv119DEC2011.dmp NOLOGFILE=y

+ Structure only:
expdp system/**** SCHEMAS=NGP DUMPFILE=NGP_STRUCT_24Sep.dmp CONTENT=metadata_only NOLOGFILE=y
expdp system/**** SCHEMAS=MDM DUMPFILE=MDM_STRUCT_23Aug.dmp CONTENT=metadata_only NOLOGFILE=y
expdp system/**** SCHEMAS=NGP DUMPFILE=NGP_STRUCT_23Aug.dmp CONTENT=metadata_only NOLOGFILE=y
impdp system/**** SCHEMAS=NGP DUMPFILE=NGP_STRUCT_23Aug.dmp CONTENT=metadata_only NOLOGFILE=y
exp qtan_vt/***** FILE=QTAN2.dmp OWNER=qtan_vt GRANTS=y ROWS=y COMPRESS=y 
imp system/****file=qtan07SEP2012.dmp fromuser=qtan touser=qtan_new

expdp ${ORA_USER}/${ORA_PASS} DIRECTORY=DUMP_DIR file=$DUMP_FILE tables=TRANSACTION_MT NOLOGFILE=y

- Kiểm tra dung lượng ASM
select * from v$asm_disk
select * from V$ASM_DISKGROUP

- Delete Archivelog
RMAN> CONNECT target /
connected TO target DATABASE: INSTANT (DBID=654321)
crosscheck archivelog ALL;
list expired archivelog ALL; 
DELETE expired archivelog ALL;
delete archivelog all completed before 'SYSDATE-1';
--force delete (nếu lệnh delete trên không xóa được): 
delete force noprompt archivelog all completed before 'sysdate -1';

- Đổi kích thước recovery
SQL> alter system set db_recovery_file_dest_size=20G;
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;
SQL> archive log all;

- Loại bỏ ký tự xuống dòng trong bản ghi
regexp_replace(status,'([^[:graph:]|^[:blank:]])','')

- Xóa Database

SQL> STARTUP NOMOUNT RESTRICT;
SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> DROP DATABASE;

Database dropped.

- Khởi động DB từ spfile
strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora > /export/home/pfile.ora
--#now edit the /export/home/pfile.ora if any wrong character and then create spfile from that.
SQL>!vi /export/home/pfile.ora
SQL>create spfile from pfile='/u01/app/oracle/admin/ora140/pfile/init.ora.518201611520';
SQL>startup

- Chuyển Partitions
ALTER TABLE table_a EXCHANGE PARTITION part100 WITH TABLE swap_table;

- Top activity
select sql_text,
       username,
       disk_reads_per_exec,
       buffer_gets,
       disk_reads,
       parse_calls,
       sorts,
       executions,
       rows_processed,
       hit_ratio,
       first_load_time,
       sharable_mem,
       persistent_mem,
       runtime_mem,
       cpu_time,
       elapsed_time,
       address,
       hash_value
from
(select sql_text ,
        b.username ,
 round((a.disk_reads/decode(a.executions,0,1,
 a.executions)),2)
       disk_reads_per_exec,
       a.disk_reads ,
       a.buffer_gets ,
       a.parse_calls ,
       a.sorts ,
       a.executions ,
       a.rows_processed ,
       100 - round(100 *
       a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
       a.first_load_time ,
       sharable_mem ,
       persistent_mem ,
       runtime_mem,
       cpu_time,
       elapsed_time,
       address,
       hash_value
from
   sys.v_$sqlarea a,
   sys.all_users b
where
   a.parsing_user_id=b.user_id and
   b.username not in ('sys','system')
order by 3 desc)
where rownum < 21;

- Drop partition for
begin
for name in (
SELECT PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_OWNER = 'VCGW' 
AND TABLE_NAME = 'TRANSACTION_LOGS' AND PARTITION_NAME LIKE 'SYS_P%'
AND SUBSTR(PARTITION_NAME,6,10)<7309
ORDER BY PARTITION_NAME ASC
)
LOOP
    dbms_output.put_line(name.PARTITION_NAME);
    execute immediate 'alter table TRANSACTION_LOGS drop partition '||name.PARTITION_NAME;
    commit;
end loop;
end;

- Change passwd dbconsole Enterprise Manager:
1. Reset password and unlock SYSMAN
1. sqlplus /nolog
2. conn / as sysdba
3. alter user sysman identified by <pass>
4. alter user sysman account unlock
2. Make EM pick up the change
1. emctl stop dbconsole
2. emctl setpasswd dbconsole
3. emctl secure dbconsole
4. emctl start dbconsole


- Convert rows into columns using XMLAGG Function:
select msisdn,rtrim (xmlagg (xmlelement (e, service_package || ',')).extract ('//text()'), ',') service_package
from  SUB_VAS.SUB_VAS_1
group by msisdn

- Resize Tablespace:
alter database datafile /u01/oracle/oradata/booktst_users_02.dbf' resize 150M;
- ADD Datafile
alter tablespace APPS01 add datafile /u01/oracle/oradata/booktst_users_02.dbf' size 150M autoextend on;

- Format SQLPlus:
SET SPACE 0;
SET NEWPAGE NONE;
SET PAGESIZE 0;
SET LINESIZE 16000;
SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET HEADING OFF;
SET TERMOUT OFF;
SET TRIMOUT ON;
SET TRIMSPOOL ON;
Spool 1228user.txt;

- Oracle SQLLoader
sqlldr userid=NGP/viettelsiyw control=pre.ctl log=pre.log parallel=true direct=true
#control file:
LOAD DATA
INFILE '/home/oracle/POS_SUB_MB.txt'
BADFILE '/home/oracle/199.bad'
DISCARDFILE '/home/oracle/199.dsc'
APPEND INTO TABLE subscribers_postpaid_new
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(msisdn,sub_id,contract_id,sub_type,status,act_status,sta_datetime timestamp "yyyymmddhh24miss",end_datetime timestamp "yyyymmddhh24miss",reg_type,vip,imsi,serial,promotion_code,product_code)

- Split function:
create function split(input_list varchar2, ret_this_one number, delimiter varchar2)
return varchar2
is
    v_list varchar2(32767) := delimiter || input_list;
    start_position number;
    end_position number;
begin
    start_position := instr(v_list, delimiter, 1, ret_this_one);
    if start_position > 0 then
        end_position := instr( v_list, delimiter, 1, ret_this_one + 1);
        if end_position = 0 then
            end_position := length(v_list) + 1;
        end if;
        return(substr(v_list, start_position + 1, end_position - start_position - 1));
    else
        return NULL;
    end if;
end split;