Tag Info

New answers tagged

0

You mention you have tried SQL*Plus and Toad; what is your client operating system, Windows? Unix? See NLS_OS_CHARSET Environment Variable that is an environment variable set on your client to a value that your client understands and supports. If you are on Unix, invoke the locale command and see what LANG or LC_ALL is set to; you may need to set ...


1

The chinese characters are decoded correctly with utl_i18n.unescape_reference. In fact they just aren't displayed properly in your query result which might not support those special characters. You can confirm that with this SQL Fiddle. It is the client which is respsonsible to display the characters appropriately. If the client can not display a ...


0

Redo Log Buffers: A log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in the redo entries. Redo entries contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations. Redo entries are primarily ...


0

It is possible to check if an index using a number of columns exists. WITH index_cols AS ( SELECT table_name, index_name, COLLECT(column_name) AS column_names FROM all_ind_columns WHERE table_name = '&table_name' GROUP BY table_name, index_name ), ...


0

I fixed the issue by going into Configure TOAD Options --> Schema Browser --> Data and Grids and there I unchecked Enable FK Lookup


0

The message indicates that no archivelog exists with this sequence in the current incarnation. Failover starts a new incarnation, and log sequences start from 1 again. Let's say your database is at sequence 10 now, and the log sequence was 159 before the failover. V$ARCHIVED_LOG at this point still contains entries about previous incarnation, so the above ...


0

How much real memory free does Windows task manager show, before you start up Oracle? If less than 2.5 GB, that's the problem. You can try increasing memory_max_target slowly from its current value, say in 256 MB increments, until it errors again, then I'd back it off by maybe 512 MB to give some headroom.


2

To answer some of your questions, firstly read this: http://www.oracle.com/technetwork/indexes/downloads/index.html and this http://www.oracle.com/technetwork/licenses/standard-license-152015.html. These are your licencing restrictions. All the manuals are available here: http://docs.oracle.com/cd/E11882_01/index.htm. Read the installation guide, ...


0

Use this instead: SELECT X, Y, Z FROM A MINUS SELECT X, Y, Z FROM B UNION ALL SELECT X, Y, Z FROM B MINUS SELECT X, Y, Z FROM A; If this does not output anything - then you know that those three columns are the same in both tables.


1

1) Audits are performed on actions you define. For example: audit all by testing by access will audit everything that the user testing does. 2) I think you should look into XML auditing. Source Example: Set up your database by issuing alter system set audit_trail='XML, EXTENDED'; This modification does require a DB restart. After you set that you'll notice ...


3

Maybe those files are not cataloged by RMAN? I would CATALOG them and then DELETE OBSOLETE once again.


2

Most of your questions are covered in official documentation. http://docs.oracle.com/cd/E11882_01/nav/portal_11.htm Read the installation guide (NOT the "quick installation" one). I don't recommend using "pre-built" virtual machines - try to install OS and db from scratch. The process is the same as installing on "real" hardware. Use CentOS as mentioned in ...


1

You might have wanted this: WITH XML_DATA AS (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?> <response> <phone> <number>859111</number> <contactModes> <mode>AUTO_DIAL</mode> <mode>AUTO_TEXT</mode> <mode>MANUAL_DIAL</mode> ...


1

You should be able to do that with an RMAN backup. You need to see what kind of backup Oracle XE supports. But you should be able to shutdown the database, startup mount, then do an RMAN backup making sure to tell Oracle where to send the backup, make sure that you include the control file with the backup. Then copy the backup over to the 64 bit server and ...


0

Is possible that for the 10g client you either have a the date format set in a local login.sql file or set in the $ORACLE_HOME/sqlplus/admin/glogin.sql. If you can find this date formatting, then you should set it the same for your 11g client and it should work. Essentially, when you put in a date string Oracle look at the nls_date_format to see how a date ...


0

I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts. First, my original question can be solved by using XMLTable: SELECT x.ID AS "MEASUREMENT_ID", x.GUID AS "MEASUREMENT_GUID", a.COLUMN_VALUE AS "MESSAGE" FROM SPERF_XML_RAW x, ...


0

I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL: SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, ...


1

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons: No need for disk/memory/maintenance of the mySQL database If the updates to the various databases happen every few seconds, and the users may only ...


1

It's the other way around. Active Data Guard is included if you license GoldenGate on Oracle Database Enterprise Edition. The GoldenGate product page on Oracle Technology Network states: The license for Oracle GoldenGate includes a full use license for Oracle Active Data Guard, and a full use license for XStream in the Oracle Database.


1

The unique names of the databases in a Data Guard configuration are listed in the log_archive_config parameter in the following form: log_archive_config='DG_CONFIG=(PDB10,PDB10_STBY)' You can remove the old one and add the new one as: alter system set log_archive_config='DG_CONFIG=(PDB10,PDB10SBY)'; After this, you can set log_archive_dest_N to the new ...


0

Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause. SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, CheckViewHrsErn.CHECK_DT, SUM(CheckViewHrsErn.EARNINGS) FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn LEFT JOIN PS_JOB JobDta ON ...


0

I believe restarting the applicable windows service will bounce the database. net stop oracleservicexe net start oracleservicexe Alternatively you can connect as sys and provide the password instead of using internal authentication, then bounce the database from there. >sqlplus /nolog SQL>connect sys as sysdba Password: You may also want to check ...


1

You should be able to backup an 11.2.0.2 database using RMAN with an RMAN repository that is set to 11.2.0.4. There seems to be some invalid objects in your RMAN repository schema. You may want to try compiling all of the invalid objects in your RMAN repository to see if they become valid. This probably has nothing to do with you backing up an 11.2.0.2 ...


5

I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of: You admitted that you are working with large record counts in your tables. What ...


0

Virtual column partitioning: create table table_name ( uri varchar2(20), part as ( case when uri like 'model://%' then 'mod' when uri like 'list://%' then 'list' else 'def' end ) ) partition by list (part) ( partition part_mod values ('mod'), partition part_list values ('list'), partition part_def ...


1

Be happy that your users seem to be going to use SQL to do the analysis, instead of pumping to excel. Make sure you have separated tasks and don't give privs to update the source data. Resource manager is your friend to prevent runaway queries. give them resources that they need, make sure other users don't get pushed out.


1

I think there is no problem, but you need to take care about data. For example, you can grant them privileges to create tables and procedures for their needs in theirs schemas, and privileges to select data from another schemas, but not to grant privileges on insert/update/delete. If they don't have privileges on update and delete data in main tables, ...


0

What version are you running? I'm not able to reproduce your results on 11.2.0.4. Here's my schema: 16:36:09 system@DB> select owner, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'USER' group by owner; OWNER GB ------------------------------ ---------- USER 1156.41901 Here's ...


0

Maybe you need something like this? create or replace procedure p1 is begin select ... into ... from ... where ...; exception when no_data_found then ... raise_application_error(-20100, 'Something not found'); when others then ... end; create or replace procedure p2 is something_not_found exception; pragma ...


0

This might be over simplifying it, but could you use an IF *some variable* IS NOT NULL block around the call to the extra procedure? As in, "check to see if the variable I just wrote to is null, if not, do more work, if it is, specifically call my custom error package? If you've built your own exception handler (and kudos to you for not falling into the ...


1

There are a variety of approaches you could take. You should pick the one you feel is most suitable for your application. Include no WHEN OTHERS exception handlers and let the raw Oracle errors propagate to your users. Include WHEN OTHERS exception handlers in the top-level blocks. Include WHEN OTHERS exception handlers in all/most blocks The first ...


-1

while installation it creates the log files in temp . location. so make sure oracle user has permissions on /tmp location. may be this doc helps you during upgrade.follow the same procedure http://orcl11gdba.blogspot.in/2014/03/upgrading-database-from-112010-to.html


0

This amazing video explains how to solve the error step by step, If you dont want to watch then follow the text below 1) "Command not found" indicates oracle instance is not running or you have not running the dbca command as oracle user sol: follow @Frank Schmitt answer 2)The No protocol specified error indicates that "the user dont know how to launch ...


1

ORDER BY (case when `cat` = 'mgr' then 1 when `cat` = 'dev' then 2 else 3 end) This structure help me in Wordpress. For example to select custom posts first theh other: poster and project is custom SELECT post_type FROM wp_posts ORDER BY (case when post_type = 'poster' then 1 when post_type = 'project' then 2 else 3 end)


0

Use the parameter NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name. For example, if the default domain is set to us.acme.com, the connect string CONNECT scott/tiger@sales ...


1

@mbonaci sqlplus / as sysdba then issue command startup; to start the database Also on Linux with this error, you can check first if Oracle background processes are up with ps aux | grep pmon. Pmon is process monitor, or check some other background process. If they are not running then the database is not running.


0

My Oracle Support Doc ID [456320.1] indicates that the IDENTIFIED BY VALUES clause is only intended to be used by internal utilities (export/import) when used in a database link definition, which may be why the link is not working. Database link passwords are stored in a separate encrypted format starting in Oracle 10.2. The only valid value to be ...


0

Use datapump if you want platform independency (windows to linux and vice versa) exdp imdp I can send you my classnotes if you want. Today's lesson was about datapump lol.


1

In your example, you have Autoextend on with 4M as the next extension, but you also have MAXSIZE set to 8G, which means that Oracle is being told that once a tablespace reaches 8G, it will no longer automatically add space. This is for safety purposes, as sometimes administrators want to know when a datafile is growing very large very quickly. In your ...


1

select sum(case when issue_date = return_date then 1 else 0 end) SAME_DAY, sum(case when return_date is null then 1 else 0 end) NOT_RET, sum(case when issue_date <> return_date then 1 else 0 end) SEP_DAY from books;


1

It's your regexp_like expression that is invalid. You're using your column as if it was a function name. The syntax is: regexp_like(thing_to_check, pattern [, options]) You should write: regexp_like(species_imagefile, '[A-Z0-9._%-]+.JPG', 'i') 'i' is for case-insensitive matching.


1

ALTER SESSION SET CURRENT_SCHEMA will work, but you still need to do all of the grants. – Phil Oct 29 at 23:33


0

Try something like CREATE TABLE test_stmp(StartDate TIMESTAMP NOT NULL, constraint chk_test_filed check(trunc(StartDate) = to_date('2000-01-01','yyyy-mm-dd')));


0

I'd expect you to be able to set the SSL_VERSIONS to 3.1 (TLSv1) and successfully avoid the POODLE vulnerability. https://docs.oracle.com/cd/B19306_01/network.102/b14268/asossl.htm#i1023429


1

It is quite simple: select AIN, max(ACCEPT_TYPE) keep (dense_rank first order by date1) ACCEPT_TYPE_1, max(ACCEPT_TYPE) keep (dense_rank first order by date1 desc) ACCEPT_TYPE_2, min(DATE1) date_1, max(DATE1) date_2 from my_table group by AIN


0

ORA-600 errors are usually bad news. ORA-600 on OraFAQ. If this is an environment you or anyone else cares about I recommend: 1) Opening an Oracle service request 2) Find your organizations DBA and engage them immediately 3) Dig those backups out because you may have a significant corruption of RDBMS binaries or data. Best of luck!


0

The archive log backups are failing hence the archive logs are getting accumulated on the mount points making the DB to get into hung state. Increase the size of the db_recovery_file_dest_size parameter and open the database and check. It should work. alter database mount; alter system set db_recovery_file_dest_size=<>G scope=both; alter database ...


0

You should post the last 1,000 lines of your alert log file. It would also help for you to give some background on what was changed before this issue came up. Did you upgrade recently, apply a patch, change a system variable? You may end up needing to create an SR with Oracle to have them help you resolve the issue. But it is not clear from the ORA-00600 ...


0

If you are creating comma delimited files, you should probably use double quote and comma delimited. Excel can read the files as is, and you are less likely to have the data in the database mess up the format. I have also used the tilde ~ as a delimiter. It doesn't get used very often so it tends to be safe to use.


1

The statement terminator in SQL Server is a semi-colon ;. http://msdn.microsoft.com/en-us/library/ms177563.aspx As queries cannot contain a semi-colon, except as the last character to identify the end of the query, this should be enough for you to separate out each statement.



Top 50 recent answers are included