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 ...


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 ...


2

You don't want your test database to retain the same internal id number as the production database. Hence it would be best to use RMAN's command DUPLICATE, because it sets a different id (and also a new database name). This command is specifically designed to do what you require.


2

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


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 ...


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

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 ...


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

Roles are not owned, but granted to users: select grantee from dba_role_privs where granted_role = 'LANDING_R'; Object privileges granted to LANDING_R: select * from dba_tab_privs where grantee = 'LANDING_R'; System privileges granted to the LANDING_R: select * from dba_sys_privs where grantee = 'LANDING_R';


1

Firstly, the oracle docs are VERY clear that you shouldn't use data pump as SYS: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL824: Do not invoke Export as SYSDBA, except at the request of Oracle technical support The problem will almost certainly be permissions related. The user that is running the Oracle database is ...


1

The error you're getting indicates that the User1 user does not have access to the dbms_job package. You'd need a DBA to GRANT EXECUTE ON dbms_job TO user1; If you create the trigger and procedure to be owned by a user other than the one that owns the table, you'd need to include the schema name in your GRANT statement inside the procedure. EXECUTE ...


1

check the file permissions because the export is done by oracle user whereas the directory belongs to osman user


1

All the information regarding statistics gathered with dbms_stats can be found on dba_tab_stats_history. Ref.: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2103.htm#BGBBBGCC


1

There shouldn't be any impact adding datafiles to an existing tablespace. Easiest is to let oracle manage the files, make sure you have DB_CREATE_FILE_DEST set: ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_BASE%\oradata\'; Now the only thing to do if your existing datafiles are full or hitting the max autoextend limit is: alter tablespace USERS add ...


1

If there is an index on the column the IN clause can make better use of it. You can test this yourself: CREATE TABLE TEST1 ( STATUS NUMBER NOT NULL ); CREATE INDEX IXTEST1 ON TEST1(STATUS); insert into test1 select MOD(level,10) from dual connect by level <= 10000; select * from TEST1 where status in (0,1,2,3,4); select * from TEST1 where status not ...


1

Yes it can. We are currently using it on a 11g RAC database. Do you need any other details?


1

Thanks for the information. I was in the middle of a case where OLS wasn't installed. So, the procedure I used was as follows: Shutdown all instances On the main node, as user oracle, run $ORACLE_HOME/oui/bin/runInstaller (it could be necessary count on installation media) Select "Advanced Options", select "OLS" (after that, option changes to ...


1

This is the expected behaviour on a unix system. either a process' effective user id allows to read/write a file or itseffective primary or its effective secondary group ids allow to read/write the files If you log on to a unix system the effective user id , the effective group id and the effective secondary group ids are the ids defined for your login ...



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