Thứ Tư, 8 tháng 1, 2020

SQL*PLus - Một số câu lệnh hay sử dụng trong Oracle Database

  1.    Set môi trường cho SQL*Plus
col owner_name format a10
col table_name format a15
col cluster_name format a7
col partition_name format a9
col subpartition_name format a12
col head_rowid format a20
col <column_name> 999,999
col target format a25
Định dạng dữ liệu kiểu string, 10 ký t






Định dạng dữ liệu cột kiểu  number, VD: 123,456
set linesize 200 
Mỗi dòng 200 ký tự
SQL> set pagesize 9999
Định dạng cỡ của trang
Set timing on
Hiển thị thời gian chạy của câu lệnh


   2.    Các câu lệnh hay dùng
Kiểm tra version
$ sqlplus -v

SQL*Plus: Release 11.2.0.4.0 Production
Xem hướng dẫn sử dụng
sqlplus -h
Đăng nhập vào hệ thống
Chọn 1 trong các cách sau đây:
Chỉ gõ sqlplus, hệ thống sẽ yêu cầu nhập username và password:
$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 11:06:15 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Hoặc nhập trực tiếp username password trên dòng lệnh. Nếu chỉ nhập user thì hệ thống sẽ yêu cầu nhập password:
$ sqlplus sys/oracle_4U as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 11:09:03 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus hr

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 11:10:13 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Nếu dùng /nolog (chú ý không có khoảng trắng), phải tự connect vào database bằng lệnh conn hoặc connect:

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 11:21:20 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> conn hr/hr
Connected.

Khi đăng nhập, ta có thể thêm chuỗi kết nối để xác định chính xác database muốn đăng nhập vào. Chuỗi kết nối có thể là tnsname hoặc thông tin đầy đủ theo dạng easy connect.

C:\>sqlplus hr/hr@ testdb01

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 11:25:23 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

C:\>sqlplus hr/hr@192.168.88.10:1521/testdb01

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 13:53:03 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>



SQL*Plus có 1 số option, trong đó có option -silent, không hiển thị bất kỳ thông tin gì ngoài các câu lệnh sql gõ vào:
$ sqlplus -s scoot/oracle

select count(*) from user_tables;

  COUNT(*)
----------
             9

exit
$

Chạy lệnh OS trong SQL*Plus
SQL>!ls -la
SQL> host ls -la
Help SQL*Plus
SQL> help connect
Chạy lại câu lệnh SQL cuối cùng
SQL>/
Chạy script
SQL>@test_script.sql
Hoặc
SQL> start test_script.sql
Chạy 1 câu lênh hoặc 1 script bất kỳ
SQSSQL> alter system set sga_target=20G;

Check câu lệnh SQL chiếm nhiều thời gian

SELECT    parsing_schema_name username,
     sql_fulltext sql,
     optimizer_cost cost,executions,
     ROUND (elapsed_time / executions / 1000000,2) AS "avg_time/exc(s)"
FROM gv$sqlarea
WHERE     optimizer_cost > 100
     AND last_load_time >= TRUNC (SYSDATE)
     AND executions > 1
     AND elapsed_time / executions/1000000 > 1
     AND parsing_schema_name NOT LIKE '%SYS%'
     ORDER BY optimizer_cost DESC


Ghi ra file:
·     Ghi kết quả SQL ra file:
set wrap off
set linesize 200
set feedback off
set pagesize 0
set verify off
set trimspool on
set termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
 spool /tmp/data.txt
select cust||','||CHARGED||','||STA_DATETIME
from table1 partition(data20131108)
where call_type = 189
and CREDIT_CHARGED>0;
spool off
Exit

      Ghi ra file mới (chưa có file sẽ tự tạo ra)
pool /export/home/oracle/binhtv_scripts/move_datafiles.sh
select 'mv '||name||' '||replace(replace(replace(name,'/u02','/s04'),'/u03','/s05'),'/u04','/s05') from V$DATAFILE WHERE TS# in (select ts# from v$tablespace where name like '%&month') and name not like '/s0%' order by name desc;
spool off

     Ghi append:
spool move_datafiles.log append
alter system set dg_broker_start=false sid='*';
alter database recover managed standby database cancel;
shutdown immediate
startup mount;
!chmod +x move_datafiles.sh
!./move_datafiles.sh
@rename_datafiles.sql
alter system set dg_broker_start=true sid='*';
alter database recover managed standby database using current logfile disconnect;
spool off
Lấy plan của câu lệnh
explain plan for select /*+ PARALLEL*/ * from testp;

jaffar@PRIMEDB> select * from table(dbms_xplan.display) dual;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    10 |    20 |     2 |
|   1 |  TABLE ACCESS FULL   | TESTP       |    10 |    20 |     2 |
--------------------------------------------------------------------
Note: cpu costing is off
Mẫu script
SET termout OFF
prompt
prompt Using &gg_user as a GoldenGate schema name.
prompt

SELECT upper('&ddl_hist_table') || '_ALT' AS ddl_hist_table_alt FROM dual;
-- set up the role
DROP ROLE &gg_role;
CREATE ROLE &gg_role;
GRANT SELECT, DELETE ON "&gg_user"."&marker_table_name" TO &gg_role;
GRANT SELECT, DELETE ON "&gg_user"."&ddl_hist_table" TO &gg_role;
GRANT SELECT, DELETE ON "&gg_user"."&ddl_hist_table_alt" TO &gg_role;

-- names of objects used for SHOW in GGSCI
define ddl_dump_tables = 'GGS_DDL_OBJECTS' -- name of DDL dump objects tables
define ddl_dump_columns = 'GGS_DDL_COLUMNS' -- name of DDL dump objects columns
define ddl_dump_log_groups = 'GGS_DDL_LOG_GROUPS' -- name of DDL dump log groups
define ddl_dump_partitions = 'GGS_DDL_PARTITIONS' -- name of DDL dump partitions
define ddl_dump_primary_keys = 'GGS_DDL_PRIMARY_KEYS' -- name of DDL dump primary keys

-- setup up security for above tables
GRANT SELECT, INSERT, DELETE ON "&gg_user"."&ddl_dump_tables" TO &gg_role;
GRANT SELECT, INSERT, DELETE ON "&gg_user"."&ddl_dump_columns" TO &gg_role;
GRANT SELECT, INSERT, DELETE ON "&gg_user"."&ddl_dump_log_groups" TO &gg_role;
GRANT SELECT, INSERT, DELETE ON "&gg_user"."&ddl_dump_partitions" TO &gg_role;
GRANT SELECT, INSERT, DELETE ON "&gg_user"."&ddl_dump_primary_keys" TO &gg_role;
-- the following will be ok only if sequences installed, but no matter if they are not
GRANT EXECUTE ON "&gg_user".replicateSequence TO &gg_role;
GRANT EXECUTE ON "&gg_user".updateSequence TO &gg_role;

spool OFF
SET verify ON
SET termout ON

prompt
prompt Role setup script complete

prompt
prompt Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
prompt
prompt     GRANT &gg_role TO <loggedUser>
prompt
prompt where <loggedUser> is the user assigned to the GoldenGate processes.

@ Trần Văn Bình - Founder of Oracle DBA AZ #BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master