Tag Info

New answers tagged

0

Just like Leigh mentioned in another answer. Considering you case in terms of user behaviour and load is critical. Having said that, there are two options to do this. One is rownum and the more recent is the row_number analytic function. row_number can do more things but in this particular case there is no little to no difference in how the code would look. ...


0

In this example I created a CTE to capture sales averages and then joined on the cte as needed. As stated in the code comments, you can adjust your avg based on product or omit product for a straight up average regardless of product. --Build the test table IF OBJECT_ID('SALES','U') IS NULL BEGIN CREATE TABLE SALES ( ...


1

The DML in Inmemory involves new features like Journal & Inmemory Transaction Manager. When there is an insert operation on a table enabled for Inmemory, these new features get updated to bring the consistency in Transaction. Sometimes, there are overheads involved in keeping Inmemory Column Unit (IMCU) transactionally consistent. Following are some of ...


0

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.


0

It may not be the most efficient method but I can't see a problem with your SQL. Recommend you check your data..


3

You could just try it out. This is logged in locally sqlplus / as sysdba: SQL> alter user mat identified by COOLPASSWORD ; User altered. SQL> select substr(sql_text,0,40) from v$sqlarea where lower(sql_text) like '%alter%' ; SUBSTR(SQL_TEXT,0,40) ---------------------------------------- select substr(sql_text,0,40) from v$sqla SQL> select ...


2

Option 3. Unplug the PDB: Unplugging a PDB ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml'; Copy the files (XML + database files), then perform the checks listed below: Plugging In an Unplugged PDB Finally plug in the PDB: Examples of Plugging In an Unplugged PDB CREATE PLUGGABLE DATABASE salespdb USING ...


1

Each element of a name must be quoted individually: drop java source "SCHEMA"."JAVASOURCENAME"; The identifier "SCHEMA.JAVASOURCENAME" refers to a java source name SCHEMA.JAVASOURCENAME in the current schema, e.g. "VORIAND"."SCHEMA.JAVASOURCENAME"


2

Just set the "long" in the sql sheet window big enough to hold the desired ddl. Example: set long 1000 select dbms_metadata.get_ddl('TABLE', 'XP','OPS$RM74384') from dual; -- Then just hit Run Script button or press f5.


1

Plugins are deployed on the OMS and the agents as well. First you should upgrade the plugin on the OMS, then on the agents. You don't need to upgrade between versions sequentially, you can upgrade to the highest available. Yes, there are. Currently 12.1.0.5 is the latest version, and there are quarterly PSUs as well. Check the below MOS note: Oracle ...


1

Yes, it is. BACKUP ARCHIVELOG ALL DELETE ALL INPUT; See: Deleting Archived Redo Logs After Backups in non-CDBs Or you can use FRA and configure an archivelog deletion policy: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK'; This way when the FRA is getting full, the database will automatically delete already backed up ...


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: ...


1

You might look at trying out Luca Canali's PerfSheet4 tool, which helps extract and visualize data from AWR tables in Excel. It comes with a video and should help you get started with some of the necessary data gathering and analysis for troubleshooting. You probably want to start with Query: Top 3 wait events and CPU option from the sheet which according ...


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; ...


0

This is a runtime error. The date function has a problem to convert the date string. so display the date string to see what you want to convert: select trunc(sysdate) || '13:00:00' from dual


1

SQL> alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS'; Session altered. SQL> select trunc(sysdate, 'DD') + 13/24 from dual; TRUNC(SYSDATE,'DD' ------------------ 29-AUG-15 13:00:00


3

Just keep one table. Then replace the other table with a view on the table. Now you should be able to insert/update/delete a row in either the view or the table. Normally this should work. If the insert/update/delete in the view cause a problem then you can always create triggers on the view that replace insert, delete, and update (create trigger t_x instead ...


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 ...


0

According to the Oracle 11g documentation, SYSTEM and SYSAUX tablespaces are the mandatory tablespace. If you dont use automatic undo management then the UNDO tablespace is not required. You can use rollback segment in the SYSTEM tablespace. In summary, SYSTEM for Data Dictionary, SYSAUX auxiliary to SYSTEM is the default storage for the AWR statistics. ...


1

The main difference compared to the architecture of Data Pump utility exp / imp and expdp / impdp are always run on the DB server and not on a client. Then the files will always on the server where and active an Oracle database. The advantages of the data pump is performance (eg. Parallel) and the presence of new features' (eg. Migration direct SQL * Net, ...


4

It gives an error: ORA-01790: expression must have same datatype as corresponding expression From the documentation for UNION: The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). Tested at SQLFiddle


0

emcli modify_target -type='oracle_listener' -name='LISTENER_mx0102.orademo.com' -properties='Machine:mx0102.orademo.com'; Target "LISTENER_m0102.orademo.com:oracle_listener" modified successfully


2

Yes, you can specify the SQL_ID. The usage would be something like this: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0)); To find the SQL IDs for the current session, you'll first need to get the SID for your session, and then search v$session for the related SQL_IDs: select sys_context('USERENV','SID') from dual; select SQL_ID from ...


0

Create a table with a select * from a large table and where 1=0. Gather stats, save them using dbms_stats and then apply those stats to the large table. You will essentially tell Oracle that the large table has no data, which would favor full table scans. You can also try switching the optimizer mode to first_rows_1 or invalidating some indexes.


0

Are you using Oracle 7? If not, please make sure to always use current versions of documentation. Oracle is already on 12C, and while some concepts still the same, a huge part of features has changed. You actually won't get column size. This is why it is only an "estimate". You need to have an idea of what you are going to need, and then you can use the ...


0

select 1 from dual where regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~', '%\.,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~'); This gives, as expeced, 1. You must escape .?$)(*+[]\^{} and | You do not need to escape , (but it works nonetheless): select 1 from dual where ...


2

Install GoldenGate for SQL Server on the server with the SQL Server database. Install GoldenGate for Oracle on the server with the Oracle database. Here is an example of setting this up: http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html It is outdated, but the point is, you can see what goes where in this.


4

As always: it depends. However in most cases the default values are adequate. The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50? INITRANS ...


0

I googled 'occivc9win32_111060.zip' and found "OCCI Downloads - Oracle" : http://www.oracle.com/technetwork/database/occidownloads-083553.html Maybe you have to register for the download.


1

This error code [ORA-01654] means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error you can simply add data file using ALTER TABLESPACE ADD DATAFILE ...


1

In addition to someone doing full table locks, there are two other things that I can think of. If you have a table where there are several columns that have a parent child relationship to the same column in a child table and if the child column is not indexed, then indexing it could help. I had a case with an online content management system that had ...


1

It may depend on whether you are using connection pooling or not. Also, do you realise LOGON and LOGOFF for a session are recorded in the same audit row? So here the LOGON is showing how many are still logged on right now. Consider the following scenario. SQL> connect / SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by ...


0

Read and follow the instructions. a is not a valid directory path. Based on your screenshot, it should be C:\Users\Ashish Jain\Desktop\global-11g-schema-1-128202.


0

Oracle is a SQL DBMS not a truly relational database. It implements as its logical model of data a variant of SQL. Its architecture was developed in the late 1970s along the same lines as IBM's System R which was an initial implementation of a DBMS based on the relational model using SQL as the data sub-language. This short background is necessary to ...


3

You have a few options: Transactional replication: this has been around for quite some time and works nicely within its narrowly defined scenario. Unfortunately, the use of non-SQL Server subscribers is deprecated. Change data capture + SSIS: this is the Microsoft recommended solution. It's a bit more involved than replication (to me anyway) but it's also ...


0

One way is to add discriminator column, something like create table c (id_c int not null, val char(1)) create index idx_c1 on c(id_c,val); alter table c add constraint chk_val check(val in ('a','b')); alter table c add constraint c_pk primary key (id_c) using index idx_c1; alter table c add constraint c_uq unique (id_c,val) using index idx_c1; create ...


1

You can not issue DDL commands (including ALTER) directly in PL/SQL. Use execute immediate instead. execute immediate 'Alter Trigger TEST.LIVE_DELETE Disable'; Replace DBMS_OUTPUT.PUT_LINE calls with logging to a table, as the output of the former will not appear anywhere in case of a scheduled job.


1

Another way: Unpivot the columns. Filter out the 0's. Group by ID and concatenate the column names. Something like this (assuming you are using Oracle 11g): SELECT ID, LISTAGG(Col, ',') WITHIN GROUP (ORDER BY Col) AS descr FROM atable UNPIVOT ( Val FOR Col IN (C1, C2, C3, C4, C5) ) u WHERE Val <> 0 GROUP BY ID ;


2

Try: select id from tab where id not in (select id from tab where index=0) group by id having max(index)+1!=count(*) order by id; Preventing that the combination of id and index is unique


2

create table index_test(id number, idx number); insert into index_test (id, idx) values (1,0); insert into index_test (id, idx) values (1,1); insert into index_test (id, idx) values (1,2); insert into index_test (id, idx) values (1,3); insert into index_test (id, idx) values (1,4); insert into index_test (id, idx) values (2,0); ...


-1

DECLARE @Line TABLE(Id INT) INSERT INTO @Line (Id) VALUES (1) INSERT INTO @Line (Id) VALUES (2) INSERT INTO @Line (Id) VALUES (3) INSERT INTO @Line (Id) VALUES (4) INSERT INTO @Line (Id) VALUES (5) INSERT INTO @Line (Id) VALUES (7) INSERT INTO @Line (Id) VALUES (9) ;WITH CTE as ( SELECT ROW_NUMBER() OVER (ORDER BY Id) as RowId, Id FROM @Line ) ...


1

Make a concatenation of every field in the form of: CASE WHEN columnX!=0 THEN 'textX,' ELSE '' END This would make: select id, trim(CASE WHEN column1!=0 THEN 'text1,' ELSE '' END|| CASE WHEN column2!=0 THEN 'text2,' ELSE '' END|| CASE WHEN column3!=0 THEN 'text3,' ELSE '' END|| CASE WHEN column4!=0 THEN ...


2

Something like (untested): select id, rtrim( case when c1 != 0 then 'column1,' end || case when c2 != 0 then 'column2,' end || case when c3 != 0 then 'column3,' end || case when c4 != 0 then 'column4,' end || case when c5 != 0 then 'column5' end, ',') as descr from tab1;


1

SQL> show parameter aq NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 SQL> show parameter streams_pool_size NAME TYPE VALUE ------------------------------------ ----------- ...


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 ...


0

Straight from the Manual: The easiest approach to migrating convertible data dictionary data is to create a new database in the target character set and then re-create all of the data dictionary and schema definitions by exporting and importing. However, this method means creating a new database. Based on what I'm reading in this manual, that's ...


0

The data of a temporary table is only visible for the same session (or transaction, depending on the temp table properties) that wrote the data.


1

You probably want to use a contextual index. Then when you search in the clob column you would do something like myclob contains ( 'aword') > 0 This would then be a substitute for regexp. I have not used this feature in a few years, but it does take some work to create and maintain the indexes. You should look for specific examples that people ...


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.



Top 50 recent answers are included