Tag Info

New answers tagged

0

You can manually create the init.ora file. Only 2 parameters are required - db_name and db_block_size. Then run: startup pfile='c:\program\11.2.0\dbhome_1\dbs\init.ora' If you have a valid spfile then run: create pfile from spfile You should be able to startup your database.


2

Solved this with help from http://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it The resulting answer with the original table looks like this: with qtys as ( select 3 as qty from dual union all select 10 as qty from dual union all select 9 as qty from dual union all select 7 as ...


1

I don't know a 'legal' way to find out the tables visible in the ALL_TABLES view of another user, so I do some reengineering of the SYS.ALL_TABLES dictionary view and found the following expression in the where clause: and (o.owner# = userenv('SCHEMAID') or o.obj# in (select oa.obj# from sys.objauth$ oa where grantee# in ...


1

Try this... select owner,table_name,grantable from dba_tab_privs where grantee = 'SCOTT' and privilege = 'SELECT'; Change SCOTT to the user you are interested in.


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


0

Generate a calendar table (that's googleable) and join your other tables to it. This makes it easy to do overlaps, etc. in an efficient way. In case it is not clear this is the standard way. I haven't gone into example usage because it is too involved for an answer here. Search for "calendar table" will give lots of examples and a full explanation to ...


0

In Windows OS's in the run, type services.msc and Enter,from the list of services find OracleService<SID name> and right click and choose Properties. In General tab in the Startup Type section choose Manual and click OK and exit. From now on your database doesn't start automatically by your machine's start, but whenever you want to start it you have to ...


0

I've had the same issue you are facing. solution was: mkdir -p /u01/app/oraInventory chown -R oracle:oinstall /u01/app/oraInventory chmod -R 775 /u01/app/oraInventory I hope it works for you.


1

Are you using Enterprise Edition? It looks like this is a EE feature: http://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm CTRL + F the docs and search for result cache and supported editions.


1

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column. The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query. Test at SQLfiddle. ...


1

You can use a hierarchical query to generate a sequence of dates, something like this: insert into my_table (my_date_column) select dt + (level - 1) from (select date '<your start date>' dt from dual) connect by dt + (level - 1) <= date '<your end date plus 1 day>'; Date arithmetics: dt + (level - 1) -- are smart enough to know when one ...


1

The lsnrctl utility sends commands to the listener process. If one sends the stop command to a listener process, the process shuts down. The listener program does not kill the process, but the process shuts down. If a unix process shuts down its process id will be removed from the process list. All fgiles locked or opened by the process will be closed by the ...


1

The procedure is described in the Remote Startup of a Database Instance part of admin guide for BS2000/OSD (I can't find this part in the Linux docs...). Essentially what you do is: Create an Oracle password file for your database if you haven't already done so Add a static registration for your instance in its listener Set the instance parameter ...


0

The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.


0

What is the output of: select * from v$database_block_corruption? Unlike regular indexes, LOB indexes can't be dropped/ recreated, because you would have to drop the column instead. So, assuming your backups are valid, you can attempt to perform block media recovery (BMR): http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmblock.htm


1

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is ...


0

Check this out: [https://docs.oracle.com/cd/E11882_01/install.112/e47798/reqs.htm#NTDBI002][1] [http://docs.oracle.com/cd/B28359_01/install.111/b32006/reqs.htm][2] Probably you will get answers for such questions quickly if you google them.


3

This is in fact a bug: Materialized View Log (Mlog$) Index Not Created in its Default Tablespace (Doc ID 1959658.1). Excerpt: The index created when a materialized view log is created appears to be created in the user's default tablespace rather than the same tablespace as the MV log table. Additionally, if the user has no quota on their default ...


0

You need elevated privileges to view DBA_USERS - that's if you can't select from DBA_USERS. Get help from a DBA or use an account with the required privileges.


3

In the simplest case, when you restore a database, you tell RMAN to recover it to the most recent point in time by applying all the redo (archived and online) that is available. That will re-apply any statements that were executed during normal database operations including things like dropping tables and deleting data. If you want to restore a database to ...


0

Well, I have finally figured it out. I clicked on Opened Help Menu (?) Troubleshooting Information Refresh Firefox This would reset the default firefox configuration. You will loose all your add-ons. Beware! Next, enter following in browser URL: about:config Change the following settings security.tls.version.fallback-limit=1 Restart firefox. ...


0

Yes, you can do that by running the runinstaller passing the silent argument. Also, it is possible to save and use response files, which basically contain standard configurations and parameters for the software installation. It's very useful if you will need to perform installations following a specific standard on a frequent basis. Here's the docs: ...


1

You link to a FAQ that says The Instant Client libraries occupy a virtual address space that is equal to the size of the files. However, only frequently used error messages from the libraries occupy physical memory. Under most cases, the physical memory load is a few kilobytes despite the larger reserved virtual address space This isn't saying ...


0

What is the business need, exactly? There are two distinct Oracle products and concepts. RAC/ Clustering means only that you are merging two or more computers and turning them into one powerful, and with lots of capabilities, including failover, load balancing. But if you're really talking about disaster, let's say Data Center 1 went on fire, Active ...


2

The Instant Client is easier to install and more lightweight. It doesn't even need to be installed, you can just unzip it and use it. You don't need an Oracle Home either. On the other hand, it does not support deprecated features like Oracle Names. If you don't need any feature the instant client omits, I suggest you use it. It looks like you are covered ...


0

use the installer in commandline mode and use the option to ignore prerequisite checks


4

Assuming that your database character set is AL32UTF8 (if there is a difference between the two, this is almost certainly your character set) and that all the data is characters that exist in the US7ASCII character set ("english" might constitute more than that depending on the definitions we're using) there won't be any noticable performance differences ...


-2

There's a page describing step by step how to set up OpenLDAP to resolve TNS aliases.


0

http://docs.oracle.com/cd/E19531-01/817-2514-10/GlossaryContext11.html indicates that setting CatalogOptions will cause the column remarks to be populated when executing SQLTables or SQLColumns.


0

Use JPA and make sure you avoid any DB secific SQL wherever possible. This does not provide 100% compatibility accross all DB types, but gets you close. Depending on your needs you are left with some lines of DB specific code. After all you have to do integration testing against all supported DBs to make sure your application really works.


1

As said earlier you will have to move all the 200+ tables in that tablespace to free up some space in your datafile and then resize to reclaim the space. But instead of running all those queries, 12c Enterprise manager does this task. You will have to navigate to Database Home > Storage > Tablespace. Select the tablespace you want to work on and click ...


0

Why exactly do you want to track down errors regarding ORA-01461? ORA-01461 is known to be related to various bugs, So depending on the problem you're facing, I would first raise a SR with Oracle. If you have MOSC access, take a look at Doc ID 456462.1.


0

1 - Make sure Recovery Catalog DB is created either on a different server or a different disk (because in case of failure of target DB and lost of access to its files, if your recovery catalog db stills up and running, you'll have access to control file and probably be able to restore and recover failed instances, given all backups/ archivelogs are ...


0

Please provide further details about what happened previously. Did a hardware failure occur? Also, refer to the .trc files that generally follows ORA-00600 errors in alert.log. If you're on linux and made a default installation, alert log will probably be located at $ORACLE_BASE/diag/rdbms/sid/sid/trace. It contains the main DB events, and it's the first ...


0

Split above current maximum value, drop the empty MAXVALUE partition, then convert to interval partitioning: alter table parttest4 split partition p_max at (2030) into (partition p_2030, partition p_max) update global indexes; alter table parttest4 drop partition p_max; alter table parttest4 set interval (10);


1

Above setting doesn't necessarily mean tracing, sometimes just enabling/disabling features if that is implemented. As far as I know, 1461 is not a diagnostic event, they are mostly in the 10000-11000 or higher regions, so I wouldn't expect anything from the above. If you want trace files to be created on specific errors, enable the ERRORSTACK trace: alter ...


3

Assuming there are no LOB fields or other specialties: select sum(blocks) as blocks, count(*) as extents from dba_extents where owner = '&table_owner' and segment_name = '&table_name';


1

I don't think that tkprof is the problem. It is a tool that summarizes information from trace file. Tracing is the problem. If you enable tracing of a process, this may slow down the process considerable. Besides that the trace files may fill up your disks. Finally you should be aware that security relevant information is written to trace files.


2

You should use SYSTIMESTAMP instead of SYSDATE, in order to get fractional seconds (which you desire as I assume) SYSTIMESTAMP returns data type TIMESTAMP WITH TIME ZONE, thus you don't have to specify explicitly. So it is SELECT TO_CHAR(SYSTIMESTAMP AT ZIME ZONE 'UTC', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') from dual


0

We don't charge for SQL Developer. If you want official support, you need a database licensed and covered by My Oracle Support. It's more complicated than this, but it's effectively free.


1

SQL> alter session set nls_timestamp_format = 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'; Session altered. SQL> select cast (sysdate as timestamp) from dual; CAST(SYSDATEASTIMESTAMP) --------------------------------------------------------------------------- 2015-07-18T01:26:09.000Z


0

Oracle Wallet is not available with Oracle XE per se but can be deployed with any Oracle version. That is to say, I can create a wallet on a licensed version of Oracle only. But a wallet can be deployed free of charge on any version. There is also the option of using openSSL. After getting a certificate, run the following to create your wallet: openssl ...


2

SQL Developer the client application is free of charge. Of course, the Oracle database would need to be licensed in a way that allowed you to connect with any tool. PL/SQL Developer is a completely different tool produced by a different company that you would need to license. Define "secure". Assuming a default database install, using any tool to connect ...


0

http://www.oracle.com/technetwork/developer-tools/sql-developer/pricing-faq-085267.html Is Oracle SQL Developer free? Yes. PL/SQL Developer is another product, that is not free. Other tools, even applications or application servers connect the database remotely. So yes, it is.


0

Unquoted identifiers in SQL are implicitly converted to upper case, so the statement create view remotetabletest creates the view named 'REMOTETABLETEST', not 'remotetabletest'. In other words, the following should return the expected result: select * from user_tab_privs where TABLE_NAME = 'REMOTETABLETEST'


0

I don't have a database on hand to test this but can't you do something like this: and SPC.CHARACTERISTIC in ('ARLFT','MINE') and SPC.CHARACTERISTIC in ('&industry') With both of those predicates present you will default to both values if you enter '%' but be restricted to just one if you enter either 'MINE' or 'ARLFT'


0

I found a doc in oracle that explains how he manage his storage with LOBs, how storage works in oracle


0

I know it is too late for the OP but here some advice to tackle such a problem: The error message says that the clients sends but does not get a response. Most of the time it is a firewall problem. The windows getaddrinfo function is described here. Error number 11001 is WSAHOST_NOT_FOUND and is described here: Host not found. No such host is ...


2

Looks like my short answer was not to everyone's taste, so let me show you the long answer First of all, let's create our lab. @WestFarmer mentioned a couple of tables SQL>create table A ( 2 some_column number, 3 another_column number 4 ); Table created. SQL>create table B ( 2 id number 3 ); Table created. Also a mentoned ...



Top 50 recent answers are included