SQL & PL/SQL :: Privilege - Just Drop Special One Table?

Jul 3, 2013

There two users a and b,and the table b.test_part.And one procedure under a ,text like below:

create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;

now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Drop Table With Special Character?

Jul 15, 2011

I have created a table with space by mistake.how to drop that..?

QL> select * from cat where table_name like 'OM_DW_RTNORD%';

TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE

[code]....

no rows selected

SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character

View 3 Replies View Related

PL/SQL :: How To Grant Privilege On User1 Based On Privilege Of User2

Jun 20, 2013

I have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?

View 2 Replies View Related

SQL & PL/SQL :: Direct Privilege Vs Privilege Through Roles

May 30, 2010

As we all know, privileges granted to a user through role are not visible from within a stored procedure. What is the reasoning behind this design?

Moreover, privileges granted through role are visible from anonymous PL/SQL block. Why such discrimination between anonymous PL/SQL and stored PL/SQL?

View 3 Replies View Related

SQL & PL/SQL :: How To Check If User Has (create Table) Privilege

Jun 29, 2011

1.How to check a user has 'create table' privilege?

2.how to check a user has privilege to grant 'create table' privilege to other user ?

View 6 Replies View Related

SQL & PL/SQL :: Privilege Required To Gather Table Statistics?

May 14, 2011

What privilege is required to gather table statistics using dbms_stats ?

View 2 Replies View Related

SQL & PL/SQL :: How To Check System Views When Create Table Privilege

Jan 4, 2013

I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.

How do i check from the system views if i have create table privilege under my own schema?

View 2 Replies View Related

Find Special DML On A Table

Apr 12, 2013

i'm just trying to find out DML Statements, that where executed on a special table at a defined time. By googeling i just found a statement to find out the last DML, that was executed on a table or something else. But i want to look into the past. How can i do this?

View 16 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

SQL & PL/SQL :: Finding Out If A Table Column Has Special Characters

Jul 15, 2010

I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).

I just need to fetch only those rows that has special characters. How do I get this?

CREATE TABLE CHECK_SPL_CHARAC
(ADDRESS VARCHAR2(100));

INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC%ABC');
INSERT INTO CHECK_SPL_CHARACVALUES ('ABC_ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC?ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC*ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('1234');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('AB C');
INSERT INTO CHECK_SPL_CHARAC VALUES ('12 34');
COMMIT;

I need to get the first 4 rows alone in the sql output. I do not wan to get the last 2 rows with space.

View 8 Replies View Related

Server Utilities :: Copy Table With Special Char Between Oracle 8 And 10?

Nov 17, 2010

I have to copy a table from oracle 8 to oracle 10
- dblink don't work between 8 - 10
- can I dump table ?
- when I try to use sqlload via TXT file, I have error messages due to a column containing 'special' caracters (
,..) and line too long (splitted in several rows)

how can I do ?

View 1 Replies View Related

SQL & PL/SQL :: Drop A Table?

Apr 6, 2010

When drop a table what happen to view,sequence,synonyms,index,constraint for the table

View 9 Replies View Related

SQL & PL/SQL :: Unable To Truncate / Drop Table

May 13, 2011

I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.

I am using Oracle 11g Release 11.1.0.7.0

View 17 Replies View Related

SQL & PL/SQL :: Drop Global Temporary Table?

Dec 6, 2011

how to drop global temporary table?

while droping global temporary table we are getting below error

"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"

View 1 Replies View Related

SQL & PL/SQL :: How To Drop Queue Table From Database

Nov 26, 2011

,how to drop the queue table from our database.when iam trying to drop the queue table aim getting this error

error dropping AQ$_DEMO_QUEUE_TABLE_G;
ORA-00942:TABLE OR VIEW DOES NOT EXIST

View 1 Replies View Related

SQL & PL/SQL :: How To Undo A Drop Table Operation

Feb 24, 2013

How do I perform Undo a drop table operation?

View 12 Replies View Related

SQL & PL/SQL :: Drop Index Which Has Constraint On Table?

May 18, 2011

How to drop an index which has a constraint on the table?

while droping the index on the table wheather i should remove the

constraint or disable the constraint or without droping the

constraint

View 1 Replies View Related

PL/SQL :: Truncating Or Drop Table Is Faster?

Jul 8, 2012

I have a table with 80 million records. Is dropping this large table is faster or truncating is faster?

View 6 Replies View Related

Drop Table Response Time?

Jul 23, 2012

I am working on 10.2.0.4 oracle version database of my production,

when executed a simple drop command , the total time it took is 26 secs. on Avg. the table is holding only 20 records. this is happening for the last few weeks , prior to that it took less than 0 secs.

05:22:58 SQL> drop table C$_100GL_INTERFACE ;

Table dropped.

Elapsed: 00:00:26.67

but on successive executions the elapsed time falls to 10-15secs.

And on the same test env, we are achieving the expected results less than 0 secs.

View 15 Replies View Related

Drop Some Partitions In Table On Production Environment

Jul 8, 2011

I have to drop some partitions in table on production environment (to get free space). The environment have to be continuously available. I was considering of use ALTER TABLE ... DROP PARTITION ... UPDATE INDEXES but it is slow, because of use clause UPDATE INDEXES. Is there another possibility to remove these data?

View 2 Replies View Related

Compressed Table - Drop Unused Column

Feb 9, 2012

what happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.

My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?

Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.

View 7 Replies View Related

SQL & PL/SQL :: Create And Drop Table Inside The Procedure

Jun 10, 2013

I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.

I cant track the error. Why is it so?

CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........

View 18 Replies View Related

Backup & Recovery :: Recover Drop Table

Dec 29, 2011

How i can recover my drop table..i use user managed backup concept after dropping table i cant recover my table..how i use step that my dropped table recover.

View 12 Replies View Related

Drop Statement On Table Taking Long Time

Sep 20, 2010

We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .

When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.

We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.

The same operation in windows is taking very less time when compared to AIX.

View 5 Replies View Related

Server Administration :: How To Drop A Column In Huge Table

Aug 8, 2011

I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.

Is there any quickly way to drop a column in a huge table?

View 5 Replies View Related

SQL & PL/SQL :: Unable To Drop Interim Table After Redefinition Process

May 11, 2012

I performed the Table Redefinition process on one of my transaction tables. I wanted to redefine the existing table to a new table with partitions. Everything worked fine until i reached the last step where we had to drop the interim table. Now in between the redefinition , we created the interim table with partition along with the constraints and indexes and then atlast ran the "dbms_redefinition.finish_redef_table" proc to finish the redefinition.

And after that when I tried to drop the interim table , I am getting the below error message

"ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys"

So this means all the constraints from my actual table were swapped with the interim table and hence I am getting this error.So is this a normal case or I have missed something while performing the redefinition? If thats normal then should i Just Drop table tablename CASCADE CONSTRAINTS?

View 10 Replies View Related

Backup & Recovery :: RMAN - How To Recover Drop Table

Jan 3, 2012

how i can recover my drop table using until time in rman scenario.

View 3 Replies View Related

Server Administration :: Drop A Table Moved To Recycle Bin?

Jan 6, 2012

I have a question about recyclebin.When i drop a table,the talbe will be moved to recyclebin,the name is changed to BIN$...,but the constraint built on the table aren't moved to recyclebin and their name are also BIN$...,why?

View 1 Replies View Related

Server Administration :: Unable To Drop User - Table Or View Does Not Exist

Jun 6, 2011

Im facing the problem whenever I try to drop a user. Following thing that I m trying..

system@vahan> drop user knp cascade;

Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

View 4 Replies View Related

PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?

Apr 24, 2013

I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:

CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;

Recreate Steps:

1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data

[code]...

Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:

ALTER TABLE admin_docindex MOVE;

View 3 Replies View Related







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