Tag Info

Hot answers tagged

5

Oracle (as well as any other RDMS) doesn't scan "rows". It operates with blocks (other vendors may use different terminology , e.g. page) , and it doesn't know which blocks have or don't have rows. Also, sequential read is much faster than random , and it's way cheaper to read more than needed into memory... A good graphical explanation of HWM : ...


4

DBA_DEPENDENCIES view has all the answers to such questions. select * from DBA_DEPENDENCIES where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';


4

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO. Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL. But it's usually not too difficult to track down the worst SQL statement. Since you've already got AWR, look at the SQL Statistics section, ordered by ...


4

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types. Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? ...


4

No you cannot. To restore a table from the recycle bin use flashback table <table_name> to before drop; <table_name> is the original table name and not the object_name from the recycle bin. Do not rename the table to restore it from the recycle bin!


4

No that is not possible, only session accessing the GTT can see it. IF you wish, you can use a physical table and model it as if it s a GTT. It isn't difficult, but isn't very efficient either.


3

The standard approach in Oracle is that global temporary tables are first-level objects. They should be created at install time when you create all your permanent tables. Doing DDL at runtime is very much frowned upon. I'm not sure how this increases maintenance or how it makes stored procedures "no longer wholly self-contained"-- you don't need to do any ...


3

Just import your data into a a staging table at the target database. Then write a SQL staement to copy the rows from the staging table into the target table while giving the two new columns an explicit value. So something like this; The following syntax should work on 11g impdp user/password directory=import_directory dumpfile=data.dmp ...


3

Code generators are great! Code generators are evil! 10-15 years ago, I would have said that having a code generator for quickly creating the boiler plate code for database driven applications would have been a great gift to mankind. 5-10 years ago, I would have said code generator sucks, they generate too much duplicate code and that having a data-driven ...


3

Well, I guess the database is expendable since there is no proper backup. So just drop and recreate the database. OK, seriously there are not many options left. Before you do anything shutdown the database and copy all files which belong to the database somewhere else! Because everything you do can make it worse. Once you did that you have 2 options: ...


2

There are some things which have to be set up correctly: The user you are logged on has to be a member of the ora_dba operating system group. The parameter SQLNET.AUTHENTICATION_SERVICES=(NTS) must be set correctly. It can include more options but "NTS" is required. You have to connect to the database correctly which is: sqlplus / as sysdba. You can also ...


2

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger. You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020 In the body of ...


2

If indexes are in place: updates in indexes are always logged. An other thing is: nologging is only done for statements that copy data. insert into x values (y,z) will always be logged, insert into x select y,z from source can be unlogged.


2

Not exactly an answer to your question but amount of changes generated by the session can be found using such query. select st.inst_id, trunc(st.value/(1024*1024)) as MB, ss.sid, ss.username, ss.osuser, ss.machine, ss.program, ss.logon_time from gv$sesstat st, v$statname sn, gv$session ss where st.statistic# = sn.STATISTIC# and sn.name = 'redo size' and ...


2

Erwin has a macro language for writing stored procedures that can be compiled against Oracle or SQL Server. You write, IMHO ugly code, with variables that will get translated to something understandable by a particular database engine, then Erwin will create a translated version for that database engine. I have not worked with Erwin in years, so I am not ...


2

First let's define ALL_OBJECTS and DBA_OBJECTS in oracle: ALL_OBJECTS describes all objects accessible to the current user. DBA_OBJECTS describes all objects in the database As result if you select everything they are different because there are multiple levels of DBA privileges on Oracle. For example if you have SysDBA privileges then you have much more ...


2

You can use both, Nagios and Oracle Cloud Control 12c (also applies for Grid Control 10g and 11g). I used Nagios about 8 years ago to run custom SQLs but I guess there is much changed since then. Maybe someone else has recent experiences with that. In Oracle Cloud Control you have 2 options: Write your own Agent Plugin. I never did that because after ...


2

The only correct way to back up an Oracle database is to use RMAN. There are alternatives but they are not as reliable as RMAN. RMAN has a ton of features and this might make it look complicated. But in fact it's not difficult and should always be the choice to back up a database. RMAN does not do logical backups like tables, indexes, procedures. RMAN does ...


2

Use regexp_like: select ename from admin_emp where regexp_like(ename,'(^[sm])'); Example SQL Fiddle here.


2

I guess these {...} get replaced somewhere in the code with something that makes sense. This is indeed an old script because compute statistics is not quite state of the art anymore. Go ahead and create the index with the sql you just posted: create index x on y(gid) tablespace IDX_TS; And then analyze the table with dbms_stats: begin ...


2

Which Database Version do you have? From Oracle 11g onward you should be using the SCAN listener. The SCAN listener knows where the database is located in the RAC and how much load is on that node. Further more you should define services for long ops and short ops for better load balancing.


2

The duplicate database ... from active database is designed to work without any existing backup. I would expect Oracle to clean up the archivelogs which were used during the clone process. So I would say this is an unexpected behavior. Try to log a Service Request at Oracle. I cannot recall if I had to clean up archivelogs after a clone processes. Well, I ...


1

0> "and set 5500 was impossible due to the error - 5500 seemed busy" This means that the port 5500 is already "in use". Well, I bet it is not in use but Oracle thinks that. There are a lot of ports reserved in /etc/services. Open the file and change the following lines: fcp-addr-srvr1 5500/tcp # fcp-addr-srvr1 fcp-addr-srvr1 5500/udp ...


1

Test case. Open 2 sqlplus sessions, run the following in the first to set the test up: create table stall_table ( a number ); create or replace trigger stall_session before insert on stall_table begin DBMS_LOCK.sleep(10000); end; Then, execute in session 1: SQL> insert into stall_table values ( 1 ) ; Execute in session 2: SQL> create or ...


1

No it wont backfire. If there is confusion, you can read the manual to see the effect of caching. It means first time you select from sequence, it will cache next 999999 values in sga. It also means, for next 999999 sequence nextval calls you will not be updating seq$ table. The downside of this is, if your instance or db crashes, those cached (and unused) ...


1

Not that I'm fond of it, but if you use Nagios, it's rather easy to add some database-related plugins. You could do worse than have a look at: PostgreSQL: Very useful. The latest versions are available at Bucardo. MySQL: quite a few are available, make your pick. Oracle: but you'd better ask the Oracle experts. MsSQL: I'm not using it yet but will test it ...


1

DBA_TABLES.NUM_ROWS column can be used to identify tables that are empty. But this column is updated by DBMS_STATS, so it is not 100% accurate. There is no view that contains all records.


1

OEM is a web-based application so the language is determined by the default on web browser like Internet Explorer, make sure to change it to English (note you may see Internet Explorer menu in English but the default language (localization is not)) it can be change from internet option. give it a try


1

Yes, there is a way to check this. You can query V$PARAMETER with this SQL: SELECT name, value FROM v$parameter WHERE name like 'commit_write' ; NAME VALUE -------------------- -------------------- commit_write (null) Afterwards change the parameter on session basis: ALTER SESSION SET commit_write = 'IMMEDIATE'; And query ...


1

I couldn't use the option suggested by BriteSponge because the value of the COLUMN Status in ALL_OBJECTS doesn't seem reliable. I could find a SYNONYM that had status VALID but the object it was pointing to didn't exist. The list of Objects I want to update is the OBJECTS_TO_UPDATE table. I could list all the synonyms from the OBJECTS_TO_UPDATE table that ...



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