Tag Info

New answers tagged

2

It seems that granting the privilege references allows the user to see the column names: grant references on the_table to app_user; The grant needs to be done for the base table not for the view.


2

version <= 11.2? No.* But in 12c, there is a new feature called Asynchronous Global Index Maintenance. The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens ...


0

List of files that you should backup: select member from v$logfile; select name from v$datafile; select name from v$controlfile; select value from v$parameter where name ='spfile'; If the last query returns nothing, backup %ORACLE_HOME%/database/initXE.ora (Windows) or $ORACLE_HOME/dbs/initXE.ora (Linux, Unix). To make it a simple as possible, you can ...


0

The answer is really use Hierarchical query model from Oracle. http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm So in my case: select * from tfinal start with request = &search and type = 'CNAME' connect by prior response = request; The solution is simpler than what I envisioned.


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


1

These are the default values related to this: NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ os_authent_prefix string ops$ remote_os_authent boolean FALSE Set remote_os_authent to true: alter system set ...


0

When you login in the terminal, Oracle lets you in because the user oracle on the server 10.10.1.64 is a member of the OSDBA group on that machine. When you connect from SQL Developer on your laptop, it's a remote connection so OS Authentication isn't normally possible. You might be able to use SSH Authentication, see ...


0

I've found one solution which works for date and other numeric values, although I need to use a fixed iterator. It does the job but I wonder if there's a way to do it without the use of a constant. SELECT maint_id, start_date + ((iterator - 1) * day_interval) AS date_due FROM maint_sched, (SELECT LEVEL iterator FROM dual CONNECT BY LEVEL <= 1000) ...


-2

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are commit might fail. This is usally not expexted by various tools and also by developers commit might last very long. This might be problem with distributed(XA) transactions. also troubleshooting might be problematic, especially when using ...


0

Well, perhaps not the best way, however, I managed to do it via this: (I ignored that extra third table, wasn't sure ... not sure how it affects things) ;) (the w_tab1 and w_tab2 are just your sample data ... ) Tested on Oracle 12. with w_tab1 as ( select 28 idproduct, 18 totalout from dual union all select 26 idproduct, 8 totalout ...


2

The Solaris 11 (11.2) install uses groups of packages called: solaris-desktop solaris-large-server solaris-small-server solaris-minimal-server The group is defined in a install manifest during AI install Oracle also provides a x86 (64bit) desktop install iso A SPARC or x86 Text server install See the install documentation for the list of files if you really ...


0

There are reasons why Oracle might change exec plan without statistics updates. There are Dynamic sampling, Bind variables peeking and Performance baselines. These features should help Oracle to adapt the execution plan with actual load and actual data characteristics. There can be various reasons why Oracle decides to use different exec plan to the same ...


-1

Basically, yes in the short term. If it were intending to go a different way it would generate a different plan, and no database (to my knowledge) backtracks and finds a different plan once it has started processing. If you are looking at a plan generated before a query was run then any statistics (rows, number of iterations of lopped constructs, and so ...


3

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan. For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups. The "No" answer is the reason that Oracle ...


0

It looks like a non-partitioned table can not be partitioned using Alter table add partition command. For the "alter table add partition" command to work, table should already have at least 1 partition. In other words, the table should be a partitioned table already. That is the reason, create Table SQL given above with partition parameters works and alter ...


1

The install/oraparam.ini contains the following lines (even in a fresh download): #MSVCREDIST_LOC flag will provide the name of the exe that is being shipped in stage/ext/bin MSVCREDIST_LOC=vcredist_x64.exe This is incorrect because you need the 32-bit version (x86). Now if you check stage/ext/bin, there is a file vcredist_x86.exe, and obviously no ...


2

Usual rules apply. Code changes should not be required but something will break if code is more complex. Also there will be some SQL plans regressions. Official documentation about deprecated features is there: Deprecated and Desupported Features for Oracle Database 12c Also you can check Metalink note 567171.1 about _fix_control parameter. This parameter ...


0

It seems that it's not possible, but, for the record, here's to get get the information via SQL. SELECT CASE INSTR ( cm_target_name, '_' ) WHEN 0 THEN cm_target_name ELSE SUBSTR ( cm_target_name, 0, INSTR ( cm_target_name, '_') - 1 ) END database_name, property_value FROM sysman.cm$em_tprops_ecm_view WHERE cm_target_type = 'oracle_database' AND ...


3

After a pretty good amount of digging, I found this blog post on doing this for date ranges, and it turns out to work equally well on numeric ranges, with a little modification for handling the 0 and 1 endpoints. Taking the query from there and renaming the columns to be a bit more clear, we have SELECT * FROM (SELECT MAX(T_END) OVER (ORDER BY T_START) ...


0

In MySQL You can try a function create function modul_id() returns INTEGER DETERMINISTIC NO SQL return @modul_id; create view v (your select... where modul_id = modul_id()...); select * from (select @modul_id:=14 modul_id) m, v; not tested on your query...


1

Based on your reply of SELECT ASCIISTR(titolo) FROM generale WHERE titolo LIKE 'Roma drogata%; TITOLO ---------------------------------------------------------------- Roma drogata: la polizia non pu\FFFD\FFFD intervenire You get Roma drogata: la polizia non pu?? intervenire because it has been ...


3

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row. What I would do is to define m to return a single row with the minimum and maximum values, and then ...


3

USER_INDEXES (and ALL_INDEXES) contains this information: select index_name, table_name, index_type from user_indexes where ityp_owner = 'CTXSYS' and ityp_name = 'CONTEXT'


0

The problem was that all oracle linked tables in msaccess database, have the link description of an odbc of the previous database version (DBA=W;APA=T;EXC=F;FEN=T;QTO=F;). One of this parameters (Enable LOBs) was diferent and causes the error. To solve the problem have to relink all the tables of de msaccess database. It's important to relink all, because ...


1

This couldn't have been easier Forms and Reports here (Fusion Middleware) and alternative (non-Fusion) here. You may need an app server - not sure. This was a relatively easy Google - that normally merits a -1, but since it's your first post here, I'll let it slide. Do they not have similar rules about question research on StackOverflow (where you have a ...


0

Continued from previous post. So now we know that DISTINCT and GROUP BY are handled indeed differently, lets go back to question 1. Knowing the above, going back to the original, slower query, this: select company_id from t1 join t2 using(product_id) join ( select company_id from (select distinct company_id from t1) ...


2

Output is from a 11.2.0.4.6 Enterprise Edition database on Oracle Linux 7.1 x86-64 platform. Lets start with question 2 and an easy example. DISTINCT and GROUP BY are handled differently: the optimizer is able to completely eliminate a DISTINCT under certain circumstances, but it can not do the same with GROUP BY. Here is an example: create table t4 as ...


0

Can we do this inside a Proc - DBMS_OUTPUT.PUT_LINE('Refreshing Materialized views'); -- drop index here BEGIN DBMS_SNAPSHOT.REFRESH('SOME VIEW'); END; -- create index here DBMS_OUTPUT.PUT_LINE('Views refreshed');


0

As @BalazsPapp mentioned, the oracle db can be connected locally even without a listener. In this case, the BEQ (Bequeath) protocol is used, instead of the tcp-based normal oracle communication. Although on Balazs's link there is nothing about the details of the BEQ. On unix, it probably uses unix domain sockets. On windows, it uses probably some local rpc. ...


0

Bind the listener to localhost only: LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))


1

The solution is really dependant on your environment. There are numerous options, from hardware firewalls, to software firewalls, to sqlnet.ora parameters. The first two are beyond the scope of this site. The quickest, probably, is sqlnet.ora. Have a look at the tcp.invited_nodes parameter: ...


0

Here you can find how to generate the password hash. (This algorithm worked both on version 11.2 and 12.1 when I tried with some simple examples.) The next challenge is - as already mentioned -, to try all possible combinations of lowercase and uppercase letters in a password, if you want to cover everything. This will not be a single query, but some PL/SQL ...


1

You can configure this behaviour through the Administration Assistant, where you can choose to start (or not to start) the database instance with the Windows service. Alternatively, you can do this with oradim, e.g: oradim -edit -sid ORCL -startmode manual The Windows service should be set to start automatically, and with the above setting, the database ...


1

I don't think this is possible for current versions of Oracle. What I understand happens is that Oracle generates an instance-specific salt and adds this to the passwords being hashed using the SHA1 algorithm, the result of which is stored as the hashed password. See for example http://marcel.vandewaters.nl/oracle/security/password-hashes for information ...


1

What if user has login "SCOTT" and password "ScoTt"? Do you still need to find this one? I think what you really need is to set password_verify_function in users profile. See http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm#i1007341 for more info.


3

Yes, but do not simply delete it - deinstall it properly: http://docs.oracle.com/database/121/LADBI/remove_oracle_sw.htm#LADBI7824 Run $ORACLE_HOME/deinstall/deinstall and follow the instructions.


0

Is FOO a DSN or a server name? on workbench you need the server name, mysql username and the mysql password. Thats all you need to connect to your mysql server using mysql workbench.


1

You can get Oracle 11 to run natively on OS X, but there is a bit of work required, and not as quick to set up as a pre-installed environment. However, this is a tried and tested method, so it will work, and it will run quicker and more responsively than if you opt for a virtual machine solution. Taken verbatim from Olivier Refalo's answer to Installing ...


0

SQL> select utl_raw.cast_to_varchar2('41424344') from dual; UTL_RAW.CAST_TO_VARCHAR2('41424344') ------------------------------------ ABCD


1

Oracle said it wasn't a known issue, and basically asked us to re-run with tracing on. Unfortunately on a large production database, and given large downtime required to rollback last time, this wasn't really an option. We had suspicion that it is related to this bug fixed in 11.2.0.4.2 Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ...


2

Proper backup syntax should have been: backup database tag "first" plus archivelog tag "first"; Now if you look into your list backup output you can notice that only backupsets with archivelogs has tag FIRST. Backupset containing backup of datafiles has autogenerated tag TAG20150515T105436.


1

No, the second query won't work. It will always return 0 rows if :MIN_ROW_TO_FETCH is greater than 1. Conceptually, if you have a query like select * /*+ FIRST_ROWS(n) */ from ( your_query_goes_here with filter and order by ) where ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH where :MIN_ROW_TO_FETCH is 11 and :MAX_ROW_TO_FETCH is 20, ...


0

First edit your /etc/oratab changing N to Y like that: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N Must be change to Y: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y Then to start you must use the dbstart script like that: export ORACLE_HOME=youroraclehome su oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" Where "oracle" in the "su" command ...


-1

A default installation of Oracle Database sets the default DATE format to DD-MON-YYYY. And you were using a different format in your sql query. Either you can use to_date function or simply change the date format model to DD-MON-YYYY. . Click here to see the oracle reference


-1

In Relational Algebra Projection means a vertical subset of a relation which contains the attribute values of given columns


0

After talking with some people with more Experience in Oracle than me, they told me I was right in my understanding. This query returns the references to an object (except the constraints) SELECT object_id, owner, object_name, object_type FROM sys.DBA_OBJECTS WHERE object_id IN (select object_id from ...


0

It is possible (but not simple). I just made the implementation of ORACLE to TimesTen real time replication. The solution is based on ORACLE LogMiner utility. Using this tool you can generate the ORACLE transactions in a real time and then ship them to the program (TimesTen side) which will read and apply those transactions to the TimesTen database. The ...


0

It is not due to any difference in Oracle's treatment of DISTINCT and GROUP BY. If we replace GROUP BY in the second query with a DISTINCT in SELECT we shall get similar performance/ plan cost as the first one I think, if we are using same clause in both the inner and outer queries with a natural join then Oracle parser is able to recognize that it will ...


0

See Oracle Database 11g: Real-Time SQL Monitoring Real-Time SQL Monitoring, introduced in Oracle Database 11g, provides a very effective way to identify run-time performance problems with resource intensive long-running and parallel SQL statements. Interactive Enterprise Manager screens display details of SQL execution using new, fine-grained SQL statistic ...


1

Install the Oracle Instant client - both the 32 and 64 bit versions find orasql12.dll and register it with regsvr32 orasql12.dll and c:\windows\syswow64\regsvr32.exe orasql12.dll for the 32 bit version. and add the oracle bin directory the the path environment variable Make sure that you can see the oracle driver in ODBC both 32bit and 64bit and under data ...



Top 50 recent answers are included