SQL & PL/SQL :: Proc Create Partition With Variable Name / Value / Tablespace
Aug 18, 2010
developing a procedure to create a partition every month passing in parms for the name and values, and also need to determine the appropriate tablespace that has adequate space available. Working within the constraints of the environment. I would like to pass in the table name as well. This code gives the general idea..
CREATE OR REPLACE PROCEDURE FS.CREATE_PART_test
declare
v_monyy varchar2;
v_yyyymm varchar2;
v_tblspc dba_data_files.tablespace_name%type;
[code]...
View 2 Replies
ADVERTISEMENT
Oct 30, 2012
I have a table TABLE1 which has following columns
proc_name
table_name
insert
update
delete.
The table contains a proc name, all tables used in that proc,operation is done on the tables. like sample data.
proc_name tab_name insert update del select
----------------------------------------------------------------------------------
Proc1 Table1 Y Y N Y
Proc1 Table2 y Y Y Y
I want to update this table throu running a generic procedure.Can it be possible to craete a procedure which parses through all procs of database and get above info and update TABLE1??
View 1 Replies
View Related
Mar 17, 2011
I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).
View 3 Replies
View Related
Apr 3, 2013
At present we have a non partitioned table.
Can we apply redefinition and create range partition and hash sub partition on it?
View 2 Replies
View Related
Jan 4, 2013
I have a range partitioned table with one lob column. Each partion is on a separate tablespace except two partitions which are on same tablespace. Now I want to move a partition from one tablespace to another tablespace along with lob data. By using a simple alter table move partition will also move the lob data or there is some special procedure to adopt.
View 9 Replies
View Related
Aug 17, 2012
i want to create tablespace in raw partion on windows.I have added a hard disk and added extended partition to it. Then created 4 logical partitions of 256 MB with diskpart.exe.I have assigned a drive letter to one of them as X:
However, I am having trouble in creating tablespace on it. I referred to [URL]... I get following error,
C:UsersAdministrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 17 15:32:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing opti
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 100;
create tablespace exampletb datafile '\.X:accounting_1' size 100
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 502;
create tablespace exampletb datafile '\.X:accounting_1' size 502
[code]...
View 1 Replies
View Related
Jun 19, 2012
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
View 1 Replies
View Related
May 3, 2012
I have to create partition on 1 table where i have date field and id.
i want to create range parttion for both the column.
View 2 Replies
View Related
Feb 1, 2012
I plan to create a copy of original partition table as below in source database DB1.
create table tblname as select * from sourcetab;
The purpose i am creating copy of original tables is i want to have same partition tables with data in target database DB2 (where the partition feature is diabled).
So i am creating copy of original partition tables ,will then create dump of duplicate tables using export method and then load the dump into target database using import.
Both source and target database are in different servers. Partition is disabled in target database. Partition table size is 2GB and it has 5 partitions P1,P2,P3,P4,P5 .Each partition is of size 100 MB. Count of partition table is 15805412
1.My question is would there be any problem while loading dump into target database (where the partition feature is not enabled)?
2.Is it feasible to create a copy of partition tables using below statements in same database ?
create table tblname as select * from sourcetab;
View 39 Replies
View Related
Aug 13, 2010
I need to create partition on the non partition table without dropping a table.
Range partition on stage tables and hash partition on fact tables.
View 4 Replies
View Related
Dec 3, 2012
I have an IOT table with partitioned on list. I have p1,p2 and p3 partitions. Now I would like to create a bitmap index on partition key.
I gave ALTER TABLE .. MOVE MAPPING TABLE;
But getting the below error,
ORA-28660: Partitioned Index-Organized table may not be Moved as a whole.
View 1 Replies
View Related
Oct 18, 2012
I want to ask whether I can create range partition of like 10 minutes? Like I have data of 1 hour and I want to make 10 partition?
View 12 Replies
View Related
Feb 20, 2012
Is there any rule in documentation, when create partition index? For tables, it is said to partition when table is greater than 2GB, but what about index? WHat size it should have to partition?
View 1 Replies
View Related
May 16, 2012
how to create materialized view on partition table?
View 1 Replies
View Related
Jan 21, 2012
This is the first time I've got a job in Windows platform, I've worked as DBA with Unix/Linux still now.
I received one command: Install Oracle 11gR2 on Windows 7 Enterprise, so that, I plan to create one Partition (really, not mounted, this is Unallocated partition by Partition Magic 3rd software) which will contain datafile in it.
And, when I install 11gR2 Grid, the step ASM creation did not recognize the Unallocated Partition I created.
View 3 Replies
View Related
Oct 27, 2011
Is there a script that it create partitions by auto? who can share it?
View 1 Replies
View Related
Aug 21, 2013
I want to create a database named Influx as well as tablespace influx.for this i login in sys account.now i am trying to run the following command:
CREATE DATABASE INFLUX LOGFILE GROUP 1 ('G:appdhirenderoradataINFLUX
edo01.log') SIZE 100M, GROUP 2 ('G:appdhirenderoradataINFLUX
edo02.log') SIZE 100M, GROUP 3 ('G:appdhirenderoradataINFLUX
edo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 DEFAULT TABLESPACE users DATAFILE 'G:appdhirenderoradataINFLUXINFLUX01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED FOLLWOING ERROR IS COMMING:ora-01501 datbase fialed.ora-01100
database already mount .
View 3 Replies
View Related
Sep 2, 2013
I wish to create a table with week-number as suffix _36 or _30
CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);
$WEEKNUMBER is this statement:
SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect
How to do that with SQL?
View 8 Replies
View Related
Aug 5, 2010
I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.
View 10 Replies
View Related
Dec 14, 2012
We have a table which is already partitioned by list and now we would like to add a subpartition.
Create table Item_mst
(
ccn varchar2(10),
Flag varchar2(1),
Item varchar2(10),
status varchar2(1)
)
[Code]..
Now I could like to alter the above table for adding a new subpartition on each partition on status.
ALTER TABLE Item_mst
MODIFY PARTITION Item_mst_Test
ADD SUBPARTITION Item_mst_Test_A VALUES ('A');
Above alter gives ORA-14253: table is not partitioned by composite range method.
However dropping and recreating the table with subpartitions is working fine.
But Dropping and recreating the table in production is very cumbersome as it has huge data and many indices.
View 3 Replies
View Related
May 17, 2012
I got just confused while looking at the below two create table statements:
CREATE TABLE Test (
TestID integer not null,
Name varchar2(20) not null )
PARTITION BY LIST (TestID)
(
PARTITION testPart1 VALUES (1) TABLESPACE tbspc1,
PARTITION testPart2 VALUES (2) TABLESPACE tbspc2@RemoteServer);
and
CREATE TABLE Test (
TestID integer not null,
Name varchar2(20) not null )
tablespace tbspc1
PARTITION BY LIST (TestID)
(
PARTITION testPart1 VALUES (1) TABLESPACE tbspc1,
PARTITION testPart2 VALUES (2) TABLESPACE tbspc2@RemoteServer);
View 11 Replies
View Related
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
Nov 5, 2012
Is it possible to create Dictionary Tablespace creation in 10g database?
View 5 Replies
View Related
Nov 11, 2012
Just explaining what I am trying to achieve:
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is
[Code].....
View 5 Replies
View Related
May 28, 2010
i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.
View 6 Replies
View Related
Jun 12, 2012
ORA-01658: unable to create INITIAL extent for segment in tablespace
I am getting above error when a batch job is running.Its insterting data in tables. I checked free space i dont know y its not using avalable space.
Database Version: 11202
select sum(bytes)/1024/1024/1024, sum(maxbytes)/1024/1024/1024 from dba_data_files where tablespace_name='test_data';
sum(bytes) sum(maxbytes)
---------------- -------------
60 0
select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='test_data';
sum(bytes/1024/1024/1024)
----------------------
50
View 2 Replies
View Related
Aug 19, 2011
How we can create dictionary managed temporary tablespace in Oracle 10g.
SQL> create temporary tablespace temp
2 tempfile '+GWDAAS04_TEMP_DG01/pimsb_gw/tempfile/temp01.dbf' size 500m
3 extent management dictionary;
create temporary tablespace temp
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
View 7 Replies
View Related
Aug 12, 2011
i have been trying to create a user with full access on default Tablespace so that i can access all the objects without any restrictions for the new user.
View 7 Replies
View Related
Aug 11, 2011
I have executed the below statement to move a bulk of table from source table space-SHOP3_DATA to destination tablespace -LINUX_CATA. Before executing the statement the source table space was having 85% full message . While executing the statement this is giving the error for the source tablespace means the the tablespace is getting consumed in the source TS. Currently no-one is using the tablespace SHOP3_DATA' . If I add datafile to the SHOP3_DATA the problem may be solved but why the space is getting consumed in the source. Now the tablespace SHOP3_DATA is 95% full
SQL> alter table LINUX_CATA.TST_FOLDERS move tablespace LINUX_CATA;
Table altered.
SQL> alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA;
alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA
[code]....
View 10 Replies
View Related
Apr 4, 2013
while importing data i got this error in my log file.and i cannot import my data successfuly
in my log file error i found like this
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:
i can import my data using imp utility using this syntax
C:UsersAdministrator>imp tiger/****@tcs file=E:DUMP s.
dmp log=E:DUMP s.log fromuser=tiger121 touser=tiger statistics=none
this my user tiger default tablespace its uses and its a auto extend on and locally managed tablespace,and i have enough space on my drive also.
View 21 Replies
View Related
