What SQL Command To Find Index Of A Table

May 14, 2007

1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.

P/s: I don't have OEM installed so I must use SQL command

Example of dropping the index
ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

View 2 Replies


ADVERTISEMENT

Server Administration :: How To Find Which Table Or Index Needs To Be Reorg

Apr 21, 2009

How to find which table or Index needs to be reorg..??

IS there any Query!!!.

View 10 Replies View Related

SQL & PL/SQL :: How To Create Dynamically ALTER INDEX Command

Nov 15, 2010

how I can create an index dynamically? The DBA wants me to put this code below in the beginning of a PL/SQL package. At first, I tried by just putting the below syntax in the code, but I get an 'Alter' is not a valid identifier.

alter index ZZAP_selected_invoices_n1 rebuild online;

And then once the job completes...

alter index ZZAP_selected_invoices_n1 unusable;

View 8 Replies View Related

SQL & PL/SQL :: How To Find Out Which Rows Affected By Update Command

Dec 7, 2010

Assume that we have table Test with a column TestId and Status. This table is having 10,000 records. Now, we need to write a stored procedure that does the following:

1) Select top 10 rows whose "Status = NEW'

2) Update the selected top 10 rows with "Status = PROCESSING"

3) Return those top 10 rows.

Assuming there are hundreds of such requests coming to Oracle database, how can we make sure each request NEVER gets a duplicate record at any given point of time and how to fetch the records that have been updated individual request?

View 1 Replies View Related

Real Application Clusters :: How To Find VIP From Command Line

Oct 1, 2013

find VIP in RAC from command line.

View 2 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

SQL & PL/SQL :: Find Tablespace Used By Index?

Aug 27, 2010

Is there a way to find the tablespace used by an index?

View 4 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

Query To Find Out Whether Exactly 2 Columns Are Used In Any Index

Dec 17, 2012

Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index

Ex. create index test1 on customer (ID , Status )

But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?

View 23 Replies View Related

Performance Tuning :: How To Find Index To Rebuilt

Oct 3, 2011

i am trying to find the index want to rebuild or not for that i have analyzed that index after that i don't know how to calculate the ration could any one steps to do calculate the following ratio

Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

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

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

Brtools Command Versus Oracle Command For Shutdown And Startup (statistics)

Sep 13, 2013

we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?

View 3 Replies View Related

Oracle 8 Table Export / Import Command

Apr 29, 2009

I need to truncate a database, though before doing that, want an export of one the tables. This will be re-imported once the truncate is complete.

What is the correct export syntax for Oracle 8? And possibly the import?

View 3 Replies View Related

SQL & PL/SQL :: Update Long String In Table By Command

Jan 23, 2011

I use sqlplus in oracle (linux).I have a table and the string cell have long string .

Like below :

column A Column B

A BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
.....................................BBBBBBBBBBB

So, I need to edit/update the row A and the value in Column B.But the string in Column B is so long and I only need to edit one character.IF I use update command , I need to type very long string and it is easy to wrong edit .

View 8 Replies View Related

SQL & PL/SQL :: Include Tablespace In Create Table Command

Oct 5, 2011

I want to creat a table using Execute Immediate that includes tablespace name in the create table command. But I don't know the tablespace name that the client is using. Can I incorporate in a script? If yes, then how?

I have written the following proc. While executing , it gives me the error:
ORA-00959: tablespace 'V_TABLESPACE_NAME' does not exist.

create or replace procedure pr_create_table as
v_tablespace_name varchar2(4000);
begin
select distinct tablespace_name into v_tablespace_name
from user_tables
where table_name like 'ABC%';
[code]....

View 20 Replies View Related

SQL & PL/SQL :: Insert Into Table - Command Not Properly Ended

Oct 23, 2012

INSERT INTO Table (NID, NType) SELECT DTreeNotifySequence.NextVal, 1 FROM Dee WHERE ID =354344 commit;

I get the following error:

ORA-00933: SQL command not properly ended

I tried it in several ways...are more semicolons needed?

View 4 Replies View Related

SQL & PL/SQL :: Sort Names In Table Without Using Group By Command

Jul 3, 2011

how to sort the names in table without using group by command?.

View 3 Replies View Related

SQL & PL/SQL :: Full Table Scan On Index Table

Feb 3, 2012

when i am Executing the following statement

SELECT DISTINCT EXPOSURE_REF FROM KBNAS.VW_EXPOSUREDETS_FOR_CCYREVAL
WHERE EXPOSURE_CURRENCY='THB' AND BASE_TXN_CCY='USD' AND BRANCH_CODE='7000'
AND (REVAL_STATUS='O') AND CONV_RATE<>'62' AND (EXPOSURE_AMOUNT<>0)
UNION
SELECT DISTINCT ED.EXPOSURE_REF FROM KBNAS.EXPOSURE_DETAILS ED,
[code].....

I have attached DDL for table EXPOSURE_DETAIL(PARTITION),LEDGERCARD,LEDGERCARDDETAILS, DDL for INDEX on those tables and DDL for Views..

Issue: we have created the Indexes but when we check the explain plain .. full table scan is going on..I have attached the explain plan ..

View 11 Replies View Related

PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies View Related

SQL & PL/SQL :: Update Table With Join / ORA-00933 / Command Not Properly Ended

Jan 18, 2013

I'm unable to get the below update SQL to run in Oracle, it's giving me th below error

ORA-00933: SQL command not properly ended.

UPDATE
PDR.PH_Family_Match_by_Chassis a
SET a.Launched = 'Y'
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id

[code]....

View 8 Replies View Related

Forms :: How To Use ALTER TABLE Command To Disable / Enable Constraints

Aug 11, 2010

can you use alter table command to disable/enable constraints in a form if you can how, if you cant why

View 4 Replies View Related

Server Administration :: Command To Determine Which Table Can Benefit From Shrinking

Apr 14, 2010

I use following command to determine which table can benefit from shrinking

select * from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc

then i give following command to get reclaimable space

alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;

in table t1 427MB was shown as reclaimable space, after executing above commands, i run dbms_space procedure again to check the out come, but result was same.I understand tablespaces are by default ASSM in 11g, none of table has LONG datatype or LOB indexes or MVIEW with ON COMMIT.

View 2 Replies View Related

SQL & PL/SQL :: Trigger (Alter Command) And Creating Meta Data Table

Sep 20, 2011

i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)

CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/

Meta table contains Table name and column name..i attached the table data in atext file

View 39 Replies View Related

SQL & PL/SQL :: Use Of Table Index?

Feb 10, 2012

Am pasting the sample code here, which i got from some site..

DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;

[code]...

Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?

I have tried using this, but doesnt work because loop variables are by default integers:

for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;

View 2 Replies View Related

How To Partition And Index The Table

Oct 24, 2011

how to partition and index my table for a special problem.

The table:

CREATE TABLE TEST (
ID NUMBER PRIMARY KEY,
U_VALUE NUMBER NOT NULL, -- Ranges from 0 - 30.000.000
O_VALUE NUMBER NOT NULL, -- Ranges from U_VALUE - 30.000.000
CREATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
ITEM_TYPE NUMBER NOT NULL --<< Only 4 different values >>
);

As you can see, U_VALUE is ALWAYS lower than O_VALUE.I need to have the CREATE_TS in either main- or subpartition do drop the partitions after some time so i don,t have to use DELETE statements. The table has 360 millions rows.

The application has only 8 query which will always use a WHERE clause like this:

SELECT * FROM TEST
WHERE U_VALUE <= :1 AND O_VALUE => :2 AND ITEM_TYPE = :3

1. Is there any good technique how to create a good index for the queries (application will execute 120 queries per second)?

2. how to partition this table?

View 2 Replies View Related

SQL & PL/SQL :: How To Know Index Is Created On Table Or Not

Jun 30, 2011

how to know weather procedure is working or not in a database ..

how to know index is created on table or not.

View 2 Replies View Related

SQL & PL/SQL :: NULL Index Table Key Value

Sep 26, 2010

We are getting the below error on a piece of code that chas been functioning fine for over 4 years..

ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "pd.pf33", line 148

The line it's pointing at is merely a FOR loop that opens a cursor (select query) and loops through the items.

FOR rec IN csr_cash
LOOP
END LOOP

This piece of code has been functioning fine for years.. and never got this kind of error on a for loop. Could it be something internal to Oracle/underlying memory issues?

View 8 Replies View Related

SQL & PL/SQL :: Two Index Exist On Same Table

Apr 19, 2011

Can there be an impact on performance if below two index exists on the same table:

t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));

View 5 Replies View Related







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