Tag Info

New answers tagged

0

SQL*Developer is a decent tool for this, but can still take some time. If you're looking at SQL Plus, I'd recommend that you use spool, and make liberal use of SQL Plus's set command to format your output into flat files. For delimiting the flat files, you can make use of the concatenate feature to add in column headers. Here's an example: select ...


1

I suspect you're using multiple sessions and the other session cannot "see" the work performed because it is currently uncommitted. The default Oracle transaction Isolation Level is Read Committed. This means that a session will not be able to see the work a given session has done until it has explicitly committed the transaction. The Oracle Server ...


0

As always, It will depend on how much money are you able to spend. (may I say "invest"?) Although the feature of storing binary files into Oracle is included in all Database editions, storing binary files into the database will increase hardware and human resources costs since your db will become bigger and bigger, and it's very common in these cases that ...


0

As SYSDBA: alter system archive log all; then: alter system switch logfile; Before those two steps, you may also need to do a crosscheck archivelog all in rman so that it "knows" that the deleted archivelogs have been removed (depends on how they were removed).


0

Similar to Niraj Patel select regexp_substr('hello how are you doing', '[^ ]+', 1, level) || ' ' || regexp_substr('hello how are you doing', '[^ ]+', 1, level+1) val from dual connect by level <= length(regexp_replace('hello how are you doing','[^ ]+'));


1

It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes). Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y) then the data is more likely to be in a ...


1

Have you considered Oracle's BFILE ? Sounds like it's a perfect fit for you; http://www.orafaq.com/wiki/BFILE Here's some further reading material on the subject ; http://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl12bfl.htm


-2

There is no way round the restriction with truncate as other answers have indicated, but you can of course use delete without disabling the constraints: delete from parent; Note that 'delete' and 'truncate' have different side-effects


0

The problem with your case statement is the syntax is wrong. CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST ( PersonID NUMBER, TableName NVARCHAR2, BuyerOrSeller INTEGER, a_CURSOR OUT sys_refcursor ) AS BEGIN OPEN a_CURSOR FOR SELECT TBLDEALS.*,TableName.* FROM TBLDEALS inner JOIN Tablename on Tablename.ID = ...


0

If you are using Oracle 11g or onwords version then try this SELECT REGEXP_SUBSTR('hello how are you doing','[^ ]+',1,level) AS reg_result FROM DUAL connect by REGEXP_SUBSTR('hello how are you doing','[^ ]+',1,level) is not null ;


5

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies). This means however, you ...


0

As I understand the problem, you want to leave 1000 rows in a table that starts with > 1 million. In SQL Server, I ran I to problems with the delete statement locking the table, and also taking a long time to execute. All the deleted records have to go into the transaction log. What worked better was to select the records I wanted to keep into a new ...


0

Try this: DECLARE COUNTER INTEGER :=0; CANT INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('START'); loop -- keep looping COUNTER := COUNTER + 1; --do the delete 1000in each iteration Delete TEST where rownum <= 1000; -- exit the loop when there where no more 1000 reccods to delete. -- DBMS_OUTPUT.PUT_LINE('SQL%rowcount: '|| SQL%rowcount); --this ...


2

As per the comment, perhaps something like this: CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST ( PersonID NUMBER, TableName NVARCHAR2, BuyerOrSeller INTEGER, a_CURSOR OUT sys_refcursor ) AS v_stmt_str VARCHAR2(200); BEGIN if BuyerOrSeller = 1 then v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ...


1

You're unfortunately hitting bug 12999577 (Oracle support account required) or something close to it. And the fix for that bug introduced another bug 17564992. The workaround indicated in the first bug report is to switch the conditions around, i.e. express your where clause as 0 < ( ... ). The workaround indicated in the second is to set either of ...


0

Beware the OS authentication is done by client machine, not by the database server. I think that within reasonable pessimism to Oracle tools you could expect that it boils down to this: database server receives a TCP connection from whichever IP address that might pass the network path, and that connection just claims "I promise I've done OS authentication ...


1

I completely agree with dartonw's comments about reading through the guide. That's the only real way to start understanding the concept of indexing as it relates to Oracle and the Optimizer. One thing I will add to it though is this - Philosophically, as a DBA, I rarely decide what columns to index on my own. What I mean by this is that I go back to the ...


0

It depends on the VM software you are using. In case of VirtualBox you have to check what type of connection you set (NAT, bridge, etc); if using NAT you need to enable a port-forward to the listener port: e.g. TCP 21521 on host, TCP 1521 on VM). Also check if iptables firewall is enabled on your VM.


0

So to answer your question. "So will the command retrieve it normally or give an error?" Yes it will work and won't return an error. @update You forgot your ; after the query.


0

Multiple instances against single (same) database is required in such environment where that database is distributed among different systems (either on LAN or WAN) forming cluster so users can access the data transparently. It is used for high availability of data, providing more security to the users (as each instance may not be shared with other user at ...


1

$ORACLE_HOME/network/admin is the directory wwhere tnsnames.ora file is created by DBCA and/or NetCA. An OracleHome path like /u01/app ... is an OFA compliant path that the installler uses as default if not overriden by installer.


0

Run the query in any quarter and if you want to get all the records in that quarter(in that year) based on CREATION_DATE in tables : select * from emp_table where CEIL( (EXTRACT(month from creation_date))/3) = CEIL( (EXTRACT(month from sysdate))/3) and EXTRACT(year from creation_date) = EXTRACT(year from sysdate);


1

The oracle-rdbms-server-12cR1-preinstall package includes a bash script that creates the oracle user, if not already existing, using the useradd command. In this case in log file /var/log/oracle-rdbms-server-12cR1-preinstall/results/orakernel.log you should see this entry: Please set password for oracle user You can run the following command as root ...


0

It took me about two hours to install this instant client. Then I figured out that there is a great graphical client available for Oracle DB, which would suit my needs better then oracle instant client. In case you are interested, here is info on how to install it on Ubuntu: http://askubuntu.com/questions/458554/how-to-install-sql-developer-on-ubuntu-14-04


0

If your "full cold backup" is really a full backup of all database files while the database is not running, you only have to copy them back to the original place. The files needed are the control files, online redo logs, init.ora or spfile, database files, password file and so on. Some of them are located in $ORACLE_HOME/dbs, some are in other locations ...


4

There is only one REMAP_TABLESPACE parameter in de command. It would be seperated with ",". REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT,OLD_SCHEMA_IDX:NEW_SCHEMA_IDX Oracle doc impdp 11.2 http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929


0

The issue got resolved. The error was caused by a combination of a misconfigured firewall and the Oracle client. The machine originally had the IP B. Then the addresses A & C were assigned to it. Now the Oracle client used the address A for its connections to the database server. However, those connections were not (communicated to the Ops and ...


0

Another approach would be to create UNION ALL view: select 'REDACTED' name, employeeclass, confidentiality_ind from students where (CONFIDENTIALITY_IND = 'Y' or EmployeeClass = 'XA') and not <privilege check> union all select name, employeeclass, confidentiality_ind from students where not (CONFIDENTIALITY_IND = 'Y' or EmployeeClass = 'XA') or ...


0

In Oracle 11.2.0.3, Virtual Private Database allows you to hide rows or columns. For example: create or replace function check_confidentiality( p_schema in varchar2, p_object in varchar2 ) return varchar2 as begin return 'not (CONFIDENTIALITY_IND = ''Y'' or EmployeeClass = ''XA'')'; end; / call dbms_rls.add_policy( object_schema => 'schema', ...


1

Your question amounts to, "How do I use indexes?" You should read some Oracle documentation to get up to speed on how to use indexes and how to determine what columns to index. Oracle has an entire library of free online books and documentation for their products. A few good links to start: The chapter on Indexes and Index-organized Tables from Oracle ...


1

The alert log never has been under ORACLE_HOME/log. It is in $ORACLE_BASE/diag/rdbms/<sid>/<sid>/trace and is named alert_<sid>.log If you don't have $ORACLE_BASE, this typically is equivalent to $ORACLE_HOME/../../.. This is no different in 12c than it was in 11g (or 10g if I recall correctly). You can also use the ADRCI tool: ...


0

what you can do, is to write two stored procedures with the following logic: Build a stored procedure which does the case thing. This may be optional but the resulting sql would look better and easier to debug/develop. Define a stored procedure which has as input the tablename Read out the columns of the table - maybe here can be done some ...


0

Late to the party here but I just ran into this and at least in my case I am fairly certain it is an oracle bug, possibly with compatibility between client versions. I was getting this error connecting to an 11g database using the 12g sql*plus client while another person was able to successfully log in and change the password using the 11g sql*plus client. ...


0

connect to the database machine and check if the firewall is disabled,if not diasble the firewall using the below command as root user service iptables status(used to check the firewall status) service iptables stop(used to disable the firewall)


1

I can't personally think of anything better for on-the-fly resource throttling based on to-the-second stats than Resource Manager. It can be a bit of a hassle to set up, but I don't imagine how writing custom code wouldn't just be reinventing the wheel that is Resource Manager. I'm not aware of any reliable 3rd party vendors at this time. I do know that ...


0

Without specific setup, Oracle Database will not know anything about any domain type users you set up. In fact, that's kind of the point of having OS Authentication in the first place; the OS does the authentication, and the Database assumes that this Authentication is legitimate. If you're using OS authentication for users, you (or someone else in your IT ...


1

It looks to me like the string you're passing in is too long for the UTL_RAW procedure you're trying to call. From the Oracle documentation: There is an error if the sum of the lengths of the inputs exceeds the maximum allowable length for a RAW, which is 32767 bytes. found here: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_raw.htm#ARPLS072 ...


0

Aha! I've found the answer. Talk about an edge case. First, I found this post from 2007, where someone says: ...the AFTER trigger for synchronizing the CTXCAT index on [column] is not firing (since my update statement does not include the indexed column). ... Oracle, would it not be better to generate the CTXCAT trigger to examine the ...


0

This is not possible without using PL/SQL or a loop to generate the SQL in the program you're using to execute the SQL.


0

Below SQL will work. Insert into My_Table (FILE_NAME, FILE_DATA) Values ('REGISTER.XML', ''||CHR(10)|| ''||CHR(10)|| ' Publisher_1'||CHR(10)|| ''); Here FILE_DATA is a CLOB column which contains XML content. Thanks, Kalyan


0

If you're using a current releases, then it will always have default services. Check dba_services. Unless there's an application constraint, you should connect with SERVICE_NAME.


0

I would suggest using basic auditing. Parameter audit_trail can be set to DB, and you can audit system privileges, including CREATE SESSION. Auditing CREATE SESSION captures (among others), database and O/S user IDs. Views of interest are sys.aud$ and dba_audit_session. You may not need to add a logon trigger if the information you want is already ...


1

I recommend you use a SMALLINT column with a default value required (1=true, 0=false, default value = 0)


5

I usually use a number(1) type combined with a check constraint: some_flag number(1) not null check (some_flag in (1,0)) To make things crystal clear I also add a comment to that table: comment on column some_table.some_flag is '0 is false, 1 is true'; so that the explanation on what "true" means can be seen when looking at the definition of the ...


0

Look up PIVOT command in Oracle SQL reference manual, it is what you need to solve this problem. Over a million records shouldn't be a problem. Since you want day list as static, you can "pivot max(hours) as hrs for to_char(day,'DY') in ('SUN','MON'))" etc. Once you have the query, adjust for column heading appropriately, and generate a totals column. If ...


1

Wouter got, hopefully, useful answers here: https://community.oracle.com/message/12608939 That said, being the Oracle ACE Director for this technology. Oracle XMLDB functionality is nowadays so interwoven with the Oracle database that this is an MANDATORY (install) functionality for Oracle 12c and onwards. It is the basis for much in the database like ACL ...


0

...but then again... XML has nothing to do with SQL, so some normal reasoning might not apply...(regarding object types for example). The first example is an XMLType and depending on your database version the underlying store definition might be Securefile CLOB or Securefile Binary XML. The first one is deprecated from Oracle 12c and onwards due to ...


0

XMLType CLOB is deprecated from Oracle 12c and onwards. Regarding compression, AFAIK, currently this won't add to your performance speed. XMLType Securefile Binary XML and/or XMLType Object Relational storage are the best way to go (XML schema or schemaless for Binary XML, XML schema based for XMLtype OR). To get started have a look at the whitepapers at ...


0

Here's an option I put together that both gets the job done and avoids ugly boilerplate code: with vars as ( select 1000 v_min, 7 v_mgrID from dual ) select * from employees where salary > (select v_min from vars) and mgrID = (select v_mgrID from vars) This may not be the most performant code, but it's easy to ...



Top 50 recent answers are included