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

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


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

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

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

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

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


2

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


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

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

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


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


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

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

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



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