Tag Info

New answers tagged

0

Do it with: update table1 set col1_temp = to_char(col1, '99.99')||'%'; Also be careful. Your col1 can hold bigger values than 99.99. These values will give ###### as a result and also the value will be rounded on 2 decimals by the to_char command.


3

% is not a valid format specifier for numbers. See the documentation below: Number Format Models


1

It sounds like you just want a CREATE TABLE AS SELECT CREATE TABLE your_new_table_name AS SELECT * FROM your_materialized_view If there are indexes on your materialized view that you want to create on the table, you'd need to create those separately.


2

Export the PATH variable. You have missed that. PATH=$ORACLE_HOME/bin:$PATH export PATH


0

In order to grant the CREATE VIEW privilege, your user needs the CREATE VIEW privilege WITH ADMIN OPTION, or the GRANT ANY PRIVILEGE system privilege. Otherwise you have insufficient privileges. http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#i2094944 To grant a system privilege, one of the following conditions must be met: ...


0

You can only tnsping to a database that is defined in your tnsnames.ora or OID. If I understand well then FIHOST is a server. To see if that is up you must use a common ping.


1

NOAFFIRM means the redo transport will be acknowledged before writing the received redo to disk (standby redo logs). ASYNC noaffirm means the log transfer method is ARCH right? No, it does not. Without specifying LGWR or ARCH, LGWR is the default. You can check it on the primary database by: select archiver from v$archive_dest where dest_id = 5; ...


2

Just start the database normally and run DBMS_DST.UPGRADE_DATABASE Then you must see the messages "An upgrade window has been successfully ended." and "Failures:0" Double check that it succeeded by running: 1: SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY ...


2

While copying a tablespace, it automatically includes data files and indexes associated with it. You need to make sure that tablespace is self contained. Confirm that tablespace is self contained execute DBMS_TTS.TRANSPORT_SET_CHECK('<TABLESPACENAME>', true); Use the TRANSPORT_SET_VIOLATIONS view to check if any violations condition exists select * ...


3

This answer is wrong, because the value for rownum is assigned before the order by is executed. Below is the correct one, SELECT start_date, end_date, resort_id, ROWNUM FROM (SELECT start_date, end_date, resort_id FROM employee_activity WHERE employee_id = 27 ...


1

No. The rman command 'BACKUP FULL ...' makes a backup of the datafiles but does not backup any archivelogs. If you want to backup archivlogs you have to make an archivlog backup too. This can be done by adding the 'PLUS ARCHIVLOG' clause to the 'BACKUP FULL' command or by issuing a 'BACKUP ARCHIVELOG' command after the 'BACKUP FULL' command. Online ...


0

You're right, excuse any errors or agreement error. My question is a concept so much, but explain. I have users who have their logins, but there is a login that has some privileges that also and the same User used in important processes in the environment and is in source code. At one point in the past knew the password for this login, and the more I say ...


0

Personally, I would start with DBA_HIST_ACTIVE_SESS_HISTORY, and look at all statments that contain that type of hint. From there, you can pull the index name coming from that hint, and then do a lookup on dba_indexes to see if the index exists, is valid, etc. You should be able to do this via PL/SQL if you want to make it really fancy and do it all in ...


1

From the Oracle 11g Documentation: A datafile can be associated with only one tablespace and only one database. So you don't have to worry about losing data from another tablespace.


0

I recently converted an Oracle DB into MySQL using a program called DBConvert There is a free version that inserts watermarks into large tables, but that can be fixed. ;) It's not perfect and might take some tinkering to fix up after the conversion.


0

I'm a little rusty on Oracle but I think that the above answer might fail if there are no likes on an article and those articles might fail to be brought back. Should the join between: ART.NEWS_ARTCL_ID = LIK.NEWS_ARTCL_ID be a left outer join, so that the query would read: SELECT ART.NEWS_ARTCL_ID, ART.NEWS_ARTCL_TTL_DES, ...


1

Based on Paul White's answer here is my solution. It skips only weekends and groups rows without comment. WITH GroupStart AS ( -- Identify the start of each new group SELECT td.id, td.holidaydate, td.holidaycomment, CASE --New group, if more then two days between two dates (more than a weekend) WHEN ...


0

Would this work? select owner,table_name from dba_tab_privs where privilege = 'SELECT' and grantee = UPPER('&SCHEMA');


2

The method you describe is ok. This method was the backup method used before Oracle introduced RMAN and is still valid. It is still used by tools that do snapshot backups (like Netapp's backup tool for Oracle) and I often use this in similar situations. You also should make a controlfile backup if all the controlfiles are on these disks.


0

This is my attempt at solving this "groups and islands" problem, with comments in-line: WITH GroupStart AS ( -- Identify the start of each new group SELECT TD.id, TD.holidaydate, TD.holidaycomment, -- New group if the holidaycomment has changed -- (in holidaydate order) CASE WHEN holidaycomment = ...


1

If you can create a snapshot of everything at the same time, you do not even need backup mode. Backup mode (User Managed Backup) is not deprecated, but RMAN is the preferred tool for making backups.


1

I would do a shutdown of the database to be sure that really nothing is written on the filesystem of the SAN. Do you have your archivelogs on the same filesystem? Restart it after you have made the snapshot. The alter database begin backup; is in the 11g documentation so it must be still valid (have not checked it for 12c). Do you make full backups with ...


1

Try the following: SELECT ART.NEWS_ARTCL_ID, ART.NEWS_ARTCL_TTL_DES, ART.NEWS_ARTCL_CNTNT_T, ART.NEWS_ARTCL_PUB_DT, ART.NEWS_ARTCL_AUTH_NM, ART.NEWS_ARTCL_URL, ART.MEDIA_URL, ART.ARTCL_SRC_ID, SRC.ARTCL_SRC_NM, MED.MEDIA_TYPE_DESCRIP, LIK.TOTAL FROM RSKLMOBILEB2E.NEWS_ARTICLE ART, RSKLMOBILEB2E.MEDIA_TYPE ...


0

I wouldn't use connect by to solve that, I'd just use some good old analytics :) [edit] based on comment, adjust query - still use analytics, this is NOT a heirarchical query .. [/edit] with w_group as ( select id, sum(grp_cnt) over (order by id) grp_id, holidaydate, holidaycomment from ( select id, holidaydate, ...


0

Why wouldn't this work? select index_owner,index_name, table_name,column_name,column_position from all_ind_columns where column_name = UPPER('&COLUMN_NAME1') and column_name = UPPER('&COLUMN_NAME2') and column_name = UPPER('&COLUMN_NAME3') ;


0

As per compatibility matrix, the target and the RMAN client version has to be the same. The workaround is connect to the catalog database from the target database ( whose version is lower than that of the RMAN client) and register the database in the catalog. Thanks!


0

You must use the RMAN executable that is in the classpath that is used to run (or how would you say that) your database. That is the one from 11g. Just do .oraenv <database> to set the correct path and then execute rman.


-1

SELECT NAME,VERSION,CURRENTLY_USED,FEATURE_INFO FROM DBA_FEATURE_USAGE_STATISTICS WHERE NAME = 'Data Guard'


0

You should be able to find what you're looking for by clicking the "Top Activity" link in the bottom right. When you get to the Top Activity page, try changing the drop down menu on the right side from "Top Sessions" to "Top Clients." You can then click on an individual user session to get more details about that session.


0

You might try using ADRCI. ADRCI> ADRCI> show alert -p "message_text like '%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE" This will show your error within the past 5-minutes. You could put this into a shell script and add a cron job that runs it every 5-minutes. #!/bin/bash LOG = ...


0

Oracle specifically split out the sysaux from the system, and IMO this was a good choice by them. Essentially, without going deeply into the documentation which you can read for yourself, SYSAUX stores things like statistics history, non-essential performance views, and other pieces of metadata that may or may not be in-use in a particular database, ...


0

What powershell syntax are you using? If you do not enclose the select between single quotes, Powershell will treat it like a variable. If that's the problem, please try: $command.CommandText = 'SELECT COUNT (*) AS COUNT FROM v$session'


1

You won't need downtime just to add additional redo logs to your redo groups. Take a look at this: Oracle Configuration Best Practices And then look at this: Creating Redo Log Groups and Members You will need downtime to add additional control files: Creating Additional Copies, Renaming, and Relocating Control Files


1

First, it is important to understand the purpose of SYSAUX tablespace. As the name implies, it is intended to support system tablespace, by holding metadata about database components and other frequent changing data, such as reports. Docs: "If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database ...


-1

You need to grant the DBA role to the user. SYSDBA does not give you the right to these objects.


1

The note you provided is regarding issues with shutdown: Bug 8299200 - Shutdown immediate hangs (Doc ID 8299200.8). Is it the correct doc you wanted to refer? Have you recently upgraded to 11.2.0.3? If so, there's this MOSC note that might be helpful, referring to a bug that causes Clusterware Synchronization Services operations to be very slow, leading to ...


3

There is a built-in procedure for that. set serveroutput on declare feature_boolean number; aux_count number; feature_info clob; begin dbms_feature_data_guard(feature_boolean, aux_count, feature_info); dbms_output.put_line(feature_boolean); dbms_output.put_line(feature_info); end; / Sample output when Data Guard is not used: 0 Data Guard ...


2

Some notes first: Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2. Adding account_type to Payments (and modifying accordingly the foreign key to ...



Top 50 recent answers are included