PL/SQL :: Retrieve Primary Key Information

Dec 20, 2012

How can i retrieve which tables have composite primary keys, and only one of the primary key columns is a foreign key to another table?

Like:
CREATE TABLE club (
clubId NUMBER,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (ClubId)
);
  
CREATE TABLE  team (
teamid NUMBER(10),
clubid NUMBER(10),
teamname VARCHAR(10) NOT NULL,
PRIMARY KEY (teamid, clubid),
FOREIGN KEY (clubid) REFERENCES club (clubid));So in this case, the team table

View 4 Replies


ADVERTISEMENT

PL/SQL :: How To Retrieve Information About Columns Of Tables

Nov 16, 2012

I am trying to get information about the columns of tables in Oracle databases. In MySQL, show columns provides good deal of information.But in Oracle, we do not have such equivalent call."describe" shows only the primary key.

But it does not show whether any column has unique constraint or an index.

I created a column in a table with unique constraint (but not a primary key), but it was not listed with CONSTRAINT_TYPE = 'U' in dba_constraints view, but was listed with CONSTRAINT_TYPE = 'C' - Check constraint.

View 6 Replies View Related

SQL & PL/SQL :: Retrieve Private Package Procedures Information?

Apr 16, 2013

how i cant get the private package procedures(the procedures that are defined only in the pcakage body) information?Specially the name and arguments??

Every simple Oracle client presents this type of information, so I think there is a way to get it?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve All Information Of Dept Along With Two Employees Of Each Department

Jan 21, 2013

I WANT to RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT.is any OTHER WAY to DO THIS due to performance in Oracle 10g


SELECT *
FROM DEPT D,
(SELECT DEPTNO,
MAX(DECODE(RN,1,ENAME,NULL)) ENAME1,
MAX(DECODE(RN,2,ENAME,NULL)) ENAME2
[code]......

View 6 Replies View Related

Export/Import/SQL Loader :: ORA-39086 Cannot Retrieve Job Information

Jul 27, 2012

UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

View 4 Replies View Related

SQL & PL/SQL :: To Retrieve Only Latest Repair Information Based On Latest Date

Apr 3, 2012

I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.

drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........

View 10 Replies View Related

During Primary (old Standby) To Standby (old Primary) Switchover Hang?

Apr 4, 2013

I am using RAC to RAC data guard environment. Os hp-unix. during primary(old standby) to standby(old primary) switchover I am ussing:

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

I have 11.2.0.1 environment with standby and when performing switch over - it was hanging for more than 2 hours and i had to cancel it. This is what all i see in the database alert.log:

Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m000_15160.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Thu Apr 04 17:44:03 2013
Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m000_15780.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Thu Apr 04 17:59:03 2013
Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m001_16373.trc:

[code]....

ORA-01155: the database is being opened, closed, mounted or dismounted.

View 1 Replies View Related

Query MP3 ID3 Information?

Apr 25, 2007

Is it possible to query a mp3 file's ID3 information, or the properties where you can put in artist, album, etc. information?

View 1 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

SQL & PL/SQL :: Fetch Information For A Foreign Key

Jul 13, 2010

the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.

View 3 Replies View Related

Security :: See Audit Log Information?

Dec 17, 2011

i enabled auditing by setting

audit_trail=db,extended in spfile,and restart the database and after that i issued command to audit as below :
audit select on emp by access;

but how to see audit log information(ie from which table),i checked dba_audit_trail but it doesnot have any record.

View 4 Replies View Related

SQL & PL/SQL :: Information On Package Contents

Oct 25, 2012

I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.

To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...

SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;

However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).

So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?

Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?

View 3 Replies View Related

SQL & PL/SQL :: Partitioned Table Information

Jan 8, 2013

How to know weather table is partitioned or not ? Is there any data dictionary view to know the information about the Partitioned type,keys on table ?

View 4 Replies View Related

SQL & PL/SQL :: How To Get Information From Another Identical Table

Oct 16, 2010

adjusting a query. I have a table which looks like this:

CREATE TABLE PF.PF_ANOMALIES
(
ID VARCHAR2(10 BYTE) NOT NULL,
PLAN_ID VARCHAR2(10 BYTE),
CLIENT_ID VARCHAR2(10 BYTE),
SEDOL_CODE VARCHAR2(7 BYTE),
LTY_ID VARCHAR2(10 BYTE),

[Code]....

I have the following query that will return me the anomalies of client transactions (based on joining other tables for extra info too)

SELECT pc.client_id,
pc.client_name,
NVL(pa.sedol_code, 'N/A'),
DECODE(ps.stock_name, NULL,
DECODE(plt.lty_name, NULL, 'N/A', UPPER(plt.lty_name) || ' LEDGER - ' || pa.orig_price_ccy),

[Code]....

-- Get cash anomalies on top as these will have 'N/A' sedols I have another temp table that also gets populated with anomalies:

CREATE GLOBAL TEMPORARY TABLE PF.PF_ANOMALIES_TMP
(
ID VARCHAR2(10 BYTE) NOT NULL,
FVD_TYPE VARCHAR2(10 BYTE) NOT NULL,
CLIENT_ID VARCHAR2(10 BYTE),
SEDOL_CODE VARCHAR2(7 BYTE),

[Code]..

My problem is that I also want my query to return rows from this tmp table too.

View 17 Replies View Related

Capture Information From AWR Snapshots

Oct 20, 2010

The sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)

View 3 Replies View Related

How To Get Information Of Hidden Parameters

Sep 16, 2012

)How do you view the value of the parameter that is being used by instance? show parameter..?
2)How do you get the information of hidden parameters
3)What is the database object that stores information related to various types of db connections over network
4)How do you verify since when the db session is running
5)How do you verify the Originating machine details of the database session
6)How do you verify the name of program that the db session is running
7)What is the naming convenion of Base tables. Where is the information of base tables stored?
8)How are dynamic views created. Whre is the information of dynamic views stored?

View 8 Replies View Related

PL/SQL :: Pulling DDL Information For A Table

Nov 5, 2013

We are running Oracle 10g. I need to pull the DDL information from our Oracle tables. The following SQL statement returns the result as a "HUGECLOB". Is there a way to convert the result to text in varchar2 data type? Since tables being processed have numerous partitions, the DDL information for them is quite large, therefore, using substring would not be a viable alternative. 

[code]select DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE') from DUAL; [/code]

View 6 Replies View Related

Redo Log Information - Arch Vs LGWR?

Oct 25, 2012

Redo log information can be transmitted in one of two ways from the primary database to the standby database: either by ARCH or LGWR.

1. when ARCH involves
2. when LGWR involves
FAL_Client = (Should i enter net service name or Db name or service_name
FAL_Server = Should i enter net service name or Db name or service_name )
FAL_CLIENT='whichone'

[code]...

View 9 Replies View Related

Sequence Nextval Storage Information?

Dec 23, 2012

Sequence nextval is a pseodocolumn, but where the value of nextval is stored and why before we use CURRVAL for a sequence in our session, we must first initialize the sequence with NEXTVAL?

View 5 Replies View Related

SQL & PL/SQL :: Hierarchical Query To Get Metadata Information?

Mar 5, 2010

i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).

[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID

[code]....

Expected output

table_name path
DEPT DEPT
EMP DEPT/EMP
EMP_AUTHORIZATION DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP

but by using below query i am getting complete heirarichy.

SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Clause Retrieving Information From Varray

Apr 12, 2012

select *
from
table(it_count_tc('bbs_swe_tve_db'))

gives this output:

COLUMN_VALUE
------------
0
2
37
8
0
1
0
0
1
3
0
0

12 rows selected

BUT: what I what is like this

Jan Feb Mars April May ...
0 2 37 8 0 ...

it_count_tc is a function returning a varray of 12 integers input is a varchar2 string

View 2 Replies View Related

SQL & PL/SQL :: Version Wise Objects Information?

Dec 11, 2012

Is there any way to capture the version wise objects (Packages) information from the oracle data base.

Ex : I have package P1 which was created on 01-NOV-2012 with version 1 . After 10 days same package has been updated version 2 with some enhancements.Like that the package will be updated according to the latest requirement.

Now I need to capture the total audit trail history of the package with version wise specific changes and when changes has been occurred hoe can I achieve this?

View 2 Replies View Related

Security :: Audit Trail Information?

Jun 7, 2011

I am trying to setup logon/logoff auditing for our databases which reside in 9i and 10G on sun solaris servers. I am asked to turn on auditing sending the audit data to syslog! How exactly do you do that?

View 3 Replies View Related

SQL & PL/SQL :: Taking Selective Information From String?

Jan 31, 2011

I want to take selective output from String input like

Input:'Six Thousand * Four Hundred Seventy Two Rupees And Zero'

Output:'Six Thousand Four Hundred Seventy Two Rupees'

I am using substr and instr functions but not getting the desired output.

View 22 Replies View Related

Client Tools :: How To See Information Before Commit

Feb 24, 2013

I want to see the pending operations before commit. How can I view what operations are going to commit when I enter the commit.

View 7 Replies View Related

Forms :: Getting Hardware Information In Oracle 6i?

Feb 9, 2010

How Can We Get Hardware Information In Oracle forms 6i for example harddisk size,volume,processor etc.

View 1 Replies View Related

How To Collect Synonym Information And Compare

Oct 29, 2013

We are making Oracle Insight Application based on Oracle 11gR2 DB.We have two DEV environments. On Oct 17, we had trouble in one DEV environment.From dba_common_audit_trail , we found some user did drop public synonym statement and we doubt it as cause of the problem. I would like to collect synonym information of the two DEV environment.1How could I do that ?2And we are thinking of using Logminer to investigate the cause of problem.Logminer is usable to investigate the cause of problem if we have the REDO log of Oct 17 ?

View 5 Replies View Related

Swap Information Shows Zero In Trace File

Nov 8, 2010

I got the below message in trace file. What does the line "swap info: free = 0.00M alloc = 0.00M total = 0.00M" trying to say?

I have

RAM=1.5G
Swap=3.5G

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle
System name:Linux
Node name:linuxdev
Release:2.6.5-7.97-default
Version:#1 Fri Jul 2 14:21:59 UTC 2004

[Code]....

View 1 Replies View Related

Client Tools :: SqlPlus Version - How To Get Bit Information

Sep 26, 2013

Oracle client version whether it is of 32-bit or 64-bit.

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation. All rights reserved.

C:>sqlplus -v
SQL*Plus: Release 10.2.0.1.0 - Production
C:>

it shows me only version not bit information.is there any other command to get it.

View 26 Replies View Related

Server Administration :: Getting Wrong Information Through Views?

Feb 7, 2013

I am getting unexpected issue while fetching records from dba_ts_quotas & DBA_USERS Views.

FYI..
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
where username='DWHODS'
USERNAMEDEFAULT_TABLESPACETEMPORARY_TABLESPACE
DWHODS DWHODS_TBS_DATADWHODS_TEMP_DATA
select tablespace_name, username, bytes / 1024 / 1024 "Used MB",

[code].....

View dba_ts_quotas is giving tablespace name "USERS" where DBA_USERS showing default tablespace name "DWHODS_TBS_DATA".

View 1 Replies View Related







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