SQL & PL/SQL :: How To Check Object (table / Index) Growth Day Wise
Apr 20, 2011DB:Oracle 9i
os :solaris 8
how to check the object(table,index) growth day wise?
ADVERTISEMENT
Growth Of Database Month Wise?
Feb 24, 2013how to find out growth of database month wise?
according to me, v$datafile can be use to know growth in database in terms of datafiles added in past months as per creation_time. but hows about those existing datafiles whose size has been re size more .
Whats your inputs on this ??
SQL & PL/SQL :: Index Of Table Type Object
Apr 4, 2012I have a table type object loaded (tto) with data and am using it as :
SELECT tto.B, tto.C
FROM TABLE(tto)
WHERE tto.A = <some value>;
This is working fine. BUT, can I also select the index of each element too along with other fields of each element ?
Server Utilities :: Processing Object Type SCHEMA_EXPORT / TABLE / INDEX
Apr 28, 2011I am importing the dump.All the tables are getting imported but when it comes to the part as shown in the error it gets struck there and nothing is showed after.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
The dumps for which I am doing the import is huge. Its around 46 GB.
select
sl.sid,
sl.serial#,
sl.sofar,
sl.totalwork
from
v$session_longops sl,
v$session s,
dba_datapump_sessions d
where
s.saddr = d.saddr
and s.sid = sl.sid
and s.serial# = sl.serial#
order by start_time;
It showing the lock for two sid's. Should I kill the process and import the dump again? I used below command to import dums
impdp sys/*****@dbname directory=dump_dir dumpfile=prod_dump_%U_200311 SCHEMAS=int_aaa int_bbb int_ccc int_ddd int_eee int_fff
int_ggg int_hhh int_iii int_jjj int_kkk int_lll
Check All Object Privileges
Sep 6, 2011How can i list all the object privileges from n user?
for example:
I granted to USER1:
GRANT ALL on my_table1 to USER1;
GRANT ALL on my_table2 to USER1;
GRANT ALL on my_tableN to USER1;
How can i list all privileges from this user over all tables?
I don't know if this is possible, but i wanna to list:
USER1 ----- UPDATE, INSERT, DELETE ON my_table1
USER1 ----- UPDATE, INSERT, DELETE ON my_table2
USER1 ----- UPDATE, INSERT, DELETE ON my_tableN
Is this possible?
SQL & PL/SQL :: Check If Object Exist Or Not While Creating Procedure?
Mar 5, 2012I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().
for ex:
-------
CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
FOR R IN C1
LOOP
[code].....
My question is while creating procedure i want to check if the object exist in database or not.
If EXIST
NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE
IF NOT EXIST
I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.
The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.
Check Index Used In Oracle Select Query?
Mar 21, 2013I have partitioned an oracle table and created Local Index for the Partitioned table .
Now i want to make sure that the Local index is being used when i perform select query on the Table partition.
How do i confirm that ? can i check the explain plan generted for the select query to confirm local index is being used ?
Methods To Check Index - How To Control Internal Integrity
Jun 28, 2012How to control internal integrity (lack of self-reference keys in an index) without using regular validation and rebuilding ?
I'm using 10.2.0.5.
PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full
Oct 5, 2013Let'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
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I 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 RelatedPerformance Tuning :: Force Index If Table Not Using Index?
Aug 9, 2013How to force an index if the table not using the index?
View 10 Replies View RelatedHow To Find Tablespace Growth On Daily Basis
Feb 15, 2013How can we find a tablespace growth on a daily basis for a 11.2.0.2 database?
OS: AIX 6.1
Partition Non-partitioned Table To Partitioned Table Date Wise?
Nov 1, 2012what is the best way to partitioned a non-partitioned table to partitioned table date wise? I have data from last four year?
View 4 Replies View RelatedServer Administration :: Total Data Growth Compared With Last Year
Sep 15, 2011Is there any way to tell the total data growth increase of some tables when compared with last year.
View 4 Replies View RelatedData Guard :: Clarification Regarding File Growth In Physical Standby
Feb 3, 2011I am in need of a clarification regarding the file size growth in Physical Standby Database.Say we have 150 GB size of LIVE DB and we have created the same as in STDBY(150 GB).Will there be a growth in STDBY DB datafiles with respect to the LIVE DBOR only the logs are applied currently to the STDBY and the changes will only be reflecting when there is a switchover or failure scenario??
View 4 Replies View RelatedPL/SQL :: Error In Modifying Object Value In Triggers Defined On Object Tables
Sep 26, 2012While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
TimesTen In-Memory :: OracleDataReader - Object Reference Not Set To Instance Of Object
Jan 25, 2013I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
Server Utilities :: Estimate Tablespace Growth While Loading Data Using Sqlldr?
Jun 1, 2011We load large amount of data into multiple tables using sqlldr. Amount of data that we need to load varies according to the situation. We want to estimate the tablespace usage growth due to this data load, so we can verify/extend the tablespaces before the data load. Though, setting to autoextend will work in this case, We want to avoid extending the tablespace during sqlldr executing due to performance.
Our initial attempt was to note the tablespace size before and after executing the sqlldr and use the delta. But this delta was not consistent in different environments for the same amount of data. Different environments mean different oracle servers, different existing sizes of tablespaces, One data file Vs multiple data files etc.
How do we reliably estimate how much tablespace we need for the given amount of data?
Windows :: Prepare Tablespace Growth Report For 106 DB - Reduce Manual Activity
Jun 29, 2011I need tablespace name,tablespace total size and used size of every tablespace for my 106 databases. bcz I am planning to prepare tablespace growth report for my 106 dbs. If I have the 2 months data from now in xl sheet, then I can predict the growth rate of every tablespace accurate almost.
it is not a easy task to fill the space detail manually in xl sheet everyday.Hence I need some mechanism to do this activity automatically.
I know we can collect this detail in a csv file every day while running the script. but is there any mechanism to store these detail in a particular xl sheet one by one column automatically?
SQL & PL/SQL :: Single Excel File For Emp Table Department Wise In Different Sheets Of Excel
Mar 30, 2012I need to generate a single excel file which each department details in seperate sheets of the excel file, each sheet should ne named with department no (ex :Dept_10, Dept_20,Dept_30) and the main excel should be named as Emp_details.
Header row and total row should be in bold.I have created 3 individual excels thru toad and moved them to single excel and renamed the sheets.
Can we programatically do this thru SQL or PL/SQL, does Oracle provide any build packages for excel.As excel files cannot be uploaded,I am unable to upload the excel file I generated manually.
select to_char(empno) empno,ename,job,mgr,hiredate,
nvl(sal,0) sal,nvl(comm,0) comm,nvl(sal,0) + nvl(comm,0) "Total Income" ,deptno
from emp
where deptno = 10
union all
select 'Total',null,null,null,null,sum(nvl(sal,0)),sum(nvl(comm,0)),sum(nvl(sal,0))+sum(nvl(comm,0)),null
from emp
where deptno = 10;
SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table
Jun 4, 2013I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
Forms :: Put One Check Box To Check All The Check Boxes?
Jul 30, 2011I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies View RelatedFind Dependent Object Details For Any Object
Dec 24, 2012Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...
View 5 Replies View RelatedSQL & PL/SQL :: Insert Data Into Object Relational Table
Dec 26, 2010I had my previous table model as below.
CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,
[Code]...
How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.
SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table
Nov 11, 2012I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)
[code].....
DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]
SQL & PL/SQL :: Can Create Table Type Object By Using %ROWTYPE
Apr 20, 2012Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare
2 type table_emp is table of scott.emp%rowtype index by binary_integer;
3 employees table_emp;
4 begin
5 select * bulk collect into employees from scott.emp;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer;
2 /
Warning: Type created with compilation errors
SQL> show errors
Errors for TYPE DBO.TABLE_EMP:
LINE/COL ERROR
-------- ----------------------------------------------------------
1/19 PLS-00355: use of pl/sql table not allowed in this context
0/0 PL/SQL: Compilation unit analysis terminated
SQL>
How can I create global table type object with %rowtype
SQL & PL/SQL :: Inserting Object Type Into Table - Too Many Arguments
Mar 6, 2012Ive just been trying to create an add member procedure to retrieve an object that is in another table. But before i get the ref to bring the toy across i wanted to make sure i could insert an object into the new table. I keep getting theres too many arguments, the lack of sqlplus code, the spool function isnt working.
DROP TABLE completed_toys;
/
CREATE OR REPLACE TYPE comp_toyobj AS OBJECT (
completed_id NUMBER(5),
request_id REF toy_obj,
[Code].....
Here is the error on the procedure call
Error report:
ORA-06550: line 4, column 9:
PLS-00306: wrong number or types of arguments in call to 'ADD_COMPLETED'
ORA-06550: line 4, column 9:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
SQL & PL/SQL :: Pass Object (or) Table As Argument To Remote Procedure?
Jul 18, 2012Does it possible to pass object (or) table as an argument to a remote procedure?
View 2 Replies View RelatedHow To Check A Table If Exists
Oct 12, 2005I have a script like this:
------------------------------------------------------------
DROP TABLE CON_TEST CASCADE CONSTRAINTS ;
CREATE TABLE CON_TEST (
IDI NUMBER(10,0),
USERID VARCHAR2(10),
PWD VARCHAR2(10),
NOTE VARCHAR(100)
)
----------------------
But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?
By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)
SQL & PL/SQL :: Check Every Table For Data?
Oct 30, 2012I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:
SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)
Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:
SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables
but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.