Server Administration :: Invalidation Of Dependencies?
Aug 10, 2011In a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".
View 4 RepliesIn a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".
View 4 RepliesI have been asked to take the export of a schema and then asked to remove all dependencies on tablespace. Provide me the syntax.
View 1 Replies View RelatedUp until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
I am getting the below error with my bulk delete code.
ORA-14403: cursor invalidation detected after getting DML partition lock
declare
Ty_LMRowId is table of rowid;
Tbl_LMRowId Ty_LMRowId := Ty_LMRowId();
v_err_cnt number;
Ex_Dml_Errors Exception;
v_dte date := sysdate;
pragma exception_init(Ex_Dml_Errors, -24381);
[code]....
Record get deleted successfully for the first rowid in the collection, But for the remaining rowids it throws above error.
I am having to pull out all the dependencies for a column as below.
When I select a column in a table, it should list
1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.
Is there a easy way of getting this information from the Oracle dba tables?
How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..
View 2 Replies View RelatedI have a hierarchical data structure where a child can have many parents, and a parent can have many childs.See the attached file hierarchy_iliustration.jpg. This example has 4 hierarchy levels, in real problem there can be unlimited number of levels.I want to write a SQL query that lists all indirectly dependent child nodes for a given parent node.
Test structure for example attached:
CREATE TABLE TEST.NODE_T
(
ID NUMBER PRIMARY KEY,
TEXTAS VARCHAR2(254)
);
[code]....
Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.
Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.
find out the list of referenced objects which are dependent on INVALID objects.
I wrote the below query to get the referenced objects which depends on invalid objects.
select owner,name,type, referenced_owner,referenced_name, referenced_type, referenced_link_name , dependency_type from dba_dependencies
where type not in ('JAVA CLASS')
AND referenced_type NOT IN('JAVA CLASS')
AND (NAME, TYPE) IN (
select object_name, OBJECT_TYPE from dba_objects
WHERE STATUS='INVALID')
And the output is something like below -
Sr. No OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE
1PUBLICVEHICLE_INSPECTION_REQUESTSYNONYMINSBIN$Uu99fysmRj6Ppn2QppCTWg==$0TABLEHARD
2PUBLICPRODUCT_HIERARCHY_MAP_TEMPSYNONYMCONFSYSBIN$ndGddLcKSDWRwsn5g91Rcg==$0TABLEHARD
3PUBLICACPKG_SUB_RECEIPTINGSYNONYMINSACPKG_SUB_RECEIPTINGPACKAGEHARD
[code].....
Our requirement is to drop all the invalid objects. But we need to know whether it effecting to any other valid objects?Do any valid objects gets INVALID after dropping of the INVALID objects. We need to know the hierarchy of it.
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
I need to identify the dependencies of all the Tables on Packages at column level.
E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T
The resultant query / code should return like this
PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T
Identify the dependencies at column level.
i'm a student currently learning database administration security.
I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.
I have attached the schema that was given to me for this assignment.
My manager is asking to create a SQL scrip, which will provide following information.
Our database is 9.2.0.7.0 on Windows 2000 Server
-How many records Insert / Update or Delete daily in my Oracle database?
- Check Archive log / Redo Log switching information?
-Check database growth ?
i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
I have to migrate production database version 10gR2 on windows 2003 server to oracle 11gR2 on windows 2008 server . So far i have just installed the oracle software on 64bit windows 2008 server. How to move the data or migrate from old server to new server .
View 22 Replies View Relatedafter a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
I have database in single server. I already have ORACLE_SID=stagedb. So i want to create new sid and named as proddb.Is it possible have multiple db in single server? This server not running as RAC. It run as single only.
View 1 Replies View Relateddatabase administration , we are planning to use amazon cloud database , this database does not allow us to login to server machine , unfortunately amazon don't provide ssh to this machine , in general for doing any of adminstration task on the database will there be need to log into the machine ? we can always log in through toad or any other sql client but we cannot do ssh to server..general can this limitation effect administration ?
View 9 Replies View RelatedI want to install Oracle 11g R2 in windows 2008 64 bit server. How can I know whether my server is ready to install Oracle ie is all components are available in server or any patch is to be applied etc.
View 3 Replies View RelatedI'm trying to connect a oracle client application on the client machine to a remote oracle server on the server machine but i get a connection fail.
On the server machine I configured oracle server in the following way:
Installed oracle server. Created a database "DB_Test" with the database configuration assistant Created a LISTNER with the Oracle NET Manager with the following parameter:
Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521 Created "dbtest" service with the Oracle NET Manager with with the following parameter:
Service Name: "dbtest" Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521
All services on the server machine are running and I opened port number (1521) in the router. On the client machine I installed SQL PLUS and SQL Developer.
With SQL Plus as by the official documentation I have entered the following command:
CONNECT username/password@[//]host[:port][/service_name]. In my case is:
CONNECT SYSTEM/oracledb@//ENZOVAIO:1521/testdb.
With SQL Developer I have entered the same parameter.
But with both SQLPlus and SQL Developer the connection fails.
We a running oracle9i database and forms 6i application.
We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.
Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.
we have oracle server. we have to connect 500+ client machine to oracle server.
is it i need to install client version to all 500+ or any other shortcut way?
We performed image copy of production Oracle server (OS and instances) to a backup server. After a few weeks, we try to restore a latest Oracle database backup from production server to backup server. As we know, Oracle instance must be unique on the network.
Even we log on to backup server and bring up the instance, I think that still point to production instance since all init file, TNSNAMES.ora and listener file are still same. If we restore the database, we will end up bring down the production instance and restore on top of productions. How to change instance name on backup server including TNSNAMES, sqlnet, listener files in order for us to restore Oracle database from production to backup server?
I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.
View 5 Replies View RelatedI have installed oracle 11g Release 11.1.0.1 on windows server 2008.I access this server from a client machine running on winXP through SQL PLUS. I wanted to shutdown and start DB server from this client machine. Accordingly i issued shutdown immediate command from SQLPLUS.
Now when i tried to start the DB with "STARTUP NOMOUNT" command i received error 12514. I understand this error means that the listener received a request to establish connections to a database but since DB services are not up and running, hence the error. So my first question is how do I resolve this error.
Continuing further, i tried to re-connect to the database as sys, and i received another error ORA-01041. So, my second question is why did i receive this error and again how can i resolve it
may be you can point me to relevant reference in Oracle documentation) how can i start and stop oracle from a client machine using SQL interface in CLI mode and windows environment and avoid this error.
I am connected as System. It was the only user I set-up a password when installed the database on personal computer.
SQL> alter user sys identified by mypass007
2 /
User altered.
SQL> connect sys/mypass007
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
[code]...
SQL> conn sysoper/mypass007
ERROR:
ORA-01017: invalid username/password; logon denied
I want to know if I can access a procedure created on Database server A from database server B.
View 2 Replies View Related