URL = http://hylinux.3322.org/showArticle.php?ction=showArticle&id=3240
ORACLE commonly FAQ V1.0 author of the itpub: piner Transfer: itpub This article has been read: 1323 times In order to facilitate everyone to read. This post is top. Please don't keep up with some "good", "top", if you really want to thank the Piner, please submit a lot of FAQ. Thank you for your cooperation --by fenng ------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- -------------------
The first part, SQL & PL / SQL [Q] how to query special characters, such as wildcard% and _ [a] select * from table where name like 'A / _%' escape '/'
[Q] How to insert single quotes to the database table [A] can be processed with ASCII code, other special characters such as & also, such as Insert Into T Values ('I' || CHR (39) || 'M'); - chr (39) represents characters' or use two single quotes to represent an or insert Into T Values ('I''m '); - two' 'can represent one'
[Q] How to set a transaction consistency [a] set transaction [isolation level] read committed; default sentence level consistency set transaction [isolation level] serializable; read only; transaction-level consistency
[Q] How to use the cursor update data [a] Cursor c1 isselect * from tablenamewhere name is null for update [of column] ... Update Tablename set column = ... WHERE CURRENT OF C1;
[Q] How do custom exception [A] pragma_exception_init (exception_name, error_number); if immediately thrown raise_application_error (error_number, error_msg, true | false); exception variable SQLCODE error which number from -20,000 to -20,999, an error message largest 2048B Code SQlerRM error message
[Q] Transformation of decimal and hexadecimal [A] 8i or above: TO_CHAR (100, 'xx') TO_NUMBER ('4D', 'XX') 8i The transformation of the following to the following script Create OR replace function to_base (p_dec in number, p_base in number) return varchar2isl_str varchar2 (255) default nULL; l_num number default p_dec; l_hex varchar2 (16) default '0123456789ABCDEF'; beginif (p_dec is null or p_base is null) thenreturn null; end IF; if (trunc (p_dec) <> p_dec or p_dec <0) THENRAISE Program_ERROR; END IF; LOOPL_STR: = SUBSTR (L_HEX, MOD (L_NUM, P_BASE) 1, 1) || L_Str; L_Num: = Trunc (l_num / p_base); exit when (l_num = 0); end loop; return l_str; end to_base; / create or replace function to_dec (p_str in varchar2, p_from_base in number default 16) return numberisl_num number default 0; l_hex varchar2 (16) default '0123456789ABCDEF'; beginif (p_str is null or p_from_base is null) thenreturn null; end if; for i in 1 .. length (p_str) loopl_num: = l_num * p_from_base instr (l_hex, upper (substr (p_str, i, 1 ))) - 1; end loop; return l_num; end to_DEC; /
[Q] Cannot introduce the detailed usage of sys_context [a] You will understand SelectSys_Context ('useerenv', 'Terminal') Terminal, Sys_Context ('Userenv', 'Language') Language, Sys_Context ('Userenv ',' SESSIONID ') sessionid, SYS_CONTEXT (' USERENV ',' iNSTANCE ') instance, SYS_CONTEXT (' USERENV ',' ENTRYID ') entryid, SYS_CONTEXT (' USERENV ',' ISDBA ') isdba, SYS_CONTEXT (' USERENV ', 'NLS_TERRITORY') nls_territory, SYS_CONTEXT ( 'USERENV', 'NLS_CURRENCY') nls_currency, SYS_CONTEXT ( 'USERENV', 'NLS_CALENDAR') nls_calendar, SYS_CONTEXT ( 'USERENV', 'NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT ( 'USERENV', 'NLS_DATE_LANGUAGE ') nls_date_language, SYS_CONTEXT (' USERENV ',' NLS_SORT ') nls_sort, SYS_CONTEXT (' USERENV ',' CURRENT_USER ') current_user, SYS_CONTEXT (' USERENV ',' cURRENT_USERID ') current_userid, SYS_CONTEXT (' USERENV ',' SESSION_USER ') session_user, SYS_CONTEXT ( 'USERENV', 'sESSION_USERID') session_userid, SYS_CONTEXT ( 'USERENV', 'pROXY_USER') proxy_user, SYS_CONTEXT ( 'USERENV', 'PROXY_USERID') proxy_userid, SYS_CONTEXT ( 'USERENV', 'dB_DOMAIN') db_domain, SYS_CONText ('useerenv', 'db_name " ) Db_name, SYS_CONTEXT ( 'USERENV', 'HOST') host, SYS_CONTEXT ( 'USERENV', 'OS_USER') os_user, SYS_CONTEXT ( 'USERENV', 'EXTERNAL_NAME') external_name, SYS_CONTEXT ( 'USERENV', 'IP_ADDRESS') ip_address , Sys_context ('useerenv', 'network_protocol') network_protocol, sys_context ('useerenv', 'bg_job_id') bg_job_id, sys_context ('useerenv', '
FG_JOB_ID ') fg_job_id, SYS_CONTEXT (' USERENV ',' AUTHENTICATION_TYPE ') authentication_type, SYS_CONTEXT (' USERENV ',' AUTHENTICATION_DATA ') authentication_datafrom dual [Q] how to get day of the week, but also on the other date functions use [A] can be used To_CHAR to solve, such as select to_char (to_date ('2002-08-26', 'YYYY-mm-DD'), 'day') from Dual; set the date language before getting, such as Alter Session Set NLS_DATE_LANGUAGE = 'American '; You can also specify select to_char in the function (to_date (' 2002-08-26 ',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGE = American ') from dual; other more usage, you can refer to To_char and to_date functions such as getting a complete time format Select to_char (sysdate, 'yyyy-mm-mm-dd hh24: mi: ss') from DUAL; ), 'dd') Days from dual This year SELECT Add_Months (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from Dual Next Monday SELECT NEXT_DAY (Sysdate, 'Monday ') From Dual
[Q] Randomly draws the previous N record problem [A] 8i or above version Select * from (Select * from tablename ORDER BY SYS_GUID ()) Where rownum [Q] Patted records from N rows to M row, such as record from 20 lines to 30 lines [A] Select * from (SELECT ROWNUM ID, T. * From table where ... and rownum <= 30) Where id> 20; [Q] How to extract repeated records [A] Select * from table t1 where where t1.rowed! = (SELECT MAX (Rowed) from table t2where t1.id = t2.id and t1.name = t2.name) or SELECT Count (*), t.col_a, t.col_b from table tgroup by col_a, col_bhaving count (*)> 1 If you want to delete your duplicate record, you can replace the SELECT of the first statement to Delete [Q] How to set up autonomous affairs [A] 8i or later, does not affect the main transaction Pragma Autonomous_Transaction; ... Commit | rollback; [Q] How to suspend the specified time during the process [a] DBMS_LOCK package Sleep process such as: dbms_lock.sleep (5); indicates that the pause is 5 seconds. [Q] and log time how to quickly calculate the amount of the transaction [A] may be used such as the following script DECLAREstart_time NUMBER; end_time NUMBER; start_redo_size NUMBER; end_redo_size NUMBER; BEGINstart_time: = dbms_utility.get_time; SELECT VALUE INTO start_redo_size FROM v $ mystat m , v $ statname sWHERE m.STATISTIC # = s.STATISTIC # AND s.NAME = 'redo size'; - transaction startINSERT INTO t1SELECT * FROM all_Objects; - other dml statementCOMMIT; end_time: = dbms_utility.get_time; SELECT VALUE INTO end_redo_size FROM v $ mystat m, v $ statname sWHERE m.STATISTIC # = s.STATISTIC # AND s.NAME = 'redo size'; dbms_output.put_line ( 'Escape Time:' || to_char (end_time-start_time) || ' CENTISECONDS '); DBMS_OUTPUT.PUT_LINE (' redo size: '|| to_char (end_redo_size-start_redo_size) ||' BYTES '); END; [Q] How to create a temporary table [A] 8i or more version of Create Global Temporary Tablename (Column List) on Commit Preserve Rows; - Submit the Reserved Data Session Tempory Timetime On Commit Delete Rows; - Submit Delete Data Transaction Temporary Temporary Table For sessions, other sessions do not see the data of the session. [Q] How to perform DDL statements in PL / SQL [A] 1,8i The following version of the DBMS_SQL package 2,8i or more version can also use Execute Immediate SQL; DBMS_UTILITY.EXEC_DDL_STATEMENT ('SQL'); [Q] How to get an IP address [A] server (817 or more): UTL_INADDR.GET_HOST_ADDRESS client: sys_context ('useerenv', 'ip_address') [Q] How to encrypt the stored procedure [A] Use the wrap command, such as (assuming your stored procedure saved as a.sql) Wrap iname = a.sqlpl / sql wrapper: release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001copyright (c) Oracle Corporation 1993, 2000. All rights reserved.processing a.sql to a.plb prompt A.sql converted to A.PLB, this is the encrypted script, execute A.PLB Generate encrypted stored procedures [Q] How to run the stored procedure in Oracle [A] can use the dbms_job package to run the job, such as performing a stored procedure, a simple example, submit a job: variable jobno number; begindbms_job.submit (: Jobno, ' UR_Procedure; ', sysdate,' sysdate 1 '); commit; end; after you can use the following statement select * from user_jobs; [q] How to get milliseconds from the database [A] 9i or above There is a TIMESTAMP type to get milliseconds, such as SQL> Select TO_CHAR (SYSTIMESTAMP, 'YYYY-MM-DD HH24: MI: SSXFF') TIME1, TO_CHAR (CURRENT_TIMESTAMP) Time2 from Dual; Time1 Time2 ------------------------------------------------- --------------------------------------------- 2003-10-24 10: 48: 45.656000 24-OCT-03 10.48.45.656000 AM 08: 00 can be seen that in milliseconds correspond to FF. 8i or more version can create a Java function SQL> Create or replace and compilejava source "MyTimeStamp" asimport java.lang.string; import java.sql.timestamp; Public class myTimeStamp {public static string getTimeStamp () {return (new timestamp (system.currenttimemillis ())))))). Tostring ();}}; sql> java created. Note: Note Java's grammar, pay attention to case SQL> Create or replace function my_timestamp return varchar2as language javaname 'MyTimestamp.getTimestamp () return java.lang.String'; / SQL> function created.SQL> select my_timestamp, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') ORACLE_TIME From dual; my_timestamp oracle_time --------------------------------------------- 2003- 03-17 19: 15: 59.688 2003-03-17 19:15:59 If you just want to get 1/100 seconds (HSECs), you can also use dbms_utility.get_time [Q] If there is an update, you don't exist if you can use a statement to implement it. [A] 9i has been supported, it is MERGE, but only support SELECT sub-query, if it is a single data record, you can write SELECT ... from DUAL Subproof. The syntax is: MERGE INTO tableUSING data_sourceON (condition) WHEN MATCHED THEN update_clauseWHEN NOT MATCHED THEN insert_clause; The MERGE INTO course cUSING (SELECT course_name, period, course_hoursFROM course_updates) cuON (c.course_name = cu.course_nameAND c.period = cu.period) WHEN MATCHED THENUPDATESET c.course_hours = cu.course_hoursWHEN NOT MATCHED THENINSERT (c.course_name, c.period, c.course_hours) VALUES (cu.course_name, cu.period, cu.course_hours); [Q] ZuoLian how to achieve right Union with outslocadies [A] Before 9i, you can write this: Zuo: SELECT A.ID, A.Name, B.Address from A, Bwhere A.Id = B.ID ( ) right: SELECT A.ID , A.Name, B.Address from A, Bwhere A.Id ( ) = B.ID Outline Select A.ID, A.Name, B.AddressFrom A, Bwhere A.Id = B.ID ( ) UnionSelect B.ID, '' Name, B.AddressFrom Bwhere Not Exists (Select * from awhered); above 9i, SQL99 standard has begun, so the above statement can be written: Default internal connection: SELECT A.ID, A.Name, B.Address, C. SubjectFrom (a inner join b on a.id = B.ID) Inner Join C on B.Name = C.Namewhere Other_clause Zuo SELECT A.ID, A. Name, B.AddressFrom A Left Outer Join B on A.Id = B.Idwhere Other_clause Right SELECT A.ID, A. Name, B.AddressFrom A Right Outer Join B on A.Id = B.Idwhere Other_clause Forefolio Select A.ID, A.Name, B.AddressFrom A Full Outer Join B on A.Id = B.Idwhere Other_ClauseorslectElect A.ID , A.Name, B.AddressFrom A Full Outer Join B Using (ID) Where other_clause [Q] How to implement a record inserted according to the conditions [A] 9i, can be done by the Insert all statement, just a statement, such as: INSERT Allwhen (ID = 1) Teninto Table_1 (ID, Name) VALUES (ID, Name) WhenInto Table_2 (ID, Name) VALUES (ID, NAME) Elseinto Table_other (ID, Name) VALUES (ID, NAME) SELECT ID, NAMEFROM A; if there is no condition, complete each table Insert, such as Insert Allinto Table_1 (ID, Name) VALUES (ID, NAME) INTO TABLE_2 (ID, NAME) VALUES (ID, NAME) INTO TABLE_OTHER (ID, NAME) VALUES (ID, NAME) SELECT ID, NAMEFROM A; Q] How to implement ranked conversion [A] 1, the ranks of fixed columns, such as Student Subject grade ----------------------------------------------------------------- Student1 language 80student1 mathematics 70student1 English 60student2 English language 90student2 mathematics 80student2 100 ...... converted into mathematical language English student1 80 70 60student2 90 80 100 ...... statement is as follows: select student, sum (decode (subject, 'language', grade, null)) "language ", SUM (Decode (Subject, 'Mathematics', Grade, NULL)" Mathematics ", SUM (Decode (Subject, 'English', Grade, Null)" English "from TableGroup by Student 2, unprofite race transformation, such as C1 C2 -------------- 1 I 1 is 1 who 2 know 2 3 not ... Convert to 1, who 2 know 3 not this type The conversion must be done by means of PL / SQL, here is an example crete or replace function get_c2 (TMP_C1 Number) Return VARCHAR2ISCOL_C2 VARCHAR2 (4000); Beginfor Cur in (SELECT C2 from T where C1 = TMP_C1) LoopCol_C2: = COL_C2 || Cur.c2; end loop; col_c2: = RTRIM (col_c2, 1); return col_c2; end; / sql> SELECT DISTINCT C1, GET_C2 (C1) CC2 from table; [Q] How to achieve the first N records of the packet [A] 8i or more, using analytical functions, such as the three employees or each of the three employees or each class of students. Select * from (Select Depno, ENAME, SAL, ROW_NUMBER () over Sal Desc) RnFrom EMP) Where rn <= 3 [Q] How to combine adjacent records into a record [A] 8i or later, the analysis function LAG and the LEAD can be extracted or recorded to this record before one or the previous day. Select Deptno, Ename, Hiredate, Lag (Hiredate, 1, Null) over (partition by deptno order by hiredate, ename) Last_hirefrom Emporder by DepNo, HiRedate [Q] How to get the value of the Northern Number N? [A] SELECT *, DENSE_RANK () over (Order By T2 DESC) Rank From T) Where rank = & n; [q] how to output query content to text [a] Spool such as SQLPLUS -S "/ as sysdba" << EOFSET Heading Offset Feedback Offspool Temp.txt Select * from Tab; DBMS_OUTPUT.PUT_LINE ('Test'); Spool OffexiteOf [q] How to execute an OS command in a SQL * Plus environment? [A] For example, I entered SQLUS, launched the database, and suddenly remembered that the listening has not started, there is no exiting SQLPLUS, nor does it need to have another command line window, enter directly: SQL> Host Lsntctl Start or UNIX / Linux platform SQL> ! [Q] How to set the calling process called the memory process [A] The normal stored procedure is owner privilege, if you want to set the caller permissions, please refer to the following statement crete or replaceProcedure ... () AuthiD current_userasbegin ... End; [Q] How to quickly get the number of records of each table or the table partition [A] can analyze the user, then query the USER_TABLES dictionary, or use the following script to set serveroutput on size 20000Declaremicount INTEGER; beginfor c_tab in (SELECT TABLE_NAME FROM User_tables) Loopexecute Immediate 'Select Count (*) from "' || C_TAB.TABLE_NAME || '"' Into MiNT; DBMS_OUTPUT.PUT_LINE (RPAD (c_tab.table_name, 30, '.') || LPAD (MICOUNT, 10, )) '.'; - if it is partition tableSELECT COUNT (*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name; IF miCount> 0 THENFOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOPEXECUTE IMMEDIATE ' Select count (*) from '|| c_tab.table_name ||' Partition ('|| c_part.partition_name ||') ' INTO MICOUNT; DBMS_OUTPUT.PUT_LINE ('' || RPAD (c_part.partition_name, 30, '.') || LPAD (MICOUNT, 10, '.'); End loop; endiff; end; [A] How to send mail [Q] in Oracle [q] can use UTL_SMTP package, the following is an example program that sends simple mail / ********************* *********************************************************** ***** Parameter: RCPTER in varchameter: RCPTER IN VARCHAR2 Receiver mailbox mail_content in varcha2 mail content DESC: · Send mail to the specified mailbox · You can specify a mailbox, if you need to send multiple mailboxes, additional auxiliary procedures **** *********************************************************** ******************************* / CREATE OR REPLACEDURE SP_SEND_MAIL (RCPTER IN VARCHAR2, MAIL_CONTENT IN VARCHAR2) isconn UTL_SMTP.CONNECTION; - WRITE TITLEPROECEDURE Send_Header (Name In VARCHAR2, HEADER IN VARCHAR2) ASBEGINutl_smtp.write_data (conn, NAME || ':' || HEADER || utl_tcp.CRLF); END; BEGIN - opne connectconn: = utl_smtp.open_connection ( 'smtp.com'); utl_smtp. Helo (CONN, 'ORACLE'); UTL_SMTP.MAIL (CONN, 'ORACLE INFO'); UTL_SMTP.RCPT (CONN, RCPTER); UTL_SMTP.OPEN_DATA (CONN); - Write Titlesend_Header ('from', 'Oracle Database " Send_Header ('to', '"Recipient" <' || rcpter || "); Send_Header ('Subject', 'DB INFO'); - WRITE MAIL Contentutl_smtp.write_data (conn, utl_tcp.crlf | | mail_content; - Close Connectutl_smtp.close_dat a (conn); utl_smtp.quit (conn); EXCEPTIONWHEN utl_smtp.transient_error OR utl_smtp.permanent_error THENBEGINutl_smtp.quit (conn); EXCEPTIONWHEN OTHERS THENNULL; END; WHEN OTHERS THENNULL; END sp_send_mail; [A] How to write an operating system file in Oracle, such as writing log [q] can take advantage of UTL_FILE package, however, before this, pay attention to setting UTL_FILE_DIR initialization parameters / *********** *********************************************************** ************ Parameter: TextContext in varcha2 log content Desc: · Write log, write content to the server specified in the directory · You must configure the UTL_FILE_DIR to initialize the parameters, and ensure that the log path is consistent with the UTL_FILE_DIR path or One of the************************************************ ************************************* / CREATE or Replace Procedure SP_WRITE_LOG (Text_Context VARCHAR2) ISFILE_HANDLE UTL_FILE.FILE_TYPE; WRITE_CONTENT VARCHAR2 (1024); Write_File_Name VARCHAR2 (50); begin - open filewrite_file_name: = 'db_alert.log'; file_handle: = UTL_FILE.FOPEN ('/ u01 / logs', write_file_name,' a '); Write_Content: = to_CHAR (Sysdate,' YYYY-mm -DD HH24: MI: SS ') ||' || '|| TEXT_CONTEXT; - WRITE FILEIF UTL_FILE.IS_Open (file_handle) Thenutl_File.Put_Line (file_handle, write_content); endiff; - close fileutl_file.fclose (file_handle) EXCEPTIONWHEN OTHERS THENBEGINIF UTL_FILE.IS_Open (file_handle) Thenutl_file.fclose (file_handle); endiff h; End; End; End SP_WRITE_LOG; The second part, Oracle architecture [Q] Oracle's data type [A] Common data type has a CHAR fixed length character field, the maximum length of up to 2000 bytes Nchar multibly character set fixed length character domain, Length with the character set, up to 2000 characters or 2000 bytes VARCHAR2 variable length character field, the maximum length of 4000 characters NVARCHAR2 multi-byte character set variable length character domain, length with the character set Up to 4000 characters or 4000 byte DATEs are used to store a fixed length (7 byte) character fields of all dates, and the time is stored as part of the date. Unless the date format is replaced by setting the NLS_DATE_FORMAT parameter of the init.ora file, the date is represented in DD-MON-YY format, such as 13-APR-99 represents 1999.4.13Number variable length value column, allowed value 0, Positive and negative numbers. Number values typically store with 4 bytes or less bytes, up to 21-byte long variable length character fields, maximum length to 2GBRAW represents variable length character fields of binary data, up to 2000 bytes Longraw Represents the variable length character field of binary data, up to 2Gbmlslabel only for TrustedoCle, this data type uses 2 to 5 byte blob binary large objects per line, the maximum length is 4Gbclob character large object, the maximum length is 4GBNCLOB multi-character The CLOB data type of the character set, the maximum length is 4Gbfile external binary file, the size is determined by the operating system to represent the binary data of the ROWID, the value of the Oracle8RowID is 10 bytes, and the defined RowID format used in Oracle7 is 6 bytes. UroWID is used for binary data for data addressing, which is 4,000 bytes [Q] Oracle's common keywords, can not be used for object name [A] Take 8i version, generally keep keywords cannot be used name ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO T Rigger Uid Unique Update User Validate VALUES VARCHAR VARCHAR2 View WHENE WITH DESCRIPTION OF V $ RESERVED_WORDS view [Q] How to view the database version [A] Select * from v $ version contains version information, core version information, bit information (32-bit or 64-bit), etc. View, such as File $ ORACLE_HOME / BIN / ORACLE [Q] How to view database parameters [a] show parameter parameter name, if you can see if you can use SPFILE file or select * from v $ parameter In addition to this part of the parameters, Oracle There is a lot of implicit parameters, you can view: select name, value, decode (isdefault, 'true', 'y', 'n') as "default", decode (ISEM, 'True', 'Y', 'N') As Sesmod, Decode (Isym, 'Immediate', 'I', 'Deferred', 'D', 'False', 'N') AS SYSMOD, DECODE (IMOD, 'Modified', 'U', 'Sys_modified', 's', 'n') as modified, decode (iadj, 'true', 'y', 'n') as adjusted, descriptionFrom (--gv $ system_parameterslectlect x.inst_id as instance, x.indx 1, KSPPINM AS Name, Ksppity, Ksppstvl As Value, Ksppstdf as ISDEFAULT, DECODE (Bitand (KSPPIFL / 256, 1), 1, 'True', 'False') AS ISEM, DECODE (Bitand (KSPPIFLG / 65536, 3 ), 1, 'immediate', 2, 'deferred', 'false') AS ISYM, DECODE (Bitand (Ksppstvf, 7), 1, 'Modified', 'false') AS IMOD, DECODE (Bitand (Ksppstvf, 2 ), 2, 'true', 'false') AS IADJ, KSPPDesc As DescriptionFrom X $ KSPPI X, X $ KSPPSV YWHERE X.INDX = Y.INDXAND SUBSTR KSPPINM, 1, 1) = '_'AND x.inst_id = Userenv (' instance ')) Order by name [Q] How to view the database character set [A] Database server character set Select * from NLS_DATABASE_PARAMETERS, which is from PROPS $, which is a character set that represents the database. Client character set environment Select * from nls_instance_parameters, comes from V $ parameter, indicating the settings of the client's character set, may be parameter files, environment variables, or registry session character set environment Select * from NLS_Session_Parameters, come from V $ NLS_Parameters, indicating that the session is your own setting, which may be the environment variable of the session or the ALTER session complete, if the session does not have a special setting, will be consistent with NLS_INSTANCE_PARAMETERS. The client's character set requires the same as the server to correctly display the non-ASCII characters of the database. If multiple settings exist, the Alter Session> Environment Variable> Registry> Parameter file character set is consistent, but the language settings can be different, and the language settings are recommended in English. If the character set is ZHS16GBK, the nls_lang can be American_america.zHS16GBK. [Q] How to modify the character set [A] 8i or more version can modify the character set through Alter Database, but it is only limited to subset to superchard, and it is not recommended to modify the PROPS $ table, which will cause serious errors. Startup Nomount; Alter System Enable Restricted Session; ALTER System Set Job_Queue_Process = 0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET ZHS16GBK; [Q] How to establish a function index [A] 8i or more, make sure query_rewrite_enabled = trueQuery_rewrite_integrity = trustedcompatible = 8.1.0 above Create Index IndexName on table (Fired); [Q] how to move a table or table partition [A] Syntax Alter table tablename move moving table [Tablespace new_nameStorage (initial 50M next 50Mpctincrease 0 pctfree 10 pctused 50 initrans 2) nologging] Syntax alter table tablename move the mobile partition (partition partname After [Update Global Indexes] must be rebuilt alter index indexname rebuild If the table has a LOB segment, then normal ALTER cannot move the LOB segment to other tablespaces, but only move the table segment, you can use the following method to move LOB Segment ALTER TABLENAME MOVELOB (LOBSEGNAME) Store As (TableSpace Newts); [Q] How to get the current SCN [A] 9i SELECT MAX (KTuxescnw * Power (2,32) KTuxescnb) from x $ ktuxe; if it is 9i or later, you can also get SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM Dual; [Q] RowID structure and composition [A] 8 or higher version of RowID composition oooooofffbbbbbrr8 or less RowID composition (also called restricted ROWID) bbbbbbbbbbbb.rrr.ffff, O is object ID, F is file ID, B is block ID, R is a line ID If we query a table's RowID, depending on the block information, you can know how many blocks do it exactly, and it knows how much data space occupies (this data space is not equal to the allocation space of the table) [Q] How to get the object's DDL statement [A] Third-party tools do not say mainly say that 9i or more version of DBMS_Metadata1, get a single object of DDL statement set heading offset echo offset feedback offset pages Offset long 90000SELECT DBMS_METADATA.GET_DDL ('Table' , 'Tablename', 'Scame') from Dual; If you get the script of the entire user, you can use the following statement Select DBMS_METADATA.GET_DDL ('Table', u.table_name) from user_tables u; Table to INDEX [Q] How to create a constrained index on another table space [A] 1, create an index first, create a constraint 2, create create table test (c1 number constraint pk_c1_id primary keyx, c2 varchaint pk_c1_id primary key ingusing index tablespace useridex, c2 varcha2 (10) )) TABLESPACE Userdate; [Q] How do you know that those tables do not build primary key [A], the primary key of the table is necessary, and there is no primary key to say that it does not meet the design specifications. SELECT table_nameFROM User_tables tWHERE NOT EXISTS (SELECT table_nameFROM User_constraints cWHERE constraint_type = 'P'AND t.table_name = c.table_name) other relevant data interpretation dictionary table column user_constraints user_tables user_tab_columns table constraints and column constraints user_cons_columns user_indexes index and column index user_ind_columns Relationship [Q] dbms_output prompts the buffer is not enough, how to add [A] dbms_output.enable (20000); [Q] How to modify the column name [A] 9i or more version You can use the RNAME command ALTER TABLE Username.tabnamerename Column SourceColumn To DestColumn9i The following versions can be used to use create table ... as select * from sourceTable. In addition, 8i or more can support deleting alter table usrname.tabnameset unused (columnname) Cascade constraintsalter table username.tabnamedrop (columnname) Cascade Constraints [Q] How to install SQLUS installation [A] SQLPLUS help must be manually installed, the shell script is $ oracle_home / bin / helpins must set the system_pass environment variable first, such as: $ setnv system_pass system / manager $ helpins if Without setting the environment variable, you will prompt the Environment variable when you run the script, in addition to the shell script, you can also use the SQL script installation, so you don't have to set the environment variable, however, we must log in with system. $ SQLPLUS System / ManagerSQL> @? / sqlplus / admin / help / helpbld.sql helpus.sql installation, you can use the following method to help SQL> Help Index [q] how to quickly download Oracle Patch [A] Let's get the download server address, there is ftp://updates.racle.com on the HTTP page, then log in with FTP, the username and password are Metalink username and password, as we know the patch number 3095277 (9204 patch set) , the ftp> cd 3095277250 Changed directory OK.ftp> ls200 PORT command OK.150 Opening data connection for file listing.p3095277_9204_AIX64-5L.zipp3095277_9204_AIX64.zip ...... p3095277_9204_WINNT.zip226 Listing complete Data connection has been closed.ftp:. 208 bytes Received in 0.02seconds 13.00kBytes / Sec.FTP> I know this information, we use flashget, the network ants can download it. Add the following connection ftp://updates.oracle.com/3095277/p3...04_aix64-5l.zip or replace the back part of the desired content, if it is flashget, network ant Please enter the authentication username and password, Is your Metalink username and password! [Q] How to move data file [A] 1, turn off the database, use the OS copy A.SHUTDOWN IMMEDITE Turn the database b. Copy data files under the OS C.Startup Mount Start to Mount D. RARTER DATABASE RENAME DATAFILE 'Old file' to 'new file'; E.ALTER DATABASE open; Open Database 2, using RMAN online operation RMAN> SQL "ALTABASE DATAFILE '' File Name '' 'Offline"; RMAN> Run {2> Copy DataFile' Old File Location'3> To 'New File Location'; 4> Switch DataFile 'Old File Location'5> To DataFileCopy' New File Location '; 6>} RMAN> SQL "ALTABASE DATAFILE' 'File Name' 'Online"; Description: Use OS copies can also be operated online, do not close the database, like the RMAN's steps, using the RMAN as the principle of using the OS copy, COPY is copy data file, equivalent to the OS, and Switch is equivalent to ALTER DATABASE RENAME, is used to update the control file. [Q] If you manage online log groups and members [A] is a common action, if you pay attention to the wire number under OPA / RAC Add a log file group ALTER DATABASE Add logfile [group n] 'file full name' size 10m; in this Add a member ALTER DATABASE ADD Logfile Member 'file full name' to group n; delete a log member ALTABASE DROP logfile men file in this group '; delete the entire log group ALTABASE DROP LOGFILE Group N; [Q] How to calculate the size of Redo Block [A] Calculation method is (Redo size redo Wastage) / Redo Blocks Written 16 See the following example SQL> SESSSTAT WHERE NAME LIKE '% Redo% '; Name Value -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------- Redo Synch Writes 2redo Synch Time 0redo Entries 76redo Size 19412redo Buffer Allocation Retries 0redo Waffer 0redo Writer Latching Time 0redo Writes 22redo blocks written 51redo write time 0redo log space requests 0redo log space wait time 0redo log switch interrupts 0redo ordering marks 0SQL> select (19412 5884) / 51 16 ' "Redo black (byte)" from dual; Redo black (byte) ------------------ 512 [Q] control file contains the basic elements [A] contains the following main control file entries, see DATABASE ENTRYCHECKPOINT PROGRESS RECORDSREDO THREAD RECORDSLOG FILE RECORDSDATA FILE RECORDSTEMP FILE RECORDSTABLESPACE RECORDSLOG FILE HISTORY RECORDSOFFLINE RANGE RECORDSARCHIVED LOG RECORDSBACKUP SET RECORDSBACKUP PIECE by dump the contents of the control file RECORDSBACKUP DATAFILE RECORDSBACKUP LOG RECORDSDATAFILE COPY RECORDSBACKUP DATAFILE CORRUPTION RECORDSDATAFILE COPY CORRUPTION RECORDSDELETION RECORDSPROXY COPY RECORDSINCARNATION RECORDS [Q] If it is found in the table bad blocks, how to retrieve the other is not bad data [a] first find the ID of bad blocks (run dbverify implemented ), Suppose to be [Q] I created all users of the database, can I delete these users? [A] When the Oracle database is created, create a series of default users and tablespaces, the following is their list · sys / change_on_install or internal system user , Data Dictionary Owner, Super Permissions Owner (Sysdba) Create a script: • / rdbms / admin / sql.bsq and various cat * .sql recommended to create a password immediately After this user cannot be deleted • System / Manager database default management user Have DBA Role Permissions Create Scripts: • / Rdbms / Admin / SQL.BSQ Recommended Change After Creating Password This user cannot be deleted • Outln / Outln Optimization Plan Storage Outline User Create Script:? / Rdbms / admin / SQL.BSQ It is recommended to change the password immediately after the creation This user cannot be deleted --------------------------------------- ------------ · Scott / Tiger, Adams / Wood, Jones / Steel, Clark / Cloth and Blake / Paper. Experiment, Test User, An Example EMP and DEPT Creation Script:? / Rdbms /admin/utlsampl.sql can modify the password user can be deleted, delete or lock the product environment, HR / HR (Human Resources), OE / OE (ORDER Entry), Sh / SH (Sales History). experiment, test user Include Script Employees with DEPARTMENTS: • / demo / schema / mksample.sql You can modify the password user can be deleted, the product environment recommends delete or lock · DBSNMP / DBSNMPORACLE Intelligent Agent creates a script:? / Rdbms / admin / catsnmp . SQL, Called from catalog.sql can change your password - you need to place a new password to snmp_rw.r file If you don't need Intelligent Agents, you can delete ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------- The following users are optional to install the user, if not, you don't need to install · CTXSYS / Ctxsysoracle interface management user creates a script:? / Ctx / admin / dr0cs Ys.sql · TracesVR / TraceOracle TRACE Server creates scripts: • / rdbms / admin / otrcr.sql · Ordplugins / OrdpluginsObject Relational Data (ORD) User Used by Time Series, etc. Created script:? / ord / admin / orderinst.sql · ORDSYS / ORDSYSOBJECT RELATIONAL DATA (ORD) User Used by Time Series, etc creation script:? / Ord / admin / ordinst.sql · dssys / dssysoracle Dynamic Services and Syndication Server creates scripts:? / Ds / sql / dssys_init.sql · MDSys / MDSysoracle Spatial Administrator User creates scripts:? / Ord / admin / order.sql · Aurora $ Orb $ Unauthenticated / Invalidused for Users Who Do Not Authenticate In Aurora / ORB creation script: ? /Javavm/install/init_orb.sql called from /javavm/install/initjvm.sql·PERFSTAT/PERFSTATOracle Statistics Package (STATSPACK) that supersedes UTLBSTAT / UTLESTAT create a script:?? / Rdbms / admin / Part III statscre.sql, Backup and recovery [Q] How to turn on / off Archive [A] If the archive is turned on, ensure that log_archive_start = true enables automatic archiving, otherwise it can only manually archive, if it is closed, set this parameter to pay attention: If it is an OPS / RAC environment Need to comment out first, then execute the following steps, finally restart 1, turn on the archive a. Close the database shutdown immediateb. Startup Mountc. Alter Database ArchiveLogd. Alter Database Opne2, disable archive a. Turn off Database Shutdown ... immediateb startup mountc alter database noarchivelogd alter database open archived information can view SQL> archive log listDatabase log mode archive ModeAutomatic archival EnabledArchive destination E by the following words: / oracle / ora92 / database / archiveOldest online log sequence 131Next log sequence to archive 133Current Log sequence 133 [Q] How to set up timing archiving [a] 9i or more, to ensure that the unit of archive does not exceed n seconds set Archive_lag_target = n unit: second range: 0 ~ 7200 [Q] How to export / import in different versions [A] Export use low version, imported with the current version If the version is too large, you need to use the intermediate version of the transition [Q] How to pass the different character sets [a] a. Precate condition is to ensure that the export / import meets other character set criteria, such as the customer environment consistent with the database character set. b. Modify the 2,3 bytes of the DMP file to the character set of the target database, pay attention to replace it with hexadecimal. Reference function (ID in the following functions): NLS_CHARSET_NAME Name NLS_CHARSET_ID according to the character set ID Name Name Name [Q] How to back up control file [A] Back up backup to a binary file ALTER DATABASE BACKUP ControlFile to '$ backup_dept / controlfile.000' [Reuse]; Backup to text file method Alter Database Backup ControlFile to TRACE [RESETLOGS | NoresetLogs]; [Q] Control file damage How to restore [A] 1. If you are damaged a single control file, you only need to close the database, copy a good data file to overwrite the extracted data file or modify the related part of the init.ora file, If you lose all control files, you need to create a control file or a script that creates a control file from the backup recovery can be obtained via the ALTER DATABASE Backup ControlFile to TRACE. [Q] How to back up a table space [A] ALTER TABLESPACE Name Begin Backup; Host CP This tablespace Data file destination; ALTER TABLESPACE Name End Backup; if you are backup multiple tablespace or the entire database, you only need one The operation of a table space is OK. [Q] How to quickly get hot standby scripts for the entire database [A] can write a similar script SQL> SET ServerOutput OnBegindBMS_output.enable (10000); for BK_TS in (Select Distinct T.TS #, T.Name from V $ TABLESPACE T, V $ datafile d where t.ts # = d.ts #) loopdbms_output.put_line ('-' || bk_ts.name); dbms_output.put_line ('alter tablespace' || bk_ts.name || 'Begin Backup ; '); for bk_file in (SELECT FILE #, Name from V $ DataFile WHERE TS # = BK_TS.TS #) loopdbms_output.put_line (' Host CP '|| BK_FILE.NAME ||' $ BACKUP_DEPT / '); End Loop ; dbms_output.put_line ('alter tablespace' || bk_ts.name || 'end backup;'); end loop; end; / [Q] Lost a data file, but there is no backup, how to open the database [A] If there is no backup, it can only be deleted this data file, which will cause the corresponding data loss. SQL> startup mount - ARCHIVELOG mode command SQL> Alter database datafile 'file name' offline; - NOARCHIVELOG mode command SQL> Alter database datafile 'file name' offline drop; SQLl> Alter database open; Note: This data file can not be System data file [Q] Lost a data file, no backup but how to recover the archive since the data file [A] guarantees the following conditions a. Can't be a system data file B. You cannot lose control files If you meet the above conditions, then SQL> Startup Mountsql > ALTER DATABASE CREATE DATAFILE 'SIZE' REUSE; SQL> Recover DataFile N; - File Number or SQL> Recover DataFile 'File Name'; or SQL> Recover Database; SQL> ALTER DATABASE OPEN ; [Q] How to restore [A] 1 online log damage [A] 1, if it is a non-current log and archive, you can use the ALTER DATABASE CLOGFILE GROUP N to create a new log file. If the log is not archive, you need to use ALTER DATABASE CLEAR UNATIVED LOGFILE Group N2, if it is the current log damage, it is generally not CLEAR, it may mean that if there is a backup, you can use a backup to perform incomplete recovery If there is no backup, you can only use _allow_resetlogs_corruption = true to force to recover, but, Such a method is not recommended, preferably under the guidance of Oracle Support. [Q] how to create RMAN recovery catalog [A] First, create a database user, usually RMAN, and give recovery_catalog_owner role permissions sqlplus sysSQL> create user rman identified by rman; SQL> alter user rman default tablespace tools temporary tablespace temp ; SQL> alter user rman quota unlimited on tools; SQL> grant connect, resource, recovery_catalog_owner to rman; SQL> exit; then, with the user login, create a recovery catalog rman catalog rman / rmanRMAN> create catalog tablespace tools; RMAN> exit Finally, you can register the target database in the recovery directory, RMAN Catalog RMAN / RMAN Target Backdba / Backdbarman> Register Database; [Q] How to move data file when recovering, restore to other locations [A] For an example of Run {set untric Time 'JUL 01 1999 00: 05: 00'; Allocate Channel D1 Type Disk; set newname For DataFile '/u04/oracle/prod/sys1prod.dbf'to' /u02/oracle/prod/sys1prod.dbf';set Newname for DataFile '/u04/oracle/prod/usr1Prod.dbf'to' / U02 / Oracle /prod/usr1prod.dbf';set newname for datafile '/u04/oracle/prod/tmp1prod.dbf'to' /u02/oracle/prod/tmp1prod.dbf';Restore ControlFile to '/ u02 / oracle / prod / ctl1prod .ora '; replicate controlfile from' /u02/oracle/prod/ctl1prod.ora';restore database; sql "alter database mount"; switch datafile all; recover database; sql "alter database open resetlogs"; release channel d1;} [Q] How to recover (Restore) control files from backup card (restore) Control file and data file [A] You can use the following method to restore the backup of the backup film RESTORE controlfile from backuppiecefile; if it is 9i automatic backup, you can Use the following method restore controlfile from autobackup; however, if the control file is all lost, you need to specify DBID, such as SET DBID =? The default format of the automatic backup control file is% f, this format is C-IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIMMMDD-QQ, The iiiiiiiiii is DBID to recover data files, and Oracle 816 starts with a package DBMS_BACKUP_RESTORE to execute in the Nomount state, you can read 815 or even previous backups, read files for recovery can run in SQLPLUS, as follows SQL> startup nomountSQL> DECLARE2 devtype varchar2 (256); 3 done boolean; 4 BEGIN5 devtype: = dbms_backup_restore.deviceallocate ( '', params => ''); 6 dbms_backup_restore.restoresetdatafile; 7 dbms_backup_restore.restorecontrolfileto ( 'E: / Oracle /oradata/penny/control01.ctl');8 dbms_backup_restore.restoreDataFileto (1, 'E: /Oracle/oradata/penny/system01.dbf'); 9 dbms_backup_restore.restoreDataFileto (2, 'E: / Oracle / oradata / penny /UNDOTBS01.DBF');10 dbms_backup_restore.restoreDataFileto (3, 'E: /ORACLE/ORADATA/PENNY/USERS01.DBF'); 11 dbms_backup_restore.restorebackuppiece ( 'D: /orabak/BACKUP_1_4_04F4IAJT.PENNY',done=> done 12 End; 13 / P The L / SQL process has been successfully completed. SQL> ALTER DATABASE MOUNT; [Q] What is the meaning of% s in the Format format in RMAN, what is the meaning of what is mean [A] can refer to the following% C backup card copy number% D database name% D at the first few days in the month (DD)% M is located in the month (mm)% F a DBID-based name, this format is C-IIIIIIIII-YYYYMMDDD-QQ, where IIIIIIII is the DBID, YYYMMDD of the database, QQ is a 1-256 sequence% n database name that fills to the right to the maximum of eight characters% U A number of eight characters represents the backup set and the creation time% p. The backup slice in the backup set, from 1st to the creation File% U A unique file name, representing% U_% p_% C% S backup set number% T backup set timestamp% T-year monthly day format (YYYYMMDD) [Q] Execute EXEC DBMS_LOGMNR_D.BUILD ('logminer.ora'), prompt to subscript superior, what to do [A] Complete error message is as follows, SQL> EXEC DBMS_LOGMNR_D.BUILD ('logminer.ora', 'file directory') Begin dbms_logmnr_d.build ('logminer.ora'); end Directory '); end; * Error is located on the 1st line: ORA-06532: The subscript beyond the limit ORA-06512: "sys.dbms_logmnr_d", Line 793RA-06512: In line 1 solution is: 1. Editing file "dbmslmd.sql" in the "$ ORACLE_HOME / RDBMS / Admin" directory Change line: Type Col_Desc_Array Is Varray (513) of col_description; Type Col_Desc_Array IS VARRAY (700) oF col_description; 2. save the file and run the script changed after SQLPLUS> Connect internalSQLPLUS> @ $ ORACLE_HOME / rdbms / admin / dbmslmd.sql3 recompile the package SQLPLUS> alter package DBMS_LOGMNR_D compile body;. [Q] execution execute dbms_logmnr.start_logmnr (DictFileName => 'DictFileName') prompted ORA-01843: invalid month, this is what causes [a] we analyzed start_logmnr package PROCEDURE start_logmnr (startScn iN NUMBER default 0, endScn iN NUMBER default 0, startTime iN DATE DEFAULT TO_DATE ('01-Mon-1988 ',' DD-MON-YYYY '), EndTime In Date Default To_Date ('01-Jan-2988', 'DD-MON-YYY'), DictFileName In Varchar2 Default ', Options in binary_integer defau LT 0); You can know if to_date ('01-marN-1988 ',' DD-MON-YYYY ') failed, will result in the above error, so the solution can be 1, alter session set nls_language = American2, with the following Method Execution Execute DBMS_Logmnr.Start_logmnr (DictFileName => F: /TEMP2/testDict.ora ', starttime => to_date ('01 -01-1988', 'DD-MM-YYYY'), endtime => to_date ('01 -01-2988 ',' DD-MM-YYYY '); Section IV, Performance Adjustment [Q] If you set automatic tracking [A] Log in to do $ oracle_home / rdbms / admin / uTLPLAN.SQL Create a schedule to create a Plustrace Role if you want to plan The table allows each user to use, then SQL> CREATE PUBLIC SYNONYM Plan_Table for Plan_Table; SQL> GRANT All on Plan_Table to public; if you want to let the role of automatic tracking allow each user to use, SQL> Grant Plustrace to public PUBLIC The following statement open / stop tracing set autotrace on | OFF | ON EXPLAIN | ON statistics | TRACEONLY | TRACEONLY EXPLAIN [q] If you track your own session or someone else's session [a] Track your own session very simple ALTER session set SQL_TRACE True | FalseOREXEC DBMS_SESSION.SET_SQL_TRACE (TRUE); if you track the session of others, you need to call a package of EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, Serial #, TRUE | FALSE) Tracking information can be found in the user_dump_dest directory or through the following script Name (Suitable for WIN environments, if you are unix needs to do a certain modification) SELECT P1.VALUE || '/' || p2.value || '_ora_' || p.spid || '.ora' FileNameFromv $ Process P, V $ Session S, V $ Parameter P1, V $ Parameter P2where P1.Name = 'user_dump_dest'and p2.name =' db_name'and p.addr = s.paddrand S.Audsid = Userenv ('sessionID') Finally, TkProf to parse the trace file, such as TKPROF original file target file sys = n [Q] How to set up the entire database system tracking [A] In fact, the ALTER SYSTEM SET SQL_TRACE = TRUE on the document is unsuccessful but can complete this work by setting events, and the active alter system set events'10046 Trace Name Context Forever, Level 1 '; If the trace is closed, you can use the following statement alter system set off'; where Level 1 and the above 8 are trace level Level 1: Track SQL statements, equal to SQL_TRACE = TrueElevel 4: including variables Details Level 8: Includes Waiting Level 12: Includes Binding Variables and Waiting Events [Q] How to quickly get DB process information and the statement in the OS process [A] Some time, we can operate on the OS, I get the OS process we get after Top, how quickly I get DB information according to the OS information? We can write the following script: $ more whoit.sh # / bin / shsqlplus / nolog << EOFconnect / as sysdbacol machine format a30col program format a40set line 200select sid, serial #, username, osuser, machine, program, process, to_char (! logon_time, 'yyyy / mm / dd hh24: mi: ss') from v / $ session where paddr in (select addr from v / $ process where spid in ($ 1)); select sql_text from v / $ sqltext_with_newlineswhere hash_value in (select SQL_HASH_VALUE FROM V / $ Session WHEREPADDR in (SELECT AddR from V / $ Process Where SPID = $ 1)) Order by Piece; EXIT; EOF then, we can do it as follows in the OS environment. / Whoit.sh SPID [Q] how to analyze a table or index [A] command line may be employed analyze commands such as Analyze table tablename compute statistics; Analyze index | cluster indexname estimate statistics; ANALYZE TABLE tablename COMPUTE STATISTICSFOR TABLEFOR ALL [LOCAL] INDEXESFOR ALL [INDEXED] COLUMNS ; ANALYZE TABLE tablename DELETE STATISTICSANALYZE TABLE tablename VALIDATE REF UPDATEANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE] | [INTO TableName] ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName] and so on. If you want to analyze the entire user or database, you can also use a toolkit to analyze DBMS_UTILITY (8i previous toolkit) dbms_stats (toolkit provided after 8i) such as dbms_stats.gather_schema_stats (user, estimate_percent => 100, cascade => true ); dbms_stats.gather_table_stats (user, tablename, deterree => 4, cascade => true); this is some summary of commands and kits 1. For partition tables, it is recommended to use dbms_stats instead of using the Analyze statement. a) You can perform parallelism, multiple users, multiple Tableb), can obtain data of the entire partition table and data of a single partition. c) COMPUTE STATISTICS: single partition, subsequent area, full table, all partition d) can fall out of statistics E) can automatically collect statistics 2, DBMS_STATS Disadvantages a) cannot be validate structureB) Cained CHAINED Rows, you cannot collect information about Cluster Table, which still needs to use an Analyze statement. c) DBMS_STATS The default is not analog ZE, because the default cascade is false, you need to manually specify True3, and the external table inside Oracle 9, Analyze cannot be used, you can only use dbms_stats to collect information. [Q] How to quickly reorganize index [A] Via the Rebuild statement, you can quickly reorganize or move the index to other tablespace Rebuild's feature to rebuild the number of indexes, and can change the index without deleting the original index. Parameter syntax is ALTER INDEX INDEX_NAME REBUILD TABLESPACE TS_NAMESTORAGE (...); if you want to quickly rebuild the index of the entire user, you can use the following scripts, of course, you need to modify SQL> Set Heading Offsql> Set Feedback Offsql> based on your own situation. spool d:! /index.sqlSQL> SELECT 'alter index' || index_name || 'rebuild' || 'tablespace INDEXES storage (initial next 256K pctincrease 0 256K);' FROM all_indexesWHERE (tablespace_name = 'INDEXES'OR next_extent =! (256 * 1024)) And Owner = UserSql> Spool Off This statement is ALTER INDEX INDEX_NAME COALESCE. This statement is merely the same level of Leaf Block in the same level in the index. There is a lot of space in some indexes. In case, there are some effects. [Q] How to use Hint Tips [A] Subway / * hint * / like SELECT / * INDEX (Table_Name Index_Name) * / COL1 ... Note / * and Can't have space between * and If you specify an index with Hint Select / * index (cbotab) * / col1 from cbotab; SELECT / * INDEX (CBOTAB CBOTAB1) * / Col1 from cbotab; SELECT / * INDEX (a cbotab1) * / col1 from cbotab A; where Table_Name is necessary Written, and if you use the alias of the table in the query, you must use the alias of the table instead of the table name; index_name can do not need to write, Oracle will select an index according to the statistical value; if the index name or table name is wrong The hint will be ignored; [Q] How to quickly copy the table or insert data [A] Quick copy table You can specify the NOLogging option, such as: CREATE T1 NOLOGGINGAS SELECT * FROM T2; fast plugging data can specify an APPEND prompt, but you need to pay attention to NOARCHIVELOG mode, default Append is NOLOGGING mode. Under ArchiveLog, you need to put the table settings NOLOGGING mode. Such as INSERT / * APPEND * / INTO T1SELECT * FROM T2 Note: If in the 9i environment and set force logging, the above operation is invalid, and it will not speed up, of course, can be set to no force logging by the following statement. ALTER DATABASE NO Force Logging; Whether to open Force Logging, you can use the following statement to view SQL> SELECT force_logging from v $ database; [Q] How to avoid using a specific index [A] In many cases, Oracle will use indexes to use indexes, we can use a little skill to avoid using the index that should not be used, such as: Table Test, with fields A, B, C, D, establish a combined index inx_a (A, B, C) on A, B, C, and an index inx_b (b) is created separately on B. Under normal circumstances, where a =? And b =? And c =? Use index inx_a, where b =? Use index inx_b but where a =? And b =? And c =? Group BY B will Which index is used? Oracle often uses index inx_b when the analysis data is incorrect (long time is not analyzed) or without analysis data. Through the analysis of the plan, this index will be greatly consumed. Of course, we can avoid using INX_B by tips, and use INX_A. WHERE A =? and b =? and c =? group by b || '' - If B is character where a =? and b =? and c =? group B 0 - If B is the number Simple change, it is often possible to have a lot of query time, we can also use no_index prompts. I believe that many people have not used them, and it is also a good way: SELECT / * NO_INDEX (T, Inx_B) * / * from test twhere A =? and b =? and c =? Group by b [q] Oracle When will Oracle use a jump index scan [A] This is a new feature jump index scan (INDEX SKIP Scan). For example, the table has an index INDEX (A, B, C), when the query condition is WHERE B =? may be used in index index (a, b, c), such as, the implementation plan appears as follows: Index (Skip Scan) of 'Test_IDX '(Non-Unique) Oracle Optimizer (herein, CBO) can apply at least a few conditions for querying INDEX SKIP SCANS: 1 Optimizer is considered appropriate. 2 The number of the unique values of the leading list in the index can meet certain conditions (such as a lot of repetitions). 3 Optimizer To know the value distribution of the leading list (obtained by analysis / statistics). 4 Suitable SQL statement, etc.. [Q] How to create a virtual index [A] You can use the Nosegment option, such as CREATE INDEX Virtual_index_name on table_name (col_name) nosegment; if you need to test the virtual index, you can use an implied parameter to handle the Alter Session Set "_USE_NOSEGMENT_INDEXES" = True; You can use Explain Plan for select ... to see the virtual index effect Use @ $ oracle_home / rdbms / admin / utlxpls to view the execution plan, according to need, we can delete the virtual index, such as the normal index, Drop Index Virtual_Index_name; Note: Virtual indexes are not physical existence, so virtual indexes are not equivalent to physical indexing, do not test virtual indexes with automatic tracking, because it is the actual implementation, it is not used to use virtual indexes. [Q] How to monitor useless index [A] Oracle 9i or more, can monitor the use of indexes, if there is no index in a period of time, generally useless index syntax is: start monitoring: alter index index_name monitoring usage; check usage status: select * from v $ object_usage; stop monitoring: alter index index_name nomonitoring usage; of course, if the index want to monitor the entire user, a script may be used as follows: set heading offset echo offset feedback offset pages 10000spool start_index_monitor.sqlSELECT 'alter index '|| Owner ||'. '|| index_name ||' Monitoring Usage; 'from dba_indexeswhere Owner = User; Spool Offset Heading OnSet Echo Onset Feedback ON ----------------- ------------------------------- Set Heading Offset Echo Offset Feedback Offset Pages 1000spool Stop_index_monitor.sqlselect 'ALTER INDEX' || Owner || '.' || index_name || 'Nomonitoring Usage;' from dba_indexeswhere = user; spool offset heading onset echo onset feedback on [q] How can I fix my execution plan [A] Can use Outline to secure SQL statements The implementation plan can create an OUTLINECREATE OE Replace Outline OutlN_Name Onsewhere ... If you want to delete Outline, you can use Drop Outline Outln_Name; for Outline that has been created, the OL $ HINTS table in Outln users For some statements, you can update Outl using Update Outln.OL $ HINTS INE is like update outln.ol $ hints (ol_name, 'test1', 'test2', 'test2', 'test1) where l n n n 中; then, you will use Test1 Outline with Test2 Outline Changed if you want to use the existing OUTLINE, you need to set the following parameters alter system / session set query_rewrite_enabled = true_rew_outline = true [Q] V $ sysstat Represents what [A] statistics category 1 representative example activity 2 Representative Redo Buffer Activity 4 Representative lock 8 generation data buffer activity 16 represents OS activity 32 represents parallel activity 64 representative table Access 128 Representative debugging information [Q] How to kill a specific database session [A] ALTER SYSTEM KILL Session 'SID, Serial #'; or Alter System Disconnect Session 'SID, Serial #' Immediate; on WIN, ORAKILL provided by Oracle can also kill A thread (actually a Oracle process) on Linux / UNIX, you can directly use KILL to kill the database process [Q] How to quickly find the lock and lock waiting [A] Database lock is a comparative resource, especially When there is a lock waiting, we must find a waiting lock, there is a possible voice, kill the process. This statement will find the lock generated by all DML statements in the database, and you can find that any DML statement actually produces two locks, one is a table lock, one is a line lock. You can kill the session through Alter System Kill Session 'SID, Serial #' to kill seres SELECT / * RULE * / S.USERNAME, DECODE (L.TYPE, 'TM', 'Table Lock', 'TX', 'Row Lock " , NULL) LOCK_LEVEL, O.OWNER, O.Object_name, O.Object_Type, S.SID, S.SERIAL #, S.TERMINAL, S.MACHINE, S.PROGRAM, S.OSUSERFROM V. SESSION S, V $ LOCK L , DBA_Objects Owhere L.SID = S.SIDAND L.ID1 = OBJECT_ID ( ) And S.Username Is Not Null If a lock is waiting, we may even want to lock the table and wait for the following The statement can check who is locked, and who is waiting. SELECT / * Rule * / LPAD ('', Decode (L.XIDUSN, 0, 3, 0)) || L. Oracle_Username User_Name, O.Owner, O.Object_name, Object_Type, S.SID, S. Serial # from v $ locked_Object L, DBA_Objects O, V $ session swhere l.object_id = o.Object_idand L.Session_ID = s.sidorder by O.Object_id, Xidusn DESC or above query results are a tree structure, if there is a child node, Then it is waiting to occur. If you want to know which roll band is used, you can also associate with V $ rollname, where Xidusn is the USN of the rollback segment. [Q] How to effectively delete a big table (many tables) [A] a table with a lot of (100K) extents, if just simply use Drop Table, it will consume a lot of CPU (Oracle wants FET $ , The UET $ data dictionary is operated, which may be used for a few days, better ways to delete extents multiple times to mitigate this consumption: 1. Truncate Table Big-Table Reuse Storage; 2. Alter Table Big -Table Deallocate Unused Keep 2000M (original size N-1 / N); 3. ALTER TABLE BIG-TABLE DEALLOCATE UNUSED KEEP 1500M; .... 4. Drop Table Big-Table; [Q] How to shrink the size of the temporary data file [A] 9i The following version uses ALTER DATABASE DATAFILE 'File Name' Resize 100m Similar statement 9i The above version uses ALTER DATABASE TEMPFILE 'File Name' Resize 100M Note, temporary data file is in use Generally, it is generally not shrinking unless there is a shutdown database or disconnect all sessions to stop the use of temporary data files. [Q] How to clean up the temporary section [A] Can be used as follows 1, use the following statement to see Who I do in using the Times Select Username, SID, Serial #, SQL_ADDRESS, MACHINE, Program, TableSpace, SegType, ContentsFrom V $ Session SE , V $ sort_usage su.session_addr = su.session_addr2, those processes SQL> ALTER SYSTEM KILL Session 'SID, Serial #'; 3, retract the TEMP tablespace SQL> ALTER TABLESPACE TEMP COALESCE; Can use diagnostic events 1, determine TEMP tablespace TS # SQL> SELECT TS #, Name from V $ TABLESPACE; TS # name ---------------------------------------------------------------------------------------------------------------------------------------- -0 Sysyem1 RBS2 Users3 * Temp ... 2, execute cleaning operation ALTER session set events 'immediate trace name drop_segments level TS # 1' Description: TEMP table space TS # is 3 *, SO TS # 1 = 4 If you want Clear the temporary section of all table spaces, TS # = 2147483647 [Q] How to Dump Database Internal Structure, Structure of Control Files shown above [A] Common, analyze data file block, dump data file N block Malter System Dump DataFile N Block M2, Analysis Log File ALter System Dump logfile logfilename; 3, the content of the Analysis Control file ALTER session set events 'immediate trace name controlf level 10'4, analyzing all data file headers ALTER session set events' immediate trace name file_hdrs level 10'5, analysis log file header ALTER Session set events' immediate trace name redohdr level 10'6, analyzing system status, best every 10 minutes, do three compared ALTER session set events' Immediate Trace Name SystemState Level 10'7, Analysis Process Status ALTER Session Set Events' Immediate TRACE NAME ProcessState Level 10'8 analyzes the details of Library Cache Alter Session Set Events 'Immediate Trace Name Library_Cache Level 10' [Q] How to get all event code [A] Event code range is generally from 10,000 to 10999, and the following list is listed in Event Code and Information SET Serveroutput OnDeclareRr_msg varchar2 (120); begindbms_output.enable (1000000); for err_num in 10000..10999LOOPERR_MSG: = SQlerRM (-ERR_NUM); if err_msg not limited '% message' || err_num || 'not found%' thnds_output.put_line (err_msg); end if; end loop; end; / on UNIX system Event information in a text file $ ORACLE_HOME / RDBMS / MESG / ORAUS.MSG You can use the following scripts Event information Event = 10000WHile [$ Event -ne 10999] DoEvent = `expr $ Event 1`ooerr Ora $ EventDone has ensured / event being tracked, you can use the following script to obtain SET SERVEROUTPUT ONDECLAREl_level NUMBER; BEGINFOR l_event iN 10000..10999LOOPdbms_system.read_ev (l_event, l_level); IF l_level> 0 THENdbms_output.put_line ( 'event' || TO_CHAR (l_event ) || 'Is set at level' || to_char (l_level)); end if; end loop; end; / [q] What is Statspack, how do I use it? [A] Statspack is a very good performance monitoring and diagnostic tool provided above Oracle 8i, basically all of the features of BSTAT / ESTAT, and more information can be referred to with document $ Oracle_Home / Rdbms / Admin / SPDoc.txt. Install Statspack: CD $ Oracle_Home / Rdbms / Adminsqlplus "/ as sysdba" @ spdrop.sql - Uninstall, the first time you don't need SQLPLUS "/ as sysdba" @ spcreate.sql - need to enter the table space name to use Statspack : SQLPlus Perfstat / PerfStatexec Statspack.snap; - Information Collection Statistics Snapshot; @ spreport.sql - Enter the startup number to view the startup number and end the snap number other related scripts S: spauto.sql - Submit a job with dBMS_JOB, automatically perform StatPack's information collection statistics sppurge.sql - clear a range Statistics, need to provide start snapshots and end snaps sptrunc.sql - clear (TRUNCATE) all statistics Part 5, Oracle Network and Security [Q] How to define a specific IP access database [A] You can use the login trigger, CMGW, or add a protocol.ora file under $ OREACLE_HOME / Network / Admin (Some OS may be. Protocol.ora), 9i can directly modify SQLNET.ORA: Added: tcp.validnode_checking = YES # iptcp.Inited_nodes = (IP1, IP2, ....) # iptcp.excluded_nodes = (IP1, " IP2, ...) [q] How to pass through the firewall connection database [A] This issue will only appear on the WIN platform, and UNIX platforms will be saved. Solution: In SQLNET.ORA server should look similar SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME) TRACE_LEVEL_CLIENT = 16 HOME0 registry plus [HKEY_LOCAL_MACHINE] USE_SHARED_SOCKET = TRUE [Q] How to use the hostname mode to connect to the database host name only support the TCP / IP protocol Small LAN to modify the following information in Listener.ora (SID_DESC = (Global_DBNAME = UR_HOSTNAME) - Your Machine Name (Oracle_Home = E: / Oracle / ORA92) - ORACLE Home (SID_NAME = Orcl) - Sid Name) Then in the client's SQLNET.ORA, make sure there is Names.Directory_Path = (HostName) You can use the name of the database server to access the database. [Q] What security hazards can be brought by dbms_repcat_admin [A] If a user can execute a DBMS_REPCAT_ADMIN package, great system permissions will be obtained. The following may get the execution permission of the package: 1. Grant Execute on dbms_repcat_admin to public [| User_name] 2 under SYS, the user has an Execute Any Procedure privilege (below 9i, 9i must display the authorization) If the user performs the following statement : EXEC SYS.DBMS_REPCAT_ADMIN.GRANT_ADMIN_ADMIN.GRANT_ADMIN_ANAY_SCHEMA ('user_name "); the user will get great system privileges to get detailed information from user_sys_privs [Q] When do not know the user password, how to jump to another user does not affect the user? [A] We can safely use the user safely through the following method, then jump back, Some useful use of ALTER USER privileges or DBA privilege: SQL> SELECT Password from dba_users where username = 'scott'; password ----------------------- ------ F894844C34402B67SQL> alter user scott identified by lion; User altered.SQL> connect scott / lionConnected.REM Do whatever you like ... SQL> connect system / managerConnected.SQL> alter user scott identified by values' F894844C34402B67 '; User altered.sql> Connect Scott / TigerConnected. [Q] How to reinforce your database [a] To pay attention to the following: Modify Sys, System passwords. 2. LOCK, modification, delete default users: dbsnmp, ctxsys, etc. 3. Change Remote_OS_AUTHENT to FALSE to prevent remote machines from logging in directly. 4. Change O7_DICTIONARY_ACCESSIBILITY to False. 5. Cancel some permissions from Public Role. 6. Check the security of the database's data files. Do not set to 666. Check other DBA users. 7. Close some unwanted services (such as FTP, NFS, etc.) 8. Restrict the number of users above the database host. 9. Regularly check the security alert above the MetaLink / OTN. For example,: http: //otn.racle.com/deploy/security/alerts.htm10. Put your database with the application in a separate subnet, or other user passwords are easily went by Sniffer. Or use Advance Security to log in to the user. 11. Restrictions only some IP can access your database. 12. lsnrctl To add a password, other people can easily turn off your Listener from the outside. 13. If possible, do not use the default 1521 port [Q] How to check if the user uses the default password [A] If you use the default password, it is likely that you can create a certain security hazard for your database, then you can use the following query to get those users using the default password Select UserName "User (s ) with Default password "from dba_userswhere password in ( 'E066D214D5421CCC', -! dbsnmp'24ABAB8B06281B4C ', - ctxsys'72979A94BAD2AF80', - mdsys'C252E8FA117AF049 ', - odm'A7A32CD03D3CE8D5', - odm_mtr'88A2B2C183431F00 ', - ordplugins'7EFA02EC7EA6B86F ', - ordsys'4A3BA55E08595C81', - outln'F894844C34402B67 ', - scott'3F9FBD883D787341', - wk_proxy'79DF7A1BD138CF11 ', - wk_sys'7C9BA362F8314299', - wmsys'88D8364765FCE6AF ', - - xdb'F9DA8977092B7B81 ', - tracesvr'9300C0977D7DC75E', - oas_public'A97282CE3D94E29E ', - websys'AC9700FD3F1410EB', - lbacsys'E7B5D92911C831E1 ', - rman'AC98877DE1297365', - perfstat'66F4EF5650C20355 ', - Exfsys'84b8cbca4d477fa3 ', - si_informtn_schema'd4c5016086b2dc6a', - sys'd4df7931ab130e37 ') - System / [Q] How to modify the default xdb listening port [A] Oracle9i default XML DB puts HTTP's default port to 8080, this is a very common port, and many other webserver use this port if we installed it. It is best to modify it, avoid conflicts, if not, do not install three modifications 1.DBCA, choose your database, then Standard Database Features-> Customize-> Oracle XML DB Option, enter this You should know how to change the picture. 2.Oem Console, modify it in XML Database Configuration 3. Use Oracle Provided by the package: - Change the HTTP / WebDAV port from 8080Sql> Call dbms_xdb.cfg_update (UpdateXML (dbms_xdb.cfg_get (), '/ xdbconfig / sysconfig / protocolconfig / httpconfig / http-port / text () ', 8081)) / - FTP port to change from 2100 to 2111SQL> call dbms_xdb.cfg_update (updateXML (dbms_xdb.cfg_get (),' / xdbconfig / sysconfig / protocolconfig / ftpconfig / ftp-port / text () ', 2111)) / SQL> Commit; SQL> EXEC DBMS_XDB.CFG_REFRESH; - Check if the modification has been successful SQL> SELECT DBMS_XDB.CFG_GET from DUAL; [Q] how to capture user login information, such as SID, IP address, etc. [A] may be utilized login trigger, such as CREATE OR REPLACE TRIGGER tr_login_recordAFTER logon ON DATABASEDECLAREmiUserSid NUMBER; mtSession v $ session% ROWTYPE; CURSOR cSession (iiUserSid IN NUMBER) ISSELECT * FROM v $ sessionWHERE sid = iiUserSid; BEGINSELECT sid INTO miUserSid FROM v $ mystat WHERE rownum <= 1; OPEN cSession (miUserSid); FETCH cSession INTO mtSession; - if user exists then insert dataIF cSession% FOUND THENINSERT INTO log $ information (login_user, login_time, ip_adress, ausid, terminal, osuser, machine, program, sid, serial #) VALUES (ora_login_user, SYSDATE, SYS_CONTEXT ( 'USERENV', 'IP_ADDRESS'), userenv ( 'SESSIONID'), mtSession.Terminal, mtSession.Osuser, mtSession.Machine, mtSession.Program, mtSession.Sid, mtSession.Serial #); ELSE - if user do not exists then return errorsp_write_log ( 'Session Information Error:' || SQLERRM); CLOSE cSession; raise_application_error (-20099, 'login exception', false); end if; close csession; Exceptionwhen Others thensp_write_log ('login trigger error:' || sqlerrm); End TR_Login_Record; Need Note What is the following 1 1, the user has a V_ $ session object query permissions to V_ $ MyStat, which can explicitly authorize the support under SYS. 2, sp_write_log originally a process of writing logs, can be replaced with your own needs, such as NULL skip. 3. You must create a log address login message before creating the trigger. [Q] DDL statements how capture the entire database or that variations and modifications object structure [A] may be employed DDL triggers, such as CREATE OR REPLACE TRIGGER tr_trace_ddlAFTER DDL ON DATABASEDECLAREsql_text ora_name_list_t; state_sql ddl $ trace.ddl_sql% TYPE; BEGINFOR i IN 1..ora_sql_txt (sql_text) LOOPstate_sql: = state_sql || sql_text (i); END LOOP; INSERT INTO ddl $ trace (login_user, ddl_time, ip_address, audsid, schema_user, schema_object, ddl_sql) VALUES (ora_login_user, SYSDATE, userenv ( ' SESSIONID '), sys_context (' USERENV ',' IP_ADDRESS '), ora_dict_obj_owner, ora_dict_obj_name, state_sql); EXCEPTIONWHEN OTHERS THENsp_write_log (' Capture DDL Excption: '|| SQLERRM); END tr_trace_ddl; when you create more trigger points to note 1. You must create a DDL $ TRACE table, used to record DDL record 2, sp_write_log originally a process of writing logs, can be replaced with your own needs, such as Null skip. [Q] DML statements on how to capture a table (not select) statement) [A] may be employed dml triggers, such as CREATE OR REPLACE TRIGGER tr_capt_sqlBEFORE DELETE OR INSERT OR UPDATEON manager.testDECLAREsql_text ora_name_list_t; state_sql capt $ sql.sql_text% TYPE ; BEGINFOR i IN 1..ora_sql_txt (sql_text) LOOPstate_sql: = state_sql || sql_text (i); END LOOP; INSERT INTO capt $ sql (login_user, capt_time, ip_address, audsid, owner, table_name, sql_text) VALUES (ora_login_user, sysdate , sys_context ( 'USERENV', 'IP_ADDRESS'), userenv ( 'SESSIONID'), 'MANAGER', 'TEST', state_sql); EXCEPTIONWHEN OTHERS THENsp_write_log ( 'Capture DML Exception:' || SQLERRM); END tr_capt_sql; Create Pay attention to the above trigger, you must pay attention to a table of Capt $ SQL, used to record DDL record 2, sp_write_log is originally a write log process, can be replaced with your own needs, such as Null skips. Section 6, OS-related and other [Q] how to generate a date format file [A] On Linux / UNIX, use the `Date % Y% M% D` (` this is the key on the keyboard ~ Or $ (DATE % Y% M% D), such as: Touch Exp_table_name_`date % Y% M% D`.dmpdate = $ (DATE % Y% M% D) or Date = $ (Date % Y % M% D --Date '1 Days Ago') # Get the date of yesterday or more days, using% DATE: ~ 4, 10%, where 4 is the start character, 10 is the extraction length, indicating from DATE During the date, the extraction of the start length is 10 strings of 10. You can change to other numbers you need, such as: Echo% Date: ~ 4, 10% If you want to get more accurate time, Win can also use TIME [q] test disks and array performance [A] Similar methods Test Writing Ability TIME DD IF = / Dev / Zero of = / ORADATA / BIDDB / TESTIND / TESTFILE.DBF BS = 1024000 Count = 1000 System IO Use (UNIX): iostat-xnp 2 Display Busy Level [Q] How to configure SSH key [A] Can prevent "Intermediary" offensive mode 1, ssh-keygen or ssh-keygen -d (ssh 2.x) generates a key 2, then copy a table to the server you want to log in , Renamed Authorized_Keys, if it is 3.0 or less, you need to change to Authorized_Keys23, you can also use the config file to further simplify operations such as Host * BJHostName machine name or IPuser username with this configuration file, you can use SSH BJ to access the specified The machine, you can use SCP to transfer files with SFTP. [Q] How to automatically upload / download in the script / download [A] You can write FTP to the shell script, such as ftp -n -i host ip << Eofuser username passcd target directory PUT Fileget File # Query file LS # Exit Byeeof
