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

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure. begin hotel.fill_city(10000); end; / should correctly invoke your procedure. Second, you have issues with the naming of your local variables. Normally, you would not create local variables like city and postal_number that are the ...


3

You can create tables because the RESOURCE role has the CREATE TABLE privilege granted. To create a view, you need to have granted the CREATE VIEW privilege additionally, because, by default that privilege is not granted, not even to RESOURCE. SQL> select privilege from dba_sys_privs where grantee='RESOURCE'; PRIVILEGE ...


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

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

They are Oracle Resource Manager waits. Resource Manager is a set of Oracle functionality that lets you prioritise work. See the list of possible waits in the documentation.


2

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


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

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

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


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

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

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


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

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

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

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :) Don't really see any solutions here except updating the SQL query. This ...


1

That is the MySQL documentation, not the Oracle Database documentation. There is no such clause in Oracle Datababase. Use a trigger. Here is an example: link


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

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

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


1

I'm hard-pressed to imagine how you'd accomplish that with a single cursor in SQL Server. The same question has been asked and answered on StackOverflow. If you want to get really tricky, you could also use an XML Query to search across objects with a single query. In either case, the result is going to be insanely slow since you're full scanning every ...


1

DB links are probably not going to work as a solution. Oracle7 is crazy old. The question's facts are little limited. What types of objects (e.g., tables, stored procs, triggers, etc.) are involved? How much data? That said: As it relates to table data, my solution would be to spool the data to text and access the text via external tables you setup in the ...


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

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

It looks like resource manager is in action. Your dba configured this most likely to give certain processes priority over other processes. Oracle Scheduler is fuly integrated with Resource Manager but the way this is presented is a bit misleading. Often it has nothing to do with Scheduled Jobs at all. You can find which sessions are waiting for resource ...


1

A new subpartition can be added to the existing partitioned table using the following command: ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS');



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