Tag Archives: oracle

Impdp meets ora-31685

Impdp error:

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
Job "SYS"."SYS_IMPORT_FULL_01" completed with 9 error(s) at 14:37:45

The reason is that there are materialized views in the exported objects, but the user does not have permission to create materialized views

After the user is authorized, delete the imported data and import it repeatedly to solve the problem

grant create materialized view to user2;

Kettle stores data report ora-00600 to Oracle: internal error parameters [KQD object $], [u], [0], [107], [CS], []

Check ORACLE data has a CS trigger compilation error, the problem is that the trigger state is invalid (invalid), but complie and can not pass, to drop and will report object invalid can not drop, so need to register him in sys.objerror$, in order to drop
Do you have this kind of error message ?
SQL> drop user MYUSER cascade;
drop user MYUSER cascade
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [101],
[MYTRIGGER_MYCALCULATION], [], [], [], [], [], [], []
It means that one or more object is invalid. And in the error message you have the object name causing this error and then you can find the ids of instances of this object causing errors.
SQL> select object_id from dba_objects where object_name=‘MYTRIGGER_MYCALCULATION’;
OBJECT_ID

867767
840888

Now you can have logs about these objects.
SQL> select name, type , line, position, text from all_errors where name like ‘MYTRIGGER_MYCALCULATION’;
NAME TYPE LINE POSITION

TEXT
MYTRIGGER_MYCALCULATION TRIGGER 2 6
PLS-00049: bad bind variable ‘NEW.SYSVERSION’
MYTRIGGER_MYCALCULATION TRIGGER 3 4
PLS-00049: bad bind variable ‘NEW.SYSVERSION’
MYTRIGGER_MYCALCULATION TRIGGER 6 4
PLS-00049: bad bind variable ‘NEW.SYSVERSION’
Here we understand the errors are caused by compilation errors on a trigger. As we just want to drop the user object that contains this trigger, we can ignore these errors :
SQL> insert into sys.objerror$ values(867767);
insert into sys.objerror$ values(840888);
1 row created.
SQL> insert into sys.objerror$ values(840888);
insert into sys.objerror$ values(840888);insert into sys.objerror$ values(840888)
1 row created.
Then we can drop the user successfully.
SQL> drop user MYUSER cascade;
User dropped.

After Oracle 11g is installed, SQL_ Plus.exe The problem of Chinese garbled in command window

Today, after installing Oracle 11g, we encountered sqlplus.exe Command window user login Chinese garbled problem, hope to give yourself a note, also hope to let more people know how to solve. After entering the CMD, right-click the mouse, click properties, find the box as shown in the figure below, and use the old version of the console!

Python uses CX_ Oracle batch insert error report ora-01036 error solution

Recently, in the process of using Python to write data import program, CX is used_ When the Oracle database was imported into Oracle database, there was an error of “ora-01036: illegal variable name/number”. After querying the data and trying, the problem was solved.

The Error statement is:

sql = ‘insert into \”mytable_ a\” values(%s,%s,%s)’

cursor.executemany (sql, data)

As a result, the error “ora-01036: illegal variable name/number” appears.

resolvent:

Change the place holder of parameter transfer to “: 1,: 2,: 3”,

The modified statement is as follows:

sql = ‘insert into \”mytable_ a\” values(:1, :2, :3)’

cursor.executemany (sql, data)

Execute again and solve the problem.

Oracle quick replacement undo table space method

Undo table space is not enough. There are two ways to deal with it

1. Expand the table space size;

2. Create a new undo table space and delete the original one.

1、 Preliminary operation

Confirm undo table space name

select name from v$tablespace; 

Check the occupancy of undo table space and the storage location of data files;

select file_ name,bytes/1024/1024 from dba_ data_ files where tablespace_ name like ‘UNDOTBS1’; 

2、 Expand undo table space

alter   database  UNDOTBS1 datafile   ‘/opt/oracle/oradata/inms/undotbs02.dbf’   resize   4000M; 

3、 Create a new undo table space and delete the original one

1. Create a new undo table space and set the automatic extension parameters;

create undo tablespace undotbs2 datafile ‘/oradata/oradata/ddptest/UNDOTBS1.dbf’ size    2 1000m reuse autoextend on next 800m maxsize unlimited; 

2. Change the SPFILE configuration file dynamically;

alter system set undo_ tablespace=undotbs2 scope=both; 

3. Delete the original undo table space;

drop tablespace undotbs1 including contents; 

4. Confirm whether the deletion is successful;

select name from v$tablespace; 

5. Determine $Oracle_ HOME/dbs/ spfileoinms.ora Whether the content has changed:

$more spfileoinms.ora  

*.undo_ management=’AUTO’

*.undo_ retention=10800

*.undo_ tablespace=’UNDOTBS2′ 

If there is no change, execute the following statement:

SQL> create pfile from spfile; 

File created. 

6. Delete the data file of the original undo table space, and the file name is the result of the step.  

#rm $ORACLE_ BASE/oradata/$ORACLE_ SID/undotbs01.dbf

4、 Conclusion

According to the actual situation, expanding undo table space can only support for a period of time. After running for a period of time, the undo table space data file will eventually reach the upper limit. Therefore, the second scheme is adopted. You can use the script to replace the undotbs1 and undotbs2 table spaces alternately, which can quickly solve the problem.

（1） Script 1: replace the undotbs2 table space with undotbs1

create undo tablespace undotbs1 datafile ‘/u01/oracle/oradata/orcl/UNDOTBS1.dbf’ size 512m reuse autoextend on next 512m maxsize unlimited;

 

alter system set undo_ tablespace=undotbs1 scope=both;

 

drop tablespace undotbs2 including contents;

 

rm ‘/u01/oracle/oradata/orcl/UNDOTBS2.dbf’

（2） Script 2: replace the undotbs1 table space with undotbs2

create undo tablespace undotbs2 datafile ‘/u01/oracle/oradata/orcl/UNDOTBS2.dbf’ size 512m reuse autoextend on next 512m maxsize unlimited;

 

alter system set undo_ tablespace=undotbs2 scope=both;

 

drop tablespace undotbs1 including contents;

 

rm ‘/u01/oracle/oradata/orcl/UNDOTBS1.dbf’

 

 

Oracle data file space release

When the data of Oracle database takes up a large space, and the data stored in it does not take up such a large space, the reason may be that the user has deleted some data, but the size of the data file will not automatically shrink. At this time, if you want to reduce the size of the data file, you can use the following methods.

1、 Use the following statement to query data files that can free up space:

select a.file#,

a.name,

a.bytes/1024/1024 CurrentMB,

ceil(HWM * a.block_ size)/1024/1024 ResizeTo,

(a.bytes – HWM * a.block_ size)/1024/1024 ReleaseMB,

‘alter database datafile ”’ || a.name || ”’ resize ‘ ||

ceil(HWM * a.block_ size)/1024/1024 || ‘M;’ ResizeCmd

from v$datafile a,

(SELECT file_ id, MAX(block_ id + blocks – 1) HWM

FROM DBA_ EXTENTS

GROUP BY file_ id) b

where a.file# = b.file_ id(+)

And (a.bytes – HWM * a.block_ size) >0

and rownum < 10

View the data file that belongs to the system table space, and reset it.

2、 Find out the data file that needs to be reset, and execute the reset statement

An error is reported because the reset data file size needs to be set to an integer.

Adjust the size of resize to 16GB, 16384mb;

3、 View free disk space

The size of the system file is reduced to 16GB, the remaining space of the root disk is greatly increased to 19.6gb, and the utilization rate is reduced to 78%.

Lock mechanism in Oracle

In order to explore lock, I set up a test table test, the structure and test data are as follows:
sql & gt; select * from test;

        ID NAME
———- ———-
         6 yoxi
         2 robinson
         2 tttt
         3 david
         4 jason
         5 lucy
         1 test

7 rows selected.

Row share if row share locking is performed on a table, other sessions cannot perform exclusive locking on the table, but they can perform any other locking. That is to say, if a session performs row share lock on the table, other sessions can be executed except drop command.
In session 1:
sql & gt; lock table test in row share mode;
the table is locked.
Session 2:
sql & gt; lock table test in row share mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in row exclusive mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in share mode;
the table is locked.
SQL & gt; commit;
submit complete
sql & gt; lock table test in exclusive mode nowait;
lock table test in exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but the specified nowait method is used to obtain the resource
sql & gt; update test set id = 10;
7 lines have been updated.
SQL & gt; rollback;
rollback completed.
SQL & gt; drop table test;
drop table test
*
error in line 1:
ora-00054: the resource is busy, but it is specified to obtain the resource by nowait// in fact, the drop table command will perform exclusive locking on the table
row exclusive when executing insert, update, Delete will obtain a row exclusive lock, which has all the characteristics of row share. If a session obtains a row exclusive lock, then other sessions cannot perform exclusive lock on the table or share lock on the table. DML operation can be performed on the table, but drop is not allowed;

In session 1, the
sql & gt; lock table test in row exclusive mode;
table is locked.
Session 2:
sql & gt; lock table test in row share mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in row exclusive mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in share mode nowait;
lock table test in share mode nowait
*
error in line 1:
ora-00054: the resource is busy, but it is specified to get the resource in nowait mode
sql & gt; lock table test in exclusive mode nowait;
Lock table test in exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but the nowait mode is specified to obtain the resource

Share (shared lock) when a session has a share lock on a table, other sessions cannot lock the table in exclusive, row exclusive mode. In other words, if row exclusive is not available, insert, update, delete and drop operations cannot be performed.
In session 1, the
sql & gt; lock table test in share mode;
table is locked.
In session 2, the
sql & gt; lock table test in row share mode;
table is locked.
SQL & gt; lock table test in row exclusive mode nowait;
error in line 1:
ora-00054: the resource is busy, but the specified nowait method is used to get the resource
sql & gt; lock table test in share mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in exclusive mode nowait;
lock table test in exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but it is specified to get the resource in nowait mode

Exclusive (exclusive lock) when the drop command is executed, an exclusive lock is generated. As the name suggests, as long as a session has an exclusive lock on a table, other sessions cannot lock the table.
In session 1, the
sql & gt; lock table test in exclusive mode;
table is locked.
In session 2,
sql & gt; lock table test in row share mode nowait;
lock table test in row share mode nowait
*
there is an error in line 1:
ora-00054: the resource is busy, but the specified nowait method is used to obtain the resource
other locks do not need to be tested, because row share cannot obtain other locks, let alone other locks

Share row exclusive: if a session obtains a share row exclusive lock, other sessions can only obtain the row share lock on the table, that is to say, other sessions can’t perform insert, update, delete or drop operations on the table. Row share locking is good for nothing except preventing drop…
sql & gt; lock table test in share in session 1 Row exclusive mode;
the table is locked.
In session 2
sql & gt; lock table test in row share mode nowait;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in row exclusive mode nowait;
lock table test in row exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but it is specified to get the resource in nowait mode
sql & gt; lock table test in share mode nowait;
Error in lock table test in share mode nowait
*
line 1:
ora-00054: the resource is busy, but the specified nowait method is used to obtain the resource
sql & gt; lock table test in exclusive mode nowait;
error in lock table test in exclusive mode nowait
*
line 1:
ora-00054: the resource is busy, However, it is specified to obtain resources in nowait mode

To sum up:

1. If a table is locked by a session, no matter what the lock is, the other session will not be able to obtain the exclusive lock for the table, that is, the table cannot be deleted. In fact, it is very easy to be the same. If someone is using that table, you must delete it

2. If row exclusive locking can be performed on a table, DML operation can certainly be performed

3. Share lock can be understood in this way. As the name suggests, share lock is used for sharing. Since it is shared, other users can only view it and cannot change it.

4. We can understand exclusive lock in this way. As the name suggests, exclusive lock is exclusive. Other users can only view it and cannot modify it.

Note: the lock command above can only get TM (table) lock

There are two kinds of locks generated by update, delete and insert. One is TM lock, the other is TX lock. TX lock means transaction lock

Select….. For update will also get a TX lock and a row exclusive lock
as well

Reprint address: http://blog.csdn.net/robinson1988/article/details/4675371

How can Oracle query tables of other users without adding a table user name

Question: how can Oracle access other users’ tables without adding a table user name

Reasons: 1. Convenient access to common tables

2. Hide the users of the table

3. Limit authority

Solution:

1、 Create consent word

         CREATE [PUBLIC] SYNONYM synonym_ name FOR [schema.] object[@db_ link];

2、 Create view

        create view DF_ YHJB_ BZ  as select * from MIS.DF_ YHJB_ BZ

3、 Modify current_ Schema parameter

The schema used to switch the current session. When parsing the object name, if there is no qualified schema name before the object name, Oracle will automatically query the matching objects under this schema.

for example, when the “Scott” user executes the “select * from EMPs;” statement, Oracle will query the “EMPs” table under the “Scott” architecture by default; however, if the “alter session set” current_ Schema = XX; the current schema of the session is changed. When this statement is executed, Oracle will query the EMPs table under XX schema.

Oracle stops a job

1. Related tables and views

　dba_ jobs 　　all_ jobs 　　user_ Jobs contains all the job information DBA of the login user_ jobs_ Running contains information about running jobs. Note that you must use the sys user of Oracle to log in to the database to view the DBA_ jobs_ Running, V $process, V $session table. At the same time, when logging in to the operating system, the Oracle user should be used.

2. Problem description

Solve a problem for colleagues, when the network connection is poor, execute a very long time SQL insert operation.

Since the network condition is not good, we choose to use disposable job to complete the insertion operation. After the job was executed for a period of time, I found that there was something wrong with the inserted table (I’m sorry, I didn’t check it first at that time). I’m going to stop job, because when job is running, all my modifications will report the error that system resources are busy.

It is not feasible to forcibly kill session, because the job will be restarted later. If the executed SQL is also killed, the restarted job will be executed again.

3. Solutions

The better way to do it is to do it;

1. First determine the job number to stop

In 10g, it can pass DBA_ Jobs_ Running to confirm.

Find running job:

    select sid from dba_ jobs_ running;

 

Find the SPID of the running job:

    select a.spid from v$process a ,v$session b where a.addr=b.paddr  and b.sid in (select sid from dba_ jobs_ running);

 

2. Break your confirmed job

Pay attention to DBMS_ Job package to identify your job as broken.

    SQL> EXEC DBMS_ JOB.BROKEN (job#,TRUE);

Note: after executing the command, the job you selected is still running.

 

3. Oracle session corresponding to kill

If the job is required to stop immediately, you need to find the corresponding session (SID, serial #) of the job, and then execute the following command:

    ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Or directly kill the session of the corresponding operating system. If you use alter system kill session to execute for a long time, you can use the OS command to quickly kill the session

     For Windows, at the DOS Prompt: orakill sid spid

     For UNIX at the command line> kill –9 spid

 

4. Check if your job is still running

Check whether the job you want to stop is still running. In most cases, it should have stopped. In particular, the execution of the third step of the “killer” command. If it really doesn’t stop, we have to do it again from the first step and the third step.

 

5. Change the number of job queue processes to 0

First, confirm the current number of job queue processes

    SQL> col value for a10

    SQL> select name,value from v$parameter where name =’job_ queue_ processes’;     

Then change the number of job queue processes to 0

    SQL> ALTER SYSTEM SET job_ queue_ processes = 0;

Make sure all jobs stop.

 

6. Modify anything you want to modify, even the content in the job.

 

7. After the modification is completed, stop the broken state of the job.

SQL>EXEC DBMS_ JOB.BROKEN (job#,FALSE):

 

8. Resume job_ queue_ The original value of processes

     ALTER SYSTEM SET job_ queue_ processes = original_ value;

At this point, the entire stop and modify job completed

    

However, it should be noted that when mark is in a broken state, it may take a while for job to execute SQL internally. Therefore, you must consider it carefully when creating a job. At the same time, if possible, you can add some parts to judge the “stop signal” in your PL/SQL code. To perform the above steps.

After all, Oracle is very stubborn when executing jobs

 

4. Annex: usage of orakill

Oracle’s lock table solution “ora-00031: session marked for kill” often encounters a session that is always active, making the CPU always in use. Although it kills, it can’t make the thread end. Kill session is just to kill the process, but the thread is always active. It needs a real kill thread to solve the problem of high CPU utilization. OS: Windows 2003 orakill uses orakill Sid SPID, where sid is the instance name and SPID is the thread number. How to get this SPID?The following is a statement to find SPID. Select SPID, osuser, s.program from V $process P, V $session s where p.addr = s.paddr and s.sid = XXX; — Note: XXX is the SID of session (not database SID). You can input it yourself.
orakill instance_ Name SPID can successfully solve the problem of high CPU utilization in this way

The provider is not compatible with the version of Oracle client systems

In fact, this problem is very intuitive, which is what you use in the C # project Oracal.DataAcess.dll The version of the file is the same as the one installed on your computer ODAC.EXE ( ODP.NET ）Version inconsistency, whether it’s 32-bit or 64 bit platform inconsistency, or version inconsistency, anyway, it’s the mismatch that causes the problem. There are many specific solutions on the Internet, so I won’t go into details here.

 

But this time I had a strange problem. I have many on my machine ODAC.EXE Version, including two 64 bit, a 32-bit, and clearly I use in C # Oracle.DataAccess.dll The version of is exactly the same as an ODAC, but the exception “the provider is not compatible with the version of Oracle client sometimes” is thrown, which makes me very puzzled. What’s more strange is that this exception is not thrown out every time. Sometimes it doesn’t exist. That is to say, the connection between my code and the database, which can or can’t be connected, all depends on luck.

 

After a long search, we finally found the root of the problem. Because this is a legacy project, I downloaded it locally and saw the files circled in this project. Although I felt strange, I didn’t care too much. But it is these documents that cause the above problems. When I remove these files and recompile them, everything is OK and I can connect to the database normally. Therefore, the reason for this summary is that in the C # project of Oracle, you should not introduce OCI files casually. (at the moment, I don’t know why predecessors introduced these documents when they did this project.)

 

Initialization error of PLSQL connection to Oracle

Download as many as 64 = 32-bit database

Oracle is 32-bit and your PLSQL must be 32-bit; Oracle is 64 bit and your PLSQL must be 64 bit;

After entering the account password, this box will pop up

Solution: see how many bits your Oracle is, and then you can download how many bits of your PLSQL

This interface is usually installed correctly

Tns-03505: the ultimate solution to failed to resolve name

System environment: centos7

Oracle environment: 11g

Fault description: the server directly restarts after running data crash, and various errors are reported after restart,

Tns-03505: failed to resolve name,

The external PLSQL connection prompts ora-12170: TNS: connection timeout.

The specific solutions are as follows:

1. Check the file configuration information listener and tnsnames, and confirm that the following is correct. If the configuration of this file is wrong, the listening service will not be started normally.

 

2. Start the monitoring service lsnrctl start and update the registration

Services Summary…
Service “orcl” has 1 instance(s).
  Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
 

“Unknown” is prompted here. Registration update is required,

3. Open orcl service and update the registration

sqlplus/as sysdba

startup

alter system register;

quit

lsnrctl status

Service “orcl” has 1 instance(s).
  Instance “orcl”, status READY, has 1 handler(s) for this service…
Service “orclXDB” has 1 instance(s).
  Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully

Confirm that the monitoring status is ready
then

4. Test results

Tns-03505 failed to resolve name is displayed. After confirming that there is no problem in the previous steps, proceed to the last step to view the firewall and port information

 

5. Check the firewall information, view the specific port information, and add the corresponding port. After confirming that the port is added, test again, and you can access it normally