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 Is Select * from tablename where 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 varchar2is l_str varchar2 (255) default nULL; l_num number default p_dec; l_hex varchar2 (16) default '0123456789ABCDEF'; begin if (p_dec is null or p_base is null) then return NULL; END IF; IF (trunc (p_dec) <> p_dec or p_dec <0) THEN RAISE PROGRAM_ERROR; END IF; LOOP L_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 numberis l_num number default 0; l_hex varchar2 (16) default '0123456789ABCDEF'; begin if (p_str is null or p_from_base is null) then return null; end if; for i in 1 .. length (p_str) loop l_num: = l_num * p_from_base instr (l_hex, Upper (SubStr, I, 1))) - 1; end loop; return l_num; END TO_DEC; /
[Q] Cannot introduce the detailed usage of Sys_Context [A] Using the following query, you understand SELECT SYS_CONText ('Userenv', '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 ('useerenv', 'session_userid') session_userid, sys_context ('useerenv', 'proxy_user') Pr oxy_user, SYS_CONTEXT ( 'USERENV', 'PROXY_USERID') proxy_userid, SYS_CONTEXT ( 'USERENV', 'DB_DOMAIN') db_domain, SYS_CONTEXT ( 'USERENV', 'DB_NAME') db_name, SYS_CONTEXT host ( 'USERENV', 'HOST'), Sys_Context ('useerenv', 'os_user') os_user, sys_context ('useerenv', 'external_name') external_name, sys_context ('useerenv', '
IP_ADDRESS ') ip_address, SYS_CONTEXT (' USERENV ',' NETWORK_PROTOCOL ') network_protocol, SYS_CONTEXT (' USERENV ',' BG_JOB_ID ') bg_job_id, SYS_CONTEXT (' USERENV ',' FG_JOB_ID ') fg_job_id, SYS_CONTEXT (' USERENV ',' AUTHENTICATION_TYPE ' Authentication_type, sys_context ('useerenv', 'authentication_data') Authentication_datafrom dual [q] How to get today is the day of the week, but also about other date functions [A] can be solved with to_char, 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_LANGUAGUE =' American '; can also specify Select to_Char in the function (to_date " 2002-08-26, 'YYYY-MM-DD'), 'Day', 'NLS_DATE_LANGUAGUAGE = American') from dual; other more usage, you can refer to to_CHAR and TO_DATE functions such as getting complete time format select to_char (sysdate , 'YYYY-MM-DD HH24: MI: SS') from Dual; Jellylys Several Other Functions: This Month Select TO_CHAR (Last_Day (Sysdate), '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 t group 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 your autonomous Transaction [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 DECLARE start_time NUMBER; end_time NUMBER; start_redo_size NUMBER; end_redo_size NUMBER; BEGIN start_time: = dbms_utility.get_time; SELECT VALUE INTO start_redo_size FROM v $ mystat m, v $ statname s WHERE m.STATISTIC # = s.STATISTIC # AND s.NAME = 'redo size'; --transaction start INSERT INTO t1 SELECT * FROM all_Objects; --other dml statement COMMIT; end_time: = dbms_utility .get_time; SELECT VALUE INTO end_redo_size FROM v $ mystat m, v $ statname s WHERE 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'; [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 stored procedures in Oracle [A] can use the dbms_job package to run jobs, such as performing stored procedures, a simple example, submit a job: variable Jobno Number; begin dbms_job . Submit (: Jobno, 'UR_Procedure;', sysdate, 'sysdate 1'); commit; end; then you can query the submitted job SELECT * from user_jobs with the following statement; [Q] How to get milliseconds from the database [A] 9i or more, 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_TIMEfrom 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 Oracle's commonly used FAQ_2 (Oracle architecture system) [q] Oracle's data type [A] Common data type has a CHAR fixed length character field, the maximum length can reach the fixed length of the Nchar multibly character set The character domain, the length follows 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 characters The set, up to 4000 characters or 4000 byte DATE is used to store the fixed length (7 bytes) character fields of all the date, 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, the maximum length is 4000 bytes [Q] Oracle's common keywords, can not be used for object name [A] Take 8i version as an example, generally reserved keywords that cannot be used to do object name Access Add All Ander and any as as as for AS AS ASCOMNCOMMENT COMPER CHECK Cluster ColumnComment Compress CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESCDISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVINGIDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESSNOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIORPRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECTSESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TOTRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEWWHENEVER WHERE WITH more information can view 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) )) 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 can support deleting alter table usrname.tabname set unused (columnname) Cascade constraintsalter table username.tabname Drop (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 ftp://updates.racle.com/3095277/p3095244_9204_Aix64-5l.zip or replace the back part of the desired content, if it is flashget, network ant, please enter the authenticated user name and password, is your metalink User name 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 / statscre.sql Oracle commonly used FAQ_3 (backup 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 dwhere 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 #) loop dbms_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] 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 '); Oracle commonly used FAQ_5 (Oracle Network and Security) [Q] How to limit a specific IP access database [A] You can use the login trigger or modify SQLNET.ORA (9i or more): increase the following: tcp.validNode_Checking = YES # iptcp.inited_nodes = (IP1, IP2, ...) # iptcp.excluded_nodes = (IP1, IP2, ...) [Q] how to pass through the firewall connection database [ip1, ip2, ....) [q] This problem will only appear in 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 / lion Connected.REM Do whatever you like ... SQL> connect system / manager Connected.SQL> alter user scott identified by values 'F894844c34402b67'; user altered.sql> Connect Scott / Tiger Connected. 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 from 4 start is 10 strings. 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 = 1024000count = 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
