TRẦN VĂN BÌNH MASTER: SQL*PLus - Một số câu lệnh hay sử dụng trong Oracle Database

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* SQLPlus: 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* SQLPlus: 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* SQLPlus: 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 --Service Name* C:\>*sqlplus hr/hr@192.168.88.10:1521:testdb01 --SID* 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
	SQLPlus 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 --Nếu Windows: `spool "D:\test\test.txt" select a.ename from employee a inner join department b on ( a.dept_id = b.dept_id ) ; 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

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