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


ADVERTISEMENT

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

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

SQL & PL/SQL :: Rebuild All Indexes For The Tables In Control Table?

Mar 6, 2013

I have one control table as below.I want to rebuild all indexes for the tables in control table.

The control table is having the following data.

SEQ_IDTABLENAME SCHEMA_NAME
1GEDIS_ORDER_FORM_STATES ALL
2GEDIS_NOTES ALL
3GEDIS_CARD_TYPE_AUDIT APRT
4FAX_HEADER OMS

In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).

I tried the following code it is executing for all 30 schemas(ALL).But it is not executing for specific schemas.

CREATE OR REPLACE PROCEDURE Rebuilding_index
IS
l_sql VARCHAR2(4000);
CURSOR cur_tab_schema
IS
SELECT tablename,schema_name
FROM control_table3;

[code]....

This contains the 30 schema names.

SELECT owner_name FROM global_bu_mapping;

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

Performance Tuning :: Table Rebuild Should Be Done After Removing Most Of Its Data?

Oct 23, 2012

We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.

However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?

I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?

View 4 Replies View Related

Server Administration :: Index And Table Rebuild Method

Jan 17, 2012

The best way to rebuild index and table and also give the reason as why we need to use this method?

View 10 Replies View Related

Rebuild Local Index Partition Is Using Full Table Scan

Apr 23, 2012

I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.

The DDL of the partition table/indexes:
=========================
CREATE TABLE TESTME
( INST_NO CHAR(3) NOT NULL,
ACCT_NO CHAR(16) NOT NULL,
REC_NO CHAR(9) NOT NULL,
TRAN_TYPE CHAR(2) DEFAULT ' ',
STAT CHAR(2) DEFAULT ' ',
[code]...

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

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 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 :: 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

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

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

SQL & PL/SQL :: Analyze And Rebuild The Index?

Apr 11, 2011

I need to Analyze and Rebuild the indexes with a script at once for a schema.

View 30 Replies View Related

Oracle Sys Index Rebuild?

May 28, 2012

Below Table Index are rebuild,This table are relation with sys user or not?after rebuild index, getting error in alert log file

ORA-00600: internal error code, arguments: [psdnop-1], [1410], [], [], [], [], [], [], [], [], [], []
ORA-01410: invalid ROWID

when trying to select * from dba_network_acls getting error massage Rowid invalid

ALTER INDEX CTXSYS.SYS_C003684 REBUILD;
ALTER INDEX CTXSYS.SYS_C003683 REBUILD;
ALTER INDEX CTXSYS.SYS_C003682 REBUILD;
ALTER INDEX CTXSYS.SYS_C003681 REBUILD;

[code]...

It is possible to recreate this index again.......

View 1 Replies View Related

Check That Indexes Need Rebuild?

Jul 31, 2013

How can i check that indexes need rebuild?

View 6 Replies View Related

SQL & PL/SQL :: Rebuild And Move Not Working As Expected

Nov 11, 2011

I ran below query and got the indexes to be rebuild:

SELECT (CASE
WHEN b.partitioned = 'NO'
THEN 'alter index '
|| b.owner
|| '.'
|| b.index_name
|| ' rebuild online; '
--|| b.initial_extent
[code]....

Why the fragmented size is not reducing.

View 14 Replies View Related

SQL & PL/SQL :: Procedure To Rebuild Partitioned Indexes

Oct 3, 2012

I have a task to create a procedure that disables (particular) partitioned indexes and REBUILD after ETL load.

Developers wants 3 parameters for the procedure:

1) TABLE_OWNER2) TABLE_NAME3) TRANSACTION_DATE = 13-JUN-12

And here is what I have done. I have created a procedure that takes 3 input parameters, please see the attached script:

1) TABLE_OWNER 2) TABLE_NAME 3) PARTITION_NAME - requires to query the particular partition to get the partition name

a situation where they will input dates as the partition was on a DATE column, now my challenge is how to incorporate this into the procedure to accept DATE as an input which will require one to query the particular table to get dates.I thought of using (HIGH_VALUE - 1) to get the dates from ALL_IND_PARTITIONS.

View 2 Replies View Related

Server Administration :: Indexes To Be Rebuild?

Jan 30, 2010

How to find indexes which are eligible to rebuild in the database environment? Is there any query to find it?

View 7 Replies View Related







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