Tag Info

Hot answers tagged

6

Internal Oracle users (SYS, SYSTEM etc) should never be modified in any way, except for password changes. As far as roles & grants are concerned, SYS already has unrestricted access to the entire RDBMS due to the nature of the user.


4

It's signalling a 'ORA-214' during instance startup, which is really bad. $ oerr ORA 214 00214, 00000, "control file '%s' version %s inconsistent with file '%s' version %s" // *Cause: An inconsistent set of control files, datafiles/logfiles, and redo // files was used. // *Action: Use a consistant set of control files, datafiles/logfiles, and redo ...


4

Your table's segment is only 2 MB. The rest of the data is stored in the LOB segments which are physically separate from the table segment. SELECT column_name, segment_name FROM dba_lobs WHERE owner = 'MYUSER' AND table_name = 'MYTABLE' should show you that there are two LOB segments associated with the table. You can then query dba_segments to ...


3

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1. Easily reproduced with the following: create view v1 as ( select 'view' as vvv from dual ); create view v2 as ( select * from v1 ); with v1 as ( select 'cte' as vvv from dual) select * from v2;


3

Not an Oracle/SQL*Plus problem. The default size of commandline window screen buffer is 300 (lines), and the maximum is 9999 (lines), so it is unable to display 157998 rows. Better spool the output to a file: SQL> spool output.txt SQL> select ... SQL> spool off After that you can view the result in the file output.txt


3

You can't restrict by IP address. All you can really do is create a new user and only grant them SELECT on that one table. create user newuser identified by 'p4ssword'; grant connect, resource to newuser; grant select on yourtable to newuser; It is actually possible to check the IP address a user is logging in from using a login trigger, and deny the ...


3

Use expdp with the flashback_scn or flashback_time parameters, along with SCHEMAS= to export the schema at a consistent point in time. This is the same as using CONSISTENT=Y in the legacy exp utility. Examples here. If the data for the schema in question is in a tablespace of its own with no other objects from other schemas, you can use rman to do a ...


3

I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data. Example below. Test table: create table yourperftest ( pk number, col1 number, col2 number, col3 number ); Test data: insert into yourperftest values (1,1,1,1); insert into yourperftest values (2,2,2,2); insert into yourperftest values ...


3

Use this one: DROP INDEX SYSTEM."IX_isbn_send_h.D_STAT"; Note, when you use quotes, then names are case-sensitive in Oracle.


2

It's possible to add a_type to b, and have check constraint on b.a_type. Breaking normalization a bit allows enforcing storing details in proper table . In Oracle you can have 2 constrains supported by the same index, for instance PK on a.id, and UNIQUE on (a.id,a.type) , then some tables that require proper type may have FK to (a.id,a.type), and other ...


2

If I understand you correctly, this PL/SQL will do the job for you: DECLARE CURSOR c1 IS SELECT table_name FROM USER_TABLES; QUERY varchar(4000); BEGIN FOR rec IN c1 LOOP SELECT 'SELECT '||RTRIM(EXTRACT(COLXML,'//COLUMN_NAME/text()'),',')||' FROM '||rec.table_name||';' COL_STRING INTO QUERY FROM (SELECT E.table_name, ...


2

When the O7_DICTIONARY_ACCESSIBILITY parameter is set to false, users with SELECT ANY TABLE privilege can not access tables such as SYS.USER$, unless they have direct privileges to them.


2

Oracle Golden Gate has always supported replication between Oracle and SQL Server. For a project I recently confirmed that Golden Gate for Oracle 10 is still available if you push a bit. If you are using an Oracle version that is out of support then you need to update for a number of reasons. Synchronization with Azure is detailed here.


2

Your query doesn't ask for the first ten rows. For that you need to write something like: SELECT salute, gender, fid FROM fisgen WHERE ROWNUM <= 10; The hint first_rows(10) is only needed to instruct the optimizer that it should optimize for retrieving the first 10 rows as fast as possible rather than optimizing for whatever percentage of the table the ...


2

There is a 3rd option, using window functions, either max(): with latest as (select *, max(inserted_day) over (partition by hostname) as max_inserted_day from age_data ) select * from latest l where inserted_day = max_inserted_day ; or rank(): with latest as (select *, rank() over (partition by hostname order ...


2

Sounds like a data pump using a network link would do the job. It's always preferable to run it during quieter hours but if your source database is not under powered then you might be able to run it during regular hours without your user's noticing any difference in performance. From the article create a database link from the destination to the source ...


2

There is absolutely nothing about this on the Internet - even the Oracle docs regarding the %_ASSEMBLIES views don't actually state what they show. However, if you go digging around in $ORACLE_HOME/rdbms/admin you'll see the following reference to the data dictionary views (which sit on assembly$: remark remark FAMILY "ASSEMBLIES" remark remark Views for ...


2

Yes! This is called a 10053 trace in Oracle parlance. It's more formally known as "Tracing of Optimizer Computations". Check out MOS doc 225598.1, or Jonathon Lewis' blog post here: https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/ There's loads of hits for "10053 trace" so if you can't find what you need, it's probably out there in search-land. ...


2

As it was started by Andreas it will not compress the data. Also this online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data ...


1

The question would have been a better candidate for stackoverflow. Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title. From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of ...


1

select c.classid, c.classname, count(case when s.status = 'failed' then 1 end) as no_of_failed, count(case when s.status = 'passed' then 1 end) as no_of_passed from class c left join students s on c.classid = s.classid where c.schedule = 'MWF' group by c.classiid, c.classname; This exploits the fact that aggregate functions will ...


1

Try $ sqlplus / as sysdba | tee sqlplus.log


1

Most likely ,(SELECT NVL (I.sap_xref_no, part_doc_nbr) FROM eissap.tbitem i WHERE i.item IN (SELECT comp_part FROM eissap.tbpart_config_comp_config WHERE item = comp_part)) AS sap_xref_no, returns more than one row (exactly as error says). When subquery is used in SELECT clause of ...


1

I assume the role where granted to the SYS user because it created the role. This is a side affect of the CREATE ROLE statement. SQL Language Reference If you create a role that is NOT IDENTIFIED or is IDENTIFIED EXTERNALLY or BY password, then Oracle Database grants you the role with ADMIN OPTION. So you can safely revoke the role from the SYS user, ...


1

The concept of cardinality does not change whether it is used in reference to the Oracle database or some other SQL database. Cardinality is the size of a set of some elements. From the mathematics point of view a set contains only distinct elements. When you reference values of a certain column as a set its cardinality is the number of unique values. When ...


1

Following sample may be useful to build query which you want to write. USE tempdb GO CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY ) GO INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4') GO CREATE VIEW v_V1 AS SELECT 'id1' id, 2 value UNION SELECT 'id1' id, 1 UNION SELECT 'id1' id, 3 UNION SELECT 'id2' id, 1 GO CREATE VIEW v_V2 AS SELECT 'id3' ...


1

It turns out the user had been granted other privileges by a different account as well as the one we use, so we were unable to see those privileges in ALL_TAB_PRIVS. We had to look in DBA_TAB_PRIVS, and sure enough, there were the other permissions. Thanks to all for chiming in!


1

Assuming your DB target name as seen in EM is ORCL11.WORLD and you want to set it's Monitoring Configuration's Oracle Home to /oracle/product/11.2.0.4, the syntax would be the following: emcli modify_target -type='oracle_database' -name='ORCL11.WORLD' -properties='OracleHome:/oracle/product/11.2.0.4' Probably would be similar approach for Listeners, ASM, ...


1

Please see the following to understand the issue So even though you're connected with user SYSTEM to the pdb, when you issue connect without specifying the pdb, it'll try to connect to the container and the user is not defined there.


1

You can do this using an UPDATE: UPDATE inventory i SET quantity = ( SELECT s.quantity+i.quantity FROM shipment s WHERE s.itemname = i.itemname ); or a MERGE: MERGE INTO inventory USING ( SELECT itemname, quantity FROM shipment ) ship ON (ship.itemname = inventory.itemname) WHEN MATCHED THEN UPDATE ...



Only top voted, non community-wiki answers of a minimum length are eligible