Skip to main content

All Questions

Tagged with
Filter by
Sorted by
Tagged with
0 votes
1 answer
147 views

How to COPY table privileges dba_sys_privs into different table in Oracle

i want to copy table privileges dba_sys_privs in table A to new table B. i have create table B. What left now was it's table privileges'. Kindly advice. Thanks
Ahmad Hasanul Ishraf Shuib's user avatar
0 votes
1 answer
378 views

Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL

I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
Niki's user avatar
  • 1
0 votes
1 answer
80 views

Flyway Migrate - External Table Create Throwing ORA-00922: missing or invalid option

We use flyway in our company to make changes to the various regions. The guy who set all this up is unfortunately left the company so I don't have anyone really to run this past. I have a bit of SQL ...
mcquaim's user avatar
  • 11
0 votes
1 answer
125 views

create table based on another table

I have a table called " Product " it has: ID_P, Name, expiration date Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
kylie's user avatar
  • 39
0 votes
1 answer
390 views

Oracle - Big Table Archiving

Version: Oracle 12cR2 EE The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2 Table size: 95GB Table row count: 20M Table partitioned? No Indexes on table: there ...
jrdba123's user avatar
0 votes
1 answer
291 views

Need help identifying how to make a function selectable using table() clause and a custom data type

I would like to be able to run the following code to select records: Desired Syntax select * from table(my_new_package.get_data(123)) I have been successful in creating code that compiles, but I am ...
Eneerge's user avatar
  • 23
0 votes
0 answers
238 views

BFILE datatype in mysql

My backend is writing The Soap request within a table using a BLOB column so that I have very big tables in mysql with more of 100GB. I need store this information out of dabase in file for example. ...
acano's user avatar
  • 60
5 votes
2 answers
2k views

Table size grows indefinitely after deleting and then inserting the same data

I create a table and populate it with data: create table tab as select 'value' || level val from dual connect by level <= 10000; If I then delete all rows and insert the same data: delete from ...
Adamantium's user avatar
0 votes
1 answer
197 views

Creating a table in Oracle procedure [closed]

I am trying to create a table inside a procedure but getting some issues related to quote identifier or may be something else. BEGIN EXECUTE IMMEDIATE 'Create newTable as select * from oldTable'; END;...
vikrant rana's user avatar
1 vote
3 answers
750 views

Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

I have 2 queries SELECT * FROM sys.dba_indexes WHERE Table_Name = 'XDB$RESOURCE' and SELECT * FROM sys.dba_tables WHERE Table_Name = 'XDB$RESOURCE' Are there any reasons why a table wouldn'...
Ruanne Bornman's user avatar
1 vote
2 answers
3k views

Table function in oracle

I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that ...
user2271033's user avatar
1 vote
1 answer
2k views

How to deal with a table that exists in the ALL_OBJECTS table but cannot be selected

I am seeing a weird behaviour in my Oracle DB schema around a 'missing' table. The end goal is to gather statistics for all the tables that our team uses. All those tables have a common prefix - rca. ...
Sarath Chandra's user avatar
0 votes
1 answer
63 views

Insert iterated values from one table to another in OracleDB

I'm pretty new in Oracle universe and I need help. I have to iterate through Users Table and insert these 3 specific rows into Task_Group Table. The hard thing to do is to check if these rows even ...
NewJavaEnthusiast's user avatar
0 votes
1 answer
651 views

External Table: VARCHAR in Create but CHAR in Access Parameters

Consider this SO Question UTF-8 And External Tables. A proposed solution contains VARCHAR2 columns in the CREATE section but those same columns get CHAR attributes in the ACCESS PARAMETERS section. ...
zundarz's user avatar
  • 103
1 vote
0 answers
842 views

What are the advantages of using a Materialized View directly on a table?

Recently, in enterprise's Oracle database, I have seen some materialized views that filter directly columns of table without adding aggregate fields and without calling custom functions. Example: ...
schlebe's user avatar
  • 191
-1 votes
2 answers
160 views

How to display null records/rows from table

For ex: I have a record with name = "sga_target", and no record exists where name = "_exafusion_enabled". I still want to display a row with "_exafusion_enabled" as name and NULL as value, without ...
satya's user avatar
  • 11
5 votes
1 answer
2k views

Same table alias for two different tables (Oracle)

This example has the same table alias used for two different tables. I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense. create table Table_A (x number);...
Tom Warfield's user avatar
1 vote
2 answers
4k views

Table behind synonym does not exist

when I do select * from all_synonyms where synonym_name='SYNAAA' I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A. Given SYNAA is public, I should be able to query it ...
zzzz's user avatar
  • 11
0 votes
1 answer
48 views

Why the length of JOBHISTORY field is 172 here?

It should be 150. Is Varray allocating some extra space?
Parth Mangukiya's user avatar
4 votes
2 answers
261 views

Alert on Table size

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system ...
Sakshi Sharma's user avatar
3 votes
2 answers
4k views

How do I swap tables atomically in Oracle?

How to do I swap two tables in an atomic fashion in Oracle? I would like to perform the equivalent of: ALTER TABLE foo RENAME foo_tmp; ALTER TABLE foo_new RENAME foo; but what happens if a query ...
Hans Deragon's user avatar
1 vote
1 answer
83 views

Naming convention for automated table outputs (processed with Python)

I have several tables that I process nightly with Python. For example, I have read-only access to an incidents table. I process the table with a specialized Python library called ArcPy. The script ...
User1974's user avatar
  • 1,497
-1 votes
1 answer
10k views

Adding data to a newly added column

I added an extra column to my table and now need to populate it against the data already there. As a dummy example, I have a table already named customers with CustomerID, FirstName, LastName. I've ...
Aaron's user avatar
  • 1
0 votes
1 answer
992 views

Range Partitioning Oracle

I need to understand how partitioning can be applied to tables for an oracle 11.2.0.4.0 database. Please start a guide with me that hopefully with helps others who would like to learn as well. After ...
Rostricalv's user avatar
-1 votes
2 answers
2k views

How can I estimate the size of a new table?

We have tables with a lot of data (50M new rows each month, just in one table). Now I want to know what would be the impact of adding a new column to those tables, or creating a new table. Something ...
greenkey's user avatar
  • 109
1 vote
4 answers
4k views

Update data of one table from another with comparing columns have same initial strings

I have two tables: NewData: EName Job Sal smith clerk 2000 allen sales 2000 jones.domain.com Manager 6000 OldData: EMPNO ...
Yuvraj 's user avatar
0 votes
1 answer
151 views

Unable to view partition table in a schema [closed]

I am using oracle 11g enterprise edition, I have created a schema as "TESTSCHEMA" and in it i have created a partition table as "sales_test". The query for the table, create table sales_test (...
Mohammed Tabish's user avatar
2 votes
1 answer
2k views

Setting parameters like PCTUSED when creating new tables in ORACLE 12 C

When creating new tables in ORACLE 12 C, do we need to explicitly set parameters like PCTUSED, PCTFREE, INITRANS, MAXTRANS, STORAGE etc. (as shown in example below)? Or will ORACLE automatically set ...
Mukul Pandey's user avatar
3 votes
1 answer
10k views

Importing .dmp file into SQL developer: Do I need to import seperately for each table?

I have a large .dmp file which I would like to import into SQL developer. This contains all the data I want to import across several Schemas. Do I need to split this up into each separate table in ...
java123999's user avatar
0 votes
1 answer
31k views

ORA-00942: table or view does not exist- Can it be solved by granting privileges to user?

I am creating a Java application that connects to an Oracle Database. In the application I connect to the DB as User A, which works as intended. I then try to access tables in Schema 1 and Schema 2....
java123999's user avatar
0 votes
3 answers
4k views

How can we get the table and the column name of known data value?

I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.
SVS's user avatar
  • 1
1 vote
2 answers
3k views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...
Blued00d's user avatar
0 votes
1 answer
57 views

How to persist a lot of boolean fields?

I need to persist on Oracle Db the follow check box table : The simple thing that I can imagine is to create a Table with 15 boolean fields, but since that the rows and the columns could increase I ...
user2776875's user avatar
2 votes
1 answer
63 views

A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?

We have a table that accumulates messages from local air traffic. It's approximately 5k-20k data rows a day that will only be actively accessed for a week at most. Afterwards it's just a log in case ...
Zeks's user avatar
  • 121
2 votes
2 answers
14k views

ORA-02049 and ORA-02063 Errors

Select For UPDATE NOWAIT is performed on a table by 20 processes in parallel The error below is returned by the 4 processes out of 20 (16 processes are successful) SQL_UPDATE_ERROR- : SQL update ...
Avnish Garg's user avatar
1 vote
1 answer
16k views

Copying a table in SQL Server database to Oracle database

I am new to database management. I need to copy a particular table "Attendance" in SQL Server database to Oracle database. There is no connection between the two databases. Also, "Attendance" is ...
Pribhat's user avatar
  • 111
-1 votes
3 answers
9k views

Full table scan even after adding index

i have two tables in Oracle 11g server one is partitioned and another is having a 3GB of data. I've added a composite index on both the tables based on the where clause, in the order it appears. The ...
Reuben Emmanuel Joshua's user avatar
4 votes
3 answers
1k views

Is there a good way to link tables and sequences?

I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships. Is there some ...
JonnyRaa's user avatar
  • 279
0 votes
1 answer
1k views

How to generate create table script without primary and foreign key in Oracle [duplicate]

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','...
Omer's user avatar
  • 433
3 votes
1 answer
11k views

How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','...
Omer's user avatar
  • 433
1 vote
1 answer
2k views

Drop table without its dependencies and solve block corruption

I have found alert about corrupted block in alert log. Corrupted block belonged to the table named DOCS. Because of I don't have any backup of this database, decided to recreate DOCS table to solve ...
kupa's user avatar
  • 1,717
3 votes
1 answer
459 views

How many blocks is my database

How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)
Phil_Charly's user avatar
0 votes
1 answer
657 views

Save and restore specific tables

in an Oracle 11.2 Database, Enterprise Edition, we are running calculations divided into 3 different "modules". These modules can be started on their own, but not parallel, so in between users are ...
semTex's user avatar
  • 101