Hot answers tagged

8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


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


5

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


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

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

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


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.


3

All the comments above are appropriate. If you provide the details on the data types and any not null constraints of the columns in question, then we can better determine whether your function-based approach is appropriate. I would also gather statistics on the indexes and look at the unique values, etc. You can compress your baseline index too, if say ...


3

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table. UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you ...


3

You can simply do it in PL/SQL in a loop: begin for c in (select distinct profile from dba_profiles) loop execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited'; end loop; end; /


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

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

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.


2

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


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


2

The problem is most likely in you not having sufficient privileges to create the index in question -- nothing to do with EXPLAIN PLAN itself. To quote the manual, "To create an index in another schema, you must have the CREATE ANY INDEX system privilege".


2

Thats is not what you should execute. sma is the procedure, c4 is simply a cursor in it. You do not execute the cursor local to a procedure, you can not even access it. Execute the below: set serveroutput on begin sma; end; /


2

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement: create table blah2 ( ctascolumn1 not null, ctascolumn2 null ) as select col1, col2 from blah;


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

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing: alter session set NLS_LENGTH_SEMANTICS='CHAR'; Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener: >export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 >export ORACLE_SID=<YOURINSTANCESID> ...


1

this is surely a resources or limits problem. For the first check if you have RemoveIPC enabled (Man for login.conf If it does not solve, review the Database Quick Installation Guide in the sections Configuring Kernel Parameters and Resource Limits and Creating Required Operating System Groups and Users Regards Giova


1

I can't do hard PIVOTs, it's just one of those things that breaks my brain. You need @bluefeet for that. Anyway, you can do it the oldskool way with case ... when and max() if the number of groups per col_id is finite (I'm assuming the Col_Group names are unknown, and there's a finite number: select Col_Id, ...


1

Is the process I/O bound (there is lots of disk activity) or CPU bound (which is possible if the functions are complex)? It is most likely the former and if so then if there are indexes covering those columns it is usually better to drop them before operations that update every row and recreate them afterwards (don't drop the PK and/or clustered index ...


1

According to the output of the command ps -ef|grep pmon shows that the instance name of the standby database is teststan. [oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon But in the description of listener on standby database, SID_NAME is given as 'test'. The Oracle ...


1

Using the ranking ROW_NUMBER() function will work. First give row numbers to all rows in both tables, then join using these row numbers, then update: with oldt as ( select fileNo , folder, fileType, row_number() over (partition by fileNo, folder order by fileType) as rn from oldtable ), ...


1

First of all you need to make those rows unique, otherwise Oracle wouldn't know which value to choose. In SQL Server I would use something like this: WITH NewT AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY fileNo , folder, fileType ORDER BY (SELECT NULL)) AS RowN, fileNo, folder, fileType ...



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