New answers tagged

0

I had the same issue and nothing of the above helped. In my case it was just an invalid domain (= no domain). Once I changed from localhost to localhost.localdomain (in /etc/hostname and sysconfig) everything worked fine.


-1

If you do not have space limitation, you can create a new table, the same as your table with your new column added to that table and delete the old table: create new_table as select old_table.*, (with or without default_Value) as new_column from old_table;


0

Just thought I'd do a quick test. Used UTL_FILE to export 4 columns (comma separated) from a table of approx 1.3Million records. This is the code; DECLARE fl_output UTL_FILE.FILE_TYPE; s_output_line VARCHAR2(2000); CURSOR lcur_test IS SELECT col_a||','||col_b||','||col_c||','||col_d AS line_out FROM insurance_policy; BEGIN fl_output := utl_file....


3

I have seen this only once so far, but there is an issue described in the My Oracle Suppport note: FIPS use causes ORA-01017: Invalid Username/password with Managed ODP.NET (Doc ID 1937500.1) I will not post the full content of this as it is not public, but here is the essence: This bug has been fixed in 12.1.0.2 Managed Provider. Please note that the ...


1

Checking Allow inprocess for the OraOLEDB.Oracle provider seems to have resolved the problem.


0

export ORACLE_SID=test export ORACLE_HOME=/u01/oracle/product/10.2.0/db_1/ vi $ORACLE_HOME/dbs/inittest.ora control_files = (/u01/testcontrol1.ctl,/u01/testcontrol2.ctl,/u01/testcontrol3.ctl) undo_management = AUTO undo_tablespace = UNDOTBS1 db_name = test db_block_size = 8192 sga_max_size = 440401920 sga_target = 440401920 $ORACLE_HOME/bin/orapwd file=...


0

Tablespaces with different block sizes than the database exists because of transportable tablespaces, where you want to move the tablespace to a different database that has a different block size than the database you are moving the tablespace from (usually from an OLTP database to a data warehouse). Although certain sites claim you can get a performance ...


0

Yes. First you need a parameter file (init.ora). The only mandatory parameter is db_name to have a fully working database. Steps 1 - Create the parameter file: [myhostname@oracle $] vi $ORACLE_HOME/dbs/initorcl.ora db_name='orcl' :wq 2 - Export ORACLE_SID to reflect the db_name you which to create: [myhostname@oracle $] export ORACLE_SID=orcl 3 - ...


1

Yes, databases can be created without using DBCA from command line. 1) you need to export required environment variables 2) Create a parameter file (init$sid.ora) under $ORACLE_HOME/dbs substitute $sid with your instance name. 3) Create a SQL script for database creation 4) Connect to sqlplus, put the instance in no mount state and run the script to ...


0

In simple words, Instance is created first. Because when you create database manually, you start the instance first in no mount state and run the script for creating database. Another point to support my answer, access to Oracle database or creation of it is impossible without an instance.


3

Introduction to the Oracle Database Instance A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database. Every running Oracle database is associated with at ...


0

Instance can not be created, it can "only" be started. Instance is something which is running. oradim is something Windows specific. It creates a record in Windows SERVICE registry, to start the instance during the boot. So in fact oradim does not create an instance, it creates a Windows service associated with the database (instance).


0

If you created the table with the above statement as user MDR_139, then that table belongs to the MDR_139 schema, not TESTSCHEMA. If you want to create tables in other schemas, add the schema specifier to the create statement, for example: create table TESTSCHEMA.sales_test (year number(4), product varchar2(10), amt ...


2

db_block_size is the default block size of the database, but it does not necessarily mean all your tablespaces have the same block size, you can have tablespaces with different block sizes. You need to define seperate cache areas for tablespaces with non-default block size. For example, if your db_block_size is 8192, and you want to create a tablespace with ...


0

SQL*Plus supports an additional format: DEFINE StartDate = TO_DATE('2016-06-21'); DEFINE EndDate = TO_DATE('2016-06-30'); SELECT * FROM MyTable WHERE DateField BETWEEN &StartDate and &EndDate; Note the ampersands where the substitutions are to be performed within the query.


0

"Or will ORACLE automatically set these values efficiently" Well, Oracle will set them to something by default ... probably not very "efficiently" though ;) For that, you're going to want to do a bit of research and groundwork on what YOU need. As mentioned by Wernfried in comments, LOCALLY managed tablespaces are the way to go, and once you have those ...


0

you can use SET LINESIZE 20000 TRIM ON TRIMSPOOL spool c:\tmp\output.txt or column XXX format A10 select XXX from tbl; -- will show XXX with 10 char length


0

I finally found a solution. I changed sys password using orapwd. There is just one issue. As documentation says, the password for users which do not have sysdba or sysoper privileges will be expired after some time. So, to avoid this login as sysdba and grant sysdba and sysoper to the sys.


2

It looks like your ACCEPT prompt includes quotation marks. But quotation marks are also used to delimit the prompt string, so if you want to include those characters into the prompt, you need to double them: … prompt 'Fecha de inicio (''DD/MM/YYYY-HH24:MI:SS''): ' … prompt 'Fecha de fin (''DD/MM/YYYY-HH24:MI:SS''):' Although I think you could simply ...


0

You need to move the dump-file to an area where the database has access to it referenced inside the database as a directory object. You can run impdp from you local computer.


0

To prevent that, just bypass the email and password option


0

The Oracle documentation says: A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values. This answers your question. It generates unique integers and can be used (and I think in over 99% of the cases) for primary values. This is a way to be used to ...


0

The same issue I have just came across on CentOS 7. The installer form is disabled and does not allow any input. But clearly it is waiting for some input. In my case it is a GUI glitch. I was not able to find and resize any dialog. However, pressing Enter was enough to return focus to the main window. Though I can't really say what I confirmed or cancelled. :...


0

What OGG version are you using? Instead of DBA role, have you tried granting the required privileges through dbms_goldengate_auth.grant_admin_privilege? "Requirements When Configuring Extract or Replicat in Integrated Mode (Oracle) If using DBLOGIN to issue ADD EXTRACT, ALTER EXTRACT, or REGISTER EXTRACT to initiate integrated capture or ADD REPLICAT, ...


1

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks. Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/...


0

The solution turned out to be to adjust the timezone on the DB server, clients and the entire cluster to UTC+1 (Europe/Oslo). This is the same as the cluster that has run for years without problems. However, I'm not completely sure why the "+1" does not cause the same problems as the "+3" in MSK.


-2

alter index "MYSRVR"."PK_ZXCATSET_CATID" rebuild tablespace MYSRVR_IDX_TB; or alter index MYSRVR.PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;


1

You use fund_acct_reg_view.fund_acct_no fund_acct, in SELECT but use pr_line_item.fund_acct_no in GROUP BY. Use fund_acct_no from the same table in both cases.


0

Oracle export tools use their own proprietary format that MySQL can not process. Just unload your data to CSV or insert statements with SQL Developer, then load the result in MySQL http://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer


3

WHERE PARENT_ID IN my_array; This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist. So first you define the type: CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE); And after that, you can use the ...


1

$ alias sqs='sqlplus / as sysdba' $ sqs SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 20:33:31 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application ...


1

One possible reason: Bug 18162779 : ORA-01428: ARGUMENT '000000000000000000' IS OUT OF RANGE Try running the query after setting the below: alter session set "_disable_function_based_index" = TRUE;


1

TO_DATE functions accepts the NLS language as an optional parameter .. have you tried pulling it in via SQL*Loader as a CHAR, then do a TO_DATE conversion on it inline? so your SQL*loader syntax might look like: ... my_date CHAR "TO_DATE(:my_date, 'dd-MON-RR', 'NLS_DATE_LANGUAGE=American')" ... See if that works any better ??


0

use command prompt to connect to Oracle Db schema using the below syntax: sqlplus user/password@hostname:portnumber/Servicename


4

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them): The order by has ties. There is not one "first row" but 3 of them. So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!


0

If you want to connect your java software to a Oracle DB, I recommend you to use JDBC API instead http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html You are welcome. PS- ask for help to your IT-team/DBAs


2

It should be a simple boolean. This should work and be more efficient. The SQL%ROWCOUNT attribute will be populated with the number of rows deleted. If you didn't delete any then just exit. FOR i IN 1..100 LOOP DELETE FROM mytable WHERE date <= (SYSDATE - 30) AND ROWNUM < 5000; EXIT WHEN sql%rowcount = 0; COMMIT; END LOOP; If you aren'...


3

You can try as below: SELECT name, LISTAGG(item_name, ' ') WITHIN GROUP (ORDER BY item_name) AS item_name FROM item GROUP BY name;


4

path to data files is included in full dump. No, SIDs don't have to match You may want to add REMAP_DATAFILE option (https://docs.oracle.com/database/121/SUTIL/GUID-7C9DDBED-91AC-4FA2-AB91-1FF214DA89B0.htm#SUTIL926 ) to impdp . Alternatively, you can just pre-create all tablespaces on target database.


-1

Just think of of this: The child table owner schema begins to create record in its table and unknowingly prevents the parent table schema user from deleting records from the parent table. Is it something it anticipates and appreciates?


0

I used Oracle datapump expdp/impdp. On the source database using expdp: SQL*PLUS: CREATE USER username IDENTIFIED BY pw; GRANT dba TO username; CREATE DIRECTORY db_copy AS ‘E:\DB_copy’; GRANT read, write ON DIRECTORY db_copy TO username; CMD: expdp username/pw DIRECTORY= db_copy DUMPFILE=filename_%u.dmp filesize=5gb FULL=Yes METRICS=Y EXCLUDE=STATISTICS ...


-1

Another solution: The .oui file is available in the part2of2/install directory. Just copy that file and paste it in part1of2/install and run the ./runInstaller. It worked for me.


0

Community Wiki answer containing the solution given by Justin Cave in a question comment execute immediate takes a string. The snippet you posted has no strings in it. You'd need to assemble the appropriate string and then pass that to execute immediate. Something like: execute immediate 'alter table ' || var_table_name || ' rename ' || var_col ||...


0

Add port listener 1521 in firewall: Inbound Rules and Outbound Rules. Add permissions in ORACLE_HOME in regedit: cmd → regedit → HKEY_LOCAL_MACHINE → SOFTWARE → ORACLE


-2

Yes, it is. Just create a Non-CDB (the old architecture) database, but keep in mind this architecture will be deprecated in the future.


0

I think this might be the kind of thing you need. WITH get_detail AS (SELECT get_addr(col1) AS obj_type from my_test) SELECT x.obj_type.district_name FROM get_detail x / This is what I created to test it out. CREATE OR REPLACE TYPE my_ADDRESS_DETAIL_T AS OBJECT ( apt_number NUMBER, district_name NVARCHAR2(40), zipcode NUMBER ) /...


0

i assume that you have installed oracle database 12c. if that's correct, you should can manage oracle database in website GUI. maybe you can open the url such as https://:5500/em which 5500 is the default port when you installed this oracle database. you can manage the database such as tablespace, users, and etc in there. i never installed it on windows to ...


0

You already have a database, so you don't need the 'create database'. Since you are working with a 12.1 database, you need to throw out your 10.2 docs and go to the 12.1 doc set. When you create a user, don't use that "@localhost' construct. Your simple 'create user foo identified by bar' is the correct syntax, but as @a1ex07 pointed out, you need to be ...


0

Rather than putting the data in a case statement where you might have to repeat that case statement in more than one query, you could create a table with static data, then joint to that table. The advantage would be that you can change the data in the table easier than changing all of the queries that have that case statement.


1

Just add a WHERE condition. And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should display in the event none of the conditions is met; a default value. So here is the code to your original question: SELECT 'RUNNING' AS A from message_log ...



Top 50 recent answers are included