Tag Info

Hot answers tagged

4

I'm afraid it is practically impossible. In Oracle's terminology external authentication means: "If users's name on client machine is identical to user's username in database, then user does not have to enter ANY password". This way of authentication is very weak and must be combined with other security features, for example you allow users to login only ...


4

Why do you use this view at all for this query? The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint ...


3

If a system privilege is granted with ADMIN OPTION to a user, this user can grant this system privileges to other users, e.g. after GRANT CREATE SESSION TO SCOTT WITH ADMIN OPTION Scott is able to grant CREATE TABLE to other users. He is also able to grant CREATE TABLE to other users WITH GRANT OPTION. WITH GRANT OPTION works for roles too. An object ...


3

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1. Easily reproduced with the following: create view v1 as ( select 'view' as vvv from dual ); create view v2 as ( select * from v1 ); with v1 as ( select 'cte' as vvv from dual) select * from v2;


3

You can't restrict by IP address. All you can really do is create a new user and only grant them SELECT on that one table. create user newuser identified by 'p4ssword'; grant connect, resource to newuser; grant select on yourtable to newuser; It is actually possible to check the IP address a user is logging in from using a login trigger, and deny the ...


2

Generate the statements you need select 'DELETE FROM '||table_name||' ;' from user_tables;


2

The chinese characters are decoded correctly with utl_i18n.unescape_reference. In fact they just aren't displayed properly in your query result which might not support those special characters. You can confirm that with this SQL Fiddle. It is the client which is respsonsible to display the characters appropriately. If the client can not display a ...


2

You can do this by using a save point. create or replace PROCEDURE SPTest AS BEGIN -- We create a savepoint here. SAVEPOINT sp_sptest; insert into emptest(empid,empname,deptno) (1,'ravi',10); insert into test1(id,name,sal) (1,'raju',4444); update emptest set empname='hari' where empid=1; -- If any exception occurs EXCEPTION WHEN ...


2

In-fact if you need to do the external authentication for client you it can be done via external password store: 1) you need to make the password external use SHA-1 hashing algorithm. (note this is not compatible with jdbc-4). 2) open sqlnet.ora and make sure it contain the following line (if note you need to add it then save and close) ...


2

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required) Oracle client version 12c is compatible with Oracle server version 11.2 (and below), so no your error isn't likely to come from that. Check that the listener is indeed ...


2

There are three things going on here. When handling the exception, you do an insert followed by a rollback. That means that insert is rolled back as well. At the very least, switch the order of the statements: DBMS_OUTPUT.PUT_LINE('Whoops'); ROLLBACK TO sp_sptest; INSERT INTO Z_AUDIT_HISTORY(AUDIT_HISTORTY_ID,ASSET_ID,STATUS) ...


2

I guess these {...} get replaced somewhere in the code with something that makes sense. This is indeed an old script because compute statistics is not quite state of the art anymore. Go ahead and create the index with the sql you just posted: create index x on y(gid) tablespace IDX_TS; And then analyze the table with dbms_stats: begin ...


2

There's not much information you provide, so this is only a guess. Your WHERE clause Tab2.Col1 = Tab1.Col1(+) may be backward, with regard to the (+) ... I would recommend switching it to a LEFT JOIN regardless, just to make it more readable.. select count(b.Col1) "Number of Records", --- CHANGED a.Col1 to b.Col1 a.col2 "Type", ...


2

You can use SQL Developer, Tools - Database Diff, this would give you an idea. There is a tutorial here http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52sqldev-1735911.html. Only use the compare/diff section.


1

You should look here at Managed Tablespace Alerts, notably this bit where you can monitor by two criteria with warning and critical thresholds. By percent full For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued. By free space remaining (in kilobytes (KB)) For ...


1

In order to find out the users and the profile assigned you can use the commands below. 1- DESC DBA_USERS; it will show you all the fields name for which you want to query 2- SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS; This command will show you the user name, profile and account status i.e. which profile is assigned to which user


1

I am suspecting with the case sensitive name or special character(i.e. ' or white-space) included with the package name unknowingly while creating package. I tried to get object name from dictionary itself which ensures correct object name in your drop command in the following script, it can help in your context: select 'DROP PACKAGE ' || '"' || ...


1

edit was a 16-bit application and 16-bit applications don't run on 64-bit Windows, therefore it is not included in 64-bit Windows. https://support.microsoft.com/kb/896458 Just use notepad. You should run this with elevated privileges, for example in a command prompt started with "Run as Administrator".


1

As an example of the possibilities: because it's pl/sql in back, one can define an object to represent a row, define a pl/sql table of those objects, create type T_MY_TABLE as table of t_my_object; and end with OPEN p_recordset FOR select * from table( v_my_table ); So rather than constructing mongo, often dense and/or cryptic direct queries on a ...


1

You are, as per your tag, not using Oracle 12c. The syntax id NUMBER DEFAULT t1_seq.NEXTVAL, is only valid from 12c onwards, so you get an error. On Oracle 11 and prior, you want to use an insert trigger to apply the sequence's nextval to the primary key: create or replace trigger binaries_ins before insert on binaries for each row when (new.id is ...


1

You really need to generate an explain plan. Since your query has an OR there may be situations where the query is being short circuited based on data not existing, hence the performance is not consistent. Also, don't join everything together, subqueries with low cardinality put at the top in a WITH subquery_name clause, and join similar tables together, ...


1

1) Audits are performed on actions you define. For example: audit all by testing by access will audit everything that the user testing does. 2) I think you should look into XML auditing. Source Example: Set up your database by issuing alter system set audit_trail='XML, EXTENDED'; This modification does require a DB restart. After you set that you'll notice ...


1

Do both servers have similar hardware and OS? Is there enough storage space for the 11g database? Have you thought about using data pump instead? With data pump and enterprise edition you can export and import in parallel as well as compress. With 10g you can compress the meta data but not the data. With 11g you can compress the data and meta data. How does ...


1

It could be that one of the imp default parameters for Oracle 10G is different in Oracle 11G. I would try adding the buffer=4096000 parameter to the imp command to see if that helps. That parameter is available on both exp and imp commands.


1

I see no reason you can't do this. Typically, you would perform the following steps. Move the *.dbf files found in %ORACLE_BASE%\oradata\ to a safe place. Move the files found in %ORACLE_BASE%\fast_recovery_area\ to a safe place. Move the spfile found in %ORACLE_HOME%\database\spfileXE.ora to a safe place. Install a new Oracle instance and Configure it ...


1

Short version: CREATE TABLE Relation ( stu_id INT NOT NULL REFERENCES Student, par_id INT NOT NULL REFERENCES Parent, PRIMARY KEY (stu_id, par_id) ) ; Long version: Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and ...


1

You can't embed DDL in a stored procedure like this. Either use execute immediate and your DDL statement passed to it as a string, or, better, remove the stored procedure entirely an execute the DDL directly.


1

If you're talking about moving the path of the directory object, just use the Create or replace option : CREATE OR REPLACE DIRECTORY Result AS 'c:\newpath';


1

since you confirmed the character set in the database is AL32UTF8, you need to make sure the fonts is installed on the Linux machine, and install the message libraries. that will make ORA_NLS33 has been set appear when you execute alter session set nls_language=


1

This is not possible without using PL/SQL or a loop to generate the SQL in the program you're using to execute the SQL.



Only top voted, non community-wiki answers of a minimum length are eligible