Tag Info

Hot answers tagged

5

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered. (This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ...


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


4

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


4

UPDATE In order to start the instance without interfering with an existing running instance (e.g ORCL), before starting anything: sqlplus / as sysdba create pfile='/tmp/initORCL2.ora' from spfile='?/dbs/spfileORCL.ora'; exit Edit /tmp/initORCL2.ora, and modify (add if doesn't exist) the instance_name: *.instance_name='ORCL2' Prevent listener ...


4

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( ...


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


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


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

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


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

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


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.


2

The nice clean solution is to: install Oracle 11.2.0.3 on a new machine with the same machine name depending on your network putting the new one a different sub-net or in a fire walled zone could prevent network issues create an instance with the same name set the parameters and table spaces to be the same as the original import the data test, test, test ...


2

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


2

Indexing Expressions I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself. For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated ...


2

Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table. create table tmpIds(id int not null) insert into tmpIds (59156) insert into tmpIds (57045) etc... SELECT COUNT(*) FROM "VERSION" INNER JOIN tmpIds ON "VERSION"."VERSION_ID" = tmpIds.id drop table tmpIds


2

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY. After setting PGA_AGGREGATE_TARGET > approximately 12G the V$PGA_TARGET_ADVICE view is empty. This issue will be fixed in version 12.1 and a fix is expected to be included in patch set 11.2.0.4. Apply Patch 11872813


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

You can improve efficiency of your query by (for example) replacing: ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(*) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL AND pv.quarantine_status = 'QUARANTINED' GROUP BY pv.item_id ) ct1 ,( SELECT /*+ NO_MERGE */ ...


1

From here, it appears that you can use it for the very purposes you describe at no cost. The relevant passage is here License Rights and Restrictions Oracle grants You a nonexclusive, nontransferable, limited license to internally use the Programs, subject to the restrictions stated in this Agreement, only for the purpose of developing, testing, ...


1

Yes you do. If you install the Oracle software you need to pay for it. However numerous exceptions exist including you can use Oracle Express 11g for free your customer may have licencing provisions that apply to you Either way, do not take anything from my answer or anyone else's answer as valid. Oracle licencing is complex and a final word can only ...


1

However oracle keeps making my numbers 22 bytes long This assumption is wrong. Values stored in a NUMBER column take up only as much space as needed. Quote from the manual A NUMBER value requires from 1 to 22 bytes (emphasis mine) This can be validated using the VSIZE() function: create table foo (id number(22), some_value number(22,8)); ...


1

See here ixora. Number has variable length, like string. each digit occupies one nibble (4bits, half byte) NUMBER implementation is platform independent you can validate size of number value in a culumn by calling dump() function PS: Oracle also supports native intergers like PLS_INTEGER, but these can only be used in PL/SQL context only.


1

There is no reasonable way how to do that. For example till 11gR1 SELinux was not certified to be used with Oracle database. Since 11gR2 this is at least not recommended. Let's assume that you and Oracle DBA are adult, where is the problem then? Oracle is not running with root privs. On the other hand Oracle requires many kernel tunables(sysctl) to be set ...


1

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file. Your entry in tnsnames.ora file should be like this: AARCER1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524)) (CONNECT_DATA = (SID = ...


1

As soon as your recovery thread is interrupted, making it impossible to recover from an earlier backup to the current time, the remedy is to take a backup before something more serious happens. Next investigate how the archives became corrupted and solve that. Backups are of no importance, important is the ability to recover from a disaster and for that ...



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