The Oracle database is known for its powerful features and stability, but at the same time, there are many difficulties in development and management. The author summarizes some common issues for Oracle database development and management, hoping to help everyone. Q: If there is a bad block in the table, how to retrieve other abundant data? A: First, you need to find a bad block ID (you can run a dbverify implementation), assuming 1234. Run the following query search segment name: select segment_name, segment_type, block_id, block from dba_extents where (1234 Between Block_ID and (block_id block - 1)); Temporary table, storing data. If the section is an index, remove it and rebuild. create table good_table as select from bad_table where rowid not in (select / index (bad_table, any_index) / rowid from bad_table where substr (rowid, 1, 8) = 1234) Q: as how close FORM call REPORT Small window? A: During the DEVELOP 2000 design development interface using the Oracle database, when the Form calls Report, a background window Report BackGroud Engine is displayed, waiting for Form to call the Report, when Form calls other Report, also use this background server No matter how many Reports are called, this background server exists and only one. However, when Form and Report exit all, the window is still in the waiting state, which is not closed. At this time, we need to turn it off. We can use the following parameters that do not display this small window when calling Report: add_parameter (PL_ID, 'ORACLE_SHUTDOWN', TEXT_PARAMETER, 'YES'); what to note is that this parameter must be added to all parameters, ie it must be The first parameter. Q: How to dynamically display records in Report in Report according to query criteria? Answer: 1. In the "User Parameters" below the "Data Model" below, create a user-defined parameter w_clause, w_clause is the query condition passed from the Form, the data type is a character, the default is NULL. 2. Modify the query Q_1, modify the SQL query statement to SELECT from dept && w_clause. 3. When running the report, the report will automatically display the query condition records that match && w_clause. If the w_clause passed from Form is where dept = 1, the SQL query of this report is automatically converted to select from dept where dept = 1, and the record complies with the query condition is displayed in the report operation result, if it is passed from Form W_Cluase is where to_char (annual, 'yyyy.mm') = '2000.03', automatically displays records in March 2000 in the report operation results.
Q: How do we check the constraints on a table in Oracle? A: We can use the following constraint statement on a table to see from the view ALL_CONSTRAINTS: SELECT constraint_name, table_name, r_owner, r_constraint_name FROM all_constraints WHERE table_name = 'table_name' and owner = 'owner_name'; also includes another view of the composition of Table ALL_CONS_COLUMNS Information on the constraint. Q: How do I change the database from the NOARCHIVELOG mode to the ArchiveLog mode? A: First open the init.ora file, make sure the archive log target points to a valid directory. Then start the Server Manager svrmgrl> shutdown immediate svrmgrl> startup mount svrmgrl> alter database archivelog; svrmgrl> alter database open; svrmgrl> archive log list; archive_log_start = TRUE setting parameter in the init.ora, it is set to start automatically archive log. Support for multiple archive logs in Oracle 8i, so the parameters are modified to log_archive_dest [n], where N is 1 to 5. Q: How do we increase the size of the table space in the Oracle database? A: In the development of the Oracle database, we have two ways to increase the size of the table space: 1. Add additional data files to table spaces, for example: alter tablespace users add datafile '/u01/oradata/orcl/users02.dbf' size 25m; 2. Modify the current data file for table space, for example: alter database datafile '/u01/oradata/orcl/users01.dbf' resize 50m; Q: How do I set up a dynamic display domain in Report? A: The method of setting up a dynamic display domain in Report is as follows: 1. In "User Parameters" below Report, create a user-defined parameter, such as RQ, RQ is a display date, data passed from Form Type is a character pattern. 2. In the layout model, create a display domain F_1, modify the source of the display field as a user-defined parameter RQ in the property tab, and set to "invisible". 3. In the dynamic preview, create a text domain D_1, adjust the location and width of the text domain, entries &&f_1 in the contents of this domain, the domain dynamically displays the value of the user-defined parameter RQ. Q: The password I am running Oracle8i 8.1.7 on the Solaris system. I created two databases: Sugar and TestDB. Set the remote_login_passwordfile of both to "exclusive". I tried to connect to TestDb as SYSDBA, but failed to succeed.
Here is my approach: $ SQLPLUS / NOLOG SQL> Conn Sys / Change_on_install @ Testdb Connected. (Connected) SQL> SELECT * from V $ PWFILE_USERS; UserName Sysdb Sysop -------------- -------------------------- Internal true true sys true true sql> conn sys / change_on_install @ testdb as sysdba error (error) ORA-01017 : invalid username / password; logon denied A: Usually the SYS password in Oracle is synchronized with the INTERNAL password, and the SYS password is stored in the password file. In the above case, you have established a password file containing a password, rather than using the default "change_on_install," this is the problem. I hope the following methods will help you. First, create a password file, which contain a password, this password does not match the system password: $ ORAPWD File = ORAPW Password = FOOBAR Entries = 40 Then, enter the server, start the database: $ svrmgr svrmgr> Connect Internal connezed. ) SVRMGR> startup ORACLE instance started. (ORACLE instance started) Total system global area (system global area size) 193073136 bytes fixed size (fixed size) 69616 bytes variable size (variable size) 141639680 bytes database buffers (database buffer) 45056000 BYTES Redo Buffers 6307840 BYTES DATABASE MOUNTED. Database has been opened. Now use the SYS user password, with SYS identity: SVRMGR> Connect Sys / change_on_install @ Ora81 Connected. (Connected) is successful. Now try sysdba identity connection: SVRMGR> Connect Sys / change_on_install @ ora81 as sysdba; ora-01017: invalid username / password; logon denied (invalid username / password; login rejected) here appears what you said . Your SYS password is: change_on_install, but the password in the password file is foobar. SYS users are dedicated, as SYSDBA identity is like an Internal connection, you must use passwords in the password file.
Try this: SVRMGR-Connect Sys / Foobar @ ORA81 AS SYSDBA; Connected. (Connected) is not everyone needs to use passwords in the password file; users need to use their own password. By authorizing Sysdba to Scott, you can understand this: SVRMGR> GRANT SYSDBA TO Scott; Statement Processed. (Procedure) This command adds Scott to the voucher of Scott to the password file. If you change the password of Scott, the password file will automatically change. Now, you can try SCOTT as Sysdba: SVRMGR> Connect Scott / Tiger @ ORA81 AS SYSDBA; Connected. (Connected) everything is normal. You can now use the alter user command to change the password of the SYS user. SVRMGR> alter user sys identified by change_on_install; Statement processed (successfully changed) SVRMGR & Gt;. Connect sys / change_on_install @ ora81 as sysdba;. Connected (connected) You can also use change_on_install, because changing the SYS user password will also change the password file The password in. When you have created a password file, the Oracle database is placed in two accounts: SYS and INTERNAL, and the password you provide in the command line as the password of these two accounts. When you change the SYS user password in the database, the database will rush out the SYS and INTERNAL passwords in the password file. The following will display the password foobar is already invalid: SVRMGR> Connect sys / foobar @ ora81 as sysdba; ora-01017: invalid username / password; logon denied (invalid username / password, login rejection) Q: Use Query Option Output Data I know In Oracle8i, you can use Query to selectively output table data. I want to use the Exp command, but I have not succeeded. Below is the command I have written, and the resulting error message: exp ddd / ddd file = / dbf / u11 / customer.dmp tables = aasc.ast_customer_keep query = / 'where ua_trans_dts / In Windows, you must do this: C: / exp> exp userid = tkyte / tkyte tables = t query = "" "WHERE Object_id <5000" "" Note: In Windows, you need to use three ends on the WHERE statement. Double quotes. In UNIX, you must do this: $ exp userid = / tables = t query = / "where object_id / <5000 /" exp userid = / TABLES = T PARFILE = Exp.par If you include query = "where object_id <5000" Parfile file, I can use the same command in two systems: exp userid = / TABLES = T PARFILE = Exp.par in two operating systems, exactly the same. This is easy to use different Query strings in different platforms. Q: DBMS_Random Can you tell me the best way to write a random number generator that produces more than 0 less than 1 random number? A: Oracle8 version 8.0 introduces the dbms_random package, and Oracle8i 8.1.6 introduces new features of the DBMS_RAndom package, but there is no detailed and comprehensive introduction to its function in the Oracle8i document. Fortunately, there is a new DBMS_RAndM package function to return the random number between 0-1. This new function is: function value return number; function value (low in number, high in number) return number; function normal return number; function string (Opt char, len number) returnavhar2; Value function's first form returns one A random number greater than or equal to 0 and less than 1; the second form returns a random number greater than or equal to LOW, less than HIGH. Below is an example of its usage: SQL> Select dbms_random.Value, dbms_random.value (55,100) 2 from dual; value dbms_random.value (55,100) --------------- --- -------------------------- .782821936 79.6367038 NORMAL function returns a set of normal distributions. This normal distribution standard deviation is 1, the expected value is 0. 68% of the value returned by this function is between -1 and 1, 95% between -2 and 2, 99% is between -3 and 3. In fact, this is what you see in Listing 1. Finally, it is a String function. It returns a random string of 60 characters. The parameter OPT can be any single character in the value displayed by Listing 2. About these functions and dbms_random packages are included in SQLPLUS: select text from all_source where name = 'dbms_random' and type = 'package' Order by line; Q: Connection order with predicate evaluation in the query, WHERE statement Which part is performed first? Select Field Names from Emp, Dept WHERE Emp.DEPT_NUM = Dept.Num and Emp.Name Like 'S%' and dept.name = 'IT'; A: Executation Some Index, Statistics, and Session / Init. The ORA parameter varies. Assume that there is an index that is built on DEPT (Name) and EMP (DEPT_NUM). Assuming that the optimizer thinks that DEPT is unique, it can be operated in the order below: Using the indexed index of DEPT (Name) to find the matching EMP column using indexes built on EMP (DEPT_NUM) (ie connected EMP) .dept_num = dept.num) Based on the establishment in Emp.Ename Like 'S%', we must now do not establish an index on EMP (DEPT_NUM), nor is it indexed on DEPT (NAME), and there is an establishment Index on EMP (Name) and DEPT (NUM). The optimizer may operate according to the order below: Find the EMPS with S-EMP (NAME) to find the matching item to find the matching item according to DePt.Name = 'IT' filtering result predicate The order in which the value is uncertain, and it can be changed over time and determined by the optimizer. Don't assume that anything will happen at a certain order. If you do then, over time, your app may appear very strange errors. See the following example: Create a table and enter some data. When x = 'a', the data "y" of the second column is a value, when x = 'b', "Y" is not a number. SQL> Create Table T (x varcha2 (1), y varchar2 (1)); Table Created. SQL> INSERT INTO T VALUES ('A', '1'); 1 Row Created. SQL> INSERT INTO T VALUES (' B ',' x '); 1 row created. Now run a query according to this table: lookup satisfies X =' a ', Y = 1 line. SQL> Select * from t where x = 'a' and y = 1; error: ORA-01722: Invalid Number No Rows Selected (error, invalid number, no line), no success.
