SQL & PL/SQL :: Space Between Output Columns
Feb 15, 2012How to add space between columns in the dbms output statement.I tried to do so in the following way which i attached.
View 8 Replies
ADVERTISEMENT
SQL & PL/SQL :: Sum And Create New Columns For Output?
Dec 4, 2011How do I modify SQL to sum and also add 2 columns to hold calculation results?
In functional terms, we need to sum two pay periods' retirement deduction amounts for a BDCA deduction code R03 to arrive at the monthly total for those deduction amounts.
In other words, to sum payroll deduction data for 2 pay periods PAYNO = 21 & 22 for (EMPLOYEE_AMT and EMPLOYER_AMT) and have the totals in new columns named EENOVR03 and ERNOVR03. Column names representing EE for employee, ER for employer, NOV for November, and R03 for deduction code.
Here's the basic SQL without calculations. I need to modify SQL to produce both EENOVR03 and ERNOVR03.
select SPRIDEN.SPRIDEN_LAST_NAME "LastName",
SPRIDEN.SPRIDEN_FIRST_NAME "FirstName",
SPRIDEN.SPRIDEN_MI "MiddleName",
[Code]...
Reports & Discoverer :: How To Skip Columns With Space Based On Parameters
Jan 10, 2011I have Two Parameters like 'YES' & 'NO'
when parameter is YES, column having values
when Parameter is NO, column having no values
In this scenario, how can i skip the column with space when parameter is NO because the column exist in middle of columns in report and Present with space when parameter is YES.
SQL & PL/SQL :: Formatting Output Into Multiple Columns?
Apr 16, 2013as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.
We must show the output as evidence of doing the work.
Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!
BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/
SQL & PL/SQL :: UNION ALL Output In Multiple Columns?
Nov 26, 2012Following is my table structure:
14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;
ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C
now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,
ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60
Server Administration :: Change Space Allocation For Character Columns In Database
Oct 24, 2012I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;
System altered.I bounced the instance just to make sure
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....
And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:
SQL> drop table check_char;
Table dropped.
SQL> create table check_char (some_name varchar2(10));
Table created.
SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';
C
-
B
SQL>
What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?
SQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters
Jul 1, 2013I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
Any Way To Free Space From Sysaux Table Space?
Oct 3, 2012Just now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output
Jul 1, 2013DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows
Sep 25, 2013Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);
[Code]....
My expected output should be like below:
Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40
how I can achieve the expected output using a single sql query
Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns
Jun 16, 2010I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies View RelatedPerformance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?
Apr 4, 2011I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query
Jan 21, 2011I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?
Jul 12, 2012I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
SQL & PL/SQL :: How To Remove Space
Oct 20, 2010query string in such like that index.php?name=tejaspatel
i have table in record is available below
select * from emp where name = :name // it is query string parameter
emp
name
tejas patel
then how to match this record ?
SQL & PL/SQL :: Avoiding Space
Jan 5, 2012I have table like below.
Name Gender
----- ------
f1 Female
f2 Female
m1 Male
m2 Male
f3 Female
m3 Male
m4 Male
but I need the output like below
Male Female
----- ------
m1 f1
m2 f2
m3 f3
m4
I have tried to get the above O/p but getting along with null values.
SQL> ed
Wrote file afiedt.buf
1 select case when gender='male' then name end male,
2 case when gender='female' then name end female
3* from details s1
4 /
MALE FEMALE
--------------- ---------------
f1
f2
m1
m2
f3
m3
m4
7 rows selected.
how the get the above o/p with out null values.
XE :: How To Reduce Space Of DBF
Apr 14, 2013i have data about 3 gb but my files in e:oraclexeapporacleoradataXE has grown to 16 gb
1)my e: drive has less space ,pls tel how can i fee some space and give it back to OS
2)is there any other place where i can free up some space from oracle and give it back to OS.
-----------------------
windows (2008)
---
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production (2008)
NLSRTL Version 11.2.0.2.0 - Production
[code]....
PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows
Aug 22, 2012what are the collections available in Oracle Plsql, what are concepts of collection.
How to Transpose a Table from rows to columns or columns into rows.
DDL and DML concepts.
What is the concepts of statistics in Oracle Plsql.
How To Shrink Table Space
Jun 11, 2013I am trying to Shrink Table space using the following SQl. As we are dropping large datasets. Later i am trying to shrink the table space
Alter Tablespace table_name Shrink Space Keep 20M;
Is this the best way to do in oracle 11G
Split A String Up If It Has Space?
Jun 19, 2008i have a column called name in a table. now what iwould like to do is to check if it has two parts "paulh some" and then output the second part!
SELECT LTRIM(name,' '), length(name) length
FROM list
WHERE INSTR(name,' ') = 1;
but that doesnt work.. the fucntion is NOT checking for the space! if i use another character (a or b etc) it works..
Table Space Fragmentation
May 4, 2011I got the error ORA-01653: unable to extend table <OWNER.TABLE_NAME> by <BYTE> in tablespace <TABLESPACE> in my production database. But I could see 4GB of free space available in the tablespace. But this was resolved after increasing the Tablespace size by 1 more GB. So I wanted to know how I can I reclaim the 4GB space ?
While searching in internet I got few tips like there will be a fragmentation in the tablespace, the free space available in the tablespace may not be a continuous block. To avoid this we need to reorganize the tables using ALTER TABLE <TABLE_NAME> MOVE <TABLESPACE>; command.
But in my tablespace there are huge number of tables exists I cannot do reorganization of all the tables. So I need to know how to identify particularly what are the tables has more fragmentation? so that I can go for reorganizing those tables only.
My Database version in 9.2.0.7
Tablespaces are Locally Managed
Query For Blob Space
Oct 26, 2011explain the difference in numbers between the queries? I am acutally more concerned about the ETM_XML clob since the descrepancy appears to be bigger.
SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = 'ETM_RAW_XML';
ETM_RAW_XML
IFD_XML
SYS_LOB0007260522C00012$$ 87870668800
ETM_RAW_XML
ETM_XML
SYS_LOB0007260522C00011$$ 125199974400
SQL> SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
83848300852 ~ 78.0898154266179 GB
SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
61907953222 ~ 57.6562743838876 GB
Table Space Discrepancy
Apr 17, 2012I have a tablespace of size 512 GB. On the basis of tables and indexes created on it, the space consumed should be 319GB but when I am retrieving the free space size , I am getting only 124GB of free space. That means around 70GB of space is missing.
View 1 Replies View RelatedSQL & PL/SQL :: How Much Space Is Used By The Materialized View
Oct 20, 2010How can we find how much space is used by the materialized view.
View 1 Replies View RelatedTarget Name Space Response Is Low?
Feb 27, 2013We are using oracle 10.2.0.3.0 version on HP-UX B.11.23 U ia64.
We are facing a lot of delay in TNS ping reply. It is taking 8000+ msec.
$ tnsping XXXXXX 20
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 27-FEB-2013 12:37:15
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
$ORACLE_HOME/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = xxxxxxx)))
OK (1250 msec)
OK (1150 msec)
OK (860 msec)
OK (860 msec)
OK (790 msec)
OK (810 msec)
OK (790 msec)
OK (570 msec)
OK (650 msec)
OK (630 msec)
OK (790 msec)
OK (1240 msec)
OK (1090 msec)
OK (1030 msec)
OK (870 msec)
OK (690 msec)
OK (740 msec)
OK (650 msec)
OK (490 msec)
OK (560 msec)
$
PL/SQL :: Representing Double Space?
Aug 20, 2012i'm trying to read a text file into an oracel table. I'm having issues with this statement FIELDS TERMINATED BY ' ' fields are separated by 2 spaces. How ever Oracle is not recognizing ' ' or " " as double space. It is treating as one space thus shifting all coulmns.
How can I represent double space to Oracle ?
Maximum TEMP Space Ever Used
Oct 2, 2012how to check the maximum space ever used for TEMP. I want to know it because I need to resize the TEMP and I want to know how small it can be. As I can see from a documentation hURL....max_size is max number of extens ever used in a segment
I could multiply max_size by extent_size and it would give me the max size of temp ever used
SQL> select segment_file, extent_size, max_size from v$sort_segment;
SEGMENT_FILE EXTENT_SIZE MAX_SIZE
0 128 23625
0 128 753
How To Check Fragmented Space
Jul 14, 2012huge deletion happened from app side. How to check fragmented space ?
View 8 Replies View RelatedHow To Monitor Space Of Database In RAC And NON-RAC
Nov 17, 2012I need to monitor the Database daily, so i need to check whether the size of the DB is increasing @ a slow rate or rather than that. I need to do it in RAC & NON-RAC.
View 3 Replies View RelatedSQL & PL/SQL :: Empty Space Creation
May 29, 2013While i am inserting data into a specific table that table assigns empty spaces.
For example:
CREATE TABLE DEALER_MST
("DEALER_CODE" CHAR(10) NOT NULL ENABLE,
"REGION_CODE" CHAR(3),
"SUB_REGION_CODE" CHAR(3),
"DEALER_NAME" VARCHAR2(80),
[Code]....
But it is giving result as DS with 8 spaces. It assigns spaces in db automatically while fetching.
