SQL & PL/SQL :: RESTRICT_REFERENCE Changing A Database State

Mar 24, 2010

When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?

as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.

RESTRICT_REFERENCE states that the code should not do the following
RNPS : Read no package state,
WNPS : Write no package state
RNDS : Read no database state
WNDS : Write no database state

what is there to state as i know what my code is doing.

without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).

View 7 Replies


ADVERTISEMENT

Recover Tablespace (Database) To Older State

Jan 30, 2013

i have the following problem:

At a test database instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.

First i've done a fullbackup with RMAN:
----------
# rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
----------
Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace:
----------
# sqlplus / as sysdba
SQL> drop table TESTTABLE;
----------
After that i would start the restore and recover process by restoring and recovering the tablespace in which I deleted the table with RMAN. At first i've set the tablespace offline:
----------
SQL> alter tablespace TESTTABLESPACE offline immediate;
----------
Then I go to RMAN and start the restoring:
----------
RMAN> restore tablespace TESTTABLESPACE;
----------
Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I dropped the table from the tablespace this operation was written in the redolog files. So when i make a :
----------
RMAN> recover tablespace TESTTABLESPACE;
----------
The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?

And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?

View 4 Replies View Related

Data Guard :: Query Regarding Standby Database State When Primary Is Down / Unreachable

Feb 20, 2013

I want to know if there is any reflection on the standby database states when it's primary is down or not reachable? I want to use this information from standby node to determine if I can programmatically execute (dgmgrl) failover operation or not? This is considering that the broker configuration has no fast-start failover and no observer enabled/configured.

View 4 Replies View Related

Application Express :: Changing Sequence In Content Frame Not Changing Ordering Of Regions

Apr 24, 2013

Using Application Express 4.2.1.00.08

Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.

The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.

View 3 Replies View Related

Changing A Lot Of Information In Database

Feb 18, 2011

I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).

what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.

View 1 Replies View Related

Database Idle But SCN Is Changing Continue

Jul 22, 2013

Current database scn is changing faster even though no application,no transaction is running. Database is idle but scn is changing continue, why?

17:25:00 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2477896
1 row selected.
[code]....

View 2 Replies View Related

Server Administration :: Changing Of SID Oracle Database

Jan 3, 2011

i do know how change the database but can any one assist me how to chnage SID of database which in windows 2003 server?

View 1 Replies View Related

Data Guard :: Changing Password On Primary Database?

Nov 10, 2011

I'm using 2 databases : Primary and Standby DB. It working fine. I've just one question : I want to change SYS password on primary DB. Can I change it without any action on Standby DB?

View 8 Replies View Related

Data Guard :: Changing Redo Log Folder On Primary Database

Mar 3, 2012

I want to replace one redo log on the primary database. actually this redo log is on G:oracleoradata; I want to move it on F:oracleoradata. How to do that cause the same redo log is also on standby database?

View 3 Replies View Related

Index Is In Unusable State

Sep 1, 2010

I can see the error in alert log like

2437312:ORA-12801: error signaled in parallel query server P009
2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state

and tried to rebuild the index and i got following error.

ORA-14086: a partitioned index may not be rebuilt as a whole.

The table size for the index is large. we need to rebuild the hole index.

View 9 Replies View Related

SQL & PL/SQL :: Index Unused State

May 7, 2013

I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.

marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.

View 2 Replies View Related

PL/SQL :: Get CITY And STATE From Address

Jul 9, 2013

I have got a table where the site address is stored as follows - 

SOUTH PARK ROAD #822,,,,
HOLLYWOOD,FL,33021,,
USPO BOX 1111,,,,
PHILADELPHIA,PA,19178-3478,,
USNORTH AVE EAST,
P.O. BOX 1234,,,ELIZABETH,NJ,07207-6031,,
US12345 PARK SORRENTO SUITE 222,,,,
CALABASAS,CA,91302,,US 

I have got 2 fields to be populated from this - CITYSTATE How can I pick up the 5th and 6th field separated by the comma.

For example: -

Following output should be shown for the above recordsCITY  
 
STATEHOLLYWOOD       FLPHILADELPHIA        PAELIZABETH       NJCALABASAS       CA

View 3 Replies View Related

Session Inactive State?

Jul 1, 2012

want to know the following

1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?

View 6 Replies View Related

Oracle Solid State Disk

Feb 1, 2013

I understand the architecture of solid state drives favor different types of I/O. Oracle 11g introduced the database flash cache which allows flash storage to be used as a secondary cache. With database flash cache serving as a secondary cache to my Oracle buffer cache.

My understanding is I need to config two database parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE. If i want to have certain tables/indexes to take advantage of this extra cache I would set the value KEEP within the parameter.

Is there anything that needs to be done to the tablespaces / datafiles.

View 1 Replies View Related

SQL & PL/SQL :: List Most Popular Product For Every State?

Feb 11, 2011

so here is the query : For every state, list the most popular product. Here is the query I have so far :

SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;

Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2

I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...

View 4 Replies View Related

PL/SQL :: Existing State Of Package Has Been Discarded

Nov 2, 2012

I am getting the following error only for the ""first execution"", even the package is in "valid" state. If the execute the same package again "second execution", it runs ok.

usually we complie all the "invalid objects" in the schema when we move the code to TEST or PRE-PROD env.

but i am getting the following error, even if the package is in "valid state".

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "TEST_OWNER.TEST_PKG" has
been invalidated
ORA-04065: not executed, altered or dropped package
"TEST_OWNER.TEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called:
"TEST_OWNER.TEST_PKG"
ORA-06512: at line 2I have gone through the following thread, blu explained the cause of this error,
but do we get this error even when the package is in "valid" state too?

Re: When should be package invalidated?

View 4 Replies View Related

ORA-04068 - Existing State Of Packages Has Been Discarded

Feb 4, 2011

We are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.

I'll provide the timeline of what occurred:

1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.

2.) I ran a "drop package body" statement to drop the package body from MY schema.

3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.

4.) I ran a script to check for any invalid objects under the application user schema. There were none.

5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.

6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.

I also ran utlrp.sql.

I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.

I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.

View 4 Replies View Related

Make Tablespace Offline In Mount State?

May 7, 2012

I started database in mount state;startup mount; alter tablespace users offline;

I tried to make tablespace offline In another case online but showing error database not open same for command alter tablespace begin backup; do think we can make tablespace offline in mount...

View 1 Replies View Related

SQL & PL/SQL :: ORA-01502 / Partition Of Index Is In Unusable State

Aug 3, 2011

after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.

View 7 Replies View Related

SQL & PL/SQL :: ORA 20000 Index Partition In Unusable State?

Jan 3, 2012

ora 20000 index partition in an unusable state. what can i do

View 6 Replies View Related

SQL & PL/SQL :: ORA-06575 Package Or Function In Invalid State

Feb 15, 2012

The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.

However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:

select GetDate ('010312', 'dd-mm-yyyy')
from dual

Here is the code I used to create the function:

create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is

vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;

View 5 Replies View Related

ORA-04068 / Existing State Of Packages Has Been Discarded

Feb 6, 2007

I am trying to use dbms_jobs in Oracle 8i database.

This is the syntax I am using:

begin
dbms_job.submit(:jobno, 'SYSTEM.SHRINK_ROLLBACK_SEGS', '07-FEB-2007', NULL);
commit;
end;

i have defined the variable jobno as number!

And i am getting the error :

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_JOB" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

I then go and look at the Sys.dbms_job package and see its not compiling

I have this error:

(1): PLS-00201: identifier 'ANY_INSTANCE' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00801: internal error [21076]
(4): PL/SQL: Item ignored

View 2 Replies View Related

Forms :: How To Populate City Based On Selected State

May 14, 2010

I'am having a query regarding population of a list item based on another selected list item that is whenever i select the state from the combo box all the city's under that state should be automatically populated.

View 4 Replies View Related

Application Express :: Display State Of Interactive Report?

Jul 15, 2013

I've got an interactive report with detail and icon view enabled. How do I get the current display state (user looks at report, detail or icon view) out of the apex dictionary ?Is there any place in APEX_APPLICATION_PAGE_IR_... where I can find it ? I'm on APEX 4.0.2. Upgrade to APEX 4.2.2 is planned this fall. 

View 3 Replies View Related

Application Express :: Filter SQL Statement With Value From Session State

Feb 27, 2013

I need to filter a SQL statement with an application item value retrieved from session state. I am assuming I could do this using a Dynamic Action on a Page Load event but I'm not sure where to go from here. How could I append the filter to an existing report page SQL statement?

View 7 Replies View Related

Application Express :: Session State Protection Violation

Nov 21, 2012

I have upgraded from APEX 4.1 to 4.2.all read only items with dynamic action are arising below error:

session state protection violation: this may caused by manual alteration of protected item P2_IDACCSET.

View 16 Replies View Related

SQL & PL/SQL :: Count Of Date Difference Using Pending State Value In Table Field

Feb 2, 2012

Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.

Example as needed:
NoPendingDays = 23 (8+15)

I have attached Create table and Insert table values sample as SQL file.

View 10 Replies View Related

Application Express :: Session State Protection Violation Error

Jan 15, 2013

Using apex 4.2. I have created form item on page and item attribute DISABLES IN SETTINGS , set value as YES and SESSION STATE value as YES.

SETTINGS->DISABLES-> SESSION STATE

by doing this i am getting the error. Error is

Session state protection violation: This may be caused by manual alteration of protected page item P98_CHECK_AMOUNT. If you are unsure what caused this error, please contact the application administrator for assistance.

View 8 Replies View Related

Application Express :: 4.2.2.0.11 Page Item Session State Not Showing?

Sep 20, 2013

I have searched for comment on this, but could'n find. So problem is ... not all of the page items are showing in SESSION STATE.

View 4 Replies View Related

When Inserting Data Unique Index Initially In Unusable State

Apr 9, 2013

We are getting an error as below when trying to load data into a table.

INSERT /*+ APPEND parallel(IA_SBSCR_DED_MAX,4) */ INTO EDW.IA_SBSCR_DED_MAX
*
ERROR at line 1:

ORA-12801: error signaled in parallel query server P000
ORA-26026: unique index EDW.XPKIA_SBSCR_DED_MAX_A initially in unusable state

The index has been rebuilt but we still have this issue.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved