All Questions
43 questions
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
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 ...
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 ...
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, ...
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 ...
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 ...
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.
...
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 ...
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;...
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'...
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 ...
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. ...
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 ...
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.
...
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:
...
-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 ...
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);...
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 ...
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?
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 ...
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 ...
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 ...
-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 ...
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 ...
-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 ...
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 ...
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 (...
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 ...
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 ...
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....
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.
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 ...
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 ...
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 ...
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 ...
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 ...
-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 ...
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 ...
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','...
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','...
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 ...
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)
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 ...