Oracle Database FAQ

oracle| Data | database | Problem 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 Oracle database development and management of common problems, I hope to be helpful to everyone.

Q: If you find bad blocks in a table, how do you retrieve other data that is not bad?

A: First you need to find the ID of the bad block (you can run the dbverify implementation), assuming 1234. Run the following query to find the segment name:

Select Segment_name, Segment_type, block_id, blocks

From Dba_extents

Where (1234 between block_id and (block_id + blocks-1));

Once you find a bad segment name, if the segment is a table, it's a good idea to create a temporary table and store the data. If the segment is an index, delete it, and then rebuild it.

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: How do I close the small window when form calls the show?

A: In the process of designing and developing the interface using the Oracle database develop 2000, when the form invokes the show, a background window, the Backgroud ENGINE, is displayed, waiting for the form to call the answer. This backend server is also used when the form invokes another call, regardless of how many of the calls are invoked, the backend server exists and has only one. But when the form and the story are all exited, the window is still waiting and does not close, so we need to close it manually.

We can use the following parameters to not display this small window when the form invokes the show:

Add_parameter (pl_id, ' Oracle_shutdown ', Text_parameter, ' Yes ');

It is important to note that this parameter must precede all parameters, that is, it must be the first argument.

Q: How do I dynamically display records in the list according to query criteria?

Answer: 1. In user parameters, under the data model of the W_clause,w_clause, create a user-defined parameter for the query criteria passed from form, the data type is character type, and the default value is null.

2. Modify the query q_1, modify the SQL query statement to selectfrom dept &&w_clause.

3. When you run the report, the report automatically displays the query criteria records that match the &&w_clause.

If the w_clause passed from form to where dept=1, the SQL query for this report is automatically converted to Selectfrom dept where Dept=1, and records that match the query criteria are displayed in the report run results if the w_ passed from form Cluase is where TO_CHAR (annual, ' YYYY. MM ') = ' 2000.03 ', the record for March 2000 is automatically displayed in the report run results.

Q: In Oracle, how do we see the constraints on a table?

A: We can use the following statement to view the constraints on a table from the All_constraints view:

SELECT constraint_name, TABLE_NAME, R_owner, R_constraint_name

From All_constraints

WHERE table_name = ' table_name ' and owner = ' owner_name ';

Another view All_cons_columns also includes information that makes up the constraint columns on the table.

Q: How do I change a database from a noarchivelog way to a archivelog?

A: First open the Init.ora file to ensure that the archive log target points to a valid directory.

Then start server Manager

Svrmgrl〉shutdown Immediate

Svrmgrl〉startup Mount

Svrmgrl〉alter database Archivelog;

Svrmgrl〉alter database open;

Svrmgrl〉archive log list;

Set the parameter Archive_log_start=true in Init.ora, which sets the archive log to start automatically. The target of multiple archive logs is supported in Oracle 8i, so the parameter is 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 developing an Oracle database, there are two ways to increase the size of the table space:

1. Add additional data files to the table space

For example: Alter tablespace users add datafile '/u01/oradata/orcl/users02.dbf ' size 25m;

2. Modify the current data file in the table space

For example: ALTER DATABASE datafile

'/u01/oradata/orcl/users01.dbf ' resize 50M;

Q: How can I set up a dynamic display field in the list?

A: The way to set up dynamic display fields in the answer is as follows:

1. In the "User parameters" below the "data Model" of the paper, create user-defined parameters, such as RQ,RQ, the display date passed from form, and the data type as character.

2. In the layout model, create a display field f_1, modify the source of the display field in the property palette to user-defined parameter RQ and set to invisible.

3. In Dynamic preview, create a text field d_1, adjust the position and width of the text field, and enter &&f_1 in the contents of the field, the field dynamically displays the value of the user-defined parameter RQ.

Q: About passwords

I run the Oracle8i 8.1.7 Enterprise Edition on the Solaris system. I created two databases: Sugar and TestDB. Set both Remote_login_passwordfile to exclusive (exclusive). I tried to connect to TestDB as a sysdba, but I didn't succeed. Here are my practices:

$sqlplus/nolog

Sql> Conn Sys/change_on_install@testdb

Connected. (connected)

Sql> select * from V$pwfile_users;

Username Sysdb sysop

-----------------------------------------

Internal true

SYS true True

Sql> Conn Sys/change_on_install@testdb as

Sysdba

Error (wrong)

Ora-01017:invalid Username/password; Logon

Denied (invalid username/password, login denied)

Warning:you are on longer connected to

Oracle (WARNING: You have disconnected from Oracle)

Why can't I connect to TestDB as a sysdba?

A: Normally the SYS password in Oracle is synchronized with the internal password, and the sys password is stored in the password file. In these cases you have created a password file that contains a password, rather than using the default "Change_on_install," which is where the problem lies.

I hope the following methods are helpful to you. First, create a password file that contains a password that does not match the system password:

$ orapwd FILE=ORAPW Password=foobar

Entries=40

Then, go to the server and start the database:

$ svrmgr

Svrmgr> Connect Internal

Connected. (connected)

Svrmgr> Startup

Oracle instance started. (Oracle instance started)

Total system Global Area (System global Zone size)

193073136 bytes

Fixed size (fixed-size)

69616 bytes

Variable size (variable size)

141639680 bytes

DB buffers (database buffer)

45056000 bytes

Redo buffers (Redo buffer)

6307840 bytes

Database mounted. (the database is loaded)

Database opened. databases are open.

Now use the password of the SYS user to connect in sys identity:

Svrmgr> Connect sys/change_on_install@ora81

Connected. (connected)

It worked. Now try to connect as a SYSDBA:

Svrmgr> Connect sys/change_on_install@ora81

As SYSDBA;

Ora-01017:invalid Username/password; Logon

Denied (invalid username/password; login denied)

Here's what you said was wrong. Your sys password is: change_on_install, but the password in the password file is foobar. The SYS user is private and the SYSDBA identity connection is like a internal connection, and you must use the password in the password file. Try this:

Svrmgr-connect sys/foobar@ora81 as SYSDBA;

Connected. (connected)

Not everyone needs to use the password in the password file, and the user needs to use their own password. By authorizing Sysdba to Scott, you can understand this:

Svrmgr> Grant SYSDBA to Scott;

Statement processed. (processed)

This command adds Scott's credentials to the password file. If you change Scott's password, the password file will also change automatically. Now, you can try to connect Scott with the SYSDBA identity:

Svrmgr> connect scott/tiger@ora81 as SYSDBA;

Connected. (connected)

Everything's fine. 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. (Changed successfully)

Svrmgr≫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 in the password file. Once you have created the password file, the Oracle database is placed in two accounts: SYS and internal, and the password you provide on the command line is used as the password for these two accounts. When you change the SYS user password in the database, the database will flush out the SYS and internal passwords in the password file. The following action will show that the password Foobar is already invalid:

Svrmgr> connect sys/foobar@ora81 as SYSDBA;

Ora-01017:invalid Username/password; Logon

Denied (invalid username/password, login denied)

Q: Using the query option to output data

I know that in oracle8i, you can use query to selectively output table data. I wanted to do it with the exp command, but it didn't work. Here is the command I wrote, and the error message I got:

Exp DDD/DDD FILE=/DBF/U11/CUSTOMER.DMP

Tables=aasc. Ast_customer_keep

Query=\ ' where Cua_trans_dts \<

Add_months\ (sysdate, -6\) \ '

TABLE_EXPORT[2]: Cua_trans_dts:not found. (Not found)

A: The operating system is different, and the method used to specify the query= parameter is different. The WHERE statement often has many special characters, such as =.>.< and spaces, and so on. Shell command prompts in UNIX and Windows operating systems do not welcome these characters, and these characters are ignored. You should use a different method depending on the operating system. I typically use a parameter file (parfile) with the query option, using Parfile to use exactly the same method without regard to the operating system platform.

An example is given below. I built a table T with the SELECT * from All_objects, and I want to output all object_id less than 5000 rows. 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 double quotes at both ends of the where statement. In Unix, you must do this:

$ exp userid=/tables=t query=\ "where

object_id \< 5000\ "

Exp userid=/tables=t Parfile=exp.par

If you use a Parfile file that contains query= "where object_id < 5000", I can use the same command on both systems:

Exp userid=/tables=t Parfile=exp.par

In both operating systems, exactly the same. This is much easier than using different query strings in different platforms.

Q: Dbms_random

Can you tell me the best way to write a random number generator that produces a random number greater than 0 less than 1?

A: Oracle8 8.0 introduced the Dbms_random package, Oracle8i 8.1.6 Edition introduced the new features of the Dbms_random package, but the Oracle8i document does not provide a detailed overview of its functionality. Fortunately: There is a new Dbms_random packet function that can return a random number between 0-1. This new function is:

FUNCTION value return number;

FUNCTION value (low in number;

Number) return number;

FUNCTION normal return number;

FUNCTION string (opt char, len number)

return VARCHAR2;

The first form of the value function returns a random number that is greater than or equal to 0 and less than 1, and the second form returns a random number that is greater than or equal to or less than high. The following 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

The normal function returns the number of groups that are subject to a normal distribution. The standard deviation of this normal distribution is 1 and the expected value is 0. 68% of the values returned by this function are between 1 and +1, 95% between-2 and +2, and 99% between 3 and +3. In fact, that's what you see in Listing 1.

Finally, the string function. It returns a random string of up to 60 characters in length. Parameter opt can be any single character in the value shown in Listing 2.

The files for these functions and dbms_random packages are contained in Sqlplus:

Select text

From All_source

where name = ' Dbms_random '

and type = ' PACKAGE ' ORDER by line;

Q: Connection order and predicate evaluation

In the following query, which part of the WHERE statement executes first?

Select field names from EMP, dept

where Emp.dept_num = Dept.num and

Emp.name like ' s% ' and dept.name= ' IT ';

Answer: The order of execution varies with the existing index, statistics, and Session/init.ora parameters.

Suppose you already have an index built on dept (name) and EMP (Dept_num). Assuming that the optimizer considers dept to be unique, it may operate in the following order:

Find Dept columns with an index built on dept (name)

Finds a matching EMP column using an index built on the EMP (Dept_num) (that is, the connection emp.dept_num = Dept.num)

Based on emp.ename like ' s% ' to filter

Now, we assume that there is no index on the EMP (dept_num) or an index on dept (name), and that there are indexes built on EMP (name) and dept (NUM). The optimizer may operate in the following order:

Use the index built on the EMP (name) to find the Emps with S

Find a match with an index built on dept (NUM)

Filter results according to Dept.name = ' IT '

The order of predicate evaluation is uncertain and can be changed over time, and determined by the optimizer. Do not assume that anything will happen in a certain order. If you do that, over time, your application may have some strange-looking bugs. Look at the following example: Create a table and enter some data. When x= ' A ', the second column of data "Y" is a numeric value, and when x= ' B ', "Y" is not a number.

Sql> CREATE TABLE T (x varchar2 (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 based on this table: Find rows that meet x= ' a ', Y=1.

Sql> select * from t where x = ' a ' and

y = 1;

ERROR:

Ora-01722:invalid number

No rows selected (Error, invalid number, no rows selected)

Yo, no success. In this case, the database first executes Y=1, and when it finds the row of y= ' X ', it's obvious that it can't convert ' x ' to a number, so it fails. The following program will give a different result:

sql> analyze table T compute statistics;

Table analyzed. (Tables have been analyzed)

Sql> select * from t where x = ' a ' and

y = 1;

X Y

- -

A 1

Using a different optimizer mode, it worked! Why? The optimizer says, "Hey, checking x= ' a ' is faster than checking y=1, because there's a transformation in Y=1 that converts y from character to number." So, I first check x= ' a ' and then check y=1. ”

This example shows that the order of predicate execution may be indeterminate, and you cannot expect a particular order of execution. In other words, when you rely on an implied conversion, you must be very cautious.

Q: Display sga--fixed size (fixed size) and variable size (variable size)

What does fixed size and variable size mean when running "show SGA" at the svrmgr prompt?

A: The fixed size is the size of the invariant component in the SGA, which is fixed at the time the Oracle database is compiled. It is a fixed-size memory that points to other parts of the SGA. The size of this part of the SGA cannot be changed.

Variable size refers to a variable amount of memory allocated. A variable block of SGA divided into shared pools, large pools, Java pools, cursor areas, and other structures.