FAQs about Oracle-Oracle Architecture

Part 2: ORACLE Architecture[Q] which data types are available in ORACLE?[A] common data types include CHAR fixed-length character fields. The maximum length can be up to 2000 bytes. NCHAR multi-byte character sets have fixed-length character fields. The length varies with the character set, it can contain a maximum of 2000 characters or 2000 bytes of VARCHAR2 character sets. It can contain up to 4000 characters of NVARCHAR2 multi-byte character sets. The length varies with the character set, A maximum of 4000 characters or 4000 bytes of DATE are used to store the fixed length (7 bytes) character fields of all dates. The time is stored as part of the DATE. Unless you set init. the NLS_DATE_FORMAT parameter in the ora file is used to replace the date format. Otherwise, the date is expressed in the DD-MON-YY format, for example, 13-APR-99 indicates the Variable Length Value column of 4.13number. The allowed values are 0, positive, and negative. The NUMBER value is usually stored in four or fewer bytes. A maximum of 21 bytes LONG can be a variable-length character field, and a maximum length can be 2 gbraw to indicate a variable-length character field of binary data, the maximum length is 2000 bytes. LONGRAW indicates the variable-length character field of binary data. The longest value is 2 gbmlslabel, which is only used in TrustedOracle. This data type uses two to five bytes of BLOB Binary large objects in each row, the maximum length is 4 gbclob characters and the maximum length is the CLOB Data Type of the 4GBNCLOB multi-byte character set. The maximum length is 4GBBFILE external binary file. The size is determined by the operating system. ROWID indicates the binary data of RowID, the value of Oracle8RowID is 10 bytes. In Oracle7, the limited RowID format is 6 bytes. UROWID is the binary data used for data addressing. The maximum length is 4000 bytes.[Q] Which common Oracle keywords cannot be used for object names?[A] The 8i version is used as an example, general reserved keywords cannot be used for Object 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 partition synonym sysdate table then to trigger uid union unique update user validate values varchar VARCHAR2 view whenever where with detailed information view the v $ reserved_words View[Q] how to view the database version[A] select * from v $ version contains version information, core version information, and number of digits (32-bit or 64-bit, you can view the data through file, such as file $ ORACLE_HOME/bin/oracle.[Q] how to view database Parameters[A] show parameter name. For example, you can use show parameter spfile to check whether 9i uses the spfile file or select * from v $ parameter. In addition to this parameter, Oracle has A large number of implicit parameters, you can view the following statements: select name, VALUE, decode (isdefault, 'true', 'y', 'n') as "Default", decode (ISEM, 'true ', 'y', 'n') as SesMod, decode (ISYM, 'immediate', 'I', 'referred', '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_PARAMETERSELECT x. inst_id as instance, x. indx + 1, ksppinm as NAME, ksppity, ksppstvl as VALUE, ksppstdf as isdefault, decode (bitand (ksppiflg/256,1), 1, 'true', 'false') as ISEM, decode (bitand (ksppiflg/65536,3), 1, 'immediate', 2, 'referred', '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 database character sets[A] database server Character Set select * from nls_database_parameters, which is derived from props $ and indicates the character set of the database. Client Character Set environment select * from nls_instance_parameters, which is derived from v $ parameter, indicating the character set setting of the client, which may be a parameter file, environment variable, or registry session Character Set environment select * from nls_session_parameters, it is derived from v $ nls_parameters, which indicates the session's own settings. It may be the session's environment variable or the alter session is completed. If the session has no special settings, it will be consistent with nls_instance_parameters. The character set of the client must be the same as that of the server to correctly display non-Ascii characters of the database. If multiple settings exist, the character set of alter session> environment variable> registry> parameter files must be consistent, but the language settings can be different. We recommend that you use English for language settings. If the character set is zhs16gbk, The nls_lang can be American_America.zhs16gbk.[Q] how to modify the character set[A] version 8i and later can use alter database to modify the character set, but it is also limited to the subset to the superset. We do not recommend that you modify the props $ table, which may cause serious errors. Startup nomount; Alter database mount exclusive; Alter system enable restricted session; Alter system set job_queue_process = 0; Alter database open; Alter database character set zhs16gbk;[Q] How to Create a function-based index[A] 8i or above, make sure Query_rewrite_enabled = trueQuery_rewrite_integrity = trustedCompatible = Create index indexname on table (function (field) above 8.1.0 ));[Q] How to move a table or table partition?[A] Alter table tablename move [Tablespace new_nameStorage (initial 50 M next 50 Mpctincrease 0 pctfree 10 pctused 50 initrans 2) nologging] After moving partition syntax alter table tablename move (partition partname) [update global indexes], you must re-index Alter index indexname rebuild. If the table has Lob segments, the normal Alter statement cannot move the Lob segment to another tablespace, but only moves the table segment. You can use the following method to move the Lob segment alter table tablename movelob (lobsegname) store as (tablespace newts );[Q] How to obtain the current SCN[A] select max (ktuxescnw * power () + ktuxescnb) from x $ ktuxe earlier than 9i; if it is A 9i version, you can obtain select dbms_flashback.get_system_change_number from dual through the following statements;[Q] ROWID structure and composition[A] ROWID of version 8 and later forms oooooooofffbbbbbbrrr8 and lower ROWID (also called restricted Rowid. RRRR. in FFFF, O is the Object ID, F is the file ID, B is the block ID, and R is the row ID. If we query the ROWID of a table, based on the block information, you can know the exact number of blocks occupied by the table, and then know how much data space is occupied (the data space is not equal to the allocated space of the table)[Q] How to obtain the DDL statement of an object[A] third-party tools will not talk about it. It mainly refers to dbms_metadata1 of 9i and DDL statement set heading offset echo offset feedback offset pages offset long 90000 select dbms_metadata.get_ddl ('table', 'tablename', 'scame') from dual; if you get the script for the entire user, you can use the following statement select dbms_metadata.get_ddl ('table', u. table_name) from user_tables u; of course, if it is an index, You need to modify the relevant table to the index[Q] How to Create a constrained index on another tablespace[A] 1. create an index first, then create A constraint 2. Use the following statement to create A create table test (c1 number constraint pk_c1_id primary keyusing index tablespace useridex, c2 varchar2 (10 )) tablespace userdate;[Q] How do I know that tables do not have primary keys?[A] in general, the primary key of A table is necessary. A table without A primary key does not comply with 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 related data dictionary interpretation user_tables table user_tab_columns table column user_constraints constraint user_cons_columns constraint and column relationship user_indexes index user_ind_columns index and column relationship[Q] dbms_output prompts that the buffer is not enough. How can I increase it?[A] dbms_output.enable (20000); in addition, if dbms_output information cannot be displayed, set serveroutput on[Q] how to modify the column name of a table?[A] 9i and later versions can use the rname command alter table UserName. TabNameRENAME COLUMN SourceColumn TO DestColumn9i and earlier versions can use create table ...... As select * from SourceTable. In addition, you can delete alter table UserName. TabNameSET UNUSED (ColumnName) cascade constraintsalter table UserName. TabNameDROP (ColumnName) CASCADE CONSTRAINTS[Q] how to install help for sqlplus?[A] SQLPLUS help must be manually installed. The shell script is $ ORACLE_HOME/bin/helpins. Before installation, you must set the SYSTEM_PASS environment variable, for example: $ setenv SYSTEM_PASS SYSTEM/MANAGER $ helpins if this environment variable is not set, the SYSTEM will prompt you to enter the environment variable when running the script. Of course, in addition to the shell script, you can also use the SQL script for installation, you do not need to set environment variables. However, you must log on to the system. $ Sqlplus system/managerSQL> @? After installing/sqlplus/admin/help/helpbld. SQL helpus. SQL, you can use the following methods to help SQL> help index[Q] How to quickly download Oracle Patches[A] First we get the download server address, there is A ftp://updates.oracle.com on the http page and then login with ftp, the username and password are the username and password of metalink. If we know Patch Number 3095277 (Patch Set of 9204), 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 encoded ed in 0.02 Seconds 13.00 Kbytes/sec. ftp>. We can download this information using flashget. Add the following connection ticket![Q] How to move data files[A] 1. Shut down the database and use OS to copy. shutdown immediate shut down database B. copy the data file to the new location under the OS c. startup mount start to d. alter database rename datafile 'old file' to 'new file'; e. alter database open; open database 2. Use Rman to operate RMAN> SQL "alter database 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 "alte R database datafile ''file name''' online "; Note: You can use OS copy to perform online operations without shutting down the database. The procedure is the same as that of rman, using rman is the same as using OS copy. In rman, copy Copies data files, which is equivalent to OS cp, while switch is equivalent to alter database rename, which is used to update control files.[Q] If you manage online log groups and Members[A] The following are common operations. If you add A log file group Alter database add logfile [group n] 'file full name 'size 10 M under the OPA/RAC thread number; add a member Alter database add logfile member 'file full name' to group n to this group; delete a log member Alter database drop logfile member' file full name' from this group '; delete the entire log group Alter database drop logfile group n;[Q] how to calculate the size of the REDO BLOCK[A] The calculation method is (redo size + redo wastage)/redo blocks written + 16. For details, see the following example SQL> select name, value from v $ sysstat where name like '% redo % '; name value reserved ---------- redo synch writes 2 redo synch time 0 redo entries 76 redo size 19412 redo buffer allocation retries 0 redo wastage 5884 redo writer latching time 0 redo writes 22 redo blocks written 51 redo write time 0 redo log space requests 0 redo log space wait time 0 redo log switch interrupts 0 redo ordering marks 0SQL> select (19412 + 5884) /51 + 16' "Redo black (byte)" from dual; Redo black (byte) ---------------- 512[Q] What basic content does a control file contain?[A] The control file mainly contains the following entries, you can view the database entrycheckpoint recordsredo ss progress thread progress file recordsdata file history range history log recordsbackup set recordsbackup piece recordsbackup datafile recordsbackup log history copy recordsbackup datafile upload uption HISTORY COPY replica uption RECORDSDELETION RECORDSPROXY COPY RECORDSINCARNATION RECORDS[Q] how to retrieve other non-bad data if a table contains bad blocks?[A] First, you need to find the bad block ID (which can be implemented by running dbverify). Assume that it is <BID> and the file is encoded as <FID>. Run the following query section name: SELECT segment_name, segment_type, extent_id, block_id, blocksfrom dba_extents twherefile_id = <FID> AND <BID> between block_id and (block_id + blocks-1) once the bad segment name is found, if the segment is a table, it is best to create a temporary table to store the data. If the segment is an index, delete it and recreate it. Create table good_tableasselect from bad_table where rowid not in (select rowidfrom bad_table where substr (rowid,) = <BID> note the difference between the limited ROWID before 8 and the current ROWID. You can also use the diagnostic event 10231SQL> alter system set events '2017 trace name context forever, level 10 '; all data except bad blocks in the TABLE that creates a temporary TABLE good_table are retrieved. SQL> CREATE TABLE good_table as select * from bad_table; finally, close the diagnostic event SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off'. For the ROWID structure, see dbms_rowid.rowid_create function.[Q] Can I delete all database users?[A] A series of default users and tablespaces are created when the ORACLE database is created. Their list is as follows: SYS/CHANGE_ON_INSTALL or INTERNAL system users, data dictionary owner, create a script for the super permission owner (SYSDBA :? /Rdbms/admin/SQL. bsq and various cat *. we recommend that you change the password immediately after the SQL statement is created. This user cannot be deleted. The default Administrator of the SYSTEM/MANAGER database has the DBA role permission to create a script :? /Rdbms/admin/SQL. bsq it is recommended that you change the password immediately after creation. This user cannot be deleted. The storage outline of the OUTLN/OUTLN optimization plan User Creation Script :? /Rdbms/admin/SQL. we recommend that you change the password immediately after creating bsq. This user cannot be deleted ------------------------------------------- · SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and blke/PAPER. experiment and test users, including the example Table EMP and DEPT creation Script :? /Rdbms/admin/utlsampl. SQL allows users to change passwords and can be deleted. In the product environment, it is recommended to delete or lock HR/HR (Human Resources), OE/OE (Order Entry ), SH/SH (Sales History ). the experiment and test users include the example Table EMPLOYEES and scripts for creating the instances :? /Demo/schema/mksample. SQL users who can change their passwords can be deleted. We recommend that you delete or lock DBSNMP/DBSNMPOracle Intelligent agent creation script in the product environment :? /Rdbms/admin/catsnmp. SQL, called from catalog. SQL can change the password-you need to place a new password to the snmp_rw.ora file. If you do not need Intelligent Agents, you can delete ----------------------------------------- the following users are optional installation users, you do not need to install · CTXSYS/CTXSYSOracle interMedia (ConText Cartridge) to manage user creation scripts :? /Ctx/admin/dr0csys. SQL · TRACESVR/TRACEOracle Trace server creation Script :? /Rdbms/admin/otrcsvr. SQL · ORDPLUGINS/ORDPLUGINSObject Relational Data (ORD) User used by Time Series, etc. Create Script :? /Ord/admin/ordinst. 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 creation Script :? /Ds/SQL/dssys_init. SQL · MDSYS/MDSYSOracle Spatial administrator user creation Script :? /Ord/admin/ordinst. 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 creation Script :? /Rdbms/admin/statscre. SQL