SQL & PL/SQL :: Describe Table Structure Of A Schema?

Apr 21, 2010

i would like to view all table statructure of a schema in a file. i need to take print out of it for one analysys.

i did like this

exp user/pwd file=dumpfile.dmp full=y rows=n log=logfilename.log

imp user/pwd file=dumpfile.dmp full=y indexfile=indexfile.sql logfile=logfilename.log

but

its look like this i could not format

REM CREATE TABLE "SYSTEM"."DEF$_AQCALL" ("Q_NAME" VARCHAR2(30), "MSGID"
REM RAW(16), "CORRID" VARCHAR2(128), "PRIORITY" NUMBER, "STATE" NUMBER,
REM "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO"
REM TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN"
REM NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" NUMBER,

[Code] ..........

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Finding User Who Made Changes In Table Structure Or Schema

Sep 21, 2011

i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?

following Output needed

userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername

View 11 Replies View Related

Server Utilities :: How To Export Table Structure As Script In All Schema

Jan 9, 2011

how can i export all table structure as script

note : i have multi schema's not one schema

i use

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM DBA_TABLES u;

but i need it for all schemas

View 3 Replies View Related

Forms :: Show Organization Structure In Top Down Organizational Structure?

Sep 3, 2012

I am struck in a requirement to show the organization structure in a Top Down Organizational structure.

Is there any way we can make the well structured report OR form?

View 4 Replies View Related

SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?

Jul 14, 2012

what command is used to create a table by copying the structure of another table including constraints ?

View 2 Replies View Related

SQL & PL/SQL :: Rebuild The Table Structure?

Feb 7, 2013

--this for txn details
CREATE TABLE txn_det(
txnid NUMBER PRIMARY KEY,
amount NUMBER,
status varchar2(50),
cust_id NUMBER);
----this for customer details
CREATE TABLE cust_det(
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50),
cust_acc number(15));

--data to insert for customer table
INSERT INTO cust_det VALUES(101,'Miller','12345');

INSERT INTO cust_det VALUES(201,'Scott','45678');
----data to insert for txn table
INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);

INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);

insert into txn_det values('tx0049',78.12,'success',101);

INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);

Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.

View 30 Replies View Related

SQL & PL/SQL :: Take Table Structure In Oracle?

Jul 11, 2012

How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"

any other way to get it? I need like table name field name datatype

View 11 Replies View Related

SQL & PL/SQL :: Create A Temporary Table With Same Structure

Mar 13, 2013

create table my_rows
(
my_envvarchar2(100),
anumber(2),
bnumber(2)
)
/
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
[code]....

The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.

View 9 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.

How could I do this without using export import. I am using TOAD 9.0.1.

View 10 Replies View Related

Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table

Nov 3, 2012

I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported.

View 3 Replies View Related

Datapump - Importing With Inconsistent Table Structure

Apr 30, 2013

I am receiving two large export files from a vendor, so I have no control over the contents. I need to import these into our database. The two export files are very similar, except the one has slightly differenet columns in it. So, export file 1 may have a table:

COLUMN_A
COLUMN_B
COLUMN_C

The second file may have:

COLUMN_A
COLUMN_B
COLUMN_D

At the destination, I have a table that has:

COLUMN_A
COLUMN_B
COLUMN_C
COLUMN_D

Is there a parameter that would let me interchangably import either (or both) files into this destination table? This is my first attempt at data pump - but I know using import this has caused me issues. Not sure if the same limitations exist? Will the missing columns cause it to fail?

View 3 Replies View Related

PUMP Export Full But Some Table Just Structure Only?

Nov 16, 2012

my working is relating with PUMP of oracle.

I would like to use command, for ex:
expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp COMPRESSION=ALL LOGFILE=export.log SCHEMAS=hr

But some tables in Schema HR, I don't want to export data, just only need table structure.

View 2 Replies View Related

Oracle Datapump - Table Structure Change?

Oct 19, 2010

we have daily partitioned table, and for backup we are using data pump (expdp). we policy to drop partition after backup (archiving).

we have archived dump files for 1year, few days back developer made changes with table structure they added one new column to table.

Now we are unable to restore old partitions is there a way to restore partition if new column added / dropped from currect table.

View 4 Replies View Related

Server Utilities :: Exporting Table Structure From A Particular DB?

Nov 9, 2010

know the process of exporting only the table structure of a Database without the actual content of it.

Note:: I don't know how many tables are present in the DB.

View 1 Replies View Related

SQL & PL/SQL :: How To Identify Which User Has Changed Table Structure

Jun 21, 2012

a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.

View 2 Replies View Related

SQL & PL/SQL :: Create Trigger On Certain Column For Table Structure

Nov 3, 2011

create trigger on certain column for table structure.

SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....

I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .

Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)

Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column

View 3 Replies View Related

SQL & PL/SQL :: Function Returning Table Of Dynamic Structure

Apr 24, 2013

I have a requirement to be coded like this:

A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,

means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type

func f return pl_tab
as
...
end;

note : pl_tab_type will vary for each run of function f

i.e.,for example, pl_tab_type can be changed to as follows:

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))

how to return pl/sql table of dynamic type from func,

View 12 Replies View Related

Find Details Of User Who Modified Structure Of Table?

Aug 3, 2011

I have a small question is it possible to find the details of a user who modified the structure of a table, including what command he ran to change the structure of the table?

View 2 Replies View Related

SQL & PL/SQL :: Constraints In Describe

Nov 1, 2013

I want to display columns along with their constraints using describe command

Is it possible?

SQL> desc dept;
Name Null? Type
----------------------------------------- -------- -------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

View 1 Replies View Related

Precompilers, OCI & OCCI :: Receiving Records Of Table In Array Of Structure

Mar 12, 2009

sample code in OCI in C for receiving records of table in array of structure? Or dynamically storing the result-set in an array..using array of pointers to structure..

View 6 Replies View Related

Precompilers, OCI & OCCI :: Using Pointer To Structure For Selecting Multiple Rows From Table

May 11, 2010

I am working on Pro*C and i have a requirement where i need to select all the rows from a table into a c - structure variable. Since i get to know the no of rows in the table which is getting selected only at run time, i need to create a pointer variable to the structure and i'll allocate the size to it based on the count of rows in the table using malloc or calloc.I tried allocating memory using calloc and it does not show any error. But when i when the exec select statement run it shows an error.

Statements i have used:
struct common *comp;
struct common_ind *comp_i;

comp = (struct common*) calloc(rowcount, sizeof(struct common));
comp_i = (struct common_ind*) calloc(rowcount, sizeof(struct common_ind));

exec sql at db1 select * into :comp indicator :comp_i from tab1;

Error i get :

Stop Error: -2112
Stop Error: -1012
Stop Error: -1012

View 2 Replies View Related

Precompilers, OCI & OCCI :: Describe STP In Package

Nov 26, 2009

I am trying to describe an STP in a package, but it gives me an error.

e.g. In package ABC suppose there is an STP XYZ, I am trying to describe ABC.XYZ function but it gives me an error code 4043 and error message object XYZ.ABC does not exist.

View 2 Replies View Related

SQL & PL/SQL :: How To Describe Number Length In VIEW

Sep 30, 2011

in the case below, when i created VIEW to add a new Department for some reason, its length is un-identified but i want the length as describe in table, how i can get ?

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

devtest@ Test.DB> desc dept
Name Null? Type
----------------------------------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

devtest@ Test.DB> l
1 select deptno, dname, loc from dept
2 union all
3 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
4* from dual
devtest@ Test.DB> create view my_dept as
2 select deptno, dname, loc from dept
3 union all
4 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
5 from dual;
[code]....

View 4 Replies View Related

Client Tools :: Script To Describe Tables?

Jun 1, 2010

how to create a script that will describe only the structure of 150 tables of a database.have it just like this, but I cannot produce the name of the table:

spool desctab.sql
select 'desc '||owner||'.'||table_name||'' from all_tables where owner='SKIMA'
order by table_name;
spool off

.. the output of the spool file is something like this:

Name Null? Type
----------------------------------------- -------- -------------
OFC_CODE VARCHAR2(3)
OFFICE VARCHAR2(35)
DESCRIPTION VARCHAR2(50)
REMARKS VARCHAR2(200)
OFFICER VARCHAR2(35)

View 4 Replies View Related

SQL & PL/SQL :: Describe Results / Define Output And Fetch Rows Of A Query

Jun 2, 2010

There are several stages for sql processing in 10g2 database concept document.The following stages are necessary for each type of statement processing:

■ Stage 1: Create a Cursor
■ Stage 2: Parse the Statement
■ Stage 5: Bind Any Variables
■ Stage 7: Run the Statement
■ Stage 9: Close the Cursor
Optionally, you can include another stage:
■ Stage 6: Parallelize the Statement

Queries (SELECTs) require several additional stages, as shown in Figure 241:

■ Stage 3: Describe Results of a Query
■ Stage 4: Define Output of a Query
■ Stage 8: Fetch Rows of a Query

Stage 3: Describe Results of a Query The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4: Define Output of a Query In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary.

I still don't understand what's Stage 3: Describe Results of a Query and Stage 4: Define Output of a Query.

View 2 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Similar Table Structure Having Similar Indexes?

Sep 24, 2010

I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?

View 2 Replies View Related

Creating Table From Existing Table In Another Schema?

Jan 4, 2009

I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?

View 4 Replies View Related

SQL & PL/SQL :: Update A Table Using Another Schema Table Of Same Style?

Aug 14, 2011

Can we update a table using another schema table of same style?

Example:

Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;

View 8 Replies View Related

How To Retrieve Table Schema

May 22, 2007

I'd like to know which SQL command (if there is any) I can use to display the current schema of a table. I'm particular interested in knowing which columns are indexes.My privileges to the DB are quite basic (I think that I'nm only allowed to run SELECT statements

This is the Oracle version that I'm using:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

View 2 Replies View Related







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